Base foms Record source off combo box selection

[oxicottin]

Hello, I wanted to have my forms Record source based off a selection from my combo box (cboFunction). I can create a query and open the form and make the selection in the combo box and the correct data comes up but I don't know how to execute it in VBA on my form? Below is the querys SQL... What would I do in the After Update of the (cboFunction) to get the data to come up in my detal section? Thanks!

SELECT tblFunctionRequirements.FuncID, tblFunctionRequirements.Requirements
FROM tblFunctionRequirements
WHERE (((tblFunctionRequirements.FuncID)=[Forms]![frmMain]![cboFunction]));

Thanks,
SoggyCashew.....

 

[oxicottin]

I figured it out thanks....

Private Sub cboFunction_AfterUpdate()

Dim mySQL As String

mySQL = "SELECT tblFunctionRequirements.FuncID, tblFunctionRequirements.Requirements " & vbCrLf & _
         "FROM tblFunctionRequirements " & vbCrLf & _
         "WHERE (((tblFunctionRequirements.FuncID)=[Forms]![frmMain]![cboFunction]));"

Me.RecordSource = mySQL
Me.Requery

End Sub

Thanks,
SoggyCashew.....

 

[dhookom]

I would change the code to remove the control reference from inside the quotes. Also you should not need the Requery after setting the record source.

Private Sub cboFunction_AfterUpdate()

    Dim mySQL As String

   mySQL = "SELECT tblFunctionRequirements.FuncID, tblFunctionRequirements.Requirements " & vbCrLf & _
         "FROM tblFunctionRequirements " & vbCrLf & _
         "WHERE FuncID =" & [Forms]![frmMain]![cboFunction]

   Me.RecordSource = mySQL

End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

I would change the code to remove the control reference from inside the quotes. Also you should not need the Requery after setting the record source. Assuming FuncID is numeric:

Private Sub cboFunction_AfterUpdate()

   Dim mySQL As String

   mySQL = "SELECT tblFunctionRequirements.FuncID, tblFunctionRequirements.Requirements " & vbCrLf & _
         "FROM tblFunctionRequirements " & vbCrLf & _
         "WHERE FuncID =" & [Forms]![frmMain]![cboFunction]

   Me.RecordSource = mySQL

End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Plus. I don't think you need to fully qualify the fields in your SQL if you go after just one table:

Private Sub cboFunction_AfterUpdate()

   Dim mySQL As String

   mySQL = "SELECT FuncID, Requirements " & vbCrLf & _
         "FROM tblFunctionRequirements " & vbCrLf & _
         "WHERE FuncID = " & [Forms]![frmMain]![cboFunction]

   Me.RecordSource = mySQL

End Sub

---- Andy

There is a great need for a sarcasm font.