Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can DLookup() look up multiple fields?
    tblProperty contains: PropertyID, Block, Floor and Flat. These 4 char fields compose a compound key.
    Before Update event, I would like to ensure the newly added record won't be a duplicate.
    Armstrong

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    DLookup can look up only one field at a time, but you can specify multiple conditions. You could use DCount instead:

    Code:
    If DCount("*", "NameOfTable", "PropertyID=" & Chr(34) & Me.PropertyID & Chr(34) & _
    	" And Block=" & Chr(34) & Me.Block & Chr(34) & _
    	" And Floor=" & Chr(34) & Me.Floor & Chr(34) & _
    	" And Flat=" & Chr(34) & Me.Flat & Chr(34)) > 0 Then
      MsgBox "This combination of PropertyID, Block, Floor and Flat already occurs!", vbExclamation
      Cancel = True
    End If
    The Chr(34) in the code is the double quote character " that is needed around text values.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780628' date='19-Jun-2009 19:05']DLookup can look up only one field at a time, but you can specify multiple conditions. You could use DCount instead:

    Code:
    If DCount("*", "NameOfTable", "PropertyID=" & Chr(34) & Me.PropertyID & Chr(34) & _
    	 " And Block=" & Chr(34) & Me.Block & Chr(34) & _
    	 " And Floor=" & Chr(34) & Me.Floor & Chr(34) & _
    	 " And Flat=" & Chr(34) & Me.Flat & Chr(34)) > 0 Then
       MsgBox "This combination of PropertyID, Block, Floor and Flat already occurs!", vbExclamation
       Cancel = True
     End If
    The Chr(34) in the code is the double quote character " that is needed around text values.[/quote]
    I'm amazed that you can reply my post less than 5 minutes! It took me more than 20 minutes to completely understand your Dcount().
    You're not only showing the mechanics of using Dcount() but also the trick of using it.
    Thanks a lot.
    Armstrong

  4. #4
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HansV,

    Taking a long time to experiment with DCount(), eventually I succeeded the coding in macro to check the duplicate.
    DCount("*", "tbl物業單位","BldgID = ctl_BldgID AND 座 = ctl_座 AND 層 = ctl_層 AND 室 = ctl_室") > 0
    The above macro is attached to the After Lost Focus event of the 4th text box (ctl_室 ).
    My macro doesn't have the Chr(34) things.
    Thank you for showing me DCount().

    Armstrong

Posting Permissions

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