Conditional Lookups (1 Viewer)

[c-bert]

I want to select an option from a lookup table and, depending on what I select automatically, modify what options are available in the next lookup field. Any ideas?

 

[AlanS]

Assuming that the fields in question are combo boxes, you can put code in the After_Update event procedure of the first combo box which changes the RowSource property (and if necessary the RowSourceType property) of the second combo box, based on what value was selected in the first combo box. Probably the easiest way to do this is to set up separate queries for each set of values that might be available in the second combo box, and then change its RowSource property to the appropriate query name.

 

[c-bert]

Thanks, that sounds about right however, having no knowledge of Visual Basic whatsoever I'm a bit stuck as to how to change the Rowsource of the second combo box. Is there anywhere I could download suitable code?

 

[ColinEssex]

Hi

I think you are talking about cascading ComboBoxes.

I remember Hayley helping someone with this a while back, she may have posted a sample Db. Have a search and see what you can find. There is also examples in the Developers Solutions CD that comes with Access and possibly also in the Northwind Db.

Post back if you don't get any joy and I'll see if I can knock you up something.

Col

 

[c-bert]

I've found Hayley's Post and I've downloaded the sample Db but I still don't really get what they've done. This is definately what I'm trying to acheive.
I've start by creating two seperate tables with the two list of data I want to chose from. I think the sample Db uses a query to pick from one table though.
I think the two tables approach is going to be more useful for the application I want but the code to change Rowsource is a bit more challenging!

 

[c-bert]

Basically, all I need is a bit of code to say, ' after selecting an option from ComboBox 1, change row source of ComboBox 2 to table A ro B depending on whether ComboBox 1 says X or Y.'

Simple.

 

[AlanS]

Switch your form to Design view, select Combo1, display the Properties window, select the Event tab, click in the white space to the right of After Update, then click the little button with the three dots that appears at the end of the white space. A Choose Builder dialog box will appear. Select Code Builder and click OK. The form's code module window will open, and you'll see a procedure stub that looks like this:

Private Sub Combo1_AfterUpdate()

End Sub

Between these two statements you must add the appropriate code, which will vary depending on the possible values in Combo1's bound column and the corresponding RowSource values for Combo 2. Here's an example:

Select Case Combo1
Case 1: Combo2.RowSource = "Query1"
Case 2: Combo2.RowSource = "Query2"
Case Else: Combo2.RowSource = "QueryDefault"
End Select

 

[c-bert]

Thanks but I've got round another way (having been trying all day!)

I put a 'like' statement in the second field's query criteria so that it only shows items that are 'like' the previous item. Then I had to add a requery line to the first box and it seems to work now. And I am vey pleased with myself!

Thanks a lot anyway guys.

 

[ColinEssex]

Sorry I couldn't get back to you, had to do other urgent stuff.

Hows Bristol? I went to school at Hengrove and lived at Knowle.

Col

 

[c-bert]

Thats OK. As for Bristol I've only worked here a couple of weeks so I don't know the area that well. Just off the M32 by Filton. Its not bad though. Apart from the traffic that is!