Junction Table IDs via a form

[markmarko]

So I have made a form for inputting sales orders which has controls referring
to numerous tables, one of which is a junction table connecting
tblAccountNumbers with tblCustomers. Once user has entered in account info
and Customer info, Access makes the new record in the junction table, and
therefore has an autonumber ID for that record in the Junction table.

My problem is that I also have a subform which gets data before Account data
& Customer data is inputted. When the subform gets focus, an error message
appears stating that it can't find the Account_Customer Junction ID (since
that data hasn't been entered yet, Access hasn't created it).

is there any way to force the junction table to create a new record, so that
my user will be able to go into that subform prior to entering customer &
account data?

 

[Beetle]

You may want to rethink your db structure. Having a many-to-many relationship
between customers and account numbers (which is implied by the fact that you
have a junction table) seems highly unusual to begin with. A customer could
certainly have more than one account, but to have an account number
associated with more than one customer would seem, on the surface anyway, to
be a bad idea.

However, if you do have some scenario where that would apply, then typically
the junction tables only purpose would be to define that relationship. If you
are trying to add new records to the junction table that DON'T include the
accountID and CustomerID, then you have a design problem.

 

[markmarko]

I understand your comments about the design, though it's ok for us. Our
company is the in the cable TV business, so for us, an account # is connected
to the dwelling, not the person in it.

The problem seems to have gone away, which is both pleasant and disconcerting!

 

[markmarko]

Well the problem hasn't gone away after all.

Sean, in answer to your question, it's not that the user won't be inputting
the customer & account info, it's that they'll be entering info in the order
it appears on our printed paperwork, and that means going into a subform or
two prior to the customer & account info being entered.

Hey, I just has an idea... What if I put in default values into the form
fields for cust & account #... Would that create a junction id? Would that
make a bunch of empty records if someone cancels data entry?

 

[Beetle]

That solution is just going to create a bunch of useless data, along with a
host of other problems. It sounds to me like you need to one of two things.

1) Redesign your forms to reflect the proper data entry order, or train your
personnel to enter the data in the correct order using the existing form.

2) Change the hierarchal structure of your tables. If you have some type of
data that must be entered prior to the Customer/AccountNumber, then the table
that contains that data would typically be the parent (or perhaps in a 1:1
relationship) of the Customer/Address tables, not the child.

In your case, the junction table is the child of both the Customer and the
AccountNumber tables

In a relational database, you can't save data in a child table without some
way to relate it back to the parent table. If you force your system to do so,
you just end up with a bunch of useless "orphan" records.