How (1 Viewer)

Okay, that may have been me again misunderstanding question. One thing can do is build form/subform arrangement.

Main form bound to Courses, subform bound to local table. Set the subform container Master/Child Links properties. Start to enter data into subform and the foreign key field(s) should automatically populate with the key(s) of main form record. Either don't show the foreign key(s) in the subform or lock textbox(es) so users cannot edit.

Since what you have is a 1-to-1 relationship, might be possible to use a single form that has a query joining Courses and local tables as RecordSource. I have not tried this in a while and don't remember if it works. I will have to try again.

Okay, just confirmed, the query results in a non-editable dataset because there is no defined primary key. It does work with two local tables where one has a defined primary key.

June7 said:

Okay, that may have been me again misunderstanding question. One thing can do is build form/subform arrangement.

Main form bound to Courses, subform bound to local table. Set the subform container Master/Child Links properties. Start to enter data into subform and the foreign key fields should automatically populate with the keys of main form record. Either don't show the foreign keys in the subform or lock textboxes so users cannot edit.

Since what you have is a 1-to-1 relationship, might be possible to use a single form that has a query joining Courses and local tables as RecordSource. I have not tried this in a while and don't remember if it works. I will have to try again.

Okay, just confirmed, the query results in a non-editable dataset because there is no defined primary key. It does work with two local tables where one has a defined primary key.

Click to expand...

Thanks. I think I understand the logic. Will digest it, try to implement it and report back in a couple of days. Thanks

Hi here's some information on setting up a standard import that you can automate to import all files in a directory into a table

https://rounduptheusualsuspects.org/?p=1825

You should be able to convert that into importing excel spreadsheets by replacing the DoCmd.TransferText to DoCmd.TransferSpreadsheet

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

Sorry I have not been back for a month but I've now been given slightly different requirements for this database. It's a bit frustrating but in a way, it's slightly simpler.



Will formulate some questions in the next few days. Will definitely need your help.


On a side note, can anyone recommend a good book on database design in general (that would be applicable to Access)?