Adding/Editing Records through a subform

[Antinomy]

Greetings, all!

I am creating a database to record and sort various projects (ala Get Things Done). So far the table setup has gone fine (I am new to Access, so this has taken some time).



Now I'm putting together the forms to enter the data, and I've run into a roadblock.

I have a table called "conversations" that describes meetings, phone calls, etc. I have another table called "people" that describes - you guessed it - the people I collaborate with. Many people can participate in one conversation; one person can contribute to many conversations. So I created a join table to unite the two.

When entering the details of a conversation, I want to attach the people who participated in the conversation to that record. So I used the form wizard to create a "conversation" form, with a subform based on the join table.

The resultant form is great if I want to attach new people to the conversation - the datasheet makes it easy to create new people entries. But when it comes to using the names of existing people (which will be what I need to do 90% of the time) I have to type in the people ID number to get the right record. Of course, I don't want to memorize these ID numbers!

I'm sure there's a way of choosing from the existing list of people without entering the ID number, but I'm stuck. Can anyone offer any advice?

Many thanks,

Ant

[Rod]

Hi Ant,

Let me describe a scenario that is, I hope, what you want. If it is, I can help you implement it.

Somewhere on the main/parent form there is a command button captioned, "Add Participants." This button must be disabled or invisible if you are adding a new conversation until that conversation has been added to the database and has obtained a primary key.

When the command button is clicked (assuming it is now visible and enabled), a list of all potential participants, that is those not already associated with this conversation, is displayed. This is a list of names, not IDs. You may select as many of the potential participants as you like. Clicking on an "Add" command button will add them to the conversation.

Deleting participants from a conversation must be considered, after all mistakes do occur. If your subform is based on the join/linking/cross reference table then it should be possible to delete directly. Otherwise a process similar to the above is required.

Get back to me.

[Antinomy]

That's a great solution, and I'd love to put it together. Let me know how to proceed, and thanks for responding!

Ant

[Rod]

That's a great solution, and I'd love to put it together. Let me know how to proceed, and thanks for responding!

Ant

The complete solution will take a number of posts, so let's take it a step at a time. As I walk you through it I'll explain some of the theory and concepts such that, hopefully, you can run with it on your own after a while. Also you will need to substitute your own names for any that I use.

My suggestion is that the pop-up pick list is a form; so step 1 is to design this form and test it. Use the Create-Form Design to obtain a blank form design canvas. (Prior to v2007 the form design wizards were quite useful; since then I find they just get in the way - my humble opinion!) This form, which I name frmPickList, should contain, as a minimum, three controls: a list box (lstParticipants) and two command buttons (btnAdd and btnCancel). I don't use the standard Reddick tag of 'cmd' for buttons preferring to reserve it for commands.

OK, a little about list boxes. List boxes can display rows of more than one data attribute, that is columns. Think of a list box as a scrolling Excel spreadsheet. Moreover some of the columns may be hidden. One of the columns, hidden or visible, is designated the bound column which means that when you refer to the list box, it is the value in this column that is returned. List boxes have one other feature: you can make them multiselect, that is you can highlight (select) more than one row. It is normal practice to arrange matters that the first column is the bound column, that the bound column contains a primary key, that the bound column is hidden. So in your situation the bound column will contain the ID number of the person (that which you have difficulty remembering ). In order to customise your list box you need to change some of its properties.

So in the form design view, add a list box; make it about 10 cm wide and as deep as you wish. Now, while the list box is selected, make the Property Sheet visible by clicking on the icon at the right-hand end of the ribbon. Select the 'Other' tab and in the top line give the list box its name, lstParticipants or whatever. The fifth line is 'Multi Select.' Alongside this choose 'Simple' from the drop-down list.

Now you need to decide how many columns you need. For the purpose of this description I shall assume three columns: ID, LastName, FirstName. So select the first tab, 'Format.' On the second line enter the number of columns (e.g. 3). The third line allows you to manipulate the column widths. Enter the widths separated by semicolons. To hide a column make its width zero. For example 0 cm;4 cm; 3 cm gives me a list where the first column is hidden, the second has a width of 4 cm and the third a width of 3 cm. While in this tab of the Property Sheet have a read of some of the other formatting you can do.

Now select the 'Data' tab. We need to tell Access how to retrieve the data and this is achieved by entering something in the Row Source entry. Type, without the quotation marks, "SELECT ID, LastName, FirstName FROM tblPeople;" alonside Row Source. Use your own names of course, not mine. The Row Source Type is defaulted to Table/Query which is what we want.

