Generally to run a query in code I would use one of two methods.
You can generate a SQL statement dynamically and assign it to a command
object for execution. (This is all VB6/Access 2000)
ie:
Dim sql as string:
sql = "INSERT INTO table (field1, field2) SELECT " _
& Me.ControlName & ", " & Me.ControlName"
Dim cmd As ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = sql
cmd.Execute
The other option is to use a stored procedure style query.
QRY1:
PARAMETERS field1In as text, field2In as text
INSERT INTO table (field1, field2)
SELECT [field1In], [field2In]
Dim cmd As ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "QRY1"
Call cmd.Execute(, Array(Me.ControlField1.Value, Me.ControlField2.Value)
I prefer the second option... only because I find it more flexible, and
more extensible. You can wrap stored procedure calls in functions and
reuse them, and it's a much more modern solution.
This might not answer your question, but it's useful information even if
it doesn't.
Luke
Thanks that helps a lot.
I don't suppose there is a way to do this pragmatically eg
Do it in the code that I posted. The reason for this though not essential
since your advice works fine, is that I have to create a couple of queries
rather than doing it all in the code.
Thanks
There are several ways, but adding the form reference to the criteria line
is 1:
Forms!FormName!ControlName
another is to have it prompt you by adding the following to the criteria
line:
[Please enter a Customer Number]
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
I have the code below in a form.
Lets say I have a variable of CustomerNumber
What do I have to add to get the
DoCmd.OpenQuery stDocName, acNormal, acEdit
Line to make a table with a specific record in it automatically
in other words
Do this query, create a table on specific customer "CustomerNumber"
automatically.
Thank You
'--------------------------------------------------------
Private Sub mktblqryContacts__Click()
On Error GoTo Err_mktblqryContacts__Click
Dim stDocName As String
stDocName = "qryContacts#"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_mktblqryContacts__Click:
Exit Sub
Err_mktblqryContacts__Click:
MsgBox Err.Description
Resume Exit_mktblqryContacts__Click
End Sub
