# Solved: Access 2003: Student Check in and Reports



## lntech (Sep 24, 2010)

I've been trying to create a program to have students scan their ID and then generate a report including the time they scanned. This is for special meetings that occur once a week and there are a little over 100 students. I'm having a hard time linking tables and reports together. Any help would be appreciated. Thanks.


----------



## OBP (Mar 8, 2005)

lntech, do you still need assistance?


----------



## lntech (Sep 24, 2010)

Yes I do! Thank you for replying. All I'm trying to do is have the students scan their student ID when they enter the room for the meeting. I would like this to then create a report with their name and the time they scanned their ID. What I currently have created is a table with their first, last name, student ID. I created a query with a parameter associated to their student ID. But now I'm stuck. I think I'm missing another table to record the date and time? Would I then associate the record to the second table?

Am I going about this all wrong?


----------



## OBP (Mar 8, 2005)

Intech, I have not worked with a Scanner, but I know a guy who has.
You are correct you should have another table for the StudentID and the date & time form the scanner.
Can I suggest that next time you post your Access question on the Business Applications forum as Access posters spend most of their time on that forum.

So have you got the scanner and access talking to each other?


----------



## lntech (Sep 24, 2010)

Sorry about not placing this in the correct forum. I'm new to this and was desperate to get help asap. I've been working on this since Sept 1 and am no where closer to finishing now than when I first started.

Yes, I do have the scanner and access talking. Right now it just acting like a keyboard and inputs the student ID in the field I have the curser.


----------



## OBP (Mar 8, 2005)

Ok, that is good I can help you from there, can you attach a zipped copy of the database with the Student's personal details removed or changed?
You should also Compact & Repair the database prior to zipping it.


----------



## lntech (Sep 24, 2010)

How do I compact and repair it?


----------



## OBP (Mar 8, 2005)

Open Access, Main menu>Tools>Database Utilities>Compact & Repair database
Browse to your database, select it, when is says save give it a new name and OK.
That will remove all the design bloat.


----------



## lntech (Sep 24, 2010)

It says it's an invalid file when I try to attach it.


----------



## OBP (Mar 8, 2005)

Did you Zip it?


----------



## lntech (Sep 24, 2010)

I resaved it and zipped again.


----------



## OBP (Mar 8, 2005)

OK, I have to go now, I am in the Uk and I have been on the forum for hours.
I will take a look and get back to you Tomorrow.


----------



## OBP (Mar 8, 2005)

Intech, I have modified your table structure, relationships and queries. This should work fine for storing may scans per Student in the Scan data table.


----------



## lntech (Sep 24, 2010)

Thanks for taking the time to work on this.
Where would the students input their ID number? Do I need to created a query with a parameter now? How would I get a report for all the students that scanned that day?


----------



## lntech (Sep 24, 2010)

I tried to make a parameter in the studentdata query but it is only recording one record at a time. It's not making a list so I can have a report. Would I want this be stored in the scanneddate table and then run a report off that table?


----------



## OBP (Mar 8, 2005)

Intech, re "Where would the students input their ID number?", do they enter their number or does the Scanner enter it? 
I thought you said you had it entering in a Form Field, there weren't any forms in the database that you sent me.
If you can Update the datebase that I posted with what you have doen I will take a look at it.


----------



## lntech (Sep 24, 2010)

You will need to combind these two files


----------



## lntech (Sep 24, 2010)

Here is the form. I had to delete a few things to make it fit.


----------



## OBP (Mar 8, 2005)

Intech, the only thing in the second database is the Switchboard and in the first database there are no Scandata records in the table.


----------



## lntech (Sep 24, 2010)

Try this. Sorry this is all messed up. 
This all I have created as of today.


----------



## OBP (Mar 8, 2005)

Intech, I think that you should be Scanning the data in to the "ScannedDate" table rather than the Student data table. I thought that you had a form that would take in the Scanned data, if so it should be based on the ScannedDate table, the form can automatically enter the date and Time when the scan is entered.


----------



## lntech (Sep 24, 2010)

That's what I'm having a hard time figuring out how to do. Linking everything together. How do I set it up to have their ID scanned into the scanneddate table?


----------



## OBP (Mar 8, 2005)

Well if it can be done in a form or query you just create a form or query based on the scanneddate table, as long as you can get the Scanner to talk to Access and put the data in to it I can get it in the table and then bring it all together for reports etc.
But I can't help you with the scanner part as I have never used one.


----------



## lntech (Sep 24, 2010)

Honestly, right now my head is spinning. I don't know where to go from here. Even if we took the scanner out of this equation and just had the students key in their numbers, I'm not able to relate this to the ScannedDate table. The Query I have only displays the information in another query, not a table, which I would then base the record off.


----------



## OBP (Mar 8, 2005)