OK, we're done with the list box so switch to Form View and make sure it's working and that you can select/deselect multiple people by clicking on them. At this point you may be thinking (correctly) that the list shows everyone; how to restrict the list comes later. Also you may ask why I recomment an SQL statement as the row source rather than a table name. The first reason is that by using SQL you can select the table fields you want and only the table fields you want. Second you are in control of the order of these fields. For example tblPeople may contain many other fields apart from the two names and the first name probably occurs before the last name.

Let's conclude this step by coding btnCancel. Yes I know there is a wizard to do it for you but a brief foray into VBA coding at this stage is good for the soul and useful experience for what comes later. So revert to Form Design view and make sure 'Use Control Wizards' is switched off. Now highlight btnCancel and make sure the Property Sheet is displayed. On the 'Other' tab name it btnCancel or whatever. On the 'Format' tab make the caption Cancel or whatever.

Now select the 'Event' tab. In the first line (On Click) double click and Access should respond by displaying Event Procedure. If not simply select Event Procedure from the drop-down list. Now click on the elipsis - the three periods or full stops. Access will immediately take you to the VBA coding window and position you inside the procedure that handles the click event for this button.

You need to write one line of code to close the form. (Actually that's all Cancel does; it simply takes no action and closes the form.) So write:

DoCmd.Close acForm, Me.Name

Your resulting code should look similar to the following:

Private Sub btnCancel_Click()
    DoCmd.Close acForm, Me.Name
End Sub

The use of 'Me' is somewhat a trick. Me refers to the object in which the code is running, in this case the form. So Me.Name resolves to the name of this form, you don't have to be explicit which is useful if you ever change the form name in future.

That's enough for now. There remains the implementation steps of invoking the pick list from your main form and coding the add button. Get back to me when you are happy and confident with the progress so far.

[Antinomy]

Wow Rod, thanks: I'm grateful!

I've created the form as per your specifications; ready to move along when you are. Thanks for teaching me to fish!

[Rod]

Hi Ant,

If I’m being too detailed for you, perhaps teaching my grandmother to suck eggs, then please say so and I shall pitch my posts accordingly. Anyway for this step I suggest that you create a feature that shows the pick list form as a modal pop-up. I’ll leave the updating of your database to a later post; for now the objective is to code and test the form handling mechanism.

First I wish to encourage you to make a change to the VBA coding options and update any existing modules accordingly. Go to the VBA coding window. If it’s not showing then there are many ways to get there; for me, the easiest is to press [Alt] + Fn11. When there, select Tools from the menu and then Options. Make sure the box, Require Variable Declaration is checked and click on OK. What this does is to add the statement, Option Explicit to any new modules you create. What this means is that the VBA interpreter/compiler requires you to explicitly declare each and every variable that you use. The benefit is that you avoid many unintentional definitions since VBA will immediately identify any misspelled names. For existing modules you should add the statement Option Explicit as the second line underneath Option Compare Database if it does not exist.

OK, a little about the structure of a VBA module. Each module is divided into two sections: there is a Declarations section at the beginning that is followed by a Procedures section. Each procedure may be one of two types: a Sub procedure executes but returns no value; a Function procedure executes and returns a value. All the event procedures are Subs (can’t think of any that aren’t). If you encounter Property procedures ignore them for the moment. Procedures may be Public or Private; Public procedures are available to every module in your project, Private procedures are available only to the module in which they are defined.

The same goes for variables: Public or Private (plus some others that we’ll ignore for now). However there is a little more to know:

• You may only define Public and Private variables in the Declarations section.
• You may define variables within a procedure using the keyword, Dim (dimension) but these variables are valid only for that procedure.

Time for something practical. Add a new command button on your main form – no wizards please. I shall assume it’s called btnAddParticipants. Invoke an event procedure for it (see my previous post re btnCancel) and you will end up with something similar to the following.



Now refer to the left-hand pane of the coding window. You should see the name of your new form, the pick list form shown with a prefix of Form_. (In my case this is Form_frmPickList.) In the Declarations section, type Private MfrmPickList as Form_frmPickList. (As long as you type before the first procedure VBA will adjust the horizontal line separating the sections. You should now have something like the following. (Use your own names, of course.)



What this declaration does is to define a new variable for an Access Form Object that is identical to the form you created in my first post. The ‘M’ prefix is a convention saying this is a module level variable (as indicated by the Private keyword) and the tag suggests it is a form object (some might use ‘obj’).

Now you need to extend the code in the procedure. Rather than give tedious step by step instructions I have just shown what the resulting code should look like.



Now is the time to test. The first test is to check your coding (as far as possible). Click Debug on the menu and then Compile Database. Any VBA compiler errors will show immediately and may be corrected.

Now return to the Access window and test the mechanism from your main form by clicking on the add participants button and playing with it. You can’t do any harm since there is no update yet coded. The modal statement means that you can’t access any other form until you close the pick list form.

As a footnote to this post let me explain why I choose this method to open the form and not the built in Access function. Apart from being more elegant, it is closer to what the Access function actually does. Moreover it gives almost total control of the called form from the calling procedure; to mimic this with the Access function means playing around with the OpenArguments and coding both in the calling module and the called form. This feature is invaluable as you will find out when restricting the list to potential participants only. Although not needed here, this method allows multiple instances of the same form to be displayed (hey, how about comparing Bill Jones with Fred Smith in an HR system?) whereas the Access function displays only one copy of the form no matter how many times you invoke the function.

Look forward to hearing from you.

[Antinomy]

I haven't yet implemented your last post - will do it tonight or tomorrow.

But I need to address this:

Too detailed? You must be crazy! I'm learning more from this exercise than the two manuals I bought from Amazon. So as long as you have the time and patience, I'll take the lessons!

Thank you...

Ant

[Antinomy]

Okay Rod, I'm back. Making my way through. Only one problem: When I ran the debug I received the following error:

Attachment 8266

Here's the code:

Attachment 8267

I'm sure I'm overlooking something minor here, but the only thing I can think of checking is the button name, which seems fine. Thoughts?

And thanks again,

Ant

[Rod]

I can open neither of the attachments in your last post.

[Antinomy]

Weird. Ok, I'll just write out the error then. This is what it says:

Compile error: Ambiguous name detected: btnAddParticipants_Click

[Rod]

To get that error on a procedure name is unusual - make sure you have not got two procedures by that name. Otherwise I cannot help without you sending me the whole db.

Ah! It could be any of the variables within that procedure.

[Rod]

Hi Ant,

The procedure name will be highlighted (yellow) but with luck there is also a variable within that procedure highlighted (blue). Look for duplicate occurrences of the variable.

[Antinomy]

Here's all the text in form_picklist:

---

Option Compare Database


Private Sub btnAddParticipants_Click()


Private MfrmPickList As Form_FormPickList


End Sub

---

Private Sub btncancel_Click()


DoCmd.Close acForm, Me.Name


End Sub

---

Private Sub btnAddParticipants_Click()


Set MfrmPickList = New Form_FormPickList
'code needed to restrict list box entries
MfrmPickList.Modal = True
MfrmPickList.Visible = True


End Sub

---

Does this contain the error?

[Rod]

Option Compare Database


Private Sub btnAddParticipants_Click()


Private MfrmPickList As Form_FormPickList


End Sub

Sure does contain an error. Move the 'Private MfrmPickList ...' statement before the 'Private Sub btnAddParticipants_Click()' statement.

I might as well give you the extended code for the button click event.

Private Sub btnAddParticipants_Click()

    Set MfrmPickList = New Form_frmPickList
    MfrmPickList.lngConversationID = Me.ID
    MfrmPickList.lstParticipants.RowSource = _
    "SELECT ID, LastName, FirstName FROM tblPeople " & _
    "WHERE ID NOT IN(SELECT PersonID FROM tblParticipants WHERE ConversationID = " & Me.ID & ");"
    MfrmPickList.Modal = True
    MfrmPickList.Visible = True
    
End Sub

[Rod]

Ant,

I'm sorry, I'm really only firing on three cylinders this morning. Please ignore my two previous posts. Somehow you have duplicated a procedure definition and got it entangled with the declarations section.

Option Compare Database


Private Sub btnAddParticipants_Click()


Private MfrmPickList As Form_FormPickList


End Sub

---

So for the third (and final time) remove the second and fourth lines above. It should end up as (I've included Option Explicit):

Option Compare Database
Option Explicit
Private MfrmPickList As Form_FormPickList

---

Also check the name: is it Form_frmPickList?