SQL Insert in VBA

[Pat Backowski]

Hi All,

I'm trying to insert a field on a form into a table. All data types are
numeric, Long.

In my Main Module:
Public LinkID as Long

In my form, when "the" button is Clicked:
Dim SQL As String
Me.Text67.SetFocus
LinkID = Me.Text67
MsgBox LinkID ' this shows the correct value
SQL = "Insert into Temp_Pend_Link Values (LinkID)"
DoCmd.RunSQL SQL


I then get a Data Parameter Box asking for the Value of LinkID.

Any and all help much appreciated.

Still learing,
Pat

 

[Ken Snell \(MVP\)]

Are you adding a new record to the table? or do you want to write (update) a
value in an existing record?

 

[mscertified]

Try:

SQL = "Insert into Temp_Pend_Link Values (" & LinkID & ")"

If LinkId is text you need a single quote before the first double quote and
after the second double quote.

SQL = "Insert into Temp_Pend_Link Values ('" & LinkID & "')"

-Dorian

 

[Pat Backowski]

Dorian -
Thanks so much - worked outofthebox!

When yourself and others have helped me, more often than not, the resolution
to my problem has been the &'s and the quotes.

Can you possibly direct me to where I might learn more about this?

Either way, you were bang on! Many Thanks,
Pat.

 

[mscertified]

I learned Access via an online tutorial in combination with a book and
judicious use of the Help files. Also I made a lot of mistakes and learned
from them.
Regarding this specific problem you just need to realize that you are
building a SQL string so you can't embed the name of a variable, you need the
*contents* of the variable. SQL does not know the contents of an VBA
variable.

-Dorian

 

[rich]

MSCertified, I think my problem is similar to this.

My query is:

INSERT INTO tblApplRoleMapping ( RoleName, Applid )
SELECT DISTINCT lktblRoleMaster.RoleName, tmptblNewRoleApplMapping.ApplID
FROM tmptblNewRoleApplMapping, lktblRoleMaster
WHERE (((lktblRoleMaster.RoleName)=[Me].[txtNewRoleName]) AND
(([lktblApplMaster].[ApplName])=[FORMS]![SfrmNewRoleApplMapping].[ApplName]));


Can you help me understand when to use Me.<objectname> vs. [FORMS]!..........
I think that may be at least part of the problem that I'm having.

Thanks,
Rich

 

[Michel Walsh]

The short answer is that Me refers to a VBA object-variable, and no VBA
variable is directly reachable within a query (that also holds for VBA
defined constant). So, you have to use FORMS!formName!ControlName syntax to
reach a control (or a public VBA user defined function to reach VBA
objects).


A long answer is that a CLASS is a blue print. All objects defined against
such a blue print SHARE the code BUT they do NOT share the memory allocated
for variables member (DIM) defined between the header of the class and the
first function/subroutine/property defined in the CLASS blue print.


Class mobile
{
'----------- class variables--------
' repeated for each object Dim ... AS mobile

public numberOfWheel AS integer
public numberOfSeats AS integer

'-----------class code -----
' common to all objects Dim ... AS mobile

public function something( ) AS double
something = numberOfSeats / numberOfWheels
end function

}


So, since the code is SHARE among all objects variables of type 'mobile',
the code has to know where is the memory related to 'numberOfSeats' and
'numberOfWheels', since each object has its own set of variable. You could
have re-written the code like:

public function something( ) AS double
something = ME.numberOfSeats / ME.numberOfWheels
end function


where ME just explicitly says that even if the code is share among all
objects, of type mobile, it should 'take' the variables numberOfSeats and
numberOfWheels of the object that did call the code. Indeed, the real code
would be like:


Dim car AS new mobile
car.numberOfWheel=4
car.numberOfSeats=4
Dim bike AS new mobile
bike.numberOfWheel = 2
bike.numberOfSeats=1

bike.Something() ' <----


in the bike.Something(), the code, reaching the line:
something = ME.numberOfSeats / ME.numberOfWheels
will know the ME refers to bike, not to car, not to another
mobile-object-variable


An ACCESS FORM, and an ACCESS REPORT are special class. Access defines, for
us, a lot of class-variables (each control, among other things) and when we
open a form, Access generates an object-variable that Access stores in the
application object FORMS. The application collection FORMS is reachable
within an Access query, but, as you see, ME has to refer to a, one, object.
A query does not know which object ME would refer to. Would it refer to the
actual form having the focus? That could have been what Access team of
developers could have assumed, but they didn't, preferring to relay on the
more implicit syntax FORMS!formName!ControlName.



Hoping it may help,
Vanderghast, Access MVP

MSCertified, I think my problem is similar to this.

My query is:

INSERT INTO tblApplRoleMapping ( RoleName, Applid )
SELECT DISTINCT lktblRoleMaster.RoleName, tmptblNewRoleApplMapping.ApplID
FROM tmptblNewRoleApplMapping, lktblRoleMaster
WHERE (((lktblRoleMaster.RoleName)=[Me].[txtNewRoleName]) AND
(([lktblApplMaster].[ApplName])=[FORMS]![SfrmNewRoleApplMapping].[ApplName]));


Can you help me understand when to use Me.<objectname> vs.
[FORMS]!..........
I think that may be at least part of the problem that I'm having.

Thanks,
Rich

Try:

If LinkId is text you need a single quote before the first double quote
and
after the second double quote.


-Dorian