Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2010
    Posts
    10

    Unanswered: Double Criteria for a dlookup function

    Hi,
    I have a form on which I intend to capture farmer details. The form is attached to table FarmerReg, which is set to allow duplicate FarmerNos because the same farmer can register for more than one season using the same number. On my form, I want MS Access to restrict the same FarmerNo from being entered twice in one season. Below is the code from which I'm able to block duplicate FarmerNos minus considering the season. My problem is I can't figure out how to ensure that only duplicate FarmerNo entries for a particular season are blocked. Would somebody edit this code for me?

    [Dim icount As Long
    'check to see if this is a duplicate value for a parituclar season before updating
    icount = Nz(DLookup("FarmerN", "FarmerReg", "FarmerN=" & Me.FarmerN), 0)
    If icount <> 0 Then
    ' this item has already been entered for this Season
    MsgBox "Duplicate Farmer Numbers are not allowed!", vbExclamation
    Cancel = True
    End If/]
    Last edited by Papa G; 04-02-10 at 05:51. Reason: corrected end of code tag to include End If

  2. #2
    Join Date
    Mar 2010
    Posts
    10
    Before you ask, the season column datatype is text with values ranging from 2009 - 2010, 2010 - 2011 etc while FarmerNo is just a Number field.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How many tables do you have? What do they look like?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Mar 2010
    Posts
    10
    I have 4 tables storing more details for the data in my main table FarmerReg i.e. tbl_FieldOfficerDetails, tbl_AreaDetails, tbl_Products and tbl_Farmers. Below are the fields in my main table FarmerReg.

    The ID is my Primary and is an Autonumber field. FOCode is code for Field Officers and it is the one that links this table to the rest of the tables mentioned above Hope this is what you needed?

    [
    ID Int
    FarmerN float
    Season nvarchar(50)
    FarmerNRC# nvarchar(50)
    FOCode nvarchar(5)
    HaEstimate float
    YieldEstimate float
    CreationDate datetime
    CreatedBy nvarchar(50)
    ProductCode nvarchar(3)
    PaymentType nvarchar(50)
    /]
    Last edited by Papa G; 04-02-10 at 11:56.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That helps a bit.

    What is the plain english description of the process you're trying to model?

    *Keep in mind that you have to start from the beginning. We have no idea what "register" means in this context, because we have no context...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2010
    Posts
    10
    We register farmers on a yearly basis. Our system entails that both new and old farmers register with us before we can give them inputs for the coming season. Upon first registration, I want the basic farmer details i.e. National ID (NRCNo) being the Primary Key, Farmer FullNames, and stuff to be entered in a separate table (tbl_Farmers). This will ensure these basic details are only entered once but can still be used over several different seasons.

    I will then want to enter all the farmers who have registered for the current season i.e. 2010 - 2011 using their unique Farmer Numbers [FarmerN] and these unique numbers are used by the same farmer for as long as they want to grow with us regardless of the change in seasons. With this setup, I can't set [FarmerN] as Primary Key as it would block me from registering the current farmer next season.

    I now need to set [FarmerReg] to allow duplicate Farmer numbers while blocking the duplicate values for a particular season only using the dlookup function. I need the dlookup function to check if farmer number has already been entered for, say, season 2010 - 2011.

    Sorry, I had to make it long so that you can have a complete understanding of what I'm trying to achieve here.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you simply add a condition to the criteria?
    Code:
    "FarmerN=" & Me.FarmerN AND Season = '" & <SoughtSeason> & "'"
    Have a nice day!

  8. #8
    Join Date
    Mar 2010
    Posts
    10
    Below is my revised code. I now get a type mismatch error. It may seem a bother but I never knew anything about dlookup functions upto this morning. Kindly tell me where I went wrong please... Users have to select the season in a combo box so I thought, just writing Me.Season in this expression would make the search for seasons based on the value in combo box, am I wrong?

    [icount = Nz(DLookup("FarmerN", "FarmerReg", "FarmerN=" & Me.FarmerN And "Season='" & Me.Season), 0)/]

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Where is the source of the recordset for your season combo box? If it isn't from a Season table, maybe it should be...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The And should be in the string, a space is missing before "Season=" and the closing single quote character is missing, it should be:
    Code:
    icount = Nz(DLookup("FarmerN", "FarmerReg", "FarmerN=" & Me.FarmerN & "And Season='" & Me.Season & "'"), 0)
    Have a nice day!

  11. #11
    Join Date
    Mar 2010
    Posts
    10
    Quote Originally Posted by Sinndho View Post
    The And should be in the string, a space is missing before "Season=" and the closing single quote character is missing, it should be:
    Code:
    icount = Nz(DLookup("FarmerN", "FarmerReg", "FarmerN=" & Me.FarmerN & "And Season='" & Me.Season & "'"), 0)
    Hi Guys,

    I still have issues getting this code right. I copied it exactly the way Sinndho put it and even created a new table Seasons for my combo box (Courtesy of Teddy) but when I try to run it, it now tells me "You can't go to the specified record".

    For arguments sake, I modified my Season Columns to 'Number datatype' and changed my code as shown below.

    []icount = Nz(DLookup("FarmerN", "FarmerReg", "FarmerN=" & Me.FarmerN & "And Season='" & Me.Season), 0)[/]

    Surprisingly, it works out fine/gives the desired results. Only problem is I don't want the season column to be a Number field, it has to be text datatype.

    Anymore ideas? For the record, I appreciate the level you have taken me to, so far. I now have a better understing of this dlookup function, Thanks alot.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'd be very surprised if your code could work. The expression in the third argument (the criteria):
    Code:
    "FarmerN=" & Me.FarmerN & "And Season='" & Me.Season
    returns:
    Code:
    FarmerN=1And Season='abc
    With a space missing between "=1" and "And" (I metioned it in my previous message but forgot to correct it, sorry!), and with a missing trailing single quote after "'abc".

    You can check with the database in the included file.

    Try again with the code I posted previously, but do not forget to insert a space before "And Season ='".

    What makes you believe that the season column is numeric? It's precisely because it's a Text that you have to enclose the value between single quotes.
    Attached Files Attached Files
    Have a nice day!

  13. #13
    Join Date
    Mar 2010
    Posts
    10
    sorry, that was just a typing error in my post, I've counter-checked it in my database and it is correctly written without the (') for text fields. I've seen your code working in the demo you have attached, let me see how it goes in my database, I'm quite sure though, that this will resolve my problem. Thanks alot.
    Last edited by Papa G; 04-04-10 at 01:09.

  14. #14
    Join Date
    Mar 2010
    Posts
    10
    Thanks Guys,
    I finally got the desired results!!!!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •