Run querry from form 1

[Bill0722]

I have a form of Customers. I want to put a button on the form and run a querry from that form, but I'd like the querry to use the current record in the form as the criteria for the querry. Is this possible?

Thanks

 

[kjv1611]

Yes.

In your button's code:

Private Sub Button_Click()
  Dim strSQL As String
  strSQL = "SELECT [blue]MyFields[/blue] " & _
           "FROM [blue]MyTable[/blue] " & _
           "WHERE [blue]MyField1 = '" & txtTextBox1 "'"
  Dim Query1 As QueryDef
  Set Query1 = CurrentDb.CreateQueryDef("QueryName", strSQL)
  DoCmd.OpenQuery Query1.Name, acViewNormal, acReadOnly
End Sub

Or whatever you want to do with the query you just created.

 

[Bill0722]

Thanks. Perhaps I misstated my question. I have the form of Customers. On the form I have a button which I can press to run a querry. In the querry I have the criteria set where I have to input a customer number. What I want to happen is when I press the button in the form, the querry runs, automatically using the number of the currently selected record as criteria for the querry. Hope this makes sense.

Bill

 

[TheAceMan1]

How are ya Bill0722 . . .

Your post origination is vague . . .

Perhaps if you post the [blue]SQL[/blue] of the intended query we can help you better! . . .

See Ya! . . . . . .

 

[Bill0722]

Here's the code I'm using per kjv1611 suggestion. I get a compile error -- expected end of statement.

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Dim strSQL As String
strSQL = "SELECT Patient_number" & _
"FROM Patient" & _
"WHERE Patient_number='" & Combo25 "'"
Dim Query1 As QueryDef
Set Query1 = CurrentDb.CreateQueryDef("tblProcedureDetail Query", strSQL)
DoCmd.OpenQuery Query1.Name, acViewNormal, acReadOnly
End Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

 

[randy700]

Take a close look at the way your strSQL is built.

strSQL = "SELECT Patient_number" & _
           "FROM Patient" & _
           "WHERE Patient_number='" & Combo25 "'"

The result will be:
SELECT Patient_numberFROM PatientWHERE Patient_number=25"

Try

strSQL = "SELECT Patient_number " & _
           "FROM Patient " & _
           "WHERE Patient_number = '" & Combo25 "'"

Note the spaces at the end of each line.


Randy

 

[Bill0722]

This is the code as I've revised it. I still get the same error message. What I am trying to do is select the record based on the value currently in ComboBox25

Thanks,

Bill

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Dim strSQL As String
strSQL = "SELECT Patient_number " & _
"FROM Patient " & _
"WHERE Patient_number = '" & Combo25 "'"
Dim Query1 As QueryDef
Set Query1 = CurrentDb.CreateQueryDef("tblProcedureDetail Query", strSQL)
DoCmd.OpenQuery Query1.Name, acViewNormal, acReadOnly
End Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

 

[randy700]

Sorry, I didn't notice this the first time.
Remove the single quotes around Combo25 - they should be used only if the data is text, not numeric.

strSQL = "SELECT Patient_number " & _
"FROM Patient " & _
"WHERE Patient_number = " & Combo25



Randy

 

[Bill0722]

Thanks. This works great, and returns the proper value to the query that it creates. However, what I want to do is run a query I have already created. Any ideas would be appreciated.

The name of my query is tblProcedureDetail Query

 

[randy700]

ok.. on the query's criteria row, in the Patient_number column, put =Forms!yourFormName!Combo25


Randy

 

[Bill0722]

Fantastic. Works pefectly. Thanks Randy

Bill