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: 126
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: 72,444
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...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
DanielPineault
post Mar 4 2018, 04:02 PM
Post#3


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



Have you tried

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




--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
rmcgaffic
post Mar 4 2018, 05:00 PM
Post#4



Posts: 126
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,235
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!

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 02:27 AM