How to link a combo box to a subform??

[Jack_Maloney]

Hi,

Im new to access and am trying to link a combo box to a subform. Basically, the combo box is called 'Project_Name_cbx' and the subform is called 'Sales_Order_tbl subform'.

The combo box has 2 columns, its row source is: SELECT [Project_tbl].[Project_ID], [Project_tbl].[Project Name], [Project_tbl].[Project_Type_ID] FROM Project_tbl WHERE [Project_Type_ID]= 22;

The subform has a field called 'Project_ID' which I am trying to use to link to the combo box key field. I have set the 'Link Master Fields' and 'Link Child Fields' to 'Project_ID.

However, nothing happens when I change the combo box selection. Could someone tell me what I am doing wrong??

Thanks,
Jack

 

[YNWA]

In your combo box properties how many columns have you set it to? Which column is your bound column (ie the one your storing in your table).

What do you want your combo box to show? A list of project IDs, names...?

 

[Jack_Maloney]

There are 2 columns and the bound column is column '1'. Is this information ok or do you need more?

Basically, the combo box shows a list of projects and I want the subform to display a list of records based on the users selection (The link will be the 'project_ID' field). At present the subform shows a list of records but it doesnt change when the user selects an option from the combo box

 

[YNWA]

I think I understand.

So you select a project from the dropdown and in the subform you want those project details to be displayed?

The child - master fields are to link the subform to the main form. Nothing to do with the combo box.

I think you might need some unbound fields in the subform that populate based on your combo selection.

Not totally sure myself.

 

[Jack_Maloney]

Yes that is correct. The project is selected and it should show all records related to the selection in the subform - which is displayed as a table.

So if I create a field showing the project_ID then link this to the subform?

 

[YNWA]

In your subform I would imagine you have all the fields for your project details.

You then need some sort of code or validation to tell each field to show specific details.

I think you might need more columns in your combo.

Someone more advanced should help on this.

 

[Jack_Maloney]

Thanks YNWA for your help.

Is there anyone else who knows how to do this? The code I have so far is this:

Private Sub Project_Name_cbx_AfterUpdate()
Dim sSalesOrdersSQL As String

sSalesOrdersSQL = "SELECT * FROM Sales_Order_tbl WHERE [Project_ID] = " & Me.Project_Name_cbx.Column(0)

Me.Sales_Order_tbl_subform.SourceObject = sSalesOrdersSQL
Me.Sales_Order_tbl_subform.Requery
End Sub


The code doesnt compile at present because the line 'Me.Sales_Order_tbl_subform.SourceObject = sSalesOrdersSQL' is not correct but you can see what im trying to do. Maybe I need some sort of result set to store the results, but im not sure how to do this in VBA?

If anyone can tell me what the correct code should be that would be much appreciated!

Thanks,

 

[Jack_Maloney]

Just for reference I have now solved this. Basically the code was pretty much there, just had to change the last line to 'Me.RecordSource = sSalesOrdersSQL' rather than 'Me.Sales_Order_tbl_subform.SourceObject = sSalesOrdersSQL'

Thanks,