Subform not allowing record add on new records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I add a new customer, click on button to open subform (made with wizard).
Add new contact data in subform, but when I exit, I get this message: [you
cannot add or change a record because a related record is required in table
'Customers']. I understand the error, but an very new at this, so don't know
how to make the Customers record update so it will take the contact record.
The contact subform also has a subform, and I have the same problem there.
Probably is easy, but I am lost. (the second subform is not button click,
but an open subform of the contact form - so I will need a different way to
auto update maybe?)
 
Just to make this a little more clear. If I exit out of the form after
entering the new customer, I can then enter the contact information because
the customer table has updated. I do not want to turn off integrity, I just
need the table to update with the new customer when I enter the Contact form,
and the same with the New Contact when I enter the Contract form from the
Contact form.
 
One way of doing it:

in the Before insert event on the subform you have to add code to load
the customerkey in the client table with the ID or whatever field you
are useing as the link between the customer and client tables.

me.customerkeynumberincontacttable =
Forms![formnameofcustomerform]![fieldnameofthecustomerkey]
 
I am not up on Access's coding standards, so please bear with me on this.
To the right of the equal sign, I would put:

Forms![Customers1]![CustomerID]

The left side has me clueless. What do you mean by the
"me.customerkeynumberincontacttable?"
The contact table name is Contacts, and the contact form name is Contacts1.
What is "me." ?
The linked field is Contacts is CustomerID. So would that be
Contacts!CustomerID?

Sorrry I sound so ignorant about this, but it is because I am. Worked with
databases for a long time, but never with Access. Looks like it has power,
but first I have to learn it.
 
The only way that Access itself will maintain relational integrety is
if the form/subforms are truely parent/child relationships, maining the
sub form is actual declared and showing on the form that the parent
record (or parent record key) is part of the data.

If the subform is being called and opened as a new form, then somehow,
you as the programmer need to supply that id number. That is why it is
giving you the error on that subform that you are opening up.

It would probably help right now if you could give us the structure of
you three tables. We don't need all the information but just tablename.
key name for that table and the fieldname for the field that would
link the second table to the first table.

Because of the error message you are getting we can tell that you have
established relationships between the tables. We need to have the
tablenames and field names of the linked fields.

Then we can more clearly give you an example of what we are talking
about.
 
Table -key field
Customers CustomerID

Contacts CustomerID
Contacts ContactID

Contracts ContactID
 
1) The contracts table ALSO needs to have the customerID foreign key in
it just as the Contacts file has

2) in the Before insert event on the subform you have to add code to
load
the CustomerID in the Contacts table with the customerID of the
customer to which this contact belongs.

me.CustomerID = Forms![Customers1]![CustomerID]

3) exactly the same process must be done on the Contract form

4) The me. is the way to address the fields of a subform/form.
If you type in me. (me with a period) then the properties and
methods AND fields that are available to address will appear in a
dropdown. If you continue to type, in this example, Customer you
will see CustomerID shown . If you hit the "tab" key it will finish
typing CustomerID for you.

If you put another period then you can see the properties etc of
customerID that you can address (For instance enable, or visible or
background color, or font size, etc.) This aspect of period followed by
properties etc. can/will continue until there are no more properties
that are addressable. For instance if you are on a main form with a sub
form, it can go me.subformname.form.fieldname.propertyname.





Reply
 
Thanks for all the help. Looks like I need to learn quite a bit. I will try
this out and pick your brains if I have any more questions. What is the
proper way of posting. If I have a question on the same project, but a
different problem, do I start a new post, or add on to this one?

One more question before I go: why do I need the CustomerID in the Contracts
Table? I did have it at one point ( and linked to the Customer file), but
when I used the Wizard to build the Forms, I couldn't get it to work, so I
pulled it. By the way, the ContactID, is the CustomerID with an *1 added to
the end. If there are two contacts, then the second contact would be
CustomerID*2, etc...
 
If you want a true parent child relationship and referential integrity
then it will have to be there. The reason the wizard didn't work is
because you were probably building the subform independent of the
parent form so the wizard could not make any connections for you AND if
you were trying to have it link via the definition you have of
contractid it would not work also.

For any of the wizards to work or even the query builder to work, then
the linking fields HAVE to be compatable and identical - having an
extra character or 2 or 3 on one will distroy the automatic linking
aspect. There is no need to change your definition of ContractID but if
Access is to automatically handle the linking etc, you will need
customerID in there and it will have to be maintained either by you
when in an independent form/subform as you have it now, or a parent
child form/subform relationship when Access will maintain it for you.

An example of what I mean. If you tried to write a query right now that
shows customer name and contracts, you will NOT be able to simply put
the two tables on the wizard and drag and connect the two customerid
fields to automatically create a link between the two tables.
For the Contacts tables you could do that, but NOT for the Contracts.
If you tried to design a form right now that had Customer name on the
top and a subform on that form with Contacts AND another with contracts
you could not. You could get the Contacts to show up just great and
have a parent child relationship, BUT the contracts one would NOT link
because of no linking criteria.

If it is a different question, the norm is to start a new thread. That
way the topic line can be more specific and you may get responses more
quickly. I will keep this one on my watch list.
 
Back
Top