Assign A Variable to rst!(Variable)

Hello,

I want to use a variable to assign a field name. Can someone explain to me how I would go about it?
Here's the code.

Dim strTableName as string
Dim fldName as String
Dim i_strTextBoxItem as String

TblName = "tblRevenue"
FldName = "Revenue"
i_strTextBoxItem = "Great"

If Not App_Conn.State = adStateOpen Then
          App_Conn.Provider = App_Provider '"Microsoft.Jet.OLEDB.4.0" 'App_Provider
          App_Conn.ConnectionString = App_InventoryPath ' C:\DS3\DS3_Data\DS3.MDB"
          App_Conn.Open
End If

' Open the recordset
rst.Open "SELECT * FROM " & strTableName, _
App_Conn, adOpenKeyset, adLockOptimistic
     
rst.AddNew                    ' Add a new record

******************* Problem Area here *************

rst!fldName = i_strTextBoxItem          'Specify the values for the fields
     
'Revenue is the name of the field that is being assigned to fldName variable. This field name
will change - that is why I need to assign it to a variable.

rst.Update                    ' Save the current record in the Recordset
rst.Close                    ' Close the recordset

-ADawn
ADawnAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
Use the fields collection directly:

rst.Fields(i_strTextBoxItem).Value = "My New Value"
0
 
DennisBorgCommented:
>rst.Fields(i_strTextBoxItem).Value = "My New Value"

Tim is 100% correct, but in this particular example, fldName stores the field's name, so you would write it as follows:

   rst.Fields(fldName).Value = i_strTextBoxItem

Since i_strTextBoxItem equals "Great" and fldName equals "Revenue", this assigns the value "Great" to the field named "Revenue".


Give Tim the points; I just thought I'd help clarify.

-Dennis Borg
0
 
DennisBorgCommented:
>rst.Fields(fldName).Value = i_strTextBoxItem

The abreviated form of this is:

   rst(fldName) = i_strTextBoxItem


0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TimCotteeHead of Software ServicesCommented:
Cheers Dennis, tag team again? I must admit that I am a little tired right now so probably not looking hard enough at the code. It is 10:15pm here and I still have 8 hours to go!
0
 
DennisBorgCommented:
Tim:

>Cheers Dennis, tag team again?


Yep!  Get some sleep, Tim; tomorrow is *your* turn!  ;-)

There are times the code looks blurry to me too!  ;-)


-Dennis Borg
0
 
TimCotteeHead of Software ServicesCommented:
Get some sleep, I wish I am here on the nightshift (10pm to 6am) to babysit users through an application roll-out. Only half an hour to go and then I can head to my bed!
0
 
DennisBorgCommented:
Hope the rollout goes well, and that you don't have any real problems.

Is the night shift your normal shift? Or are you there only because of the roll-out?
0
 
TimCotteeHead of Software ServicesCommented:
I was only there on the nightshift because of the rollout, all went well as far as that goes. I normally work days though one week in 8 I cover out-of-hours support which basically means I have to leave the mobile phone on 24/7 and answer it if it rings so it generally isn't too much of a problem. I just hate having to do odd shifts that screw up my normal pattern especially as I don't get overtime for it, but at least I do get a couple of days of instead.
0
 
ADawnAuthor Commented:
Thank TimCottee,

DennisBorg: Thanks for your input. I figured out what Tim was conveying in him first comment.

-ADawn
0
 
DennisBorgCommented:
You're welcome, ADawn!  Glad to have helped and that you got it working.

-Dennis Borg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.