VBA String to Query (1 Viewer)

businesshippieRH

Registered User.
Local time
Today, 05:38
Joined
Aug 8, 2014
Messages
60
I am trying to pass a string that is created via VBA into an append query, but can't figure out where I'm going wrong. Any help would be appreciated, as I will be trying to do this sort of thing a few other times throughout this database. When I try to run it, it asks for NewRecordNumber in a parameter value pop-up. Then, the append query fails due to type mismatch (even if I enter what the proper # should be).

My VBA:
Code:
'Create New Record Number
    'Run the Query for Similar Record Distinctions and Years
    DoCmd.OpenQuery "qry_NEWRecDistYear"
    
    'Pull Highest Record Number from query
    Dim HighRecNumber As String
    HighRecNumber = DMax("RecordNumber", "qry_NEWRecDistYear")
    DoCmd.Close acQuery, "qry_NEWRecDistYear"
    
    'Create new Record Number
    Dim NewRecNumber As String
    NewRecNumber = (HighRecNumber + 1)

'Append To tbl_Records
DoCmd.OpenQuery "qry_AppendNEWRecord"

SQL for Query:
Code:
PARAMETERS NewRecordNumber Short;
INSERT INTO tbl_Records ( RecordDistinction, RecordDateMONTH, RecordDateYEAR, Revision, RecordNumber )
SELECT [Forms]![frm_NEWDateDist]![cmb_RecordDist] AS Expr1, [Forms]![frm_NEWDateDist]![txt_NewMo] AS Expr2, [Forms]![frm_NEWDateDist]![txt_NewYr] AS Expr3, 1 AS Expr4, "& [NewRecNumber] &" AS Expr5;

Thanks in advance!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:38
Joined
Aug 30, 2003
Messages
36,118
You can't refer to a variable outside VBA. You could create a public function that returned the value of the variable, and call that in the query.
 

businesshippieRH

Registered User.
Local time
Today, 05:38
Joined
Aug 8, 2014
Messages
60
So even if I were to run the SQL from VBA, there's no way to pass it on? I tried this as well, but wasn't sure of syntax...

If not, could you point me in the direction of how to write a public function? I'm a little new to VBA programming.

Thanks!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:38
Joined
Aug 30, 2003
Messages
36,118
You can if you built a string and executed it within VBA:

DoCmd.RunSQL "INSERT INTO..." & NewRecNumber & " As Expr5"

I used RunSQL because I think it will let you keep the form references inside the string. Otherwise they'd have to be concatenated into the string as well. Here's a primer on functions:

http://www.baldyweb.com/Function.htm

Here's a FAQ on building SQL in VBA:

http://www.baldyweb.com/BuildSQL.htm
 

businesshippieRH

Registered User.
Local time
Today, 05:38
Joined
Aug 8, 2014
Messages
60
Thanks! As I expected, I had the right idea, just the wrong syntax. I was trying to include the NewRecNumber inside the SQL quotes...
 

Users who are viewing this thread

Top Bottom