Variable within a variable in a query!!!

[Nelz]

I have a query that I want to use to append a table. I have 10 sets of fields that can be updated, and I want to use the same form to update ANY of the 10 sets of fields. The field names are the same except for the number.....for example...there is a fild in the table called Opt8Choices

There is also an Opt9Choices...etc.... Is thewre a way that I can specify the fild name to be updated by calling it Opt[enter a number here]Choices or something like that? How about putting a field on the form where the user enters a number between 1 and ten, and all the field names get their number value from that field.... like Opt [the field value from the form ] name etc. There are 5 field names that contain numbers like that...and 10 sets of them.. I'd rather not make 10 forms if I dont have to.

I tried all the obvious ways and got rejected.. Any ideas???

 

[CajunCenturion]

There are several ways to do this, among them are access via collection keys, by using the Eval function, or by a public user function in a module. To help figure out which approach, could you post the relavent code around which you'll need the indirect variable reference?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Nelz]

well...basically I want to inser t a variable in the field name that I want to update.

Like in an update query it asks which field to update...and instad of calling the field Opt1name I want to call it Opt[variable number]name where the variable number is a number entered either in the original form that is calling the query, or a number that the user is prompted for.

Any ideas? I thought it would be something like: field name= Opt&[user input]&Name ...but that didnt work.

 

[JPMontreal]

Hi,

Do you know VBA?


Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[Nelz]

I can do a little if i have instructions....I've used it some..

Heres the problem somewhat reworded...
I want to make an update query...and in the field name I want the part of the field's name that's a number (the fields are named Opt1Name, Opt2Name, Opt3Name etc... ) to get its value from an input form.

So when a person enters a 3 in one box and COLOR in another...it updates Opt3Name to COLOR. I have to do it with a bunch of fields.

 

[Nelz]

I tried this: "Opt" & [Forms]![Options form]![Text8] & "Name" as the field name...but it doesnt work....

 

[JPMontreal]

Hi,

1. Create a form link to your table and put fields that will let you recognize the record you wish to update.
2. Create a table with one column and enter number in it from one to the number of fields you have.
3. Put a combo box and use the table with the number as “Row Source”. Set the property “Limit To List” at Yes. This way they won’t enter a number outside your number of fields.
4. Put a text box
5. Create code like that in the module of this form:

Private strNo As String

Private Sub cboNo_Click()
strNo = Str(Me.cboNo.Value)
End Sub


Private Sub Text47_GotFocus()
Me.Text47.ControlSource = "Opt" & Trim(strNo) & "Name"
me.Refresh
End Sub


Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[Nelz]

Hmmm...I was able to build the first part and create the appropriate field names in text boxes on the form from a numerical dropdown list as you suggested...I just cant get the update query to recognize that text box on the form as a valid field name in the query....

I am not following the VBA part or how to set it up that way.

 

[JPMontreal]

Hi,

You don’t need an update query anymore. In the textbox you enter the color and when you will move to a new record, since the form is base on the table, it will be updated automatically.
To get to the module for that form, click View > Code.


Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[CajunCenturion]

What you can do is to build the variable name like the following:

FieldName = "Opt" & [Forms]![Options form]![Text8] & "Name"

Then you can use that a key into the fields collection of the recordset

Recordset.Fields(FieldName) = xxx

or into the Forms Control Collection

Me.Controls(FieldName) = xxx

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Nelz]

Well...I tried what you suggested...I have:
Private Sub Text58_GotFocus()
Me.Text47.ControlSource = "Opt" & Trim(strNo) & "Name"
Me.Refresh
End Sub
for the text box...

I have the form linked to the appropriate table...and I have this code for the combo box that picks the number:

Private strNo As String
Private Sub Combo416_Click()
strNo = Str(Me.Combo416.Value)
End Sub


But every time I enter anything in the text box it refreshes and disappears....

 

[Nelz]

Well ...I caught one mistake. I had Text 58 as my number and I left your text47 in there. Changed that, and I now get the Contrlo Source being set....but without the number in the middle. It just says OptName no string number in the middle...

 

[Nelz]

OK...I think I have it.

I changed the name of the combo box that selects the number to SelectOption...

I changed the code on the text box to this:

Private Sub Text58_GotFocus()
Me.Text58.ControlSource = "Opt" & Me.SelectOption & "Name"

End Sub

It worked. It updated the correct record...etc. Thanks a lot. Sometime just getting a starting point sparks the ideas for me and I can get going from there.

I really appreciate your help!!!!

 

[JPMontreal]

Hi,

I’m glad I could help.




Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com