Variable Append Query (2003)

Topic

New Reply

I expect that this is easy if you know what you

Reply | Quote

Replies

If you create a parameter query, and declare the parameter explicitly in Query | Parameters…, you can use DAO to set the parameter in code. You need to set a reference to the Microsoft DAO 3.6 Object Library in Tools | Options… in the Visual Basic Editor. Code could look like the following, with the appropriate names substituted.

Sub RunAppend(strNotes As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
‘ use name of your query for MyAppendQuery
Set qdf = dbs.QueryDefs(“MyAppendQuery”)

‘ use name of your parameter for Enter NoteText
qdf.Parameters(“Enter NoteText”) = strNotes
qdf.Execute

Set qdf = Nothing
Set dbs = Nothing
End Sub

Sub AppendMultiple()
‘ call RunAppend as many times as needed
RunAppend “Letter Sent”
RunAppend “Letter Received”
RunAppend “Letter Torn”
RunAppend “Letter Eaten”
End Sub

Reply | Quote

Thanks Hans. I’ve played with the code but lack the experience/knowledge to make it work.

Reply | Quote

If you want to pursue this, please indicate where you get stuck, or where things go wrong. We will try to help you get it right.

Reply | Quote

Thanks for your help but I chickened out & did it with a clutch of individual append queries

Reply | Quote

Thanks for your help but I chickened out & did it with a clutch of individual append queries

Reply | Quote

If you want to pursue this, please indicate where you get stuck, or where things go wrong. We will try to help you get it right.

Reply | Quote

Thanks Hans. I’ve played with the code but lack the experience/knowledge to make it work.

Reply | Quote

If you create a parameter query, and declare the parameter explicitly in Query | Parameters…, you can use DAO to set the parameter in code. You need to set a reference to the Microsoft DAO 3.6 Object Library in Tools | Options… in the Visual Basic Editor. Code could look like the following, with the appropriate names substituted.

Sub RunAppend(strNotes As String)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
‘ use name of your query for MyAppendQuery
Set qdf = dbs.QueryDefs(“MyAppendQuery”)

‘ use name of your parameter for Enter NoteText
qdf.Parameters(“Enter NoteText”) = strNotes
qdf.Execute

Set qdf = Nothing
Set dbs = Nothing
End Sub

Sub AppendMultiple()
‘ call RunAppend as many times as needed
RunAppend “Letter Sent”
RunAppend “Letter Received”
RunAppend “Letter Torn”
RunAppend “Letter Eaten”
End Sub

Reply | Quote