Append Query

Hello,
Another question about VB for Access. Does anyone know how to create a user input for a filter which is used in more than one append query?.
Here is what I have so far:

Private Sub Command32_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETEQUERY", acViewNormal, acEdit
DoCmd.OpenQuery "APPEND1", acViewNormal, acEdit
DoCmd.OpenQuery "APPEND2", acViewNormal, acEdit
DoCmd.OpenQuery "APPEND3", acViewNormal, acEdit
DoCmd.OpenQuery "APPEND4", acViewNormal, acEdit
DoCmd.OpenQuery "APPEND5", acViewNormal, acEdit
DoCmd.SetWarnings True
End Sub

Three of my append queries have a filter that prompts the user for a "Job Number". The same "Job Number" is used for all three append queries. The problem is that it prompts the user three times for the same number.
My question is... Does anyone know how to prompt the user one time for a "Job Number", which then uses that input for the filters for append query 2,3,4?
Thank you,
Jason
PS - Thanks JamesLee for your expertise regarding the SetWarnings!!!!!

I'd use code myself, like this (only an example):


You can open the sql view in the query window and for the most part copy & paste the sql into your code.

Otherwise I don't know, I suppose some kind of parameter query.

Hello jasondatabase,

Doug G is right. Instead of creating your queries in design view it's more flexible with SQL statement. Hope it will help you start.

'------------------------------------
Private Sub Command32_Click()
Dim MyDB As Database, MySet As Recordset, Res As Variant
Set MyDB = CurrentDb
DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETEQUERY", acViewNormal, acEdit

Res = InputBox("Enter Job Number: ", "Get Job Number")
DoCmd.RunSQL ("INSERT INTO table1 SELECT * FROM table2 WHERE jobID = '" & Res & "';")
DoCmd.RunSQL ("INSERT INTO table3 SELECT * FROM table4 WHERE jobID = '" & Res & "';")
DoCmd.RunSQL ("INSERT INTO table5 SELECT * FROM table6 WHERE jobID = '" & Res & "';")

'Want to do something with recordset
Set MySet = MyDB.OpenRecordset("SELECT...;")
Do Until MySet.EOF
'put something here
MySet.MoveNext
Loop
DoCmd.SetWarnings True
End Sub
'-------------------------------------

I used ADO and JamesLe used DAO, you do need to settle on one database technology to use in code. DAO and ADO are different.