Displaying specific fields of a data set based on combobox choice (1 Viewer)

[barfly78]

Hi, I am new to Access (4months) and new to this forum (5 minutes) although have very much appreciated the excellent advice you have given other members.

I am struggling with the following though:

I have a Table with 57 fields. I would like to display this table in a form as a subform, but only certain fields depending on what selection is made in a combo box.

For instance, if the user selects "Missing Information" in the Combo Box, then the form will show a few standard fields such as ID, Market, Sales Manager, and then some specific ones such as date missing information requested and date missing information received.

If the user selects another option, again the standard fields will remain plus a few different ones.

I have done much searching on this and feel like I am so close but so far. I have looked into controlling the record source of the subform, columhidden =false and a multitude of others. All of which may or in fact probably do help in this situation but I can't seem to put it all together.

I would really appreciate any help anyone could give.

Thank you

Barfly78

 

[DonkeyKong]

If you are using VBA to update your form, your best bet would be to dynamically create the form. I haven't done something EXACTLY like this, but it's all basically the same idea.

Use this...

Sub ComboBoxNameHere_Change()
 
dim db as database
dim rst as recordset
 
     set db = currentdb
 
     'Use an if or select case to determine what the selection is...
     Select Case ComboBoxNameHere.Value
 
           Case "Missing Information"
 
                 strSQL = SELECT [Field A], [Field B], [Field C], [whatever] FROM[TableName]...... and so on
 
                 set rst = db.openRecordset(strSQL)
                 rst.movefirst
                 ..... whatever and ever do some searches on the rest
 
End Sub

Every time you change the combobox value, this code will run.

 

[barfly78]

Thank you. I haven't got it to work as yet, but definitely have something to work with. Much appreciated.

 

[DonkeyKong]

If that isn't working for you.... I forgot that Access is slightly different from Excel. I use Excel a lot more.

You can do the same thing as I told you, but have it auto generate some of the code for you if you right click on the combo box in design mode, click properties, click the Event tab, click inside the On Change option, and then click the "..." button. This will give you some different methods to play around with.

Selecting the "Code Builder" option will give you the basics of the code I posted above.

 

[Libre]

One idea that occurs to me, is instead of having the different subforms directly on the parent form, you could design a set of forms with all the different subforms you want - each with a different name and having the one subform you want to show. So based on the combo box selection, one of these forms would open with the subform embedded on it - or forget the subform and just run a query - with just the columns that you want to show.
Sometimes it's much easier to use different objects that fullfill a single purpose, and then just display the one that you want at the time, rather than having a single object that fullfills many purposes.