Pass Field Name Using Variable to SQL string

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I want to pass field names to an SQL string to identify the current of many
fields that I have to update in the table tblData (ie Fields ----> Jan06,
Feb06, Mar03......Dec09).

I want to use a recordset with SQL to identify the current field and edit
and update the current record in the current field.

The field is identified in the SQL as followings:

tblData." & varFieldName & " where the variable might be set to "Jan".
<----------

This syntax works perfectly in the SQL to identify the current field in the
db.openreocrdset(strsql).

The update is NOT working :
..edit
!Jan = dblAmount <---------------This works
![" & varFieldname & "] = dblAmount <----------This does not work
..update

This error message "item not found in collection" implies that it is
looking for a field "Jan" in tblData but is unable to find "Jan" which does
exists. Highlighting the variable indicates that the variable is correctly
set to Jan.

How can I make this work using variables. My syntax must be wrong? I am
using a variant variable and had the same results with a string variable.

Thanks

Ross




.

Relevant Pages

  • Re: Connection.Execute causes untrappable runtime error
    ... Set Recordset = Database.Execute ... If I want to do a delete or Update, I do a Database.Execute ("SQL Syntax") ... I believe that the dataenvironment is the best thing in vb6 (The program are ...
    (microsoft.public.vb.database.ado)
  • Re: Simple Insert Into...
    ... The form is based on an SQL in the form's Record Source property, ... holds the current text in the control, ... Sometimes the unbound controls do have a value (such as when they are set ... I need this value to assign a unique ID to the entire recordset being ...
    (microsoft.public.access.modulesdaovba)
  • Re: Emailing a Report
    ... the reason Debug.Print was done was to test the SQL of what is being generated. ... If you will add and change data here, you need to set this up as a main/subform -- or just a main form that DISPLAYS data from Users and allows modifications to Agreements. ... If you are just going to send Agreements, then AgrID should be added to the form RecordSet so you can capture it. ... maybe also some kind of category for this followup type ...
    (microsoft.public.access.modulesdaovba)
  • Re: open access recordset as an excel spreadsheet
    ... The actual SQL statement depends on values from the form. ... I'm not sure what I would have to change in the SQL string to go from ... it I should creaate the equivalent ADO string in this function and pass ... >> I guess it works OK but the recordset is empty. ...
    (microsoft.public.access.modulesdaovba)
  • Re: What is the advantage of Event programming?
    ... >> dice what lumps of data you need. ... which wouldn't work if the recordset has more than ... >My knowledge on using SQL statements is somewhat limited. ... Requesting a limited number of records at a time, was useful in the old days ...
    (microsoft.public.vb.general.discussion)