Quick DLookup question

[Stephanie T.]

Does anyone know if it is possible to have an After_Update of one field using DLookup, look up information for two separate fields? If so, do you know how to write it?

Here is what I have that works great for the one field:

Private Sub Distributor_AfterUpdate()
On Error GoTo Err_Distributor_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "Distributor = '" & Me!Distributor & "'"

' Look up Distributors's Spoilage and assign it to Spoilage control.
Me!Spoilage = DLookup("Spoilage", "tblDistributors", strFilter)


Exit_Distributor_AfterUpdate:
Exit Sub

Err_Distributor_AfterUpdate:
MsgBox Err.Description
Resume Exit_Distributor_AfterUpdate

End Sub

I'd also like to us this function to control the "FreightAllowance" field as well as the "Spoilage" field.

Your help is great appreciated.

Thanks,
Stephanie

 

[Stew]

I'm not quite sure if I understand what you want to do, I would just add one extra line to set the value for FreightAllowance:

Private Sub Distributor_AfterUpdate()
On Error GoTo Err_Distributor_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "Distributor = '" & Me!Distributor & "'"

' Look up Distributors's Spoilage and assign it to Spoilage control.
Me!Spoilage = DLookup("Spoilage", "tblDistributors", strFilter)
Me!FreightAllowance = DLookup("FreightAllowance", "tblDistributors", strFilter)


Exit_Distributor_AfterUpdate:
Exit Sub

Err_Distributor_AfterUpdate:
MsgBox Err.Description
Resume Exit_Distributor_AfterUpdate

End Sub

Paul

 

[Stephanie T.]

Paul,

Thank you! I had been doing that and it didn't work. But I figured it out and now I feel so stupid , I'm sorry I wasted your time. The Freight Allowance is just called Freight in the Distributor form. So the problem was that I needed to change the name of what the DLookup was looking for.

Ok, it all works and I come clean with my stupidity. Thank you again!!

Best to you,
Stephanie

 

[Stew]

I know exactly what you mean, I've stared at code for hours trying to figure out why it wasn't working only to discover that I misspelled a field name or referenced a wrong field.

So goes life in the trenches of the code war (kind of funny).

Have a good one.

Paul

 

[Rich]

Why not use a combo box?

 

[Stephanie T.]

Rich - A combo box wouldn't work because I need the numbers to be taken from the Distributor information directly. Not every Distributor has spoilage or freight allowances and this way the data entry person doesn't have to look anything up. Less chance of making a mistake and quicker too.

Paul - Thanks for the understanding. I do that with websites, I design more of those than I do Access stuff. And I can't tell you how many times I stare at code that works for IE but not for Netscape. I've actually started thinking in HTML, but at least I usually find those open tags.

Thanks to both of you for you input. It is all working now and I'm on to the next problem