HELLLLLLLLP!! I Need To Pull This Excel File Into Access And... - Sample.xls (1/1)

On Sat, 05 Feb 2005 14:29:14 -0700, John Vinson
wrote:

On Sat, 05 Feb 2005 19:18:40 GMT, wrote:

On Fri, 04 Feb 2005 17:33:34 -0700, John Vinson
wrote:

On Fri, 04 Feb 2005 22:01:17 GMT, wrote:

I have already pulled a list of students into Access (The sample is
only part of a list of 1362 students) 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 and lunch.

What exactly is in the Lunch field?

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

Would I next make up a form containing Student Name (First and Last)
Student ID, Lunch and Class in order to pull up these items when I
scan the students ID number off of their ID card? I just want to keep
scanning one ID after another and not have to reset anything. Is that
possible?



A scanner - usually, I'm not certain about yours - is usually
connected to the computer's keyboard port (often using a "y" cable
from the keyboard port to both the scanner and the keyboard). Is that
the case? If so, the computer can't tell whether your scanning or just
typing very fast and accurately. (This is the type of scanner I have.)

What you would need to do is to have a Form with textboxes for all of
the information that you want to see; these should all have their
Enabled property set to FALSE (Under the Data tab for Enabled the only choices I have is Yes or No)
and their Locked property to TRUE (Under the Data tab for Locked the
only choices I have is Yes or No) so
they cannot be overtyped. If you need to change a student's name,
you'll need a different form to do so.

There would be one textbox for the StudentID; let's name it
txtStudentID. It would have its Tab Order property 0, Tab Stop Yes,
and - critical here! - its Control Source property blank. You're not
going to store the scanned ID, only use it to find a record.

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.

Hope this helps... and my apologies for the tone of the netiquette
lecture!


John W. Vinson[MVP]

Never mind I figured the last two out!


On Sun, 06 Feb 2005 18:06:59 GMT, wrote:

On Sat, 05 Feb 2005 14:29:14 -0700, John Vinson
wrote:

On Sat, 05 Feb 2005 19:18:40 GMT, wrote:

On Fri, 04 Feb 2005 17:33:34 -0700, John Vinson
wrote:

On Fri, 04 Feb 2005 22:01:17 GMT, wrote:

I have already pulled a list of students into Access (The sample is
only part of a list of 1362 students) 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 and lunch.

What exactly is in the Lunch field?

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

Would I next make up a form containing Student Name (First and Last)
Student ID, Lunch and Class in order to pull up these items when I
scan the students ID number off of their ID card? I just want to keep
scanning one ID after another and not have to reset anything. Is that
possible?



A scanner - usually, I'm not certain about yours - is usually
connected to the computer's keyboard port (often using a "y" cable
from the keyboard port to both the scanner and the keyboard). Is that
the case? If so, the computer can't tell whether your scanning or just
typing very fast and accurately. (This is the type of scanner I have.)

What you would need to do is to have a Form with textboxes for all of
the information that you want to see; these should all have their
Enabled property set to FALSE (Under the Data tab for Enabled the only choices I have is Yes or No)
and their Locked property to TRUE (Under the Data tab for Locked the
only choices I have is Yes or No) so
they cannot be overtyped. If you need to change a student's name,
you'll need a different form to do so.

There would be one textbox for the StudentID; let's name it
txtStudentID. It would have its Tab Order property 0, Tab Stop Yes,
and - critical here! - its Control Source property blank. You're not
going to store the scanned ID, only use it to find a record.

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.

Hope this helps... and my apologies for the tone of the netiquette
lecture!


John W. Vinson[MVP]

On Sun, 06 Feb 2005 18:43:53 GMT, wrote:

Never mind I figured the last two out!



so... is it all working?

If so, glad I was able to help!


John W. Vinson[MVP]

