Combo Boxes

I am fairly new to access, I am used to filemaker and PHP/MySQL
databases.

I am creating an ordering system where I have need for 2 combo boxes.
One to show a list of finishes based on the range chosen in a previous
box.

Combo Box one displays ranges of blinds based on a table of ranges

Combo Box two then looks up the available finishes based on the range
chosen.

I can get combo box one to function correctly, however i am having real
trouble getting box two to be dymanic.

Any help would be greatly appreciated

Regards,

Andrew

On 7 Jan 2007 05:35:42 -0800, "Andrew"
wrote:

I am fairly new to access, I am used to filemaker and PHP/MySQL
databases.

I am creating an ordering system where I have need for 2 combo boxes.
One to show a list of finishes based on the range chosen in a previous
box.

Combo Box one displays ranges of blinds based on a table of ranges

Combo Box two then looks up the available finishes based on the range
chosen.

I can get combo box one to function correctly, however i am having real
trouble getting box two to be dymanic.

Any help would be greatly appreciated

Regards,

Andrew

Hi Andrew

Yes, the first combo is pretty straightforward if it is just based on
a table. And the second box is straightforward-ish too.

Have you gone down the route of using a query? That's the route I
would take.

Make sure the form is open - not in design view, and then create a new
query (I would use design view) for the available finishes table,
where the Criteria for the range is something like

[Forms]![frmOrder]![RangeID]

Once the query is working, then you can go back to the design of the
form and set the RowSource Type to be a Table/Query and RowSource to
be the query.

Hope that helps

Noz
--
Email (ROT13)

I seem to have this working, however, when i change combo box 1 combo
box 2 does not update with a new list.

Any ideas?

Andrew

Nozza wrote:
On 7 Jan 2007 05:35:42 -0800, "Andrew"
wrote:

I am fairly new to access, I am used to filemaker and PHP/MySQL
databases.

I am creating an ordering system where I have need for 2 combo boxes.
One to show a list of finishes based on the range chosen in a previous
box.

Combo Box one displays ranges of blinds based on a table of ranges

Combo Box two then looks up the available finishes based on the range
chosen.

I can get combo box one to function correctly, however i am having real
trouble getting box two to be dymanic.

Any help would be greatly appreciated

Regards,

Andrew

Hi Andrew

Yes, the first combo is pretty straightforward if it is just based on
a table. And the second box is straightforward-ish too.

Have you gone down the route of using a query? That's the route I
would take.

Make sure the form is open - not in design view, and then create a new
query (I would use design view) for the available finishes table,
where the Criteria for the range is something like

[Forms]![frmOrder]![RangeID]

Once the query is working, then you can go back to the design of the
form and set the RowSource Type to be a Table/Query and RowSource to
be the query.

Hope that helps

Noz
--
Email (ROT13)

Andrew wrote:
I seem to have this working, however, when i change combo box 1 combo
box 2 does not update with a new list.

Any ideas?

In the AfterUpdate event of ComboBox1 you need to use code to Requery
ComboBox2...

Me!ComboBox2.Requery

You will likely need that same code in the Current event of the form so that
ComboBox2's list will also adjust as you move from record to record.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thank you for this.

I do not understand where the:

Me!

Comes from?

Regards,

Andrew

Rick Brandt wrote:

Andrew wrote:
I seem to have this working, however, when i change combo box 1 combo
box 2 does not update with a new list.

Any ideas?

In the AfterUpdate event of ComboBox1 you need to use code to Requery
ComboBox2...

Me!ComboBox2.Requery

You will likely need that same code in the Current event of the form so that
ComboBox2's list will also adjust as you move from record to record.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Andrew wrote:
Thank you for this.

I do not understand where the:

Me!

Comes from?

Me is a shorthand reference to the containing class object of the code being
run. So when the code is in a form's module Me refers to the form, if in a
report's module then it refers to the report.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

So by writing a macro which runs Requery then put combobox2 in the
control name box this should work?

Andrew

Rick Brandt wrote:

Andrew wrote:
Thank you for this.

I do not understand where the:

Me!

Comes from?

Me is a shorthand reference to the containing class object of the code being
run. So when the code is in a form's module Me refers to the form, if in a
report's module then it refers to the report.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Andrew wrote:
So by writing a macro which runs Requery then put combobox2 in the
control name box this should work?

Sounds right. I don't use macros so I can't comment with certainty.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Ok, I have created code for the form as follows

Option Compare Database

Private Sub Form_Current()

Me!Finishes.Requery

End Sub

Private Sub VenRange_AfterUpdate()

Me!Finishes.Requery

End Sub

Finishes is the name of combo box2 VenRange is the name of combo box1

This does not appear to be working. I still have to press f9 to refresh
the combobox2

Am I being dense?

Andrew
Rick Brandt wrote:

Andrew wrote:
So by writing a macro which runs Requery then put combobox2 in the
control name box this should work?

Sounds right. I don't use macros so I can't comment with certainty.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Andrew wrote:
Ok, I have created code for the form as follows

Option Compare Database

Private Sub Form_Current()

Me!Finishes.Requery

End Sub

Private Sub VenRange_AfterUpdate()

Me!Finishes.Requery

End Sub

Finishes is the name of combo box2 VenRange is the name of combo box1

This does not appear to be working. I still have to press f9 to
refresh the combobox2

Am I being dense?

It is not enough to write the code. You also have to set the Event Properties
to [Event Procedure]. That is what causes the code to be run at the appropriate
times. Right now your code is just sitting there never being called.

In form design view bring up the properties sheet and click on the Events tab.
In the On Current property box of the form enter [Event Procedure]. Do the same
in the After Update property of the VenRange control. Then it should work.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com