Problem with communication between forms

[Carl Imthurn]

Hello out there –

I need your help with a problem I can’t seem to solve. To wit:

I have a form (hereinafter ‘Form1’) with a number of combo boxes on it. I need all of them to have identical functionality: when I double-click on one of these combo boxes (hereinafter ‘Combo1’) I need the following to happen:

I need Form2 to appear on the screen. Form2 is a popup and modal form. It has a text box for a first name, a text box for a last name, an ‘Add person’ command button, and a ‘Cancel’ button. When I fill in a first name and a last name and click the ‘Add
person’ command button, I need a record to be added to a table.

So far so good – I have all of that working correctly. Here’s what I cannot figure out to save my life:
After the person is added to the database, I need the person’s name (formatted as LastName<comma space>FirstName to appear back in Combo1 on Form1 so that the user does not have to re-type in into the combo box.

Can’t figure it out. Spent more time than I care to admit on this one, so I could use some help. The error message I get is:

Microsoft Access can't find the form 'gForm' referred to in a macro expression or Visual Basic code.


Here’s the coding I have so far – like I said, everything works up to making the user input appear back in Combo1. I have added some in-line comments


Here’s the relevant code in the double-click event of Combo1 on Form1:

Private Sub Combo1_DblClick(Cancel As Integer)
AddNewPerson Me, Combo1
End Sub


Here’s the AddNewPerson subroutine:

Option Compare Database
Option Explicit

Public gFirstName As String
Public gLastName As String
Public gForm As Form
Public gComboBox As ComboBox

Public Sub AddNewPerson(frmIn As Form, cboIn As ComboBox)

' I need to pass in the form and the combo box because this functionality
' needs to be available to a number of combo boxes on a number of forms.
Set gForm = frmIn
Set gComboBox = cboIn
DoCmd.OpenForm "frmAddPerson"

End Sub


Here’s the relevant code in frmAddPerson:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

lblMessages.Caption = ""
If Len(txtFirstName) = 0 Or IsNull(txtFirstName) Then
lblMessages.Caption = "Please enter a first name."
txtFirstName.SetFocus
Exit Sub
End If

If Len(txtLastName) = 0 Or IsNull(txtLastName) Then
lblMessages.Caption = "Please enter a last name."
txtLastName.SetFocus
Exit Sub
End If

gFirstName = txtFirstName
gLastName = txtLastName

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb()
strSQL = "INSERT INTO tblPeople (FirstName, LastName) SELECT '" _
& gFirstName & "', '" & gLastName & "'"
Set qdf = db.CreateQueryDef("", strSQL)
qdf.Execute

' now update the control on the original form
Forms!gForm!gComboBox = gLastName & ", " & gFirstName
' ERROR ON PREVIOUS LINE – ERROR MESSAGE IS:
' Microsoft Access can't find the form 'gForm' referred to in a macro expression or Visual Basic code.
' done, so close the form
cmdExit_Click

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub


Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

DoCmd.Close

Exit_cmdExit_Click:
Exit Sub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub

Am I making this *way* more complicated than it has to be?
I don't know if I'm missing something embarrassingly simple . . . if so, please let me know.

As always, any and all help is gratefully accepted.

Thanks in advance –

Carl Imthurn

 

[Pavel Romashkin]

Well... is your Form1 in fact called "gForm"? Is the form named "gForm"
open when your popup form is open?
You are passing the name of calling form to AddNewPerson, which makes me
think you reuse the code for different "Form1"s. However, you are
referring to a hard-coded "gForm" later. Perhaps you should store or
pass as a parameter the name of the source "Form1" to use it on the
offending line of cmdAdd_Click of the popup form. Then, refer to the
right Form1 as part of the Forms collection using its name.

Pavel

 

[Carl Imthurn]

Pavel --

Thanks for your reply.
gForm is a global variable that holds a reference to a form; it's not the name of one of my forms.
I tried passing both the name of the form and a reference to it. Neither one worked.
I have no idea what I'm doing wrong. If I hard-code the name of the form as follows:

Forms!Form1!Combo1 = gLastName & ", " & gFirstName

it seems to work, but for obvious reasons I can't do that.

Thanks anyway for your help.

Carl

 

[Pavel Romashkin]

Well there you go. How do you expect the Froms collection will find
gForm if it is not a form name?
Try instead

Forms(gForm).Form.Controls(gComboBox) = gLastName & ", " & gFirstName

Pavel

 

[Carl Imthurn]

Pavel --

That did it! Helps if you know what you're doing, hmm?

Thank you very much for your time and expertise -- very much appreciated.

Carl