dLookup to multiple tables (if/else)

  • Thread starter Thread starter Austin Bike
  • Start date Start date
A

Austin Bike

I am having a problem. I want to create an onExit function for a
field on a form. If the field (manufacturer) is equal to one value, I
want to do a dlookup in one table and return a value sales rep.
However, if the input is NOT equal to that value, I want to do a
dlookup on a different table and return a different value to the sales
rep field.

We have 2 types of sales reps, regular and specialist. Each
transaction has one regular sales rep and one specialist.

Half of the specialists are assigned to one vendor, so their names are
kept in the vendor table. However, one vendor has multiple
specialists, so their names are kept in the sales rep table.

Here's the code I am trying to use:

Private Sub Manufacturer_Exit(Cancel As Integer)
If Me.Manufacturer = "Microsoft" Then
Dim varCOC As Variant
varCOC = DLookup("MSFT", "ISR", "ISR =[ISR] ")
If (Not IsNull(varCOC)) Then Me![COC] = varCOC

Else

Dim varCOC As Variant
varCOC = DLookup("Specialist_name", "Vendors", "Manufacturer
=[Dell S&P Vendor] ")
If (Not IsNull(varCOC)) Then Me![COC] = varCOC

End If

End Sub

Each statement works on its own, but when you combine the two together
with an "else" it chokes and tells me that "Dim varCOC As Variant" has
already been used.

Thanks in advance.
 
Austin Bike said:
I am having a problem. I want to create an onExit function for a
field on a form. If the field (manufacturer) is equal to one value, I
want to do a dlookup in one table and return a value sales rep.
However, if the input is NOT equal to that value, I want to do a
dlookup on a different table and return a different value to the sales
rep field.

We have 2 types of sales reps, regular and specialist. Each
transaction has one regular sales rep and one specialist.

Half of the specialists are assigned to one vendor, so their names are
kept in the vendor table. However, one vendor has multiple
specialists, so their names are kept in the sales rep table.
Click to expand...
Vendors have reps. Period.
Add all the reps to a single table, where they should be and the problem
goes away and does not crop up when another vendor decides to add reps.
If you can use a dlookup to return one of several vendors then all you need
is a relationship and no code.

If a rep has to be chosen then, as long as there is just the one with more
than one rep, it is just a matter of making a combo visible in that case.
You could also just make the field a combo and base the recordset on the
vendor ID.

It is always easier and usually requires a lot less code if the rules are
followed.
 
I was able to get it working by changing the second
instance of varCOC to varCOC2.

If [Manufacturer] = "Microsoft" Then GoTo MSFT Else

If [Manufacturer] <> "Microsoft" Then GoTo nonmsft Else

GoTo Read_End

MSFT:

Dim varCOC As Variant
varCOC = DLookup("MSFT_ISR", "Microsoft", "SP_ISR =
[ISR] ")
If (Not IsNull(varCOC)) Then Me![COC] = varCOC


GoTo Read_End


nonmsft:

Dim varCOC2 As Variant
varCOC2 = DLookup
("Specialist_name", "Vendors", "Manufacturer =[Dell S&P
Vendor] ")
If (Not IsNull(varCOC2)) Then Me![COC] = varCOC2


GoTo Read_End


Read_End:
 
Back
Top