A list based on a list?

[Sivakis]

I'm sorry, but a total access-n00b here that's been tasked with a project that's giving me sleepless nights...

Tried searching for an answer, but either the titles elude me, or I'm simply not getting it...

Scenario:
I've got a combo-box "Meal Type":
Field Name: Meal Type
Data Type: Text
Display Control: Combo Box
Row Source Type: Value List
Row Source: FastFood;Chinese;Italian;French
(This one is working fine, when users reach that Field, they can select the above choices).

This is the problem here....
I've got another selection for the users based on what they chose in "Meal Type". The data is stored on an Excel file that I've already exported over using "Get External Data".
Field Name: Meal Choice
Data Type: Text
Display Control: ??? (I'm pretty much lost from here onward.)

Example: If someone chooses "FastFood", then the 2nd selection should show: "Beef Burger", "Beef Burger w Fries", "Chicken Burger", "Chicken Burger w Fries"...... and if the 1st choice was "Italian", then the 2nd selection should show: "Pasta", "Hawaiian Pizza", "Salami Pizza", "Plain Cheese Pizza", "Lasagna" etc etc.

Am I supposed to edit the Properties in the Form design using an expression?

Summary: Is there anyway to make a 2nd list (data being stored in an Excel) change itself based on the data from a 1st list?

Sorry if I'm running around in circles.....

 

[Fernando]

Hi Sivakis, Theres a solution for this, lets say you Linked Excel Table is named tblMealChoice (Please avoid spaces in field names, you will regret it if u dont) with the fields fldMealType and fldMealChoice, you will have to use VBA and SQL for this.
So you have the combo box cmbMealType (the one u created) and cmbMealChoice.
On cmbMealType After update event u will have to write:

Sub cmbMealType_AfterUpdate
Me.cmbMealChoice.RecordSource = "SELECT fldMealChoice FROM tblMealChoice " & _
                                "WHERE fldMealType = '"&  cmbMealType & "' " & _
                                "ORDER BY fldMealChoice"
Me.cmbMealChoice.Requery
End Sub

 

[Sivakis]

Hmmm, it doesn't seem to be working...

But an update of what I have done.

MealChoice remains the same:
Data Type: Text
Display Control: Combo Box
Row Source Type: Value List
Row Source: FastFood;Chinese;Italian;French

MealType:
Data Type: Text
Display Control: Combo Box
Row Source Type: ?
Row Source: ?

My Excel data is arranged as such:

In the Form Design view, I right-click my cmbMealType, choose Build Event, Code Builder, then pasted the following:

Private Sub MealType_AfterUpdate()
Me.cmbMealChoice.RecordSource = "SELECT fldMealChoice FROM tblMealChoice " & _
"WHERE fldMealType = '" & cmbMealType & "' " & _
"ORDER BY fldMealChoice"
Me.cmbMealChoice.Requery
End Sub

However, nothing happens. Now I'm not sure where I went wrong.
What I do know:
1. My Table identifier settings need to be corrected
2. My excel data import could be done wrongly
3. My "After Update" script could be wrong.

Uggh, really need help on this one.....

 

[Fernando]

Did you imported the data? or is it linked?, can you please post an image of the table that you have in ms acces, not the one in Excel.

Private Sub MealType_AfterUpdate()
Me.cmbMealChoice.RecordSource = "SELECT fldMealChoice FROM tblMealChoice " & _
"WHERE fldMealType = '" & cmbMealType & "' " & _
"ORDER BY fldMealChoice"
Me.cmbMealChoice.Requery
End Sub

All of those you see on Bold are the Name of your control, so they have to be the same depending on what is the name you gave them. Same for fldMealChoice and fldMealType are the names you gave them in your table which i think its [MealChoice] and [MealType].

 

[Sivakis]

Did you imported the data? or is it linked?, can you please post an image of the table that you have in ms acces, not the one in Excel.


All of those you see on Bold are the Name of your control, so they have to be the same depending on what is the name you gave them. Same for fldMealChoice and fldMealType are the names you gave them in your table which i think its [MealChoice] and [MealType].

Thanks for replying Fernando.

Is the data imported or linked? Well, I think it's imported. Cos I used the File -> Get External Data -> Import function. The following is an image of the data imported:

The following are my Table settings for both MealChoice and MealType:

MealChoice

MealType

I have a funny feeling my problem could be lying somewhere in here.

Oh, another thing, you mentioned that those fields in Bold should reflect the actual Field Names I have (without the "cmb" or "tbl") ?

That means, my script should look like this: ?

Private Sub MealType_AfterUpdate()
Me.MealChoice.RecordSource = "SELECT MealChoice FROM MealChoice " & _
"WHERE MealType = '" & MealType & "' " & _
"ORDER BY MealChoice"
Me.MealChoice.Requery
End Sub

without the "cmb", "tbl", "fld" ?

Thanks for the help so far

 

[Fernando]

In this case yes and use the Table name u used insted of tbl..., you have to use the name you used. If i use those prefix is to diferenciate Controls.

 

[Sivakis]

In this case yes and use the Table name u used insted of tbl..., you have to use the name you used. If i use those prefix is to diferenciate Controls.

Cool, thanks for the clarification.

Oh, can you look at my MealType Table settings? Something seems to be wrong, at the Row Source Type and Row Source. I'm not sure what to do there....