DLookup: One to many table relationships (1 Viewer)

[Rick Stanich]

I am having trouble with syntax for a DLookup.

Private Sub tbJobNo_BeforeUpdate(Cancel As Integer)
'Get the Pk value of the Job number in tblJobs
lngID = DLookup("pkJobID", "tblJobs", "txtJobNo='" _
        & Me.tbJobNo & "'")
'Now check if the revision value entered exists for that _
 Job numbers PK value in tblJobs
If IsNull(DLookup("txtJobNo", "tblJobs", "txtJobNo='" _
        & Me.tbJobNo & "' And fkJobID=" & lngID)) Then
    'above: [COLOR=red]fkJobID not exist in tblJobs[/COLOR]
    'No match was found
    Cancel = True
    MsgBox "The revision value entered does not exist for this part number."
Else
    SaveSetting AppName:="GeoMeasure", section:="CMM Data", _
            Key:="tbJobNo", setting:=tbJobNo
End If
End Sub

The foriegn key "fkJobID" is in tblJobParts, I look in tblJobs for Me.tbJobNo, how do I complete the DLookup when the foreign key is in another table?
See attached image for Relationships.

 

[boblarson]

Rick:

I'm still kind of trying to figure out exactly what you are saying for your DLookup part, but I'm wondering if your tables/relationships are proper. I usually find if the relationships seem to form a circle then something is not right.

 

[Rick Stanich]

Better example?
In this code I set a value for tbPartNo based on tblParts.

Private Sub tbPartNo_BeforeUpdate(Cancel As Integer)
'This code revision thanks to Beetle of MS Access Groups)
'Lookup the value in the table using Dlookup.
'Dlookup will return Null if the value is not found so
'we check for that using the IsNull function.
If IsNull(DLookup("txtPartNo", "tblParts", "txtPartNo='" _
        & Me.tbPartNo & "'")) Then
    'No match found. Cancel the update and display a message.
    Cancel = True
    MsgBox "The part number you entered was not found."
Else
    SaveSetting AppName:="GeoMeasure", section:="CMM Data", _
            Key:="tbPartNo", setting:=tbPartNo
End If
End Sub

Then the next textbox value is based on tbPartNo and fkPartID.

Private Sub tbRev_BeforeUpdate(Cancel As Integer)
'This code revision thanks to Beetle of MS Access Groups)
'Get the Pk value of the part number in tbPartNo
lngID = DLookup("pkPartID", "tblParts", "txtPartNo='" _
        & Me.tbPartNo & "'")
'Now check if the revision value entered exists for that
'part numbers PK value in tblPartRev
If IsNull(DLookup("txtRev", "tblPartRev", "txtRev='" _
        & Me.tbRev & "' And [COLOR=magenta]fkPartID[/COLOR]=" & lngID)) Then
    'No match was found
    Cancel = True
    MsgBox "The revision value entered does not exist for this part number."
Else
    SaveSetting AppName:="GeoMeasure", section:="CMM Data", _
            Key:="tbRev", setting:=tbRev
End If
End Sub

Then the next textbox value has to be based on the previous textbox. (My original post).
The Job Number is in tblJobs, the foriegn key is in tblJobParts.
tblJobs is related to tblJobParts.
Any better? Or about the same level of confusion.

On the circular relationship thing, I didnt design the database, I was designed for me.

 

[namliam]

Why do two DLookups, when you can do this in one simple query??

lngID = DLookup("pkPartID", "tblParts", "txtPartNo='" _
        & Me.tbPartNo & "'")
'Now check if the revision value entered exists for that
'part numbers PK value in tblPartRev
If IsNull(DLookup("txtRev", "tblPartRev", "txtRev='" _
        & Me.tbRev & "' And fkPartID=" & lngID)) Then

D...anything is to be used sparingly and far between doing 2 lookups just to see if something exists is not 'the best option', instead use a 'normal' query and see if that return any data... Will prove to be much easier.

Also tbPartNo, is this a text box with free entry or a combo box with a pick list that is potentialy bound to the PK of tblParts?

 

