UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> What's Wrong With A Simple Insert Into Statement Using Integer Variables?, Access 2016    
 
   
rmcgaffic
post Mar 4 2018, 03:44 PM
Post#1



Posts: 138
Joined: 18-June 06



Can anyone suggest the correct syntax for an INSERT INTO statement using variables which are defined as integers?

I have defined table tblPersonOrganization with two non-key fields
PersonId
OrganizationId

I have a simple form with one command button with the following code behind it.

Private Sub Command0_Click()
Dim mPersonId As Integer
Dim mOrganizationId As Integer
mPersonId = 217
mOrganization = 54

'Does not work
'CurrentDb.Execute "INSERT INTO tblPersonOrganization " _
'& "(PersonId, OrganizationId) VALUES " _
'& "('mPersonId', 'mOrganizationId')"

'This doesn 't work
'CurrentDb.Execute "INSERT INTO tblPersonOrganization " _
'& "(PersonId, OrganizationId) VALUES " _
'& "(mPersonId, mOrganizationId)"

'This works
CurrentDb.Execute "INSERT INTO tblPersonOrganization " _
& "([PersonId], [OrganizationId]) VALUES " _
& "(217,54)"
End Sub

Can anyone tell me what I am doing wrong?

I receive run time error 3061, Too few parameters, expected two when I attempt to use variables.

Thanks,
Bob

Go to the top of the page
 
theDBguy
post Mar 4 2018, 03:57 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,950
Joined: 19-June 07
From: SunnySandyEggo


Hi Bob,

When you use variables, you’ll need to concatenate them into the statement. For example:

& “ VALUES(“ & Variable1 & “,” & Variable2 & “)”

Sent from phone...
Go to the top of the page
 
DanielPineault
post Mar 4 2018, 04:02 PM
Post#3


UtterAccess VIP
Posts: 6,273
Joined: 30-June 11



Have you tried

CODE
CurrentDb.Execute "INSERT INTO tblPersonOrganization " _
                      & "([PersonId], [OrganizationId]) VALUES " _
                      & "(" & mPersonId & "," & mOrganization & ")"



Go to the top of the page
 
rmcgaffic
post Mar 4 2018, 05:00 PM
Post#4



Posts: 138
Joined: 18-June 06



Thank you both!

Boy. that syntax is tricky!
Go to the top of the page
 
John Vinson
post Mar 4 2018, 06:02 PM
Post#5


UtterAccess VIP
Posts: 4,276
Joined: 6-January 07
From: Parma, Idaho, US


It's just that VBA is one language and SQL is a different one. The SQL query engine knows absolutely nothing about VBA variables or constants, and vice versa. Your first syntax with the ' marks was trying to insert the literal text strings 'mPersonId' and 'mOrganizationId', which will error since those strings aren't integers; the second syntax just gets a puzzled look (with maybe a "huh!?") because the query engine just has no idea what you're talking about!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2018 - 02:14 PM