Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126

    Unanswered: Pearl of Wisdom - Synchronising data

    Thought I would start one of an occasional series of threads, this one devoted to synchronising data. If you have anything to add then post away.

    Why a new thread? Well this one does not ask a question but attempts to impart knowledge, hard earned knowledge I might add.

    Why a "Pearl of Wisdom" and not "Tips & Tricks"? Well the latter is so hackneyed and the Philippines is known as the "Pearl of the Orient".
    Rod

    fe_rod@hotmail.com

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    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

    fe_rod@hotmail.com

  3. #3
    Join Date
    Mar 2004
    Posts
    42
    Sound interesting! Where is the new thread?

  4. #4
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Er.... This is it.
    Rod

    fe_rod@hotmail.com

  5. #5
    Join Date
    Mar 2004
    Posts
    13
    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...

    regards

  6. #6
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    whaling

    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"
    Else
    Me.MyCombobox.RowSource = "SELECT Transaction_Id FROM Transaction WHERE Amount > 1000"
    End If

    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.
    Rod

    fe_rod@hotmail.com

  7. #7
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    I should have added to my last post that this method can be used to change the sort sequence as well as the filter.
    Rod

    fe_rod@hotmail.com

  8. #8
    Join Date
    Mar 2004
    Posts
    13
    I used the wizard to add a combo button to my form that will filter the results shown in my subform. Here's the code (I changed the '=' sign to 'Like'):


    Private Sub Combo39_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ItemNum] Like '" & Me![Combo39] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    I also added a null line to my combo box row source like this:

    SELECT dbo_vwItems.ItemNum, dbo_vwItems.Description FROM dbo_vwItems UNION select distinct null, "ALL" FROM dbo_vwItems
    ORDER BY dbo_vwItems.ItemNum;

    Now I don't know how to say in VBA 'if ItemNum is null, show all the records'....

  9. #9
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I found this on another webpage and it works pretty well. It allows you to add an "ALL" to a combo or list box.

    http://www.mvps.org/access/forms/frm0043.htm

    Canupus

  10. #10
    Join Date
    Mar 2004
    Posts
    13
    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.

  11. #11
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Quote Originally Posted by whaling
    Now I don't know how to say in VBA 'if ItemNum is null, show all the records'....
    whaling

    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 = ""
    Else
    Me.mysubformcontrol.LinkMasterFields = "Combo39"
    End If
    Rod

    fe_rod@hotmail.com

  12. #12
    Join Date
    Mar 2004
    Posts
    13

    Thumbs up

    Rod -

    It works perfectly!! Thanks for the geat idea and time you spent helping me out with this

    whaling

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •