Complicated auto populate

[duncthepunk]

I really hope this hasn't been resolved before but I am unsure of the teminology so have been unsuccessfully using search tools on this website and Google.

I work in the ICT dept. at a high school and this year all yr 11 and 12s recevied a laptop. The laptops are owned by the school so I am trying to to use Access to track them.

I have created a database in Acess 2010. I want to do two things.
1) Track current & previous laptop assignment
2) Log faults aggainst the laptop or student

My table relationships: (FDL means Fault, Damage or Loss)




I now need to create a form (with a subform I think) which auto populates. Here is the hard part. I need to be able to punch in a date and the Student's laptop number and then I need access to find the student details.

Can this even be done? What am I trying to do (keywords)? Can any one suggest good tutorials for this?

Something like this:

This looks like it is working but the date field is not used for the query.

Thanks

[duncthepunk]

Am I going about this the right way?
Anyone?

[wdrspens]

Are there a great number of students or relatively few? If not too many you could make the Student Name a combo (or possibly a list) box so that when you click on the appropriate name all the details of that student would populate the relevant fields on the form. Of course each name in the combo or list box would have to be unique so you would probably have to base the combo box on a query involving the first and last names of the student and possibly an id also.
Good luck

[duncthepunk]

Thanks wdrspens. That would be a work around but there are 1100 students to it would be bery annoying scolling throught the list. My current list querry looks at the Assignment table and excludes any record with no 'Unassigned Date' via 'Is Null'. Do you know if there is a way to get a flexible querry to look for any laptops that were assigned on a given date? (needs to look at both Assigned Date and Unassigned Date).
Thanks

[wdrspens]

If you enter in the query in design view on the criteria line under the "Date Assigned" field of the Assignment Table '[Enter date (dd/mm/yyyy): ]' substituting whatever format you use for showing dates (I use the English format), that will bring up a message box inviting you to enter the date for which you wish to search. You can not only use a specific date, but also a range of dates by appropriate adjustment to the wording. I hope this helps, but any further and you will be going beyond my comfort zone.

[duncthepunk]

Ok. I think I am nearly there.
I've worked out I need a combobox. I need it to display tblAssignment.StudentNotebook# but write the FK of tblAssignment.ID to tblFDLData (my tables have been renamed since my first post).
Does that make sense?

[Access_Blaster]

Just a side note on naming conventions. Symbols such as #, /, have other meanings in Access like date and math, so avoid using them in field names. Spaces in your field names will give you coding headaches as you move forward. Last suggestion name your primary keys, don't just call them ID, try using StudentID instead of just ID again it will cause confusion when coding.
Good luck with your project.

Richard

[pkstormy]

In the Relationships view, you may also want to double-click on the relational line between your tables - edit join type and set the join type to "Include all records from table 1 and only those matching records from table 2" (or vice versa) where appropriate. After doing this, you will see an arrow in the join line to indicate the direction. I'd also keep the joining fields as the same name in each table (ie. StudentID field name joins to a StudentID field name). Makes creating queries/code a little bit easier to see the same names but not a necessity.

And please get rid of the # key in any field/table names!