Lost Control

[Guest]

I have a main form and subform (actually, 4 subforms with tab controls)
Referential integrity is set on a field called JobNo
Initially, I changed the value in the JobNo field by using the find facility.
I have now set up a combo box instead but this has created a problem
The combo box allows me to successfully change the JobNo value (and subform details
but the remaining fields in the main form do not change
Consequently, when I change tabs in a subform I get a message "The changes you requeste
to the table were not successful because they would create duplicate values in the index,
primary key, or relationship..........
I must be making a fundamental mistake but I haven't the experience to recognise it
Please help
Regards
savri

 

[Rick Brandt]

I have a main form and subform (actually, 4 subforms with tab controls).
Referential integrity is set on a field called JobNo.
Initially, I changed the value in the JobNo field by using the find facility.
I have now set up a combo box instead but this has created a problem:
The combo box allows me to successfully change the JobNo value (and subform details)
but the remaining fields in the main form do not change.
Consequently, when I change tabs in a subform I get a

message "The changes you requested

to the table were not successful because they would

create duplicate values in the index,

primary key, or relationship.........."
I must be making a fundamental mistake but I haven't the experience to

recognise it.

Yes you are. Find was not changing the value of JobNo. It was sending you
to a completely different record that had a different JobNo. Your ComboBox
is actually changing the value of JobNo *for the current record*. While a
ComboBox can be set up to do form navigation, it does not perform that
function by default. By default it is just a dufferent way to enter data
(like a TextBox).

Currently your ComboBox is bound to the field JobNo. That's not what you
want. Change the current ComboBox to a TextBox and then add another
ComboBox making sure the Wizard is enabled on the Toolbox bar before doing
so. When the ComboBox Wizard comes up there should be an option for "finding
records" or something similar (sorry I don't have Access available to check
the exact phrase).

Once you have completed the Wizard you will have an unbound ComboBox whos
only purpose is navigation.

 

[Guest]

Thanks Rick for your suggestion (which I have implemented
There still remains an unresolved item
I based my original combo box placement after consulting MS "Step by Step" guid
for Access 2000 (published1999). The scenario relates to a fictitious enterprise calle
"Sweet Lil's". On page 100 there is an exercise in converting the CUSTOMERID text box (on the Order form
to a combo box. The result is that any change made in the CUSTOMERID value also changes other fields in the mai
part of the form
Is there an explanation for this that I can adopt for my form
Regards
savril

 

[Rick Brandt]

Thanks Rick for your suggestion (which I have implemented)
There still remains an unresolved item:
I based my original combo box placement after consulting MS "Step by Step" guide
for Access 2000 (published1999). The scenario relates to a fictitious enterprise called
"Sweet Lil's". On page 100 there is an exercise in converting the

CUSTOMERID text box (on the Order form)

to a combo box. The result is that any change made in the CUSTOMERID

value also changes other fields in the main

part of the form.
Is there an explanation for this that I can adopt for my form?
Regards
savril

I am unfamiliar with that book. However; I would be surprised if in that
example the form's other entries are really being changed when you change
the CustomerID. While that is possible to set up it would violate proper
database design principles.

What is more likely is what I said your Find process was doing. If they
have set up the ComboBox in that example as a "Navigation tool", then the
other entries on the form will "Appear" to change but it is only because
the form is moving to a different record, not because the values in the
controls are being modified.