Hi,

I've setup a db according to the structure layed out in the wiki regarding normalization.

As a certain customer can have different type addresses, I have a form to fill in the name of the customer and a subform to fill in the different addresses. On the form are also subforms for email, phone and rebate.

When inserting a new customer I copy the custID using code to the different subforms in order to get the correct relations to work.
This results in a couple of pop-ups: can't add or change record because there is no related record in tblCust.
I can click "ok" on every pop-up and everything goes through. I guess I can set warnings off in order to prevent the pop-ups.

Another problem I have with this is when the user decides to stop entering of the new customer (for wathever reason), a record is added to tblCust, tblCustAddress, ...

Long story short: can someone guide me towards a "standard"-setup for entering a new customer or tell me what I'm doing wrong?
Thanks

(IMG:style_emoticons/default/welcome2UA.gif)

as long as you set the relationship one-to-many, you have to add the parent record before yo add the child(s), but you can trap this error using something like this:

the other solution is to make your form footer invisible till the user add the parent record.

as soon as you setfocus on the subform after adding the mainform record access will save the mainform record for you
and because you can't use Undo for cancel the event:
there are two approaches to handle this, you can use unbound form, or you can use TEMP tables and set the record source of you forms/ subforms on the temp tables, then if the subform has records you can use SQL/VBA code to append the parent/child records from the temp table to the main tables, if not cancel the entire entery operation and delete the records in temp tables


hope this helps...

This post has been edited by 2ME: Nov 10 2010, 05:33 AM

Thanks for the code regarding the error-trap. I'll have a look at it.

Regarding the other issue: how can I enter for example 2 addresses using unbound forms and subforms? (I didn't think this was possible.)

hi,

you can find the unbound forms need a lot of code involved and you can't add more than one address, the reason that you can't have unbound form in datasheet or Continuous Forms view,so I don't use unbound controls in this case, but you can use temp tables and less code to get it to working
the attached DB, may help to handle preventing the subform records to be added before the mainform, and how to use Temp table, take a look at the code on exit click in form add order

Hope this helps...

This post has been edited by 2ME: Nov 10 2010, 06:22 AM

Hi

Your statement:- "When inserting a new customer I copy the custID using code to the different subforms in order to get the correct relations to work."

This indicates a structure problem in that you have NOT set your relationships correctly between your tables.

If you set these relationships up correctly then the CustomerID will automatically be populated in the related table(s)

Can you upload your Db so that we can give you a better solution??

Mike,

Setup should be ok:

tblCust:
CustID (PK)
CustName
...

tblCustAddress:
CustAddressID (PK)
CustID (FK)
CustAddress
AddressTypeID (FK)
...

tblAddressType:
AddressTypeID (PK)
AddressType

I have a one-to-many between tblCust and tblCustAddress based on CustID; and a one-to-many between tblAddressType and tblCustAddress based on AddressTypeID

In the entryform I have a control for the custID and a subform for the address with also a control for the custID (parent-child relation between form and subform on CustID).

When entering a new customername, the CustID is filled in (autonumber), but on going to the control for the address in the subform the new custID is not copied automatically to the subform as the record (from the form) is not yet saved to the tblCust.
And how would Access know that it needs to fill in the CustID in the subform, because there is always the possibility that there are no corresponding records on the many side?
Or am I completely wrong about this?

Hi

When you add a record to the Subform the CustID will populate

The Record will be saved when you add a New Record or Exit the Form

hi,

set the subform data master/detail property to:

link child field : custID
link master fields: custID


hope this helps...

@Mike:

must have had a major hangover when I was creating the form for trying to code the obvious
thanks

@2ME:

form and subform were setup that way, for the rest see the above comment

Hi

No problems I have many days like that