This is the situation.
I have 3 tables, with the following relevent fields
tblSubFile (Substantiation Files)
[ID] - autonumber
[Reference Number] - Primary Key
[Issue] - Primary Key
[Relevant NQO]
tblReqAndSubFile (Shows relationships between Substantiation Files and Requirements)
[Requirement ID] - Primary Key
[Reference Number] - Primary Key
[Issue] - Primary Key
tblReq (Requirements)
[Requirement ID] - Primary Key
[Relevant NQO for required substantiation]
The relationship orignally between tblSubFile and tblReq was many-to-many, this is the reason the bridge talbe tblReqAndSubFile was created.
What is required, is that the field [Relevant NQO] in tblSubFile is being filled out by finding the value of [Relevant NQO for required substantiation] in tblReq. As a Substantiation File can be related to several Requirements, it only uses the first Requirement.
The problem is, I want the loopup to skip to the next related Requirement if the [Relevant NQO for required substantiation] was blank, and keep going until it finds a non-null entry in [Relevant NQO for required substantiation], and then use this value. Or if all of the [Relevant NQO for required substantiation] fields for the related Requirements are null, return a null value.
Currently the code i use is as follows
Expand|Select|Wrap|Line Numbers
- ' Finds the Requirement for the current Substantiation File on the form
- var1 = DLookup("[Requirement ID]", "tblReqAndSubFile", "[Reference Number] = Forms![frmSubFile]![Reference Number]")
- ' Updates the Relevant NQO for CSOC Approval (Europe) on the CSOC form using data from the Requirements table
- var2 = DLookup("[Relevant NQO for required substantiation]", "tblReq", "[Requirement ID]=" & "'" & var1 & "'")
- Me.Relevant_NQO = var2
Thanks