Unanswered: MS Acces 2010: creat new record in a related table based on input
I am trying to create a database, using MS Access 2010, for a recycling scheme.
Thus far, I have:
main table: date of entry, item ID, status, client ID
item details: make, model, colour etc.
statuses: available, assigned, scrapped etc.
Client details: client ID, forename, surname, whether staff, student or other
Student details: client ID, student number, department, course etc.
Whether the client is a member of staff or a student is [at least I think] provided by selecting from a dropdown list (lookup list?)...
My vision is that if student is selected, a new record is created in the student details table. If staff or other is selected, nothing should happen.
I am however stuck as to how to achieve this...
I am thus wondering if anybody could give me some pointers?
Please bear in mind that until the refresher caused by setting about this project, I have not used MS Access since my school days ~10 years ago: hence the above being somewhat beyond my level of knowledge.
If you are creating a new client record on a bound form and the Client ID is an autonumber (?), then in the form AFTER UPDATE event you can execute an append query on the student table using the newly created Client ID, but only if Student is selected. You will need the determine it the saved record is a new record (or check if the Client ID exists in the Student table) first.
BTW it better not to have spaces in field names, so if it is not too late, I would chance Client ID to Client_ID and Itwm ID to Item_ID.
Thinking about this a little more I am not sure you need a separate Student table. Why not just add the student field to the Client Table.
You could then make the student specific info visible/available for filling in if student is selected.