Sync Combo Boxes repeating through every record

N

Newbie2012

Donna H

Local time

Tomorrow, 02:05

Joined

Jun 28, 2012

Messages

33

• Nov 22, 2012

• #1

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

 

Attachments

• Time Log Database.accdb

  452 KB · Views: 267

N

Newbie2012

Donna H

Local time

Tomorrow, 02:05

Joined

Jun 28, 2012

Messages

33

• Nov 22, 2012

• #2

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

 

Attachments

• 
  Form.png

  27.1 KB · Views: 281
• 
  Relationships.png

  20.1 KB · Views: 270
• 
  Query.png

  29.7 KB · Views: 267

John Big Booty

AWF VIP

Local time

Tomorrow, 00:05

Joined

Aug 29, 2005

Messages

8,243

• Nov 22, 2012

• #3

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.

 

N

Newbie2012

Donna H

Local time

Tomorrow, 02:05

Joined

Jun 28, 2012

Messages

33

• Nov 22, 2012

• #4

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...

 

N

Newbie2012

Donna H

Local time

Tomorrow, 02:05

Joined

Jun 28, 2012

Messages

33

• Nov 22, 2012

• #5

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

 

N

Newbie2012

Donna H

Local time

Tomorrow, 02:05

Joined

Jun 28, 2012

Messages

33

• Nov 22, 2012

• #6

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.

Click to expand...

 

John Big Booty

AWF VIP

Local time

Tomorrow, 00:05

Joined

Aug 29, 2005

Messages

8,243

• Nov 22, 2012

• #7

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.

 

N

Newbie2012

Donna H

Local time

Tomorrow, 02:05

Joined

Jun 28, 2012

Messages

33

• Nov 23, 2012

• #8

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

AWF VIP

Local time

Tomorrow, 00:05

Joined

Aug 29, 2005

Messages

8,243

• Nov 23, 2012

• #9

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

 

S

Severin

Snr. Developer

Local time

Today, 05:05

Joined

Mar 27, 2012

Messages

172

• Nov 23, 2012

• #10

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!~)

 

Attachments

• Time Log Database.accdb

  1.1 MB · Views: 257

N

Newbie2012

Donna H

Local time

Tomorrow, 02:05

Joined

Jun 28, 2012

Messages

33

• Nov 24, 2012

• #11

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!~)

Click to expand...

 

N

Newbie2012

Donna H

Local time

Tomorrow, 02:05

Joined

Jun 28, 2012

Messages

33

• Nov 24, 2012

• #12

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]

Click to expand...

 

S

Severin

Snr. Developer

Local time

Today, 05:05

Joined

Mar 27, 2012

Messages

172

• Nov 24, 2012

• #13

...sorry you need this one:

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

 

Attachments

• Time Log Database.accdb

  536 KB · Views: 255

N

Newbie2012

Donna H

Local time

Tomorrow, 02:05

Joined

Jun 28, 2012

Messages

33

• Nov 24, 2012

• #14

Thank you very much. It is working correctly

Severin said:

...sorry you need this one:

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

Click to expand...