John Vinson is gone for two weeks can someone help me finish up this project?

[Me]

I am almost there with John's help.

At one point when I put in the student ID it would just more all the
student information up one

I am a teacher and I would like to scan ID numbers from student ID's
to only allow students that belong in my lunch in and keep the ones
that don't belong in there out.

I pulled a list of students into Access I have a barcode scanner to
scan the student ID number into the computer which I have working. I
want to scan the students ID cards with a barcoded ID number and bring
up the students name, class, grade and lunch.

The Table contains #-Primary Key | Student ID | Last Name | First Name
| Grade | HR | Lunch | Class

I have a form as per John W. Vinson directions I have a form with text
boxes

Student ID Text box name txtStudentID control source Student ID

Lunch Text box name TxtLunch control source Lunch

First Name Text box name TxtFirstName control source First Name

Last Name Text box name TxtLastName control source Last Name

Grade Text box name TxtGrade control source Grade

Class Text box name TxtClass control source Class

And and Unbound text box ID Scan TxtIDScan

This is the code John gave me

In this textbox's AfterUpdate event put the following VBA code. Click
the ... icon by the After Update line on the Events tabl of the
textbox's properties:

Private Sub txtStudentID_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the form's list of records
rs.FindFirst "[Student ID] = '" & Me!txtStudentID & "'"
If rs.NoMatch Then ' see if this ID exists
MsgBox "Check this card, this ID was not found", vbOKOnly
Else
Me.Bookmark = rs.Bookmark ' open the found student's record
End If

This will, after you scan or type in the studentID, search the Form's
recordset for the selected student; if they're found their information
will be displayed, if not a message will pop up.

 

[Duane Hookom]

Apparently the scanned ID value is in the text box "TxtIDScan". If so then
your code should be:

rs.FindFirst "[Student ID] = '" & Me!TxtIDScan & "'"

Your code assumes the Student ID field is text, not numeric.
Also, the scanned ID should go into the txtScanID text box and the code
should be in the After Update of txtScanID not txtStudentID.

 

[Me]

Apparently the scanned ID value is in the text box "TxtIDScan". If so then
your code should be:

rs.FindFirst "[Student ID] = '" & Me!TxtIDScan & "'"

Your code assumes the Student ID field is text, not numeric.
Also, the scanned ID should go into the txtScanID text box and the code
should be in the After Update of txtScanID not txtStudentID.

If I put the code in AfterUpdate in ID Scan it appears in Student ID
Afterupdate and if I remove it from Student ID it is removed from ID
Scan?

The Student ID and ID scan are numeric

 

[Ken Snell [MVP]]

Not trying to confuse the issue, so, if I'm off base here, just let me know
and I'll drop off...

One "flaw" that I am perceiving here is that you're using the student's
table as your form's Recordsource, but that will not help you to store which
students were in lunch class on a certain day because that table does not
appear capable of storing such data for each individual day. (Now, it may be
that I am completely misunderstanding what you want, but this is what I was
understanding from the other thread...it's not that you want to just show
the student's info, but that you want to store that the student attended a
particular lunch class on a particular date.)

You're going to need a new table that will store the info for which students
were in class:
tblStudentsLunchAttend
StudentID
LunchDate

The above table is the one that I would use for your form's RecordSource.
Then the code for the barcode textbox would look up the info (does the
barcode contain the actual Student ID value?) from your current table and
display those values on the form for your info, but the barcode textbox
would be bound to tblStudentsLunchAttend.StudentID field and you'd have
another textbox with the date of the lunch class in it, and that date
textbox would be bound to tblStudentsLunchAttend.LunchDate field.

