seeking solution to synchronize two combo boxes on a subform

[whitesnow]

I have successfully synchronize two combo boxes on a subform named as
suborderdetail and the master form is frmorderentry. The two combo boxes is
cbocategory and cboproduct. Unfortunately, when I try to enter the
orderdetail and to make selection in list of the category and products, all
the rest of the records changed and became exactly the same as the latest
entry I just made. For instance, when I enter in the 10th order with cat-A,
prod-a, all my previous entries and the entries that I have not made, they
all shows cat-A, prod-a. Please kindly advice what should I do?I want every
single entry is kept as I selected from the list of those two combo boxes.

 

[Douglas J. Steele]

Sounds as though you don't have the combo boxes bound to a field in the
underlying table/query.

 

[whitesnow]

Thank you very much for your suggestion and I have now changed them to bound
boxes. But then I have other problems, when I try to make selection for the
new record from the list of category, the run time error dialogue pops up. In
fact I put below codes into the cbocategory afterupdated event:

private sub cboCategory_AfterUpdate()
me.cboproduct = Null
Me.cboproduct.requery
me.cboproduct = me.cboproduct.itemdate(0)
End sub

Could you please kind enought to point out what should I do and thank you
again for taking the time to address my inquiry as well as bearing with me
that I am a beginner of using Access.

 

[Douglas J Steele]

Assuming that's copied and pasted, it should be itemdata, not itemdate.

If it isn't copied and pasted, what's the real code you're using?

 

[whitesnow]

Sorry it was typo mistake, in fact it was correctly written as itemdata in
the cboCategory after update event procedure. Could you please kindly enough
to go ahead to point out what should I do next?

Many thanks

 

[Douglas J. Steele]

What error are you getting?

Which column is the bound column in your combo box, and which column is the
visible one?

 

[whitesnow]

In the combo box of cboCategory, the bound column is 1 and the visible column
is the 2nd. In table of tblCategory, there are 2 fields, which are
categoryID and categoryName.

I tried deleting Me.cboProduct = Null and Me.cboProduct =
Me.cboProduct.itemdata(0), it looks like the cbocategory and the snycronized
combo boxes are working fine, but the product that I selected from the
corresponding category is not kept visible when I click to the next line. I
just could not figure out why.

I wish I could copy you the screen to better explain the problem.

Many thanks for your great patient.

 

[Douglas J. Steele]

Hold on. You're talking about a continuous form, aren't you.

See whether what Rob Mastrostefano has at
http://www.fmsinc.com/free/newtips/access/accesstip16.asp is what you're
talking about.

 

[whitesnow]

Many thanks, Douglas. This solved the problem.

Hold on. You're talking about a continuous form, aren't you.

See whether what Rob Mastrostefano has at
http://www.fmsinc.com/free/newtips/access/accesstip16.asp is what you're
talking about.

 

[whitesnow]

Douglas, Even though it solved the problem of disappeared selection, but I
found that in the demonstration the link provided, it does not allow to add
new records, so it is the same to my database. I am being hanged up here.
Please help.

 

[Douglas J Steele]

Check whether Sandra Daigle's "Synch Combo Continuous" example at
http://www.daiglenet.com/MSAccess.htm helps.

If not, post back, and I'll try and pull something together based on my
Febuary, 2006 "Access Answers" column in Pinnacle Publication's "Smart
Access". (Unfortunately, my contract with them doesn't allow me to post my
columns on the web until 6 months after they appear in print)

 

[whitesnow]

Douglas, I do appreciate your reply very much. The link you offered is very
helpful. In the subform, I inserted the text box of Product to the order
detail subform by reference to the sample provided. The #error is no longer
shown. Wow, so thankful to you. But I do not know how to make the product
control directly over the cboproductID control, as a result, the control
source of product and productID are both shown in the subform. Pls. how can I
get it done?

I wish I could see the “Access Answers†you published in Smart Access. That
would be great.

 

[Douglas J Steele]

You can put the text box over the combo box using code. Try something like
the following in the form's Load event:

Me.MyTextBox.Left = Me.MyComboBox.Left
Me.MyTextBox.Top = Me.MyComboBox.Top
Me.MyTextBox.Width = Me.MyComboBox.Width - 250

(replace "MyTextBox" and "MyComboBox" with the appropriate control names)

Unfortunately, the column won't be on-line for another 2 months.

 

[whitesnow]

Doug, it is suceeded! Many thankful to you. With your BIG help, I would like
to continue having fun with my access database and looking forward to reading
your column on-line. Would that be all right for you to keep me posted?

 

[Douglas J Steele]

There are 28 columns already posted at
http://www.accessmvp.com/DJSteele/SmartAccess.html

I try to update the page during the first few days of the month, so the
Febuary one should be there in early August.

 

[whitesnow]

Doug, thanks for the link and it's very helpful to me.
As for my database, I notice that it was not completely succeeded. When I
tried to add new records, the selection for the product could not keep
visible when moving on to the next line. Well, I actually refer to the sample
of form frmSyncComboInContinuous in SynchComboContinuous.mdb, which is from
http://www.daiglenet.com/MSAccess.htm. Everything is the same except that I
built the continuous form in the subform I guess it is because I did not
successfully made the text box over the combo box. Even thought I have added
the codes that you offered into the load event of the subform, on design
view, I still see the ProductID over the ProductName. Pls advice what should
I do next. I really wish I could scan you the picture or even send you the
database.

 

[whitesnow]

Doug, Good news is that my subform works smoothly now after I copied those
text and combo controls directly from the sample and made the necessary
changes to fit mine. It is amazing that it worked. I still want to thank you
for all your time spended in replying my questions.

With regards,