I have read/participated in a number of threads recently that all refer to the requirement to synchronise a selected value with the complete details about that selection, maybe even to change those details. This is a rather grandiose way of saying, for example, that if I select a part number from a combo/list box, is it possible to display the details about that part number and then change those details?
The answer is, “Yes.”
Oh, you’re still here? I suppose you want to know how. All right, I promise not to be so flippant/pompous in the remaining post.
The requirement is common. When you first experiment with Access you are satisfied with the continuous form view, but this doesn’t give you the richness of a single record form view with all the opportunities of command buttons, etc. and the ability of getting the user to concentrate on this record and no other. OK, the continuous forms view is the most appropriate for mass data entry; but this discussion is about the random enquiry/update that is just as common in the business situation, and for this you need the single form view.
Wonderful, when you test your single form system with five test records you are quite happy to use the record navigation at the foot of your form to move between the records. It takes a while to appreciate your users’ complaints that this is not the best way to search one thousand records. What is required is to give your users an index to their data – a shortcut to the record they want. This is when you start to experiment with filters and queries, and then post questions on dBforums about queries/list boxes/combo boxes and synchronizing data.
There are two ways of fulfilling this requirement: you can use a main form / sub form structure or you can use two different forms, one to act as the index and one to act as the modifiable detail.
The main form / sub form structure has the advantage that MS Access takes care of the synchronization for you; the separate form solution has the disadvantage that you have to take care of the synchronization (through VBA) but allows you almost limitless opportunities for responding to the needs of the moment. As the separate form solution requires VBA knowledge (and is fraught with all sorts of pitfalls) I will save it for another post (or two).
So now we need to design a composite form whereby our user selects a value and, lo and behold, all details about that selection are displayed and moreover these details are modifiable. For this we use a main form / sub form structure. Design your sub form to be the same as that you would have designed if this was a simple single form solution. Make sure you include the primary key of the record even if you have to make this value invisible – the primary key is how the synchronization will work. Enable those fields on your sub form that you allow for modification, etc., etc. (I should mention for beginners to Access that there is no special category for sub forms. Sub forms are normal forms used in a special way. So when I say “design a sub form” I really mean design a form that will eventually be used as a sub form.)
Now you need to consider how to provide your users with the index into their data. List boxes and combo boxes are of course the first choice; there is another but I will leave that until later. Whatever method you choose the control will be placed upon the main form and this main form needs to include the primary key, again even if this is invisible – see the preceding paragraph. Another design consideration is that you should allow the user to change nothing on the main form, after all you would not expected to alter data in an index or table of contents.
By now you will have realized that there is no point of including extraneous/additional data on your main form, the list box / combo box is the thing. OK you can decorate the main form with all sorts of reassuring messages but the data that you want to present to your user is on the sub form. Do not allow changes to your data on the main form otherwise there will be tears.
I mentioned that the main form must include the primary key for the synchronization to work, but if the main form has only a list box / control box then how is it included? Well for list boxes and combo boxes WYSIWYG is not true. WYS (what you see) is any data where the column width is set to greater than zero. WYG (what you get) is the value of the bound column. Normally, but you are by no means restricted to this, you base your underlying list/combo box query on two extracted values: the primary key and the value that your user sees. You bind the list box / combo box control to the primary key but set that column width to 0. Your user now sees only the other value and goes up and down the list seeing something that is familiar to him/her. But, and this is important, the value of the list box / combo box control is the hidden bound value which, if set up as described above, is the primary key and this should be the value linked to the sub form primary key for synchronization. Eurika! Every time your user clicks on a new entry in the list the sub form responds with the data that corresponds to the value of the list / combo box.
This should be enough to get you started. You can develop endless variations on this theme. All that remains is to mention the other option for the main form. Well instead of a list box / combo box why not make the main form a continuous form based on a sub set of data from the table/view/query in question.
The separate form solution requires knowledge, no command, of Visual\Basic and to be honest I’m not in the business of “Teaching my grandmother to suck eggs”; anyone with that experience has been here before and does not need me to state the obvious.
Rod I'm with you so far, and I've used the combobox-for-subformfilter method with great success. But NOW my users want occassionally to be able to see all the records in the subform, i.e. a filter value of '*'. How do you do this??
I posted a seperate thread about 10 mins ago, and then read yours...
I assume your main form contains the combo box control and the Row Source of that control includes a WHERE clause thus restricting the result set to a sub set of the source data. Now the user wants to see everything in the combo box thus enabling him/her to see/edit everything in the subform(s).
I know of no way to do this via the offered Access GUI (if anyone else does then shout) but it can be done quite easily via VBA. The most difficult part is to decide what mechanism you are going to give the user to control this process. If it's a simple on/off situation then consider a toggle button, otherwise you may opt for an array of command buttons, or an option group, or ..... Well you get the idea.
To complete this solution you need to know a little VBA and a little SQL, fortunately nothing too complex. The solution itself relies upon poking different SQL strings into the Row Source of the combo box control. For the purpose of this example let's assume it's a simple on/off situation and that we are using a toggle button for the purpose with the meaning that if the button is in (true) we show all records whereas if it is out (false) we only show the filtered sub set. The VBA code for the Click event then looks something like this:
If Me.MyToggleButton = true then
Me.MyCombobox.RowSource = "SELECT Transaction_Id FROM Transaction"
Me.MyCombobox.RowSource = "SELECT Transaction_Id FROM Transaction WHERE Amount > 1000"
You may need to include a Me.MyCombobox.Requery - I can't remember exactly what Access does.
Two warnings though. Poking values into these sorts of properties will probably cause the On Current and other events for the form to fire. If you have anything attached to these events then they will be invoked. Also the toggle button setting and row source property are persistent, that is they retain their most recent setting from session to session. this may not be what you want.
I used the same link to add the null row to my combo box! The problem is I don't know how to tell VBA what to do if the value passed from the combo box is null. It's something like 'find records Like '*'' but I'm not sure of the syntax.
Now I don't know how to say in VBA 'if ItemNum is null, show all the records'....
I haven't tested thus fully but, although not elegant, it looks promising. It relies on the observed fact that deleting either of the link field properties seems to force Access to show all records in the sub form.
Therefore incorporate in your Combo39_AfterUpdate code the following:
If Me.Combo39 = "TheValueForAll" then
Me.mysubformcontrol.LinkMasterFields = ""
Me.mysubformcontrol.LinkMasterFields = "Combo39"