This "lookup code" would not use the FindFirst method of the form's
recordset at all, because the form would not be bound to the table that
contains the student data. Instead, I would use unbound textboxes for
showing the student's name, grade, etc., and the control source for each of
these display textboxes would be an expression similar to this (following is
an example for the LastName of the student:

=DLookup("Last Name", "ActualNameOfStudentTable", "[Student ID]=" &
[txtIDScan])

--

Ken Snell
<MS ACCESS MVP>

Apparently the scanned ID value is in the text box "TxtIDScan". If so then
your code should be:

rs.FindFirst "[Student ID] = '" & Me!TxtIDScan & "'"

Your code assumes the Student ID field is text, not numeric.
Also, the scanned ID should go into the txtScanID text box and the code
should be in the After Update of txtScanID not txtStudentID.

If I put the code in AfterUpdate in ID Scan it appears in Student ID
Afterupdate and if I remove it from Student ID it is removed from ID
Scan?

The Student ID and ID scan are numeric

 

[Duane Hookom]

As Ken mentioned, if you want to save the student ID in a table with the
date, then you should not have a form based on the student table.

I created an application similar to this a while back. You might not need
any code. I would suggest a continuous form bound to Ken's suggested table.

The LunchDate field is bound to a text box with a default value of either
Date() or Now(). I would set the Tab Stop property to No for the date.

The StudentID is bound to a combo box (cboStudentID) that has a Row Source
of the student table. The bound column of the combo box must match the value
you want to store in the table "tblStudentsLunchAttend". The columns in the
combo box must contain all the fields you want to display on the form.

If you want to display the Lunch field on the continuous form, make sure the
combo box contains this field. You can then add a text box to the form with
a control source like:
=cboStudentID.Column(3)
The column numbering is 0 based so Column(3) displays the value from the 4th
column.

When an ID is scanned in, it should place the student ID in the bound combo
box and display any other columns in text boxes like the Lunch field.

--
Duane Hookom
MS Access MVP
--

Not trying to confuse the issue, so, if I'm off base here, just let me
know and I'll drop off...

One "flaw" that I am perceiving here is that you're using the student's
table as your form's Recordsource, but that will not help you to store
which students were in lunch class on a certain day because that table
does not appear capable of storing such data for each individual day.
(Now, it may be that I am completely misunderstanding what you want, but
this is what I was understanding from the other thread...it's not that you
want to just show the student's info, but that you want to store that the
student attended a particular lunch class on a particular date.)

You're going to need a new table that will store the info for which
students were in class:
tblStudentsLunchAttend
StudentID
LunchDate

The above table is the one that I would use for your form's RecordSource.
Then the code for the barcode textbox would look up the info (does the
barcode contain the actual Student ID value?) from your current table and
display those values on the form for your info, but the barcode textbox
would be bound to tblStudentsLunchAttend.StudentID field and you'd have
another textbox with the date of the lunch class in it, and that date
textbox would be bound to tblStudentsLunchAttend.LunchDate field.

This "lookup code" would not use the FindFirst method of the form's
recordset at all, because the form would not be bound to the table that
contains the student data. Instead, I would use unbound textboxes for
showing the student's name, grade, etc., and the control source for each
of these display textboxes would be an expression similar to this
(following is an example for the LastName of the student:

=DLookup("Last Name", "ActualNameOfStudentTable", "[Student ID]=" &
[txtIDScan])

--

Ken Snell
<MS ACCESS MVP>

Apparently the scanned ID value is in the text box "TxtIDScan". If so
then
your code should be:

rs.FindFirst "[Student ID] = '" & Me!TxtIDScan & "'"

Your code assumes the Student ID field is text, not numeric.
Also, the scanned ID should go into the txtScanID text box and the code
should be in the After Update of txtScanID not txtStudentID.

If I put the code in AfterUpdate in ID Scan it appears in Student ID
Afterupdate and if I remove it from Student ID it is removed from ID
Scan?

The Student ID and ID scan are numeric

 

[Me]

Not trying to confuse the issue, so, if I'm off base here, just let me know
and I'll drop off...

One "flaw" that I am perceiving here is that you're using the student's
table as your form's Recordsource, but that will not help you to store which
students were in lunch class on a certain day because that table does not
appear capable of storing such data for each individual day. (Now, it may be
that I am completely misunderstanding what you want, but this is what I was
understanding from the other thread...it's not that you want to just show
the student's info, but that you want to store that the student attended a
particular lunch class on a particular date.)

You're going to need a new table that will store the info for which students
were in class:
tblStudentsLunchAttend
StudentID
LunchDate

Before I make this table will it also store the students first and
last name?

The above table is the one that I would use for your form's RecordSource.
Then the code for the barcode textbox would look up the info (does the
barcode contain the actual Student ID value?) from your current table and
display those values on the form for your info, but the barcode textbox
would be bound to tblStudentsLunchAttend.StudentID field and you'd have
another textbox with the date of the lunch class in it, and that date
textbox would be bound to tblStudentsLunchAttend.LunchDate field.

This "lookup code" would not use the FindFirst method of the form's
recordset at all, because the form would not be bound to the table that
contains the student data. Instead, I would use unbound textboxes for
showing the student's name, grade, etc., and the control source for each of
these display textboxes would be an expression similar to this (following is
an example for the LastName of the student:

=DLookup("Last Name", "ActualNameOfStudentTable", "[Student ID]=" &
[txtIDScan])

 

[Me]

You guys are great but first you guys are putting the cart before the
horse. I still don't have the scanning of ID cards (which is the
actual ID number in the table) to show the data in my form?

What information can I give you so you can understand what is going
on?

I have a table with six text boxes that are bound to the ID, Lunch,
First Name, Last Name, Grade and class and an unbound to scan in the
ID number.

 

[Duane Hookom]

There is no good reason to store the first and last names if you have the
studentid in the table tblStudentsLunchAttend. This is basic relational
database usage. Look at the Northwind.mdb. In the OrderDetails table, you
will see a productID but not a product name.

The continuous form that I suggested will use a combo box bound to the
StudentID but will actually display other fields from the student table.

--
Duane Hookom
MS Access MVP
--

Not trying to confuse the issue, so, if I'm off base here, just let me
know
and I'll drop off...

One "flaw" that I am perceiving here is that you're using the student's
table as your form's Recordsource, but that will not help you to store
which
students were in lunch class on a certain day because that table does not
appear capable of storing such data for each individual day. (Now, it may
be
that I am completely misunderstanding what you want, but this is what I
was
understanding from the other thread...it's not that you want to just show
the student's info, but that you want to store that the student attended a
particular lunch class on a particular date.)

You're going to need a new table that will store the info for which
students
were in class:
tblStudentsLunchAttend
StudentID
LunchDate

Before I make this table will it also store the students first and
last name?

The above table is the one that I would use for your form's RecordSource.
Then the code for the barcode textbox would look up the info (does the
barcode contain the actual Student ID value?) from your current table and
display those values on the form for your info, but the barcode textbox
would be bound to tblStudentsLunchAttend.StudentID field and you'd have
another textbox with the date of the lunch class in it, and that date
textbox would be bound to tblStudentsLunchAttend.LunchDate field.

This "lookup code" would not use the FindFirst method of the form's
recordset at all, because the form would not be bound to the table that
contains the student data. Instead, I would use unbound textboxes for
showing the student's name, grade, etc., and the control source for each
of
these display textboxes would be an expression similar to this (following
is
an example for the LastName of the student:

=DLookup("Last Name", "ActualNameOfStudentTable", "[Student ID]=" &
[txtIDScan])

 

[Duane Hookom]

Do you want to store the information about which students were at which
lunch?

Or, do you simply want to scan the card and display the student information
on the screen? When the next student gets scanned, the previous student
information is not viewable and not saved anywhere.

--
Duane Hookom
MS Access MVP
--

You guys are great but first you guys are putting the cart before the
horse. I still don't have the scanning of ID cards (which is the
actual ID number in the table) to show the data in my form?

What information can I give you so you can understand what is going
on?

I have a table with six text boxes that are bound to the ID, Lunch,
First Name, Last Name, Grade and class and an unbound to scan in the
ID number.

 

[Me]

I want to do both but right now I have a form that if I put an ID
number in the ID Scan textbox it just moves the information in all the
other textboxes to the next student in the table? I thought we could
fix that first then work on saving the information or does it involve
doing both to get the results I am looking for in the form?

 

[Duane Hookom]

Go back to my reply that begins "As Ken mentioned..." You will find your
solution there. Make sure you have a table as Ken suggested and then build
the form etc as I suggested. Any other attempt to use a form bound to your
student table will just result in failure and cause me to get upset and
possibly not respond.

--
Duane Hookom
MS Access MVP
--

I want to do both but right now I have a form that if I put an ID
number in the ID Scan textbox it just moves the information in all the
other textboxes to the next student in the table? I thought we could
fix that first then work on saving the information or does it involve
doing both to get the results I am looking for in the form?

 

[Me]

So the first I have to is add another textbox to the original form
with the date and that will be bound to the new table
tblStudentsLunchAttend.LunchDate field correct?

 

[Me]

So the first thing I have to do is add another textbox to the original form
with the date and that will be bound to the new table
tblStudentsLunchAttend.LunchDate field correct?

 

[Me]

Do you know of a sample of this anywhere that I could look at? It
seems like I am changing everything that was done originally with John
Vinson. Should I follow all the directions in this thread both yours
and Ken Snells advice or just yours?

I have been working on this for over a week and seem to be going in
circles at this point.

I now have built another table. What can I post to show you what I
have so far.

 

[Duane Hookom]

John is out of town so I would only follow my advice ;-) Ken is also good
but he handed this off to me.

What are the table and field names of your students table and your lunch
attendance table?

All you should have to do is create a continuous form based on your table
tblStudentsLunchAttend. You should have a combo box based on your students
table to allow the scanning of the student ID to capture a value to store in
tblStudentsLunchAttend. You can include other controls on the form depending
on what you want to see on the form when you scan.

The form should be set up as "Data Entry: Yes". This will hide all the old
records when it opens.

 

[Me]

John is out of town so I would only follow my advice ;-) Ken is also good
but he handed this off to me.

