Form/Tables not in sync 1

[CindiN]

There have been many questions like this, but I can't seem to nail it down. I have an Employee Table and a Training Table. The Training Table has the EmployeeID as a foreign key and I created a Combo box to show the Employee FName and LName in the EmplID Field. I then made a form off the Training Table. The Combo box transferred over to the form just fine, but when an Employee Name is selected, all the other fields don't update to that selected Employee. I think I need to create an Event Procedure in the AfterEvent of the EmployeeID control, but I'm not sure how to write it. My EmployeeTable and TrainingTable have a one to many relationship. Don't want a subform...any suggestions? I'm just learning about SQL and VB. Thanks in advance for your help!!

 

[RickSpr]

If I understand you, yes, this is a common problem. I think what you want is a combo box for selecting the employee to display on your Training form, right?

First off, the combo box should be unbound, meaning its Control Source property is blank. Why? Because if it is bound to the EmployeeID field, changing the combo box would update the field--definitely not something you want!

You are correct that you want code in the combo box's AfterUpdate event procedure. What it must do is search for the first record for the specified employee, and then position the form's recordset to that record.

The following code will do that, assuming your combo box is named cboEmployeeName and that it has two columns, for FName and LName respectively:

Private Sub cboEmployeeName_AfterUpdate()
    Dim rst As DAO.Recordset
    Dim intItem As Integer

    Set rst = Me.RecordsetClone
    intItem = Me!cboEmployeeName.ListIndex
    rst.FindFirst "FName = '" & Me!cboEmployeeName.Column(0, intItem) _
        & "' AND LName = '" & Me!cboEmployeeName.Column(1, intItem) & "'"
    If rst.NoMatch Then
        Beep
        MsgBox "No training records for this employee"
    Else
        ' Position the form's recordset to this record
        Me.Bookmark = rst.Bookmark
    End If
    rst.Close
    Set rst = Nothing
End Sub

Note: There may be many records in the Training table for an employee. This will position to the first one, if one is found. The user can then use the navigation bar or menu/toolbar commands to browse through the remaining records for the employee. Rick Sprague

 

[CindiN]

Hi Rick, I am so glad you are the one to respond. I've read several of your responses and I'm quite impressed.
I really appreciate you explaining as you provide the information, really helps me to understand. I did what you suggested and I think it's almost there.

I have a Select statement in the Row Source of the cboEmployeeID (Note: Just changed EmployeeName to EmployeeID) control that pulls the LName, FName from the EmployeeTable and is bound to the EmployeeID field. I think this might be why I'm getting the "No Training Records for this Employee" for ALL employees that I select from the cbo box. Therefore, the training fields are not being updated as each employee is selected. Do I need a Requery statement somewhere?

Thank you sooooo much for your help!!

 

[RickSpr]

I need to double check something. When you said the combo box "transferred over" to the training form ok, I assumed you meant you cut and pasted it there successfully, but I realize now you might have meant that the combo box is on an Employees form, and selecting the employee successfully activates the Training form. Please clarify.

If my original assumption was right, and the combo box is on the Training form, then it should not be bound to the EmployeeID. A bound control is used to view and update the contents of a field in the current record. Here, you're using it to select an employee and search for a record to make current. You don't want it to update the current EmployeeID, so it should be unbound.

That still doesn't explain the results you're getting, though. To go further, I'll need to know which form contains this combo box, and if it's on the Employees form, how you're activating the Training form from it. Rick Sprague

 

[CindiN]

Sorry, don't mean to be so confusing. I do have a separate Employee Form, just to add and update Employee Information only. But, that form does not activate the training form.

I have a separate EmployeeTable with just Employee Information on it. Then, I have a TrainingTable that has a TrainingID (PK) and all the Classes listed as fields. Under each class field is the date the employee took the class. Then, I put the EmployeeID (FK)in the TrainingTable making the EmployeeID a lookup(combo box) to the original EmployeeTable so that instead of the ID, it shows the Employee LName, FName in that EmployeeID field of the TrainingTable. Hope that makes sense!

