Dynamic Coding

[Vel]

Hello,

I was wondering if there was a way to select a field on a
form dynamically based on the information in a text box.
For example, I have a form, frmSearch, which contains two
unbound text boxes txtForm and txtStarting.

I want to select a field based on those textboxes and
update it like so:

If isNull(txtStarting) Then

Forms!frm(Forms!frmSearch!txtForm)!txt(Forms!frmSearch!
_txtForm) = Forms!frmSearch!txtID

Else

Forms!frm(Forms!frmSearch!txtStarting)!txt(Forms!
_frmSearch!txtForm)ID = Forms!frmSearch!txtID

End If

Now, that's not exactly what I need, but that's what I
need to be able to do. The reason for this is that
frmSearch is a form with no Record Source, which is given
one based on where it is entered from. It then allows
the user to search, by name, for a given item. After
clicking a "GO" button, it populates either the search
field in the form with the value, or it populates the
foreign key field in the form with the value, depending
on where the user needed to search from. I know it may
seem complicated, but if you actually see it in action
it's a rather smooth way of avoiding making a pile of
identical forms with different RecordSources.

Thanks,

Vel

 

[Albert D. Kallal]

You can always reference any collection with a string value. Understanding
how to use collections in ms-access is really fundamental. The following
applies to your own custom collections, or any of the built in collections
like forms, queries, reports etc. The following even applies to reocrdsets,
and thus you can use variables in recordsets to define what field you are
referencing.

So, for forms you can you can use:

dim strMyF as string

strMyF = "frmSearchName"

forms!frmSearchName
forms("frmSearchName")
forms(strMyF)

All 3 of the above are equivalent.