Solved

MS Access - Dynamically select fields prior to opening a form

Posted on 2014-07-27
7
492 Views
Last Modified: 2014-07-31
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.
0
Comment
Question by:CipherIS
7 Comments
 
LVL 84
ID: 40223944
You could use FormB's Open event to check FormA to see which columns should be displayed - assuming FormB has ALL Columns which could be shown, of course. I'm not sure how your user will select the columns to be displayed, but if there's something like a ListBox on FormA where the user will select one or more items:

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.ItemData(var)).Visible = True
    '/ also set that controls associated Label to be visible
    Me.Controls(Me.List2.ItemData(var)).Controls(0).Visible = True
Next
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40223958
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40224163
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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 30

Expert Comment

by:hnasr
ID: 40224941
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!!!
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40225142
Are you selecting the fields (to place on a form with controls bound to them), or the values from the fields (to filter the second form)?  If you are interested in filtering by values selected from fields, my Fancy Filters database may be something you could adapt for your needs.  Here is the link for downloading it:

http://www.helenfeddema.com/Files/accarch129.zip

And here is a screen shot of the form:
Fancy Filter formIn this database, the filters are selected from combo boxes in the form header, but they could be selected on another form.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40225148
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

Open in new window

0
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 40232426
The visual helped out a lot
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now