Thank You again for your time!

What are the table and field names of your students table and your lunch
attendance table?

Student Table - Student List 2-5 - #, Student ID, Last Name, First
Name, Grade, HR, Lunch

Lunch Attendance Table - tblStudentsLunchAttend - StudentID, LunchDate

All you should have to do is create a continuous form based on your table
tblStudentsLunchAttend. You should have a combo box (What should this Combo Box consist of? I'm haven't used one before) based on your students
table to allow the scanning of the student ID to capture a value to store in
tblStudentsLunchAttend. You can include other controls on the form depending
on what you want to see on the form when you scan.

The form should be set up as "Data Entry: Yes". This will hide all the old
records when it opens.

I have a form which consists of ID Scan, Student ID, Lunch, First
Name, Last Name, Grade, Class and now Date. This way I can see who I
am scanning and if they belong in that lunch.

Until I started changing things when I entered the Scan ID the student
list would just move up one name from the student table.

 

[Duane Hookom]

Read my most recent reply and set up your form based on
tblStudnetsLunchAttend. The StudentID field must be bound to Combo Box that
has a row source like
Name: cboStudentID
Row Source: SELECT StudentID, [First Name] & " " & [Last Name] as FullName,
Grade, Lunch, HR FROM Student;
Columns Count: 5
Bound Column: 1
Column Widths: 0,1.5,0.5, 0.5, 0.5

