Combo Boxes

[Andrew]

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

 

[Nozza]

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

 

[Andrew]

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

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

 

[Rick Brandt]

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.

 

[Andrew]

Thank you for this.

I do not understand where the:

Me!

Comes from?

Regards,

Andrew

 

[Rick Brandt]

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.

 

[Andrew]

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

Andrew

 

[Rick Brandt]

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.

 

[Andrew]

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]

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.

 

[Andrew]

I am afraid I had already done this and nothing seems to happen.

Am I able to email you the mdb to have a look at?

Andrew

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]

I am afraid I had already done this and nothing seems to happen.

Am I able to email you the mdb to have a look at?

You'll have to zip it or change the file extension. My Email server will not
allow MDB file attachments.

 

[Andrew]

I emailed and got a delivery failure....

<bay0-mc1-f15.bay0.hotmail.com #5.5.0 smtp;550 Requested action not
taken: mailbox unavailable (-790574797:1382:-2147467259)>

Andrew

 

[Rick Brandt]

I emailed and got a delivery failure....

<bay0-mc1-f15.bay0.hotmail.com #5.5.0 smtp;550 Requested action not
taken: mailbox unavailable (-790574797:1382:-2147467259)>

You have to use the masked Email in my signature. The Email address that would
come up automatically is a dummy (sign of the times I'm afraid).

 

[Andrew]

Ok, thank you. I have done that.

Andrew

You have to use the masked Email in my signature. The Email address that would
come up automatically is a dummy (sign of the times I'm afraid).

 

[Rick Brandt]

Ok, thank you. I have done that.

Your query you are using for the RowSource of the Finishes ComboBox is not
correct.

First it includes the OrdersItems table (with no join) and it should not (delete
that). Second the criteria that should be pointing to the VenRange ComboBox
was...

[OrderItems]![BRange]

....and it needs to be...

[Forms]![OrderWoodVen]![OrderWoodVenItems].[Form]![VenRange]

I will return a corrected file, but in case there are problems with that you
should be able to make the changes described and it will work.