I based my form off the TrainingTable because it has all the fields I need for my TrainingForm. I did correct the EmployeeID Combo Box in the TrainingForm so it is unbound, (Control Source Property is empty), and in the Row Source property is the Select statment (from the TrainingTable) that pulls the EmployeeID from the Original EmployeeTable and puts it in order by LName, FName in the cboEmployeeID control.

Sooo, to answer your question, the combo box was created on the TrainingForm as unbound, and I copied the Select Statement (mentioned above) from the EmployeeID Field in the TrainingTable.

Hope this makes more sense, sorry so long. Let me know if it would be easier to draw it out, or e-mail you the Tables and Forms, so you can actually see.

I do appreciate your help, this one has had me going in circles for 2 weeks.

 

[RickSpr]

Ok, it's all clear now. Your combo box is unbound, that's good. And its Row Source is the Employee table, so you can show the names, that's fine.

But when the user selects from the list, you need to search the Training table for the row for the selected employee. What you have in the training table to identify the employee is EmployeeID, not FName and LName. That's why my code didn't work--I assumed you knew your combo box would have to have the fields you would use to search for the record, and therefore I assumed you would search by FName and LName. Since your records don't have these, your search fails, and that's why you get the message.

What you need to do first is add EmployeeID to the SELECT statement in your combo box's Row Source. Put it first, so it reads "SELECT EmployeeID, FName, LName FROM EmployeeTable ORDER BY LName, FName;". Change the combo box's Column Count to 3.

Now I know you don't want to display the EmployeeID, but we can fix that. The combo box's Column Widths property lets you specify the width in inches for each column. We just need to set the width of the EmployeeID column to 0, and it won't be shown! You can enter widths for the other two columns if you want, or let them default. If you put them in, separate each column width with ";", for example, "0; 1.2; 2.5".

So now your combo box will have the EmployeeID in column 0 (columns are numbered from 0 up). Let's revisit the code for the AfterUpdate event procedure now:

Private Sub cboEmployeeName_AfterUpdate()
    Dim rst As DAO.Recordset
    Dim intItem As Integer

    Set rst = Me.RecordsetClone
    intItem = Me!cboEmployeeName.ListIndex
    rst.FindFirst "EmployeeID = " & Me!cboEmployeeName.Column(0, intItem) 
    If rst.NoMatch Then
        Beep
        MsgBox "No training records for this employee"
    Else
        ' Position the form's recordset to this record
        Me.Bookmark = rst.Bookmark
    End If
    rst.Close
    Set rst = Nothing
End Sub

That should get you working at last, I think! Rick Sprague

 

[CindiN]

Thank you for explaining things for me. Now, do I just add a requery statement or macro somewhere, so the training fields update with the employee selected? I'm still getting the message box poping up saying no records for this employee. And, I'm choosing employees that I know Do have records. I'm sure I've done something very silly that's just keeping this from clicking. I changed my row source select statement to:

SELECT DISTINCTROW EmployeeTable.EmployeeID, LName, FName FROM EmployeeTable ORDER BY EmployeeTable.LName, EmployeeTable.FName;

and changed the columns to 3 and made the column widths 0";1";1".

I also put in your code above in the AfterUpdate property.

I can't tell you how much I appreciate your help.
Thanks Rick,
CindiN

 

[RickSpr]

Darn! No, you shouldn't need to requery; the Me.Bookmark assignment will do that. I don't know what's wrong. Is your employee ID a number? I was assuming it was.

If you know how to use breakpoints, stop the code at the FindFirst method call, then use the Immediate Window to print the value of the expression Me!cboEmployeeName.Column(0, intItem) to verify that it's reasonable. If it is, open your TrainingTable and verify that there is a row with that employee ID. If there really is a record and you're getting the right value in the FindFirst, I don't understand what could be wrong.

If the Immediate Window doesn't print a reasonable value, there's something wrong with the contents of your combo box list. Rick Sprague

 

[CindiN]

Well, if I did it right, it says Compile Error - Can't find Project or Library. Then when I click OK it comes up with a References Dialog Box with "Missing Sheridan Date" check box highlighted. It didn't print anything in the Immediate Window....maybe I don't know the correct way to do this.

I did get a data type error about 3-4 messages ago, and changed the Data Type in the Tables to Number.

