Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup code producing incorrect results (2000 all

    A form, frmIndividual, is used to enter individuals who are members from any one of 4 congregations. The congregations are identified by a CongregationID #1 through #4.

    One of the entries is a RollNbr (roll number) and there cannot be 2 identical roll numbers in, for example, CongregationID #1, but each of the CongregationIDs could contain the same numbers. For example, each CongregationID could have a RollNbr 1, and a RollNbr 2, etc.

    The form is based on a table, tblIndividual, which includes fields RollNbr, CongregationID, FirstName, LastName

    Part of the code behind a "Save" button on frmIndividual is shown below. Its purpose is to check and make sure that there are no identical roll numbers entered in the same congregation.

    <code>Dim varTemp As Variant
    Dim varTemp2 As String
    Dim varTemp3 As String
    Dim varTemp4 As String
    varTemp = DLookup("[RollNbr]", "tblIndividual", "[RollNbr] = Forms!frmIndividual!RollNbr And [CongregationID] = Forms!frmIndividual!CongregationID And [IndividualID] <> Forms!frmIndividual!IndividualID")
    varTemp2 = Nz(DLookup("[FirstName]", "tblIndividual", "[RollNbr] = Forms!frmIndividual!RollNbr"))
    varTemp3 = Nz(DLookup("[LastName]", "tblIndividual", "[RollNbr] = Forms!frmIndividual!RollNbr"))

    varTemp4 = varTemp2 + " " + varTemp3

    If Not IsNull(varTemp) Then
    Call MsgBox("The Roll # entered is already assigned to" _
    & vbCrLf & varTemp4 & " ... a member of the " _
    & vbCrLf & cboCongregation.Column(1) & " congregation." _
    & vbCrLf _
    & vbCrLf & "Please change either the Roll # or the congregation." _
    , vbExclamation, "Roll # check")
    Me.RollNbr.SetFocus
    Me.RollNbr.Undo

    DoCmd.RunCommand acCmdUndo

    End If</code>

    What is happening is the code is not always producing correct results.

    For example, one entry is Howard Baragar, RollNbr 1, CongregationID 2. When the user goes to enter Beatrice Brown, RollNbr 1, CongregationID 4, the variable varTemp in the DLookup code shows up as positive and faults out.

    Can anyone spot what is wrong with the code that is making this happen?

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup code producing incorrect results (2000 all

    You could try

    Dim intCount As Integer
    intCount = DCount("*", "tblIndividual", "[RollNbr] = Forms!frmIndividual!RollNbr And [CongregationID] = Forms!frmIndividual!CongregationID And [IndividualID] <> Forms!frmIndividual!IndividualID")
    If intCount > 0 Then
    ...

    If that doesn't work, you could use

    varTemp2 = DLookup("[FirstName]", "tblIndividual", "[RollNbr] = Forms!frmIndividual!RollNbr And [CongregationID] = Forms!frmIndividual!CongregationID And [IndividualID] <> Forms!frmIndividual!IndividualID")
    varTemp3 = DLookup("[LastName]", "tblIndividual", "[RollNbr] = Forms!frmIndividual!RollNbr And [CongregationID] = Forms!frmIndividual!CongregationID And [IndividualID] <> Forms!frmIndividual!IndividualID")

    and see which name comes out (I added the same conditions as for varTemp)

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DLookup code producing incorrect results (2000

    These lines:

    varTemp = DLookup("[RollNbr]", "tblIndividual", "[RollNbr] = Forms!frmIndividual!RollNbr And [CongregationID] = Forms!frmIndividual!CongregationID And [IndividualID] <> Forms!frmIndividual!IndividualID")
    varTemp2 = Nz(DLookup("[FirstName]", "tblIndividual", "[RollNbr] = Forms!frmIndividual!RollNbr"))
    varTemp3 = Nz(DLookup("[LastName]", "tblIndividual", "[RollNbr] = Forms!frmIndividual!RollNbr"))

    need to remove the reference to the form from within the double quotes.

    varTemp = DLookup("[RollNbr]", "tblIndividual", "[RollNbr] = " & Forms!frmIndividual!RollNbr & " And [CongregationID] = " & Forms!frmIndividual!CongregationID & " And [IndividualID] <> " & Forms!frmIndividual!IndividualID )
    varTemp2 = Nz(DLookup("[FirstName]", "tblIndividual", "[RollNbr] = " & Forms!frmIndividual!RollNbr ))
    varTemp3 = Nz(DLookup("[LastName]", "tblIndividual", "[RollNbr] = " & Forms!frmIndividual!RollNbr ))

    This way to reference to the form is resolved by the code, and the value of the control is passed to the DLookup function.
    Regards
    John



  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup code producing incorrect results (2000

    John

    Making the changes you suggested produces the same results. Actually, according to the Microsoft website, formulating the DLookup code either the way I had it originally, or the way to which you suggested changing it, should work.

    Tom

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup code producing incorrect results (2000

    Hans
    The DCount method seems to work. And in order to produce the final result I need in a message box, I have to use your formulation of the DLookup lines for varTemp2 and varTemp3...with the addition of Nz to the start of each code line. Without the Nz, I get an "invalid use of Null" error.

    Thanks. I think things are resolved now.

    Tom

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup code producing incorrect results (2000 all

    Rather than using the dlookup code wouldn't it work just as well to create a unique index with CongregationID and RollNbr?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup code producing incorrect results (2000

    Judy
    Thanks. Creating a unique index probably would work just as well. Being self-taught, I generally tend to do things the hard way.

    I'll have a look at that. For the moment I have things working and since I have spent so much time on these 3 lines of code I am loathe to tinker further.

    Tom

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DLookup code producing incorrect results (2000

    Tom

    You are right. I am sorry.
    I have got into the habit of doing it that way, and I forgot that you don't have to.

    However I still prefer to do it that way as I think it is easier to debug.

    I would usually add another variable
    Dim strCriteria as string
    strCriteria = "([RollNbr] = " & Forms!frmIndividual!RollNbr & ") And ([CongregationID] = " & Forms!frmIndividual!CongregationID & ") And ([IndividualID] <> " & Forms!frmIndividual!IndividualID & ")"

    grouping each condition into a separate bracket to make sure there is no confusion.

    Then

    varTemp = DLookup("[RollNbr]", "tblIndividual", strCriteria)

    This allows me to put in debug.print strcriteria or msgbox strcriteria if I am getting strange results.

    John
    Regards
    John



  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DLookup code producing incorrect results (2000

    A unique index on both fields will prevent the duplicate being created, but it will not provide a user friendly error message.
    Regards
    John



  10. #10
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup code producing incorrect results (2000

    John
    Agreed. The problem was that I just couldn't get either my original DLookup code or your re-formulation of the code to work properly. So I switched (see Hans' approach).

    I spent a few hours trying to get the original method to work correctly, to no avail. The weird thing is that it worked okay when I tested things but, as we all know, where the rubber hits the road is when the end user starts to enter data, and somewhere down the road an unforeseen problem rears its ugly head.

    Anyway, John, thanks for trying.

    I do have things working now, so am leaving it alone for the moment.

    Tom

Posting Permissions

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