DLookup for 2 tables

[hafa2000]

Hi,

Hoping to get some assistance here. I have a DLookup code below that works fine for retrieving information, "CENTRE" from a table called "CodeLook",

Private Sub SpecNo_AfterUpdate()
On Error GoTo Err_SpecNo_AfterUpdate

Dim strFilter As String
Dim strFilter1 As String
Dim FoundConsigneeID As String
Dim FoundConsigneeID1 As String

strFilter = "CODE = " & Me!SpecNo
FoundConsigneeID = Nz(DLookup("CENTRE", "CodeLook", strFilter))

If Trim(FoundConsigneeID & "") = "" Then
MsgBox "No Consignee match could be found for this Spec Code ", vbOKOnly + vbCritical, "Default Consignee Lookup"

Else

MsgBox "Scheduled Agreement match found", vbInformation, "Default Scheduled Agreement"
Me!ConsigneeID = FoundConsigneeID

End If

And I have another table, "GCAS" which I would like to retrieve informtion, "Sched"
I have the following code below but can't seem to get my head around the If, then and Else to incorporate to the above code

strFilter1 = "Spec = " & Me!SpecNo
FoundConsigneeID1 = Nz(DLookup("Sched", "GCAS", strFilter))

If Trim(FoundConsigneeID1 & "") = "" Then
MsgBox "No Scheduled Agreement match found?", vbOKOnly + vbCritical, "Default PO Lookup"

Else
MsgBox "Scheduled Agreement match found", vbInformation, "Default Scheduled Agreement"
Me!PurchaseOrderNo = FoundConsigneeID1

Any assistance would be greatly appreciated,


Steve

 

[dhookom]

Your objective isn't clear. Please tell us in plain sentences what exactly you want to happen.

It also helps if you use TGML code tags. It's a simple matter of selecting your code block and clicking an icon.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Your CodeLook.CODE and GCAS.Spec fields both use Me!SpecNo as a value for your filter?
[tt]
strFilter = "CODE = " & [red]Me!SpecNo [/red]
...
strFilter1 = "Spec = " & [red]Me!SpecNo [/red]
[/tt]



Have fun.

---- Andy

 

[hafa2000]

Hi Dhookom,

I think I got myself confused here as well,

I have a datasheet subform with a field called "SpecNo" and a field called "Consignor" and " Scheduled Agreement".
On the AfterUpate event on the field "SpecNo" I want to code to lookup and get data from 2 tables using Me!SpecNo as the value for the filter.

The first part of the code looksup Table CodeLook and pulls the data to put in the field called "Consignor"
The second part of the code looksup Table GCAS pulls the data to put in the field called "Scheduled Agreement"

I can get this part of the code to work to fill in the field Consignor

Dim strFilter As String
Dim FoundConsigneeID As String

strFilter = "CODE = " & Me!SpecNo
FoundConsigneeID = Nz(DLookup("CENTRE", "CodeLook", strFilter))

If Trim(FoundConsigneeID & "") = "" Then
MsgBox "......"

Else

MsgBox "......"
Me!ConsigneeID = FoundConsigneeID

End If

I just don't have a clue how to code to lookup the 2nd Table to get data to fill in the "Scheduled Agreement" field from the Table is called GCAS with 2 fields called "Spec" and "Sched"

Thanks,

 

[dhookom]

I would change the SpecNo control on the subform to be a combo box. The row source would left join to CodeLook and GCAS with the Code and SpecNo fields. Make sure the CENTRE and Schedule Agreement values are included as columns in the combo box Row Source. Then use code in the after update of the combo box to put the values from the columns into the controls/fields in the datasheet.

Duane
Hook'D on Access
MS Access MVP