Code doesn't work the first time round. Bizzare! 1

[Petemush]

I have a rather strange problem.

I have a form displaying employee details. On this is a subform with details about what training courses an employee has been on.

On the AfterInsert event of the subform, I have some code that asks the user if they would like to add the training course just entered, to a table which lists what job title suits what training course.

If the answer is yes, a further form loads which has a combo box allowing the user to select the importance of this course for this job title.

There's a button on this form, which when pressed, adds the job title, course code and importance to a table.

To be able to refer to the correct course code, in the subform's AfterInsert event, I set a global variable to be equal to the course code, I ten refer to this variable on the OnClick event code in the form that loads.

The problem is, my code never works the first time the main form is loaded. The first time round, when I refer to the global variable, I get a zero length string and an error occurs. However when I add further courses, the code runs fine.

I'll post my code below, cheers for any light that anyone can shed on this!

Pete


'After a new record is inserted into EmployeeCourses
Private Sub Form_AfterInsert()
On Error GoTo Err_Form_AfterInsert

'JTitle - Used to represent the Job Title of the employee who
'has just had a course added.
Dim JTitle As String

'Set the global variable CourseCode to the current course code
'so it can be accessed from frmImportancePicker.
CourseCode = Me.Course_Code.Value
JTitle = Form_frmEmployees.Job_Title.Value

If MsgBox("Would you like to make the course " & Chr(13) & _
CourseCode & Chr(13) & " a suitable course for the job title " _
& JTitle & "?", 36, "Leo&quot = vbYes Then

DoCmd.OpenForm "frmImportancePicker"

End If

Exit_Form_AfterInsert:
Exit Sub

Err_Form_AfterInsert:
If Not (Err.Number = 3022) Then
MsgBox Err.Number & ":" & Err.Description & Chr(13) & Chr(13) _
& "If this problem persists, please refer to User Manual" & _
Chr(13) & "for a Troubleshooting contact.", vbExclamation, "Error"
End If
Resume Exit_Form_AfterInsert
End Sub


Private Sub frmImportancePickerContButton_Click()
On Error GoTo Err_frmImportancePickerContButton_Click

Dim db As DAO.Database, rs As DAO.Recordset

'CCode - Used to represent the inserted course's Course Code.
'JTitle - Used to represent the Job Title of the employee who
' has just had a course added.
'Importance - Used to represent the importance the course has
' for this job title.
Dim CCode As Form, JTitle As String, Importance As String

Set db = CurrentDb

'Set the record set to represent tblCoursesSuitableFor
Set rs = db.OpenRecordset("tblCourseSuitableFor&quot

Set CCode = Form_frmEmployeeCourses
JTitle = Form_frmEmployees.Job_Title.Value
Importance = Me.Importance

'With the defined recordset, add a new record
'with the values held in CCode, JTitle and Importance.
With rs
.AddNew
.Fields("Course Code&quot = CCode.CourseCode
.Fields("Job Title&quot = JTitle
.Fields("Importance&quot = Importance
.Update
End With

MsgBox JTitle & " has been added as a suitable job " _
& "title for this course.", , "Job Title Added"

DoCmd.Close

Exit_frmImportancePickerContButton_Click:
Exit Sub

Err_frmImportancePickerContButton_Click:
MsgBox Err.Description
Resume Exit_frmImportancePickerContButton_Click

End Sub

 

[RickSpr]

I believe your problem is arising with this line in frmImportancePickerContButton_Click:

   Set CCode = Form_frmEmployeeCourses

Here you are referring to the "default instance" variable that Access creates for the form you're using as a subform.

When the main form (frmEmployees) is opened, the subform that it contains is not the default instance, and Form_frmEmployeeCourses does not refer to it. (In fact, the subform does not appear in the Forms collection, either.)

When the above code is executed for the first time, you create the default instance of the form, because these default instance variables act like they're Dim'ed with the New keyword. When created this way (as opposed to creating it with OpenForm), the form is invisible, so you didn't realize you were creating it.

In the newly created default instance, CourseCode (which I take it you declared as a module-level variable in the form) has its initial value of an empty string, so that's why it doesn't work when you first run the code.

BTW, a "global variable" is one declared Public in a standard module, not a form module. "CourseCode" is a form module-level variable, and therefore a simple property of the form.

When your frmInstancePickerConButton_Click procedure ends, CCode goes out of scope, but the default instance it created is not destroyed because the default instance variable still refers to it. If the default instance were closed, the default instance variable would have been set to Nothing, but nothing attempts to close it.

So the next time through, when you execute the above line of code, the default instance already exists and isn't created again. Perhaps that's why your logic works after the first time, though I don't really see how.

One thing I do know, though, is that mixing default instances and private instances can get quirky. I've seen cases where I created a private instance, which was not the default instance; then created the default instance, leaving it invisible; then released the private instance, whereupon somehow the default instance was also released (Forms.Count was decreased by 2!). Access' (really, VBA's) handling of mixed default and private instances is not well documented, so this may or may not be a bug in Access.

I would recommend you change this line of code to:

   Set CCode = Form_frmEmployees!<subform control>.Form

In fact, although default instance variables seem to behave well enough if no private instances of the form are created, I would have written all of this code using the Forms collection to access the open forms. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Petemush]

Rick,

Thanks a lot, cleared up the problem a treat. In the end I didnt have to reference the subform since I changed 'Course Code' into a 'proper' global variable and was able to refer to it from anywhere.

I've always been a bit mystified about Form_... and Forms![... I've known that Forms![.. refers to the collection of open forms but didn't realise that using Form_ actually creates a hidden instance.

I had a quick look through the FAQs and couldn't see any refering to the difference between these, or the difference between the . and ! symbols although I'm pretty sure the latterare explained in the standard access help somewhere. But if you had the time and/or inclination, i think a FAQ on what Form_ and Forms! means in access would be good!

Thanks again,

Pete