Lookup Value loop

[bhcob1]

Hi guys,

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]

tblReqAndSubFil e (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 tblReqAndSubFil e 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

  ' 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

If sample data that i have would help, let me know.

Thanks

[Rabbit]

Suscribing. At first glance this looks more involved than I have time for right now.

[Rabbit]

Hi guys,

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]

tblReqAndSubFil e (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 tblReqAndSubFil e 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

  ' 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

If sample data that i have would help, let me know.

Thanks

Off topic but it seems to me as if your [Requirement ID] could just replace your [ID] from TblSubFile and you'll drop down to 2 tables.

In response to your main question:

var2 = DLookup("[Relevant NQO for required substantiation]", "tblReq", "[Requirement ID]='" & var1 & "' AND [Relevant NQO for required substantiation] Is Not Null")

I believe that's the correct syntax, if not then use Not IsNull([Relevant NQO for required substantiation]) instead.

[NeoPa]

Domain Aggregate functions are a grey area for me where it comes to using SQL syntax. Certainly the X Is Null construct is SQL specific. In VBA generally the syntax is IsNull(). I expect the Domain Aggregate functions will actually handle the SQL syntax fine.