Junction table record from a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

markmarko - 10 Dec 2007 23:42 GMT

I have 2 tables in a many to many relationship, using a junction table to
match records.

I need to build a form to allow data entry into both forms, plus create the
junction record to match the two.

I'm imagining it working like this:
User types data into all fields (some from one table, some from the other).
User then clicks a button to "Save this data." to trigger a macro.

What I'm stuck on is...
A) How to get the data to update to both forms (so far I haven't been able
to do this other than moving on to the next blank record). Is it possible to
have a macro apply the data into the record?

B) How do I get a macro to create a new record in the junction table to
match the data already entered into the two tables?

Reply to this Message

markmarko - 11 Dec 2007 17:59 GMT

btw - I've seen posts regarding forms for many-to-many's. All I've seen
advocate using a subform. I suppose this may work, but I don't think it'll be
pretty.

I'm trying to make data entry as easy as possible. The form will be made to
resemble the paper documents they'll be entering, and the way the paper form
is laid out mingles the two tables. (ie they enter one field from table A,
the next field is from table B, etc). It's not literally that mixed up, but
it is mixed up.

Also, those two tables are going to have tens-of-thousands of records, so
combo-boxes aren't a great solution.

What I was thinking of was having them type into a temp table, then using
append queries to send stuff to the right tables, including the junction
table.

Any advice on this would be greatly appreciated!

Reply to this Message