form that shows data from another table

[jlydon]

I want to make a form for a library where I scan in the book barcode
and automatically it will show on some part of the form the name and
author of the book (information stored in a table which has the
barcode information, etc). Then I can scan in the student ID to check
out that book. When I scan in the student ID the name and information
on the student (stored in a student table) will also come up. Is this
possible?
Thanks for any guidance.

 

[Guest]

A sub form would be useful for this. I do have a thought that may cause you
to reconsider your design. A borrower may check out more than one book at a
time, but a book cannot be checked out by more than one borrower at a time.
Therefore, I would suggest the main form be based on the borrower table and a
sub form based on the books table.

 

[Guest]

Hi

Yes, this is possible. Add unbound text boxes to display the info and use
the dlookup function as their source or to set their value using VBA code...

dlookup("Title", "tblBooks", "BookBarCode = " & Me.BookBarCode)
dlookup("ForeName", "tblStudents", "StudentBarCode = " & Me.StudentBarCode)
dlookup("LastName", "tblStudents", "StudentBarCode = " & Me.StudentBarCode)

Obviously, replace with your table names and column names. I have assumed
there are controls on your form called BookBarCode and StudentBarCode.

Hope this helps

Andy Hull

 

[jlydon]

I tried putting in the VBA code as you have it (changing the names for
the correct ones) but in the unbound text box there show up the
following #Name?
Does the table Books have to be in this same database. Right now it
is in another database and I link to it from the bookstatus database.
I don't know if that would be the problem, or something else causes
the #Name? to show up.
I appreciate any guidance.
John

 

[Guest]

Hi

Try an = at the beginning, like...

=dlookup(...

 

[jlydon]

Thanks for the help. Unfortunately that did not do anything. The
program had already put the = in front.
Hopefully you can give me another idea to try. Thanks
John

 

[Guest]

Hi again John

If you are setting your display with VBA code use the expressions as I've
stated...

dlookup("Title", "tblBooks", "BookBarCode = " & Me.BookBarCode)

If you are setting the control source then drop the Me. so the expression
is...

dlookup("Title", "tblBooks", "BookBarCode = " & BookBarCode)

Note, i've assumed BookBarCode is numeric. If it is text you will need to
put single quotes around it so the 2 versions of the expression become...

dlookup("Title", "tblBooks", "BookBarCode = '" & BookBarCode & "'")
or
dlookup("Title", "tblBooks", "BookBarCode = '" & Me.BookBarCode & "'")

Sorry for the confusion - hope this helps

Andy Hull

 

[jlydon]

Dear Andy: Thanks again. I just got back from a trip so only now could
try again. I would like to clarify the following:
BookBarCode= is enclosed in double quotes
This is followed by a single quote since it is a text field and not a
number field
Then comes &BookBarCode&. How is this followed by a single quote, or
is the single quote then followed by a double quote as seems to be the
case from the text?

I am typing in the text where it says Control Source on the properties
menu, so I don't know if thea is VBA code or not.

Thanks for clarifying.
John

 

[Guest]

Hi again John

Here is the statement with asterisks where there should be single quotes so
it is easier to see...

dlookup("Title", "tblBooks", "BookBarCode = *" & BookBarCode & "*")

Note this is assuming the is a table called tblBooks with a field called
BookBarCode and that your form has a control called BookBarCode.

Regards

Andy Hull

 

[jlydon]

Dear Andy: Many thanks. That worked great. John