Sheeesh! It's 3AM, I'm going to bed...|-I If you're still with me tomorrow, I'll get back at it! Thank You, Rick!
CindiN

 

[RickSpr]

Since you seem a little unsure of whether you did the breakpoint thing right, maybe it's best if you send me a copy of your database (or at least of the relevant parts that exhibit the error). My email address is handlbar@broughton-sys.com.

BTW, any time your References dialog has a "Missing" library in it, you should start by unchecking that library and recompiling all your modules. If the recompile gives you an error, it means an ActiveX control or library is missing and needs to be reinstalled. If the recompile works, you didn't need to have the missing library checked anyway. Rick Sprague

 

[CindiN]

I made a new DB called "ForRick" and put just the EmployeeTable, TrainingTable and the Training Form in it. It's now coming up with a compile error on the Dim rst As DAO.Recordset.

I really appreciate this Rick....I owe you one.

CindiN

 

[RickSpr]

The compile error is probably because you forgot to set a reference to DAO 3.6. You have to do that separately for each database, because Access 2000 defaults to using ADO.

I've got your database here at work, but I only have Access 97 here, so I'll take it home with me. I will post again tonight. Rick Sprague

 

[CindiN]

Thanks again Rick! But you're sorry you answered this one, huh? haha

 

[RickSpr]

Cindi,

Now that I've seen your form, I have a better understanding of your application. The code I gave you for the combo box's AfterUpdate event procedure wasn't appropriate to what you're doing, and we'll have to replace it. But first, there are some problems with the design of the form that have to be resolved. The possible solutions to these problems all interact with one another, so what follows will be complicated, and you may have to read it several times to understand it all.

First of all, your form doesn't display the employee name for the current record. You probably intended to show the employee name in the combo box. Normally, to make that happen, you would bind the combo box (i.e., set its Control Source property) to the EmployeeID field and let the combo's internal workings show the employee name. However, if you bind it to EmployeeID, then selecting a name from the box will change the EmployeeID in the current record, which you certainly don't want to do! We could use VBA code to display the name in the combo box instead, but that leads us to the second problem.

Until I saw your database, I didn't realize that TrainingTable has multiple records for an employee. The code I gave you will find the first record (I'll explain later why it isn't working right now), but doesn't help the user find any additional records, which could be anywhere later in the table. I can see now you probably intended, when the user selects an employee name, that only the records for that employee are shown. This makes it easy for them to browse through an employee's training records, and to add new records to the end.

To accomplish this, you would need to use the form's Filter property. A filter is a logical expression that's applied to the form's recordset to limit the selected records. When the user selects an employee name, you would set the Filter to an expression that selects records with the corresponding EmployeeID.

But sometimes the combo box is empty. This happens when the form is first opened, and when the user deletes the contents of it. Now there is no employee selected, so no filter can be set. That seems like a pretty good idea; the user can browse through all the training records when the form is first opened, or by clearing the combo box.

Here's where we get into the first interaction problem. Consider what happens when the form is first opened. The combo box is empty, so we clear the Filter property to show all records. Then Access displays the first record. Because you want to use the combo box to display the employee name, our VBA code then puts the name in the combo box. Now the box isn't empty any more. Should we set a filter? If we do, we can no longer display all the records. If we don't, then sometimes the combo box is filtering and sometimes it isn't, which could confuse your user. This is the first decision you have to make: Do you want to allow all the records to be browsed, or do you want them to be filtered to one employee all the time?

