By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

Having trouble with verifying if data exists in an Access 2013 table

P: 2
OK, so I'm helping my wife out & trying to develop an Access 2013 database that will be used on Windows PCs. They're setting up a special tutoring lab at her college where remedial students can take online refreshers for placement testing, so they need to know how many students come in on any given day, what their purpose is for coming to the lab, & how long they're in the lab.

I know I can't make it 100% foolproof, but I was trying to automate as much as possible the process. Essentially, the students type in their name & click a button. I was hoping to set up either a macro or a VBA module so that, when the button is clicked, it checks the existing table to see if the student is currently "logged in" for the lab; if they're already logged in, it'll display 1 form to say they're now "logged out", & if they're not already logged in it adds a new record to the table.

I'm trying to take it in steps, but I know I'll have to have it eventually check 3 of the fields (as they're only "logged in" if the table has an entry for that day with their name & a start time but no end time), but I'm having trouble getting it to just verify whether the name is there or not.

I thought I could do this with DLookup, but I'm not sure I have the syntax right on it.

Currently, I have the following for the database:
-- data is stored in the table "Students_in_Lab", which has the fields "ID", "Student Name", "Date in Lab", "Time Arrived" & "Time Left" (I haven't decided yet whether to use a calculated field in the table called "Total Time in Lab", or calculate that when the reports are run)
-- the main form is "Student Login", which has the textbox "Student_Name" & the command button "CommandOK"
-- the subsequent forms are named "Login Option" and "Logout Option"

My current code is:

Private Sub CommandOK_Click()
If Not IsNull(DLookup("[Student Name]", "[Students in Lab]", "[Student Name] = '" & [Student Login].Student_Name & "'")) Then
Form_Load ([Logout Option])
Else
Form_Load ([Login Option])
End If

Right now, it gives me the error message "Compile Error: Method or data member not found", with the ".Student_Name" section right after "[Student Login]" highlighted.

Not sure where I went wrong with this, or if I'm even on the right track with it. Any thoughts?
Nov 5 '16 #1

✓ answered by PhilOfWalton

Without going into too much detail, I think you need to reconsider your method completely.

A few general pointers before I start.
IMO there should never be spaces in field names. You have to put square brackets[] round the field name and that complicates things.
Same thing applies to table names.
Also consider the name of a field. You have to type it out each time. Do you really need "Students_in_Lab", would not "Students" do just as well?
Although "ID" is perfectly OK, wouldn't "StudentID" be much more descriptive.

Enough of that.

Now we get on to the area where so many people go wrong - Normalisation, and I suggest you read up on this as it is the key to successful relational database design.

Briefly it means one table for one thing that has an independent existence. In your case, a Student with a name, birthday, sex, hair colour etc. exists in his or her own right. It is irrelevant whether or not they are in or out of your wife's lab.

You need a table of Students with the StudentID as an AutoNumber and the prime key.
You need a table of InOut times with StudentID as the foreign key and LoggedIn and LoggedOut times.

So in principal the student has to exist in the student table before he can log in. We then check the In/Out status for that student. If the last entry for that student was in the "Out" column, a new record is created with the new "In" time, and if the last entry was in the "In" column, the "out time" is entered.

Phil

Share this Question
Share on Google+
1 Reply


PhilOfWalton
Expert 100+
P: 1,430
Without going into too much detail, I think you need to reconsider your method completely.

A few general pointers before I start.
IMO there should never be spaces in field names. You have to put square brackets[] round the field name and that complicates things.
Same thing applies to table names.
Also consider the name of a field. You have to type it out each time. Do you really need "Students_in_Lab", would not "Students" do just as well?
Although "ID" is perfectly OK, wouldn't "StudentID" be much more descriptive.

Enough of that.

Now we get on to the area where so many people go wrong - Normalisation, and I suggest you read up on this as it is the key to successful relational database design.

Briefly it means one table for one thing that has an independent existence. In your case, a Student with a name, birthday, sex, hair colour etc. exists in his or her own right. It is irrelevant whether or not they are in or out of your wife's lab.

You need a table of Students with the StudentID as an AutoNumber and the prime key.
You need a table of InOut times with StudentID as the foreign key and LoggedIn and LoggedOut times.

So in principal the student has to exist in the student table before he can log in. We then check the In/Out status for that student. If the last entry for that student was in the "Out" column, a new record is created with the new "In" time, and if the last entry was in the "In" column, the "out time" is entered.

Phil
Nov 5 '16 #2

Post your reply

Sign in to post your reply or Sign up for a free account.