Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Base foms Record source off combo box selection

Status
Not open for further replies.

oxicottin

Programmer
Jun 20, 2008
353
US
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!

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

Thanks,
SoggyCashew.....
 
I figured it out thanks....

Code:
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.....
 
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.

Code:
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
 
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:

Code:
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
 
Plus. I don't think you need to fully qualify the fields in your SQL if you go after just one table:

Code:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top