Thread: DLookup: One to many table relationships

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.

Better example? In this code I set a value for tbPartNo based on tblParts. Code: 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", "...

Why do two DLookups, when you can do this in one simple query?? Code: 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 ...

I would use DCount instead in this situation where you just want to know if it exists. So, something like: Code: If DCount("txtPartNo", "tblParts", "txtPartNo='" _ & Me.tbPartNo & "'") = 0 Then And Code: If DCount("txtRev", "tblPartRev", "txtRev='" _ & Me.tbRev & "' And fkPartID=" & lngID) = 0

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 ...

Quote: Originally Posted by Rick Stanich 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...

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: Quote: Run-time error '94': Invalid use of null

Quote: Originally Posted by 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: What does it highlight?

The error highlites this segment of code in its entirety. Only if a value is entered that is not in a table. Code: lngID = DLookup("pkJobID", "tblJobs", "txtJobNo='" _ & Me.tbJobNo & "'")

Change this: Code: lngID = DLookup("pkJobID", "tblJobs", "txtJobNo='" _ & Me.tbJobNo & "'") to this: Code: lngID = Nz( DLookup("pkJobID", "tblJobs", "txtJobNo='" _ & Me.tbJobNo & "'") ,0)