businesshippieRH
Registered User.
- Local time
- Yesterday, 19:56
- 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:
SQL for Query:
Thanks in advance!
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!