VBA macro and SQL query

[a7n9]

Hello all,

I have this SQL query which will create a table for the duplicates field records and one more condition:

INSERT INTO Table2
SELECT ADM200508_122704.*
FROM ADM200508_122704
WHERE (((ADM200508_122704.[ID Number]) In (SELECT [ID Number] FROM [ADM200508_122704] As Tmp GROUP BY [ID Number] HAVING Count(*)>1 )) AND ((ADM200508_122704.[Application Number])<>DMax("[Application Number]","ADM200508_122704")));

NOw, I want to run this code thru VBA and the code should ask the user names of the tables Table2 and ADM200508_122704.

I've just started using VBA in Access so I really don't know anything about the coding in it. If someone can please help me with this I would be grateful.

 

[blueshark]

Would I be correct that you have a predefined list of name you want to choose from. Or is the user free to make up any name they want?

Either way it sounds like you'd like to use a form to invoke the query. You can use the Docmd.RunSQL method. When writing you SQL statement just make it a text string with form control references in place of the table
names.

It might look soemthing like this:

Dim strSQL as String, ctl1 as String, ctl2 as String


'Table 2
ctl1=Forms!FormName!Control1

'ADM200508_122704
ctl2=Forms!FormName!Control2

'create SQL string watch the syntax 
strSQL="INSERT INTO "& ctl1 & "SELECT "& ctl2 & ".*FROM "& ctl2
etc. etc. etc. 

'print sql statement to debug window to see if it works
'don't forget to have the form open so it knows what ctl1 and ctl2 are
debug.print strSQL

'run it
DoCmd.RunSQL strSQL

 

[a7n9]

THanks blue.