Thank you very much for your time.
OK I did everything you told me.
I took me all day Sunday from 7:00 this morning until the present.
I had to use help many times but I got to this point.
I now have a form with the Student ID box blank and I cannot enter any
information.
It shows all the other information except for the student ID.
I can forward through record and it goes through all the students
showing all the information but the ID is blank.
Any ideas?

This is the Afterupdate setup

Option Compare Database

Private Sub Detail_Click()

End Sub

Private Sub Student_ID_AfterUpdate()
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
End Sub



On Sun, 06 Feb 2005 21:57:04 GMT, wrote:

Thank you very much for your time.
OK I did everything you told me.
I took me all day Sunday from 7:00 this morning until the present.
I had to use help many times but I got to this point.
I now have a form with the Student ID box blank and I cannot enter any
information.
It shows all the other information except for the student ID.
I can forward through record and it goes through all the students
showing all the information but the ID is blank.
Any ideas?

On Sun, 06 Feb 2005 21:57:04 GMT, wrote:

Thank you very much for your time.
OK I did everything you told me.
I took me all day Sunday from 7:00 this morning until the present.
I had to use help many times but I got to this point.
I now have a form with the Student ID box blank and I cannot enter any
information.
It shows all the other information except for the student ID.
I can forward through record and it goes through all the students
showing all the information but the ID is blank.
Any ideas?

What is the Recordsource property of the form? Please post the SQL
view. And what is the Control Source of the StudentID? What do you see
in the StudentID when you open the table as a datasheet?

John W. Vinson[MVP]

On Sun, 06 Feb 2005 22:08:27 GMT, wrote:

This is the Afterupdate setup

Option Compare Database

Private Sub Detail_Click()

End Sub

Private Sub Student_ID_AfterUpdate()
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
End Sub

Ah! I wasn't explaining everything I was assuming.

If the name of the field is Student_ID, *and* the name of the textbox
is also Student_ID, remove the second line, the one about
txtStudentID_AfterUpdate. I was assuming (but not explaining, sorry!)
that it's good practice to change the names of textboxes. Access
defaults to making them the same as the name of the field the textbox
is bound to, but this default can cause problems.

For the present, just remove the line.

Note that whenever you edit code you should do two things:

- put a line

Option Explicit

at the very top of the code window (only once per form or other
module)

- select Debug... Compile my database from the Menu after you've
edited the code, and correct any compilation errors (this would have
been one)

You will also probably need to select Tools... References and be sure
that the line

Microsoft DAO x.xx Object Library

(highest version number if more than one) is checked.

John W. Vinson[MVP]

On Sun, 06 Feb 2005 15:32:35 -0700, John Vinson
wrote:

On Sun, 06 Feb 2005 21:57:04 GMT, wrote:

Thank you very much for your time.
OK I did everything you told me.
I took me all day Sunday from 7:00 this morning until the present.
I had to use help many times but I got to this point.
I now have a form with the Student ID box blank and I cannot enter any
information.
It shows all the other information except for the student ID.
I can forward through record and it goes through all the students
showing all the information but the ID is blank.
Any ideas?

What is the Recordsource property of the form? Student List 2-5

Please post the SQL view. I don't know how to get an SQL view???

And what is the Control Source of the StudentID? Control Sorce is
blank.

What do you see in the StudentID when you open the table as a
datasheet?
It shows the Student ID's

On Sun, 06 Feb 2005 22:48:20 GMT, wrote:

And what is the Control Source of the StudentID? Control Sorce is
blank.

That's the problem. The Control Source should contain the name of the
field that you want to see on the form - Student ID.

John W. Vinson[MVP]

Now the form shows the Student ID but I cannot enter the ID to obtain
the rest of the information?

On Sun, 06 Feb 2005 16:47:15 -0700, John Vinson
wrote:

On Sun, 06 Feb 2005 22:48:20 GMT, wrote:

And what is the Control Source of the StudentID? Control Sorce is
blank.

That's the problem. The Control Source should contain the name of the
field that you want to see on the form - Student ID.

John W. Vinson[MVP]