I have a form with a single field. The goal is for someone to scan a barcode and based on the barcode, a different form will open. The barcode could be one of 35 different values. It would look up a table with three fields. Think of a table like this:

tblEmployee
EmployeeID
SexID
HobbyID


tblSex
SexID
SexValue


tblHobby
HobbyID
HobbyValue

Lets say the barcode could be any of 35 EmployeeID's. There are only 2 SexID's and 4 HobbyID's. If they are Male, they could only have 2 of the four Hobby's and if they are Female, the other two Hobby's. Bottom line is, I need to have a form read a barcode and determine one of four different forms to open based on the Sex / Hobby combination.

Thanks in advance.

Why not have a table that contains legitimate combinations of Sex and Hobby and which form to use for each, and use DLookup to determine the form to open?

Something like this?

tblData
Barcode
SexValue
HobbyValue
Form

And then use the DLookup like this?

DLookup("[Form]","tblData",form![Barcode]")

I did have the tables set up the way they were just in case the Hobby or Sex values changed in the future. (remember this is just a sample for simplicity sake).

That's more or less what I was suggesting, except that your DLookup is incorrect.



This assumes that the code is running in the module associated with the form that contains the Barcode text box on it. If it's running somewhere else, you'd refer to the control as Forms![NameOfForm]![Barcode]

OK, I tried it with the code you gave me. I was using it with the AfterUpdate Event and I get the following error:

Invalid Use of Property

Do I not use DLookup as an event?

You mean you strictly typed the DLookup into the AfterEvent property? No, that's not correct.

Select [Event Procedure] from the pull-down list associated with the property. Click on the ellipsis (...) to the right of the property. That'll take you into the VB Editor, in the middle of something like:



Put the code I gave you between those two lines.

That is exactly what I did and I get the error. We are so close to the solution...

Thanks for your help so far. Do you have any ideas why I would get that error?

The only thing I have done differently is used a query rather than a table, but that shouldn't matter...

Dummy (me, not you...)

Dim strForm As Form

should be

Dim strForm As String

Sorry about that!

Ugh, I should have seen that too. Anyway, it WORKS

thanks a lot for you help.