Intech, don't worry we will get to fix this.
First of all the query that you currently use to get the Scanned data, what Table is it based on?
You can see the table when you look at the Query in design view.
All you have to do is create the same kind of Query using the Scanneddate table.
Run the query (or a a form based on the query) and see what gets put in to the Scanneddate table, if data currently goes in the the original table it should also go in to the Scanneddate tabel using the new query.
What I can't test for you is whether data goes in to the current table using the current query.


----------



## lntech (Sep 24, 2010)

The query I use for the parameter is Q-StudentData and it is getting info from table StudentData. When I enter the student number in the parameter box, it opens up the Q-StudentData with their information but the date is not filled out. When I enter another student ID, is also opens up the Q-StudentData with their information but the previous student information is no longer there. There is not a running list. I've created a new query for the ScannedDate table now...how should I relate this to the Q-StudentData that has the parameter?


----------



## OBP (Mar 8, 2005)

OK, I think I understand your confusion now. You are usinga "Select" query, which is used to select and Display data. You need to use an "Append" query which is used to add records to the Table.
I have added an Append Query called "Add-ScannedDate" to my original database which shows you how to join the 2 tables (see the Main Meno>Tools>Relationships & the Q-StudentData query).
When you run the new Add-ScannedDate query it asks for the StudentID and puts it and the Current Date in to the ScannedDate table


----------



## lntech (Sep 24, 2010)

I'm glad you know what you're doing. Thanks again for all your guidance on this.
In my form MainMenu, I originall had this line of coding when the students click on the button...

Private Sub scan_Click()
On Error GoTo Err_scan_Click
DoCmd.OpenForm "FormStudentData", , , , , acHidden
Exit_scan_Click:
Exit Sub
Err_scan_Click:
MsgBox Err.Description
Resume Exit_scan_Click

End Sub

Now, I would want to relate the FormStudentData to the Append Query correct? I do not see an option to add the Append Query anywhere in order for the updates to occur.


----------



## OBP (Mar 8, 2005)

No you do not need to relate the FormStudentData to the Append Query, you either run the append query which puts the data in to the ScannedDate or you create a form based on the ScannedDate that can add the records instead.
To view the data from both tables look at the Q-StudentData query, it shows the data in there.


----------



## lntech (Sep 24, 2010)

Ok, I think I'm almost done. One last question. I have everything in order, how do I make it so when the program is opened up, the form is the only thing visible?


----------



## OBP (Mar 8, 2005)

You can go to Main Menu>Tools>Startup Untick the "Display Database Window" and then in the Display Form/Page box select whichever form you want open.


----------



## lntech (Sep 24, 2010)

Thanks! I guess there is one more question. I'm trying to use coding from another database but it's not working. What I'm trying to do is when a student enters their ID and there is no information on them, a msg box pops up. This is what I'm using in the code builder from Add-ScannedDate query. When I enter a fake number, nothing happens.

Private Sub Form_Open(Cancel As Integer)

If IsNull(StudentID) Then
MsgBox "There are no records for this student"
GoTo 1
Else
End If

Dim stDocName As String
stDocName = "DailyLog"
DoCmd.OpenReport stDocName, acNorma
GoTo 1
1
'DoCmd.OpenForm "MainMenu"

End Sub


----------



## lntech (Sep 24, 2010)

The code builder wasn't from Add-ScannedDate query. It was from FormScannedDate. Sorry


----------



## OBP (Mar 8, 2005)

I think your code is either generating an error ( you do not have an error trap) or it is continuously looping.
The Goto 1 cmmmand does not go to 1 because 1 should be
1:

Also you do not actually need the Else statement.


----------



## lntech (Sep 24, 2010)

Is the FormScannedDate the correct place to have this coding? I feel like it has nothing to do with the MainMenu. Nothing happens still. When I open the FormScannedDate by itself, then I get the msg box.


----------



## OBP (Mar 8, 2005)

What is the Main Menu for?


----------



## lntech (Sep 24, 2010)

The main menu is what opens up with the command button that opens the Add-ScannedDate query.


----------



## OBP (Mar 8, 2005)

If this line is correct
'DoCmd.OpenForm "MainMenu"
then the ' at the start will prevent it from opening the form.


----------



## lntech (Sep 24, 2010)

This is what I have so far. I'm still unable to get the msd box to pop up when a wrong number is entered.


----------



## OBP (Mar 8, 2005)

Intech, I have modified the Relationship between the table, so it will only allow users to add a valid StudentID to the table they get a System Query message telling them that 1 Record is not being Appended to the table, which you can explain means they made an error or they are not in the database yet.
Is that a sufficient message for you?


----------



## lntech (Sep 24, 2010)

That works! Thanks.


----------



## OBP (Mar 8, 2005)

Here it is.


----------



## lntech (Sep 24, 2010)

Thank you for all your help with this! I think we are good to go now. Much appreciated!


----------