We have this dilemma: If you want to be able to show all records, you can't use the combo box to display the employee name without risking confusing the user by its seemingly arbitrary behavior. But if you create a separate text box to display the employee name, that will confuse the user too, because the name will be on the form twice! The dilemma stems from trying to use the combo box both to display data and to select records, and unfortunately, it's unavoidable. (Actually, there's another problem with it, too: You can't allow the user to add a new record any time the combo box is blank, because you'll have no EmployeeID, which is required. But we could actually solve that problem with code that disallows adding records when the combo is empty.)

Let's say you decide to abandon the idea of showing all records at once, and settle for always filtering to a single employee. When the form first opens, the filter will be set for the first employee, and the employee name will be shown in the combo box. That leaves the problem of what to do when the user erases he combo box contents. I'm going to suggest that you not allow this. If they erase it, we will just use code to put the name back in. That lets us be sure we always have a filter active, and the combo box will behave consistently.

Incidentally, this also solves a problem with adding records. Because we are always unambiguously displaying an employee name, we can assume that when the user is adding a record, they intend it to belong to the employee whose name is currently displayed in the combo box. You probably meant for it to work that way, but your form doesn't currently prevent adding records when the combo box is blank.

There's one problem left: The user can use toolbar buttons or Records menu commands to change or remove filtering. That would reintroduce all the problems we just went through. We'll have to disallow that, either by disabling the menu commands and toolbar buttons or by immediately restoring our filter in VBA code. That latter is much easier, but can be confusing to the user, so we'll use a MsgBox to tell the user that changing the filter is not allowed. Depending on your application and the sophistication of your users, the loss of user-controlled filtering may be a significant disadvantage of this form design, and this may be the reason you choose not to go this way.

There's one side effect of disabling user control of filtering that I need to mention. When the user chooses Records>Data Entry from the menu, Access sets the Data Entry property to Yes and turns off the filter. The user is supposed to choose Records>Remove Filter/Sort to reset the Data Entry property, and then has to manually turn filtering back on. We could allow the user to use Remove Filter/Sort in this special case, but unfortunately we can't turn the filter back on
Unfortunately, our code can't interthe Data Entry menu command doesn't cause any events to fire, so we can't prevent it. We could allow the user to use Remove Filter/Sort when Data Entry is on, so that they can turn it off, but that would leave us without a filter, and the Access internal logic in this case doesn't let us turn the filter on from within the event procedure. The upshot is, if the user chooses Data Entry from the menu, they will only be able to add new records until they close the form and open it again. This isn't nice, but most users won't use Data Entry anyway, and it's the best we can do.

Now I'm ready to explain to you how to make it all work. I'm going to assume you will abandon the idea of letting all the records be shown at once, and the idea of allowing the user to control filtering. If you really want to keep these functions, you'll have to do some redesigning of the form to minimize the user's confusion. Let me know if that's what you want to do. Otherwise, keep reading.

This post is already long, and there is lots of code and explanation to come, so I'll continue this in a second post.
Rick Sprague

 

[RickSpr]

Before we get to the code, you need to make two changes to the form. First, you have Data Entry set to Yes. Open your form in Form View. Notice that your navigation bar is displaying "Record 1 of 1", and the record data is blank. You're positioned to add a new record, and none of the existing records are appearing in your recordset. (This was why the code I gave you before didn't work--there were no records for any employee, let alone for the one you selected in the combo box, so you always got the "No training records for this employee" message.) With Data Entry set to Yes, you're telling Access that you only want to use the form to add new records, not to display or edit existing records. You need to set it to No. Allow Additions is set to Yes, so you can still use the form to add records.

The other change is that you need to add a text box and set its Control Source to EmployeeID. Every new record must have an employee ID, and the only place it can come from is the form, so this is required. Make the text box invisible (set Visible to No), because the ID doesn't mean anything to the user, so there's no point in displaying it to them.

Here are the tasks we need to do with VBA code:
1. When the form opens, set cboEmployeeName to the first employee name, and set the Filter property to filter records for that employee. We do this in the Form_Open event procedure.
2. When the user selects and employee, set the Filter property to filter records for that employee. We do this in the cboEmployeeName_AfterUpdate event procedure.
3. When the user attempts to erase the combo box, don't allow it. We do this in the cboEmployeeName_BeforeUpdate event procedure.
4. When the user attempts to change the filtering, explain that they mustn't do this and restore the filtering to its previous value. We do this in the Form_ApplyFilter event procedure.
5. When the user adds a new record, set txtEmployeeID to the EmployeeID value for the current employee in the combo box. We do this in the Form_BeforeInsert event procedure.

Here are the event procedures:

Private Sub cboEmployeeName_AfterUpdate()
    ' If the user had chosen Data Entry from the menu, turn it off now.
    Me.DataEntry = False
    Me.Filter = "EmployeeID = " & Me!cboEmployeeName
    Me.FilterOn = True
