Combo Box Problem

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

I have a combo box that holds the customers ID. I also have a duplicate
button that is used whenever a customer reorders because most often they
just order the same products. An unusual thing occurs that I just cannot
figure out. Everytime the duplicate button is pressed the Customer ID in
the combo box is dropped and is left blank. If the user trys to put the
Customer ID back, an error message appears saying that you must save the
record first. Of course you can't save the record with a Null value in the
Customer ID field since its the primary field in the customers table and no
NULL values exist in that table. So there you are, dead in the water! This
happens when I make changes to the form but I have yet to figure out how
this "glitch" creeps into my form. Its very mysterious to me and I have
fought it since I started programming in ACCESS 2000 but I still don't
understand it. Has anyone had any experience with this same problem as it
relates to combo boxes?

I am currently trying to add another combo box to the form and the same
problem is starting to occur with this new one.

Very frustrating!

Charles
 
This has nothing to do with adding New records. I am referring to the
behavior of a combo box when you duplicate a record (using a duplicate
button created by the button wizard). The original contents of the combo
box is dropped. When you try to change it back, you will see an error
message at the bottom of the page that says "To make changes to this field,
first save the record." Well you can't save the record because a Null value
doesn't exist for that field so it won't let you save. You end up in a
catch 22 with this unknown behavior. Has anyone had this problem yet?


Tidepuddle said:
Just set the customer ID number to be re-enter automatically. New records
Click to expand...
have to have a default or a value reset, otherwise you get a blank.
 
If CustomerID is your primary key how/why do you think you can duplicate it.
Primary keys cannot have duplicates. It appears you are trying to add a new
record and duplicate the primary key. I think you have your
relationships/table setup wrong. Looks like you need something like this:

Here's the relevant tables and field names:
tblCustomers
CustomerID*

tblOrders
OrderID*
CustomerID(foreign key)
....
....

tblOrderDetails
OrderDetailsID*
OrderID(foreign key)
txtPartNo
.....
.....

Now when you duplicate the record the Customer will be brought forward.
This may not be exactly what you are looking for but something like this.
Reggie

www.smittysinet.com
----------
 
My tables are set up fine and the combo box has always worked just fine in
the past. I have recently added some new fields, changed some fields from
labels to text boxes to combo boxes, and even added some code behind them.
All of these changes work fine and the form does what it is suppose to do.
No problems result from any of these changes made to the form.

After many hours of working on this problem I have finally dwindled it down
to the following event: As soon as I go in to change the tab order (under
the "View" menu, choose "Tab Order", then click on "Auto Order") thats when
the shit hits the fan. I return to the form to use it, hit the "Duplicate"
button, the combo box that holds the Customer ID goes blank and I am stuck
in the aforementioned catch 22. I literally have to delete the form
entirely and restore from a backup just to get the damn thing to work again.

I know there is no reason to the ryme here and am completely frustrated, any
additional ideas would be wonderful? As it sits now, the form is working
fine, unfortunately, when the user tabs from one field to the next, it sends
them all over the form and is just awful as you can imagine. I have tried
manually changing each individual fields "Tab Index" but this is screwed up
as well because as soon as you change one it changes all the rest of the
fields "Tab Index" number to some other number. Unbelievable!
 
My tables are set up fine and the combo box has always worked just fine in
the past. I have recently added some new fields, changed some fields from
labels to text boxes to combo boxes, and even added some code behind them.
All of these changes work fine and the form does what it is suppose to do.
No problems result from any of these changes made to the form.

After many hours of working on this problem I have finally dwindled it down
to the following event: As soon as I go in to change the tab order (under
the "View" menu, I choose "Tab Order", then I click on "Auto Order") thats
when the problem occurs. I return to the form then click on the "Duplicate"
button, the combo box that holds the Customer ID goes blank and I am stuck
in the aforementioned catch 22. I literally have to delete the form
entirely and restore from a backup just to get the thing to work again.

I know there is no reason to the ryme here and am completely frustrated, any
additional ideas would be wonderful? As it sits now, the form is working
fine, unfortunately, when the user tabs from one field to the next, it sends
them all over the form and is just awful as you can imagine. I have tried
manually changing each individual fields "Tab Index" but this is screwed up
as well because as soon as you change one it changes all the rest of the
fields "Tab Index" number to some other number. Unbelievable!
 
Not sure then. I assume you have a combo box on your form to allow the user
to select a Customer for the current record and that this combo is populated
by the Customer table or a query/sql based on the customer table. I also
assume you have the Control Source for the combo box set to the CustomerID
field of the table/query/recordsource on your form. When you select a
customer from the combo that value is stored in the CustomerID field of the
recordsource you are working with. My confusion comes with how/why is the
procedure trying to set/change the value in your Customer table? Without
knowing more about how your application is setup I can't offer much more
assistance. Sorry!
 
All of your assumptions are correct and I share your sentiments as to why
the combo box is trying to duplicate a field in the customers table as
apposed to the orders table. All my tables are based upon and set up
exactly like the Northwind database, no rocket scientry going on here. It
seems to me I have reached some inherent limitation that I am not aware of
that is causing this problem. Perhaps if I had an idea of what limitation
it is I'm running into I could attempt to work around it, but I have no clue
where to start unfortunately.

As far as changing the tab order, it was suggested to me to do it
programatically using the OnOpen event which I will sure give a try.

Thanks to all who have tried to help me with this glitch. Anymore ideas
will get serious attention and would be greatly appreciated.

Charles
 
Upon further reflection, I don't think its really trying to update the
Customers table, I think its just simply dropping the CustomerID from the
combo box for some unseen reason. It most likely is that simple. Perhaps
the form has too many combo boxes, too many fields, too many pages...who
knows?
 
I converted the database to Access 2002 and the problem still persists.

I updated Access 2002 with the latest Office updates and the problem still
persists.
 
Back
Top