Is a reverse lookup possible?

[James Bowyer]

Bit of an odd one, I will admit, but...
I have a value in a table which I want to use to select a text box's value in a form, in other words, it will select the value of the text box named within the value of the form.

Is this possible?

The reason is I want to run a loop based on a number that decreases by 1 each time, and there is obviously a few different values that need to be picked up.

[James Bowyer]

Anyone? Or am I going to have to do a select case for every possible value?

[Stewart Ross]

Hi James. Could you explain a bit further what you mean? It is not clear from what you've posted what the table value is, and how it should relate to the textbox you mention.

Sample data would help us to see if there is a simple way to frame the 'how' steps of what you want to happen.

-Stewart

[James Bowyer]

No problem,
The table has several fields on it which are (tblFolders):
key ID (autonumber for primary key)
ID (Number)
FolderName (Text)
UserdependentYN (Yes/No)
UserInputfield (Text)

The UserInputField realtes to a specific text box on the form, for example a Value of "ClientName" in the userinputfield of tblFolders would relate to the combo box ClientName in the form, and a value of "JobNumber" would relate to the text box JobNumber in the form.

What I want the code to do, is then get the value contained within the specified box (such as the Client Name, or the Job Number) into the vba code, so I can then use it for other things.

The reason I am doing it this way, is the code sets up folders, but up until now, I have had to specify all the folders in the vba code. I have therefore specified them in a table and have used Dcount to specify the number of times it loops the code, and the ID value in the table to sepcify which value its currently on.

I hope that makes some sense!

[Stewart Ross]

The following simple function returns the value contained within a particular named control on a given named form:

Public Function FormFieldValue(ByVal FormName As String, ByVal FieldName As String)
    FormFieldValue = Forms(FormName).Controls(FieldName)
End Function

You could use it by supplying the name of the form containing the textboxes you are looking up, and the fieldname as contained in your table. Note that the fieldname is a text string without the square bracket delimiters, as in "Your Field Name" and not "[Your Field Name]".

Example from VBE immediate window on a live form:

? formfieldvalue("frmNotes", "Heading")
Finance

You can of course supply appropriate variable names or recordset fields as arguments in place of the fixed values shown above, like this:

YourValue = formfieldvalue(yourformname, RS!tablefield)

-Stewart