Add more text boxes to the form with control sources like:
to display the Grade
=cboStudentID.Column(2)
to display the Lunch
=cboStudentID.Column(3)

--
Duane Hookom
MS Access MVP

John is out of town so I would only follow my advice ;-) Ken is also good
but he handed this off to me.

Thank You again for your time!

What are the table and field names of your students table and your lunch
attendance table?

Student Table - Student List 2-5 - #, Student ID, Last Name, First
Name, Grade, HR, Lunch

Lunch Attendance Table - tblStudentsLunchAttend - StudentID, LunchDate

All you should have to do is create a continuous form based on your table
tblStudentsLunchAttend. You should have a combo box (What should this
Combo Box consist of? I'm haven't used one before) based on your students
table to allow the scanning of the student ID to capture a value to store
in
tblStudentsLunchAttend. You can include other controls on the form
depending
on what you want to see on the form when you scan.

The form should be set up as "Data Entry: Yes". This will hide all the old
records when it opens.

I have a form which consists of ID Scan, Student ID, Lunch, First
Name, Last Name, Grade, Class and now Date. This way I can see who I
am scanning and if they belong in that lunch.

Until I started changing things when I entered the Scan ID the student
list would just move up one name from the student table.

 

[Me]

Now when I go to Form View I get a box asking

Enter Parameter Value
StudentID?

Do you know of a Access project that is similiar to what I and trying
to do like the Northwind Project that comes with Access so I can see
what to do. The students have already changed lunches for a week and I
have been working on this for at least three? I have searched the
internet for a template or for something similiar to this project but
have had no luck.

Even if I can just get a form to scan in the students ID Number and
get his name, lunch, grade, and class would work for now. I can try to
work out the saved list later.

On Sun, 13 Feb 2005 23:42:49 -0600, "Duane Hookom" <duanehoot

 

[Me]

You really get around!

I saw you on TEK-TIPS FORUMS trying to find out about "Enter Parameter
Value".

And how's that grandson?

 

[Me]

This is making me crazy!!!!

Now I have a all the FirstName Last Name(It is not the actual names
but FirstName LastName) in the student ID Combo Box

The Lunch textbox has #Name?