ACCESS FEED: RE: [MS_AccessPros] Add Dynamic Column name

Selasa, 24 Mei 2011

RE: [MS_AccessPros] Add Dynamic Column name

Jim-

Blow away the quotes and use Execute instead:

CurrentDb.Execute "INSERT INTO Tbl_InvDetails ([" & colName & "]) Values (" &
Q1 & ")", dbFailOnError

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of luvmymelody
Sent: Tuesday, May 24, 2011 10:56 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Add Dynamic Column name

Hello,

I am trying to add the column name dynamically using VBA,
This is the code snippet that I am trying to use,

Private Sub Detail_Click()
Dim Q1 As Integer
Dim colName As String

Q1 = Me![SubformEventDetails].Form![Quantity]
colName = Me![SubformEventDetails].Form![Item_Name]
DoCmd.RunSQL " INSERT INTO Tbl_InvDetails ('" & colName & "') values ( " & Q1 &
" ) "

End Sub

I get the following error
The INSERT INTO statement contains the following unknown field name:
"6ft_Table". Make sure you typed the name correctly, and try the operation
again.

However, DoCmd.RunSQL " INSERT INTO Tbl_InvDetails (6ft_Table) values ( " & Q1 &
" ) " works perfectly fine.

Thanks,

Jim Wagner

------------------------------------

Yahoo! Groups Links