Do I need a macro for this? If so, how?

[Surfette]

Hi,

Here's a sample of the database that I'm working on. I'm trying to help teachers take attendance of all of their students and so have the following tables.

tblAdmin - List of Teachers and their IDs
tblAttendance - Courses, Student IDs, the Date and the Status (tardy, etc..)
tblCourseInfo - List of Courses and the Teachers teaching the course
tblEnrollment - List of the Courses and the students enrolled in them
tblStudents - Student IDs and their names

The form that I want is a "Course Information Form" that will

(1) Display all the students enrolled in their course
(2) Allow the teachers to take attendance everyday while keeping all the previous records

I've attached my sample database. I really appreciate ANY help any of you can suggest.

Thanks

 

[Rod D]

Sample attached

Hi Surfette

Attached is your sample - updated with what I think you wanted.

I created an Append Query that takes the date from the main form, and adds the list of students for a particular course to the attendance table. I have taken an optimistic approach and set all students to "Present". The staff can then change the status to "Absent" or "Late" as required.

I have changed the layout of the form a little, but kept as much as possible to what you had in your original sample. I have, however, added some lookup fields so that the staff work with course and student names, rather than ID's as this is more intuative (and to be fair, I was having fun and got carried away... )

The button "New Register" creates a new register for the course for the date entered in the Date Field. Previous registers can be accessed by changing the date and course name.

Post back if you need more clarification.
Regards
Rod

 

[Surfette]

That's Awesome!

Hi Rod,

That's awesome! Thanks so much.

The appendquery is exactly what I needed! Even though the names are not showing up on my attendance form, at least on the attendance table I can see all the information attendance date being logged in. (FYI, the names are showing up as #Error for some strange reason.)

So, can you teach me (step-by-step) how you created the Append Query and how you attached it to the button? And, what exactly does an Append Query do??

Many thanks for your help!!!!!!!!

Surfette

 

[Surfette]

Build Event

Hi Rod,

I tried to duplicate the formula you used to display the Admin Name on the "CourseInfo and attendance" form.

What I did was clicked on the AdminIDfield and under Control Source and I typed in the formula that you typed in.

=DLookUp("[aFirstname]", "Admin", "[AdminID]= "&Forms![CourseInfo and attendance]!AdminID)& " "&DLookUp("[aLastname]" , "Admin", "[AdminID]= "&Forms![CourseInfo and attendance]!AdminID)

When I close the design view and try to go back to the Form view, the file closes.

One time I was able to enter it successfully and it gave me the following message...

The expression you entered contains an invalid syntax. You may have entered an operand without an operator.

Do you know what I'm doing wrong?

Thanks.
Surfette

 

[Rod D]

Hi Surfette

Rather than type up a long post, I will put all the detail into a Wod doc and post it back here for you.

I will take a look at your last post, at a glance, it looks OK, but will delve a little deeper.

Watch this space...

Rod

 

[Rod D]

Your user manual...

Surfette
Attached is a host of files on how it was done. They should be read in the order:
1) Tables & Query
2) Forms & Fields
3) Button & Code

Hope this helps your understanding.
Rod

Mods - If having these files on the board creates a problem, please delete them after a few days...Ta

 

[Rod D]

Surfette

Your Recordsource (as described below) is in the wrong field...

What I did was clicked on the AdminID field and under Control Source and I typed in the formula that you typed in.

=DLookUp("[aFirstname]", "Admin", "[AdminID]= "&Forms![CourseInfo and attendance]!AdminID)& " "&DLookUp("[aLastname]" , "Admin", "[AdminID]= "&Forms![CourseInfo and attendance]!AdminID)

What you are attempting to do is causing a circular reference (endless loop) where the value of AdminID is being used to calculate itself...

Access also likes spaces between the "&"'s, but this is usually corrected automatically.

If you create another textbox (called AdminName say) and put the dlookup in the recordsource, I am sure you will have better luck...

Regards
Rod

 

[Surfette]

Still trying

Hey Rod,

Just wanted to say a big thank you for putting together a very detailed and easy to follow manual of what you did! The append query, field calculations, and form instructions were very helpful.

I'm trying to replicate your instructions but somehow I'm not doing something right. Now, my student and admin names show up as #Error.

And when I hit the "New Register" button I get a message box that says:

Enter Parameter Value
Students.StudentID

If I type in a number it tells me that I'm about to append 0 rows. I'm redo the database again and see if there's anything I'm missing again. I'll keep you posted on my progress!

Cheers,
Surfette

 

[Surfette]

Do I need a Macro for this, if so how?

Hi Rod,

Thanks again for the manual. But I'm running into a problem that I'm hoping you can help me with.

Once I finished everything and click on the form a Microsoft Visual Basic messagebox pops up with the following message.

You can't assign a value to this object.

So, I click debug and the following code is highlighted in bright yellow.

Me!CourseName = DFirst("[CourseName]","CourseInfo")

I've attached the database in case you or anyone else need more information.

Thanks a bunch.
Surfette

 

[Rod D]

Nearly there...

Hi Surfette

Attached is a word doc that shows all the changes you need to make.
I haven't had time to make the document "pretty", just tells you what you need to do -

If you make the suggested changes, the VB errors and field #Error's will be resolved ...

Regards
Rod

 

[Surfette]

Perfect!

Hi Rod,

Thank you for the updated manual. Everything worked out well except for the Student Name in the subform. Somehow, I couldn't get the DLookUp to work right.

What I ended up doing was adding the FirstName and LastName to the Attendance Query for the Subform. Then, I dragged the two fields into the subform. Finally, I right clicked on the gray area > clicked unhide columns > and hid the StudentID. I made sure that I locked the FirstName and LastName so that teachers would not be able to change the names.

Other than that... the AppendQuery works like a charm! And I love how the DLookUp query worked so well in the Form. I hope others are able to tap into your manuals as well!

Thanks again for all your help (on behalf of all my students here in China)

Surfette

 

[Rod D]

I'm pleased you got it working and that you are happy with the result.
Glad to have been able to help.

Regards
Rod

 

[dayna]

Heya,

I've been working with Rod's lovely tutorial, but I'm running into a problem: Access is not allowing me to use an append query as the recordsource for my Attendance Subform. I've tried doing it several different ways.

The only way I can get my attendance form to work is to base the subform on a select query - exactly like the one described in the tutorial, except without making the query an append query. My problem then, of course, is appending the record to the Attendance table.

Any suggestions?

Thanks!