CipherIS
asked on
MS Access - Dynamically select fields prior to opening a form
Is there a way to dynamically to select fields on a form?
I created a form and set the recordsource to a query. I am displaying the form as a datasheet view.
I am opening the form from Form A which is building a where clause. So i'm using Docmd.Open form. User wants to have the fields displayed on Form A and then have the result form (we'll call Form B) to display with the fields selected from form A.
I created a form and set the recordsource to a query. I am displaying the form as a datasheet view.
I am opening the form from Form A which is building a where clause. So i'm using Docmd.Open form. User wants to have the fields displayed on Form A and then have the result form (we'll call Form B) to display with the fields selected from form A.
Hi,
yes, with Datasheet View you can do that. You need to add all selectable fields from Form A as controls with the desired datatype/controltype (like textbox, checkbox, combobox, everything possible in a datasheet view) in Form B.
When you've selected the desired columns in Form A you need to pass the field list i.e. as CSV string ("field1,field2,field3") in OpenArgs to Form B and then use a loop through all controls in Form B in Form_Load and use the "ColumnHidden" property of the control to hide or unhide the desired controls. The CSV value from OpenArgs can easily be split into an array to compare the name of the bound field of the current control in the For Each loop with the name list in the CSV string (you can also use a string like ",field1,field2,field3," so that every field name is surrounded with a comma on each side and then simply compare the bound field name using InStr - that avoids a second loop and the need to split that into an array).
Of course the Datasheet form will always load all data and not only the desired data, the unwanted fields are only hidden.
Cheers,
Christian
yes, with Datasheet View you can do that. You need to add all selectable fields from Form A as controls with the desired datatype/controltype (like textbox, checkbox, combobox, everything possible in a datasheet view) in Form B.
When you've selected the desired columns in Form A you need to pass the field list i.e. as CSV string ("field1,field2,field3") in OpenArgs to Form B and then use a loop through all controls in Form B in Form_Load and use the "ColumnHidden" property of the control to hide or unhide the desired controls. The CSV value from OpenArgs can easily be split into an array to compare the name of the bound field of the current control in the For Each loop with the name list in the CSV string (you can also use a string like ",field1,field2,field3," so that every field name is surrounded with a comma on each side and then simply compare the bound field name using InStr - that avoids a second loop and the need to split that into an array).
Of course the Datasheet form will always load all data and not only the desired data, the unwanted fields are only hidden.
Cheers,
Christian
And as far as selecting which fields to display, in FormA, use a multi-select listbox, with the RowSourceType set to FieldList and the RowSource set to whatever table or query you want to select the fields from.
Another way to do this is to simply present the users with all of the fields in a datasheet, and allow them to hide the fields they don't want to see. I wrote an article recently on how to save those settings and restore them each time the user opens a form in datasheet view.
Another way to do this is to simply present the users with all of the fields in a datasheet, and allow them to hide the fields they don't want to see. I wrote an article recently on how to save those settings and restore them each time the user opens a form in datasheet view.
One way: As I think I understand from the question. First form, few fields build a where clause to the second form. And you need the second form to display only those fields that form the where clause.
While building the where clause, build the select fields to the same fields in the where clause.
Set the formB record source to this built SQL.
Example:
SELECT fld1, fld2, fld3 WHERE fld1=x AND fld2=y AND fld3=z;
Then in Form_Open event of second forrm:
Find the record set of the sql. Then get the field names, and hide all other controls.
ed.
I tried visible but looks does not work with datasheet!!!
While building the where clause, build the select fields to the same fields in the where clause.
Set the formB record source to this built SQL.
Example:
SELECT fld1, fld2, fld3 WHERE fld1=x AND fld2=y AND fld3=z;
Then in Form_Open event of second forrm:
Find the record set of the sql. Then get the field names, and hide all other controls.
ed.
I tried visible but looks does not work with datasheet!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you want to make a datasheet subform invisible, you have to reference the subform control, like this:
Set ctl = Me![subDailyCalendar]
ctl.Visible = False
ASKER
The visual helped out a lot
On Error Resume Next
'/ first hide all the controls on FormB
Dim ctl As Control
For Each ctl in Me.Controls
ctl.Visible = False
Next
Dim var As Variant
For Each var In Me.List2.ItemsSelected
Me.Controls(Me.List2.ItemD
'/ also set that controls associated Label to be visible
Me.Controls(Me.List2.ItemD
Next