Dynamically choosing form fields

  • Thread starter Thread starter Judy
  • Start date Start date
J

Judy

I would like to select one of 13 fields to load on a form based on user
input. So far the only way I have been able to do this is to put all
the fields on the form stack on top of each other and make the one that
I want to appear visible. Is their a better way to do this?

Thanks,
Judy
 
Judy said:
I would like to select one of 13 fields to load on a form based on user
input. So far the only way I have been able to do this is to put all
the fields on the form stack on top of each other and make the one that
I want to appear visible. Is their a better way to do this?
Click to expand...


If you only want to display one field, then you can just
have a single control and set its ControlSource property to
the field name.
 
Thank you for your repy. I actually want the space for one field to
display one of a choice of thirteen fields. For example, load the
amount for January if it is January, load the amount for February if it
is february, etc.
 
Judy said:
Thank you for your repy. I actually want the space for one field to
display one of a choice of thirteen fields. For example, load the
amount for January if it is January, load the amount for February if it
is february, etc.
Click to expand...


I think that's what I was suggesting. A single text box
control that can be used to "load" any of the fields in the
form's record source query. Let's say your VBA procedure
has determined the name of the field that you want to
display and the name of the field is in a string variable
named strFieldName e.g.
strFieldName = "January"
Then, you can display the value of that field in a text box
by using a line of code like:
Me.thetextbox.ControlSource = strFieldName
If you want to display a different field in the same text
box, just change its ControlSource to the other field name.

If that's not what you were describing, please elaborate
where these 13 fields are coming from and how you want to
display the value of one of them. Note, in discussions like
this, it is very important to make the distinction between
**controls** on the form and **fields** in a table/query. A
control can be used to display the value of a field, but a
control is not the field.
 
Marsh,
Can I use something like this to allow the user to choose which fields to
display on a form? For example, the form/table have like 50 text
boxes/fields. Some users may need all 50, some may just need 3.
Can I have a list box to list all 50, user selects the fields they wish to
display, click on open form and there is the form with the selected fields?

I assume i would have the 50 text boxes named text1 thru text50, and a value
i that equals the number of values selected, a loop that will loop thru my
text boxes and change the control source accordingly. I know vba ok, but not
real advanced with SQL.

If this is possible, can you point me in the right direction?

Thank you very much Marsh.
 
David said:
Can I use something like this to allow the user to choose which fields to
display on a form? For example, the form/table have like 50 text
boxes/fields. Some users may need all 50, some may just need 3.
Can I have a list box to list all 50, user selects the fields they wish to
display, click on open form and there is the form with the selected fields?

I assume i would have the 50 text boxes named text1 thru text50, and a value
i that equals the number of values selected, a loop that will loop thru my
text boxes and change the control source accordingly. I know vba ok, but not
real advanced with SQL.
Click to expand...


Take a look at Duane's example. You can get a lot of good
ideas there.

As to your specific question, the form's record source may
not be an issue at all. Unless you have some other stuff
going on, there is very little reason to not to just let it
include all the fields in the table. You just make the
slected number of text boxes visible at the same time you
set their ControlSource and Left properties.

If you do need to specify the fields in the query,
concatenate the field names into a comma separated string in
the same loop where you set the control sources. Then, when
the field list is complete, add the other parts of the SQL
statement:

strSQL = "SELECT " & strfieldlist & " FROM thetable"
Me.RecordSource = strSQL
 
Back
Top