End Sub

Private Sub cboEmployeeName_BeforeUpdate(Cancel As Integer)
    If IsNull(Me!cboEmployeeName) Then
        ' Uncomment the following lines to explain to the user that
        ' they can't do this.
        'Beep
        'MsgBox "Please select an employee."
        Cancel = True
    End If
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    ' If the user has chosen Remove Filter/Sort or Apply Filter/Sort
    ' to turn off the Data Entry mode, do that for them.
    If Me.DataEntry _
    And (ApplyType = acShowAllRecords Or ApplyType = acApplyFilter) Then
        Me.DataEntry = False
        Me.FilterOn = True
        Cancel = True
        Exit Sub
    End If
    Beep
    MsgBox "Sorry, but you can't change filtering on this form."
    Cancel = True
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me!txtEmployeeID = Me!cboEmployeeName
End Sub

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
    Beep
    MsgBox "Sorry, but you can't use filtering on this form."
    Cancel = True
End Sub

Private Sub Form_Open(Cancel As Integer)
    cboEmployeeName = Me!EmployeeID
    Me.Filter = "EmployeeID = " & Me!EmployeeID
    Me.FilterOn = True
End Sub

Try this out, and see whether the form's behavior seems reasonable and intuitive to you. If you don't like it, and want to be able to show all records and/or use filtering, let me know and I'll give you an idea on how to modify your form's design.

One more comment: Your form's code module doesn't have Option Explicit at the top. Option Explicit tells VBA that it should give you an error message when you compile the module, if the code in the module attempts to use a variable that isn't declared in a Dim statement. Without Option Explicit, if you misspell a variable name, VBA will take it to be a new variable, not the one you intended. This causes a bug in your program which can be very difficult to find. Most professional programmers always use Option Explicit. You can ensure that every module you create will include Option Explicit by choosing Tools>Options from the VBA editor menu, selecting the Editor tab, and putting a check mark by Require Variable Declaration. I strongly recommend this. Rick Sprague

 

[RickSpr]

On re-reading my first post, I discovered the third-from-last paragraph was messed up. I was going back and forth between preparing my post in a Notepad window and trying things out in Access. I had been writing this paragraph to say we couldn't do anything about the Data Entry command when I got an idea how to fix it, so I switched to Access and got it working--then I forgot to go back and clean this up. The paragraph was supposed to read:

There's one side effect of disabling user control of filtering that I need to mention. When the user chooses Records>Data Entry from the menu, Access sets the Data Entry property to Yes and turns off the filter. The user is supposed to choose Records>Remove Filter/Sort to reset the Data Entry property, and then has to manually turn filtering back on. We will allow the user to click either Apply Filter/Sort or Remove Filter/Sort in this one special case, and we will respond to it by resetting Data Entry mode and applying the filter.
Rick Sprague

 

[CindiN]

Rick, Thank you so much for taking your personal time to work on this for me. Everything you wrote is very understandable and sounds like exactly what I am trying to accomplish. It's about 3:45AM now, and I have to travel this morning to teach in another city, so I probably won't be able to test it out for a day or two.....and that is just driving me crazy.

You're right in that I do only want the user to see one employees records at a time. They do not need to browse through all the employees' records. They will only be using this form to enter dates as to when each employee took a paticular class..as a means to update their records.

I don't want the user to add new employees to this form, they will have an EmployeeForm to do that. That form will update the table and therefore, this training form will also be updated with the new employee's name, directly from the table. So, your solution handles that wonderfully. Also, I doubt that the users will be knowledgeable enough to use the Records, Data Entry or Filter/Sort options.

Oops, gotta go, I'll write more later.
Thank You Rick!!!!!!!
CindiN

 

[CindiN]

Rick!!!!!! You are incredible!!!! IT WORKS PERFECTLY!!!! I don't know how to thank you for taking the time to help me understand! I'm sorry it took me so long to get back to you, but I've been out of town for the last 3 days teaching Crystal Reports....yuck! Anyway, I got home tonight and did what you said and BOOM....it worked!!

You're the BEST!!! Thank you soooo much! s-), I
CindiN