use a variable for the field name in an INSERT INTO statement

[marvo]

Thanks for any ideas on how to use a variable for the field name in an INSERT INTO statement!

I want to have SQL INSERT INTO tablename, variablefieldname

I have declared a variable and assigned a value, but the code just considers the variable name to be a literal field name. Must be some syntax that means the code recognises the variable value?

Thanks

 

[plog]

Please post your code.

 

[JHB]

How do you want to execute it, using a query, by DoCmd.Execute or ..??

 

[The_Doc_Man]

In general, the principle will be to dynamically build your SQL code as a string, using string concatenation methods to insert the variable with proper punctuation (i.e. appropriate to the field's data type), and then use either of the common methods for executing SQL.

string-variable = "INSERT INTO table-name (field-list) VALUES (data, data, " & variable & ", more-date) ;"

DoCmd.RunSQL string-variable

 (or)

CurrentDB.Execute string-variable

 

[marvo]

I have the following simplified sample code just to test the theory, before I write the full routine, but it should demonstrate the point.
It should insert the value "John Smith" into the field UmpireName1 in the table mergerecords.
However, I keep getting the error message that 'Umpire' is not a recognised field - so it looks like the code is literally trying to insert into a field called Umpire, rather than recognising 'Umpire' as a variable name :
--------------------------

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please feel free to Remove this Comment
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Dim StrSQL As String
Dim UmpireName As String
Dim UmpireNumber As Integer
Dim Umpire As String

UmpireName = "John Smith"
UmpireNumber = 1
Umpire = "UmpireName" & UmpireNumber

StrSQL = "INSERT INTO mergerecords ([Umpire]) VALUES ('" & UmpireName & "' );"

DoCmd.RunSQL StrSQL

 

[plog]

StrSQL = "INSERT INTO mergerecords ([Umpire]) VALUES ('" & UmpireName & "' );"

If both Umpire and UmpireName are to be used as variables, why are they handled differently in the SQL you are building? You have ampersands and quote marks around UmpireName, but not Umpire.

However, this might be moot. Any table that numerates field names (Umpire1, Umpire2, Umpire3, etc.) is improperly structured. How many Umpire fields do you have in mergerecords? This isn't the way to store data like that.

 

[marvo]

Hi Plog
I was hoping to avoid the 'normalisation' question.
My DB is correctly structured, and I'm not generally storing data this way. It's part of a larger problem -

For context, I have a DB recording hockey match details and the umpires.

tblFixtures - holds main info like FixtureID, Date, Team, Venue, Time
related to -
tblFixturesUmpires - just holds foreign keys (FixtureID, UmpireID) to link Umpires with Fixtures
related to -
tblUmpires - has Umpire info like UmpireID, UmpireName, email address, phone number etc

If I run a query to give me all the details of Fixture on a certain date I get 1 row returned;

FixtureID, FixtureDate, TeamName, Venue, Time....

If I run a query to give me all the details of Fixture on a certain date, including Umpires, I get 2 rows returned;

FixtureID, FixtureDate, TeamName, Venue, Time, UmpireName
FixtureID, FixtureDate, TeamName, Venue, Time, UmpireName

for easier reading this could like;

275, 29/09/2018, Mens1, Home, 14:00, John Smith
275, 29/09/2018, Mens1, Home, 14:00, Davy Jones

(as I would expect for a well-structured DB!)

However - for reasons I won't bore you with, for certain procedures, I would like just ONE (merged) record per one fixture
e.g. for a Report - (even when grouped) one fixture will need to appear over 3 lines, or if I want to .SendObject to send an email to people to provide details of a fixture, it will create 2 emails - 1 for each row in the record set, etc.,
So, just somtimes (mainly for presentation purposes), I want to collapse/merge together 2 related records, so I get a single record for a single fixture
e.g.
275, 29/09/2018, Mens1, Home, 14:00, John Smith, Davy Jones

ps _ I don't want to simply concatenate the umpires names in a string, I want them parsed into separate fields.
Hope this makes sense

 

[Gasman]

You could have said this thread was the result of this thread
https://www.access-programmers.co.uk/forums/showthread.php?t=301600

 

[marvo]

You could have said this thread was the result of this thread
https://www.access-programmers.co.uk/forums/showthread.php?t=301600

:bangheadidn't know you could do that! newbie error! Sorry!

 

[marvo]

Solved. Plogs observations about quote marks prove to be the key - that is how you get the correct syntax to recognise the variable name:

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please feel free to Remove this Comment
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Dim StrSQL As String
Dim UmpireName As String
Dim UmpireNumber As Integer
Dim Umpire As String

UmpireName = "John Smith"
UmpireNumber = 1
Umpire = "UmpireName" & UmpireNumber

StrSQL = "INSERT INTO testMergeRecords (" & Umpire & ") VALUES ('" & UmpireName & "');"

DoCmd.RunSQL StrSQL