Sync Combo Boxes repeating through every record (1 Viewer)

Newbie2012 · Nov 22, 2012

My combo boxes are repeating data through every record and I think its because the main form is running off a query so calculation fields can work.

I don't know how to make this work and am :banghead:

If someone could have a look please

Newbie2012 · Nov 22, 2012

Here are some pics...if this helps. Thanks for any assistance

John Big Booty · Nov 22, 2012

I can't open you DB as I only have Access '03 currently.

However if you are using an unbound, calculated, control on a continuous form, then you will find that all instances of that control will show a result that is reflective of the Record that currently holds focus.

Newbie2012 · Nov 22, 2012

Being up all night after 9hrs and for some reason, I got it to work by binding the first combo...

I followed instructions from a combo box db sample but they said to insert the combo's unbound...

Newbie2012 · Nov 22, 2012

No I've got excited too quickly...

It has fixed the first combo box but now the second combo box is repeating...

1st Combo:

Private Sub cboCategories_AfterUpdate()
Me.cboProducts.RowSource = "SELECT ProductName FROM" & _
" Products WHERE CategoryID = " & Me.cboCategories & _
" ORDER BY ProductName"
Me.cboProducts = Me.cboProducts.ItemData(0)

End Sub
=========

2nd Combo row source
SELECT Products.ProductName
FROM Products
ORDER BY Products.ProductName;

Please help

Newbie2012 · Nov 22, 2012

Thanks for your reply...its not a continuous form so will what you say still apply? Will try to explain a bit more:

The Main form data is from the main query
The text boxes also come from the main query (being calculated in a query field)
The 1st combo box control source is from main query + [Event Procedure] of:

Private Sub cboCategories_AfterUpdate()
Me.cboProducts.RowSource = "SELECT ProductName FROM" & _
" Products WHERE CategoryID = " & Me.cboCategories & _
" ORDER BY ProductName"
Me.cboProducts = Me.cboProducts.ItemData(0)
End Sub

The 2nd combo box is from its own query of:

SELECT Products.ProductName
FROM Products
WHERE (((Products.[CategoryID])=2))
ORDER BY Products.ProductName;
===========================================
That seems to be all I can show and tell here...

John Big Booty said:
I can't open you DB as I only have Access '03 currently.

However if you are using an unbound, calculated, control on a continuous form, then you will find that all instances of that control will show a result that is reflective of the Record that currently holds focus.

John Big Booty · Nov 22, 2012

OK neither of those combos are bound. I'm also going to guess that Combo cboProducts is supposed to be cascading of Combo cboCategories.

If this is so your Criteria should be;

Code:

Forms![Categories And Products]!cboCategories

You then need to synchronise the combos, by using;

Code:

Me.cboProducts.Requery

In both the Form's On Current and Combo cboCategories On Change Events.

Also avoid using spaces and other special characters in control and object names, limit yourself to alpha and numeric characters and the underscore. Consider implementing a naming convention. Somthing along the lines of TBL_TableName, FRM_FormName, QRY_QueryName etc. This will make it easier to identify the object that is being refered to in your cade as well as making the task of writing code easier.

Newbie2012 · Nov 23, 2012

Thanks John...I realise I need to learn more to understand this coding thing.

Is the programming language I need to learn VBA or are there any other coding languages involved? For Access (Office etc.)

Is VBA the code language used for Access and also mainly seen in these forums?

Am only asking because I've bought Amazon e-books on VBA + Access and got books from library about VBA...cos I don't really understand what the code means...or what your reply means...now I realise for me: its not simply a matter of dipping the finger in the pie and expecting the whole cake when don't really understand the ingredients, and what they're for lol!

John Big Booty · Nov 23, 2012

Yes VBA is what you will really need to know to bend Access to your will

Severin · Nov 23, 2012

Hi, I had a look and changed tables, relations, form, recordsources and vba...

The problem was partly in the table structure and the relations with foreign keys etc.

In the form itself there were similar problems and the dropdown boxes needed to be bound, set up and a bit of coding!~)

Newbie2012 · Nov 24, 2012

Hi Severin, thanks for taking the time to have a look and making changes...however, the 2nd combo box is still not functioning correctly - it will allow 1st combo box (top of the value list) selection, and 2nd combo (value list) choices - it will save

...

...but when 1st combo box, 2nd choice (from top of the value list) selection, and any 2nd combo (value list) choices - it doesn't save

...if refresh all is done on the last record entry...

...and when navigating back through to previous records, then the 2nd combo choices are wiped blank again...

Am sorry to have to give back this feedback after you've taken the time

Severin said:
Hi, I had a look and changed tables, relations, form, recordsources and vba...

The problem was partly in the table structure and the relations with foreign keys etc.

In the form itself there were similar problems and the dropdown boxes needed to be bound, set up and a bit of coding!~)

Newbie2012 · Nov 24, 2012

Do you mean this criteria is supposed to go somewhere in a query window or the VBA editor?

If this is so your Criteria should be; [CODE said:
Forms![Categories And Products]!cboCategories[/CODE]

Severin · Nov 24, 2012

...sorry you need this one:

Private Sub Form_Current()
Me.cboProducts.Requery
End Sub

Newbie2012 · Nov 24, 2012

Thank you very much. It is working correctly

Severin said:
...sorry you need this one:

Private Sub Form_Current()
Me.cboProducts.Requery
End Sub

Sync Combo Boxes repeating through every record (1 Viewer)

Newbie2012

Donna H

Attachments

Newbie2012

Donna H

Attachments

John Big Booty

AWF VIP

Newbie2012

Donna H

Newbie2012

Donna H

Newbie2012

Donna H

John Big Booty

AWF VIP

Newbie2012

Donna H

John Big Booty

AWF VIP

Severin

Snr. Developer

Attachments

Newbie2012

Donna H

Newbie2012

Donna H

Severin

Snr. Developer

Attachments

Newbie2012

Donna H

Similar threads

Users who are viewing this thread