Command Button to Create a Pass Through Query

[cantona]

I previously posted this onto a different forum.. realised that it would be better placed on here...

I have a form that contains a combo box which i have called School. The combo box contains a list of school names, with a unique identifier for each school. I would like to create a command button which will execute some code and create a pass through query just for the school that has been chosen in the combo box. How would i go about writing the code? i dont have much experience of VBA and never really had to create pass throughs before. Im assuming i would need to create a function in the on click part of the command button. Can anyone help???

 

[dhookom]

The easiest method is to create a P-T using the user interface. Save the P-T with a name like "qsptMyQuery".

Then write DAO code to update the SQL property of qsptMyQuery.

Public Function UpdatePT()
   Dim strSQL as String
   strSQL = "SELECT * from tblWhatEver " & _
       "WHERE SchoolID = " & Me.School
   CurrentDb.QueryDefs("qsptMyQuery").SQL = strSQL
   ' you could also change the code to modify the 
   '    connection or other properties
End Function

You could call this procedure from the On Click event of a command button or the after update event of the combo box. This code makes several assumptions about your table and field names and data types.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]