Filling a combo box at form start

[anammari]

I am relatively new to Access Forms VBA Programming. I'm using 2007 Access

What I want to do is to fill an unbound combox box with values retrieved by a
query when the form starts.

Here are the steps I did:

1. create an unbound form from design view
2. create an unbound combo box on the form. qb1
3. create a query that retrieves values in two table columns. query1.
4. wrote the following code in the "on open" form event:

Private Sub Form_Open(Cancel As Integer)
Dim strRST As String
strRST = "Table/Query"
Me.qb1.RowSourceType = strRST
Me.qb1.RowSource = "query1"
End Sub

However, nothing happens when I switch to form view or close and open the
form directly in form view. The combo box stays empty.
Am I missing something? Is there another way to 'open' the form so the VBA
can run?

Cheers.

 

[Spero]

I am relatively new to Access Forms VBA Programming. I'm using 2007 Access

What I want to do is to fill an unbound combox box with values retrieved by a
query when the form starts.

Here are the steps I did:

1. create an unbound form from design view
2. create an unbound combo box on the form. qb1
3. create a query that retrieves values in two table columns. query1.
4. wrote the following code in the "on open" form event:

Private Sub Form_Open(Cancel As Integer)
Dim strRST As String
strRST = "Table/Query"
Me.qb1.RowSourceType = strRST
Me.qb1.RowSource = "query1"
End Sub

However, nothing happens when I switch to form view or close and open the
form directly in form view. The combo box stays empty.
Am I missing something? Is there another way to 'open' the form so the VBA
can run?

Cheers.

Are the selection in the dropdown based on a vale from another control
on your form? IE: State = FL, than City would be all cities in FL? If
so try
Me.Combox.Requery

 

[Linq Adams via AccessMonster.com]

I think Spero is correct in saying that the you need to requery the combobox
after assinging the RowSource, but the real question here is ***why*** are
you doing this?

If you want the combobox to load when the form opens, why not simply use the
Combobox Wizard, select Query1 as the RowSource, and choose the fields that
you want included.

Then the combobox loads when the form opens.
**********************************************************

Another thing to remember is that code does not run in 2007 unless your
database resides in a folder that has been declared a “trusted†location.

To trust your folder, click:

Office Button (top left)
Access Options (bottom of dialog)
Trust Center (left)
Trust Center Settings (button)
Trusted Locations (left)
Add new location (button)

 

[anammari via AccessMonster.com]

Cheers .. the requery command worked though I don't know why I had to do it
in addition to assigning the query to the combox box resource!

I also did the folder trust thing thanks for pointing out this.

I also added a list box whose values change based on the combo box selection.
I did it using the same way and with the help of the requery command.

Can you help me in doing the following:

In this unbound form I want to I place a bound subform to enter data that
will be saved in a table. This table has a composite primary key containing
two columns that should be linked to the values in the unbound list box that
I get its values based on the unbound combo box selection. All the input
controls in the subform are bound to the table that I want to save the data
in. But I don't want the two foreign key columns to appear in the subform.

Thank you