Nov 16 2009, 10:59 AM
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:
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.
Nov 16 2009, 11:04 AM
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?
Nov 16 2009, 11:44 AM
Something like this?
And then use the DLookup like this?
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).
Nov 16 2009, 01:33 PM
That's more or less what I was suggesting, except that your DLookup is incorrect.
Dim strForm As Form
strForm = DLookup("[Form]","tblData","Barcode = '" & Me![Barcode] & "'") & vbNullString
If Len(strForm) > 0 Then
MsgBox "Barcode " & Me!Barcode & " was not in the table."
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]
Nov 16 2009, 02:14 PM
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?
Nov 16 2009, 02:29 PM
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:
Private Sub Barcode_AfterUpdate()
Put the code I gave you between those two lines.
Nov 16 2009, 02:32 PM
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?
Nov 16 2009, 02:37 PM
The only thing I have done differently is used a query rather than a table, but that shouldn't matter...
Nov 16 2009, 02:57 PM
Dummy (me, not you...)
Dim strForm As Form
Dim strForm As String
Sorry about that!
Nov 16 2009, 03:01 PM
Ugh, I should have seen that too. Anyway, it WORKS
thanks a lot for you help.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here