[boblarson]

I would use DCount instead in this situation where you just want to know if it exists.

So, something like:

If DCount("txtPartNo", "tblParts", "txtPartNo='" _
        & Me.tbPartNo & "'") = 0 Then

And

If DCount("txtRev", "tblPartRev", "txtRev='" _
        & Me.tbRev & "' And fkPartID=" & lngID) = 0

 

[Rick Stanich]

namliam
I have two textboxes so I thought, hey, two DLookups. One for each textbox.

boblarson
I originally had DCounts, I was redirected, at this stage I do not know which methods are best until people such as yourself or namliam tell me different, none the less they are both excersizes in Access that help me understand some things.
Which is the point of all my post and questions, I am just trying to learn different ways, methods, objects, etc., not nescessarily the best method of.

Anyhow, how do I get the foreign key of tblJobParts to relate back to pkPartRevID.
Or did both of you illustrate that and I dont see it.

 

[boblarson]

boblarson
I originally had DCounts, I was redirected, at this stage I do not know which methods are best until people such as yourself or namliam tell me different, none the less they are both excersizes in Access that help me understand some things.
Which is the point of all my post and questions, I am just trying to learn different ways, methods, objects, etc., not nescessarily the best method of.

A DCount in this case is superior because you don't have to deal with nulls. If there isn't anything it will return a zero.

Anyhow, how do I get the foreign key of tblJobParts to relate back to pkPartRevID.
Or did both of you illustrate that and I dont see it.

Actually I think you probably need to change your DCount to this (now that I've looked further):

If DCount([COLOR="Red"][B]"pkPartRevID"[/B][/COLOR], "tblPartRev", "txtRev='" _
        & Me.tbRev & "' And fkPartID=" & lngID) = 0

 

[Rick Stanich]

Using your last suggestion appears to work when a user enters a value that "is" in the table holding "fkPartID". If I type any value like "x" (just an example) that isnt in the table I get an error:

Run-time error '94':
Invalid use of null

 

[boblarson]

Using your last suggestion appears to work when a user enters a value that "is" in the table holding "fkPartID". If I type any value like "x" (just an example) that isnt in the table I get an error:

What does it highlight?

 

[Rick Stanich]

The error highlites this segment of code in its entirety. Only if a value is entered that is not in a table.

lngID = DLookup("pkJobID", "tblJobs", "txtJobNo='" _
        & Me.tbJobNo & "'")

 

[boblarson]

Change this:

lngID = DLookup("pkJobID", "tblJobs", "txtJobNo='" _
        & Me.tbJobNo & "'")

to this:

lngID = [COLOR="Red"]Nz([/COLOR]DLookup("pkJobID", "tblJobs", "txtJobNo='" _
        & Me.tbJobNo & "'")[COLOR="red"],0)[/COLOR]

 

[Rick Stanich]

I have seen the use of "Nz" before, is this some method (command) to ignore Null and redirect if equals "0"?

Oh yea, it works with your new example.

 

[boblarson]

I have seen the use of "Nz" before, is this some method (command) to ignore Null and redirect if equals "0"?

Oh yea, it works with your new example.

Yes, it basically stands for Null to Zero but there used to be an ACTUAL function called NullToZero which was not flexible. The NZ function allows you to assign whatever you want (within the constraints of the datatype you are trying to use). So, if you wanted 355 to be the default if a null was encountered then you would use

Nz([FieldName],355)

or if you have a string field you can set it to something if null:

Nz([FieldName], "Whatever I Want")

does that help?

 

[Rick Stanich]

Sure! Your assitance and examples are very much appreciated!
It has helped me with some syntax for tables (DLookup, DCount) that I was not understanding to well and now may be able to apply to other commands.
We will see how that goes. LOL

Thanks for your time and effort.

 

[boblarson]

 

[Rick Stanich]

Thats funny, and scary at the same time. LOL

 

[boblarson]

Thats funny, and scary at the same time. LOL

It's how I get to look by the end of the day at work