Form not working

[keri]

I have got myself into a total muddle over this and not sure where to
turn.

I have 2 tables;
Customers - which has a primary key of customerID
Appointments - which has a primary key of AppID

I have created a form to enter appointments made (with customers).
This is based on the two tables above (one to many relationship exists
between the account ID fields)

The idea is that AppID is an autonumber filled in when I open the form.
Then the user selects the outer postcode of the customer) from a combo.
This populates a list of customers in a second combo and the user
selects the customer the appointment is with. These details autofill
fields on the form with the customer address details etc.

However if I do not have an AccountID field on the form it does not
work. If I do have an accountID field (populated automatically by the
selction from combobox 2) I get an error saying;

"Te changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or
relationship."

I don't know whether this is because the db is presuming I am trying to
re-add the account to the accounts table. Or something else! I don't
know how to fix this - I am not trying to add the account just view the
details of the account and record in the appointments table that I have
made an appointment there!

Thanks.

[Phil Stanton]

I,m guessing that the second combo box is bound to AccountID or is it
CustomerID.
This should not be bound and a bit of code written After update.
Assume you rename the Combo to CustomerIDRelay
Assume you have another control on the form called CustomerID and bound to
CustomerID

Then you need
Sub CustomerID_Afte rUpdate
DoCmd.GotoContr ol "CustomerID "
DoCmd.FindRecor d CustomerIDRelay
End Sub

HTH

Phil

"keri" <ke*********@di ageo.comwrote in message
news:11******** **************@ f1g2000cwa.goog legroups.com...

>I have got myself into a total muddle over this and not sure where to
turn.

I have 2 tables;
Customers - which has a primary key of customerID
Appointments - which has a primary key of AppID

I have created a form to enter appointments made (with customers).
This is based on the two tables above (one to many relationship exists
between the account ID fields)

The idea is that AppID is an autonumber filled in when I open the form.
Then the user selects the outer postcode of the customer) from a combo.
This populates a list of customers in a second combo and the user
selects the customer the appointment is with. These details autofill
fields on the form with the customer address details etc.

However if I do not have an AccountID field on the form it does not
work. If I do have an accountID field (populated automatically by the
selction from combobox 2) I get an error saying;

"Te changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or
relationship."

I don't know whether this is because the db is presuming I am trying to
re-add the account to the accounts table. Or something else! I don't
know how to fix this - I am not trying to add the account just view the
details of the account and record in the appointments table that I have
made an appointment there!

Thanks.

[keri]

Thanks - i've now solved this problem and found another huge one!
I thought my whole form was working but NO that would be too simple!

I can fill in the whole form and at the bottom of page 2 I have two
buttons
- Add another appointment - save and open new record and go to page 1
- Finished entering appointments - save and close

If I click "finished entering appointments" the appointment details are
saved to the table fine. However when I go back into the form my 2nd
combo box doesn't work (it is blank) and the customer details (supposed
to be populated by the 2nd combo box) show the details from the saved
appointment. I can get the form working again by clicking in and out of
design view, yet when I enter another call after doing this it
overwrites the first call stored in the table.

Or;

If I click "add another appointment" I am taken back to the beginning
of a new form page fine. However when I start using the combo boxes to
select the customer details the 1st combo box changes the record back
to the 1st saved record when use it. (If i continued to fill in the
form and save this would overwrite the first record stored in the
table). When this happens I can adjust the record number back to "2"
and then I can save a second record.

But none of this is supposed to happen! Any ideas?

[keri]

I seem better at writing problems into my database than writing
solutions. Sorry!

I have now found that no matter what I enter in my first combo box
(outer postcode area eg DL1) the combo box that is populated from a
query based on the selection in the first combo box does not change.
Instead it only shows me customer details based on the entry in the
first combo box of the last saved record!

I'm trying everything I can to solve this but only seem to be making it
worse. I am so so thankful for all of the help on here, so thanks guys.

[Wayne Gillespie]

On 4 Dec 2006 11:28:34 -0800, "keri" <ke*********@di ageo.comwrote:

>
I seem better at writing problems into my database than writing
solutions. Sorry!

I have now found that no matter what I enter in my first combo box
(outer postcode area eg DL1) the combo box that is populated from a
query based on the selection in the first combo box does not change.
Instead it only shows me customer details based on the entry in the
first combo box of the last saved record!

I'm trying everything I can to solve this but only seem to be making it
worse. I am so so thankful for all of the help on here, so thanks guys.

You will need to requery your 2nd combo after the first combo has been updated,
in order to populate the 2nd combo with the related records.

In the AfterUpdate event of the 1st combo put -

Me.NameOf2ndCom bo.Requery
Wayne Gillespie
Gosford NSW Australia