DLookup checkbox 1

[ssatech]

Hi,

Could I please have some advice on how to approach this? I have a form called Delivery with a field named "SpecNo" and "Haz" based on a table called Delivery. I have another table called GCAS with the following fields "SPEC" and "HAZ". Both fields "Haz" in table Delivery and "HAZ" in table GCAS are checkbox field. On the BeforeUpdate event on the field "SpecNo" I have the following code below which I am trying to run. I want to the code to look up in the GCAS table the matching data for the field SpecNo with the matching data field SPEC in the table GCAS. If the checkbox field "HAZ" in GCAS table is TRUE, Than I want the checkbox "Haz" in the table Delivery to be also TRUE.



Private Sub SpecNo_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_SpecNo_BeforeUpdate
Dim H
Dim Last As String

Last = "SPEC = " & SpecNo & ""

H = DLookup("HAZ", "GCAS", Last)


If IsNull(H) Then
Me.Haz = False
Else
Me.Haz = True
MsgBox "Hazardous Material match found for this Spec Code.", vbInformation, "HAZARDOUS"
End If

Exit_SpecNo_BeforeUpdate:
Exit Sub

Err_SpecNo_BeforeUpdate:
MsgBox Err.Description
Resume Exit_SpecNo_BeforeUpdate
End Sub

Sorry I can't seem to get my head around the DLookUp function or perhaps there is a simpler way of doing this
Any assistance would be greatly appreciated.

 

[MajP]

I did not look in detail, but your where clause is incorrect
If SPEC is a text field then surround with single quotes
Last = "SPEC = '" & SpecNo & "'"
this should resolve to
SPEC = 'ABC123'

Or if it is a number then
Last = "SPEC = " & SpecNo
resolves to
SPEC = 123

 

[TheAceMan1]

How are ya ssatech . . .

[blue] ... I want to the code to look up in the GCAS table the matching data for the field SpecNo with the matching data field SPEC in the table GCAS. If the checkbox field "HAZ" in GCAS table is TRUE, Than I want the checkbox "Haz" in the table Delivery to be also TRUE.[/blue]

However in your code you have:

[blue]   If IsNull(H) Then 
      Me.Haz = False 
   Else[/blue]

Here your forcing [blue]SpecNo[/blue] to be [blue]False[/blue] if DLookUp doesn't find a match. Are you sure this is what you wanted?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ssatech]

MajP,

This one "Last = "SPEC = " & SpecNo" did the job. Many thanks for help...

It's always a pleasure to have you and the AceMan provide direction.
Thanks to the both of you...

 

[MajP]

Also it is more efficient to do a dcount instead of a dlookup. It will return 0, or greater if found.

  H = DLookup("*", "GCAS", Last) 
  me.Haz = (H = 0)
  If H > 0 then MsgBox "Hazardous Material match found for Spec Code: " & SpecNo, vbInformation, "HAZARDOUS"