Pass name of calling form to pop-up form

[BruceM]

In advance, sorry this is so long. I cannot think of a way to compress it.

I have a vendor database. There is a main Vendors table, and a related
PhoneNumber table (since there could be several phone numbers).

There are two forms. One lists all vendors; the other is a subset of
vendors. The forms display significantly different information, so there
are two separate forms (frmMain and frmLimited). I want it to be possible
to add or edit a phone number from either form by calling the same pop-up
form (unless there are specific reasons why I shouldn't do it that way).

The pop-up form is bond to the PhoneNumber table. For phone number display
on frmMain or frmLimited I am using a list box (lstPhone). It's row source
is set in the Current event event of frmMain or frmLimited:

Me.lstPhone = -1
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE VendorID = " & Me.VendorID & " ORDER BY MainPhone"
Me.lstPhone.Requery

This works as intended.

To change a number I use the Click event of an unassociated label on frmMain
or frmLimited to open the pop-up form frmAddPhone (bound to the PhoneNumber
table):

Dim strArgs As String, strWhere As String

strArgs = Me.VendorID & "|" & Me.VendorName & "|" & Me.Form.Name
strWhere = "VendorID = " & Me.VendorID

Me.Dirty = False

DoCmd.OpenForm "frmAddPhone", , , strWhere, , acDialog, strArgs

So far so good. Now I click the New button on frmAddPhone, which takes me
to a new record. I add the phone number and click an Add button. This is
the awkward code I need to use (explanation of fVendID and fFormName to
follow):

Dim lngID As Long
Dim strRow As String

If Me.NewRecord Then
lngID = fVendID
Me.VendorID = lngID
strRow = "SELECT Phone FROM tblPhone WHERE VendorID = " & _
lngID & " ORDER BY MainPhone"
If fFormName = "frmMain" Then
With Forms!frmMain
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
ElseIf fFormName = "frmLimited" Then
With Forms!frmLimited
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
End If
End If

fFormName is a function in the frmAddPhone module (error handling omitted
for this posting):

Public Function fFormName()

fFormName = Split(Me.OpenArgs, "|")(2)

End Function

fVendID is similar, but returns a number.

fFormName gives me a value I can check in the Add button code above.
However, it seems I should be able to do:

If Me.NewRecord Then
lngID = fVendID
Me.VendorID = lngID
strRow = "SELECT Phone FROM tblPhone WHERE VendorID = " & _
lngID & " ORDER BY MainPhone"
If fFormName = "frmMain" Then
With Forms!fFormName
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
End If
End If

I have tried:
Public Function fFormName() as Variant

and also I tried converting the value to a variant in the Add button code.

The frustration is that I can see (when I step through the code) the value
frmMain or frmApproved that is returned by fFormName, but I cannot use the
value in the With statement.

There is similar code in the click event of an unassociated label on
frmAddPhone to delete a phone number and reset the list box:

Dim strRow As String
Dim varForm As Variant

strRow = "SELECT Phone FROM tblPhone WHERE VendorID = " & _
fVendID & " ORDER BY MainPhone"

Me.txtCounter = ""
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete
If Me.CurrentRecord > Me.RecordsetClone.RecordCount Then
Me.Recordset.MoveLast
End If

varForm = fFormName

If fFormName = "frmMain" Then
With Forms!frmMain
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
ElseIf fFormName = "frmLimited" Then
With Forms!frmLimited
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
End If

In a possibly related matter, when I run the Delete code the list box view
refreshes. That is, if there are two phone numbers, the list box (which is
one row high) shows up and down arrows. After deleting a phone number the
list box shows no arrows (just one number left). However, when I *add* a
phone number, the list box view does not refresh. If it had one number it
still has one number, and there are no up and down arrows. If I move to
another main form record and back, the list box contains the information it
should, and the up and down arrows appear as they should. I can't figure
out why the Delete code refreshes the list box and the Add code does not. I
have tried refreshing and repainting the form, but it makes no difference.

 

[Dirk Goldgar]

[...]

Me.lstPhone = -1
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE VendorID = " & Me.VendorID & " ORDER BY MainPhone"
Me.lstPhone.Requery

This works as intended.

But FYI it's unnecessary to requery the list box after you have changed its
RowSource property. Changing the RowSource propery automatically forces a
Requery, so your code above is effectively running the query twice.

So far so good. Now I click the New button on frmAddPhone, which takes me
to a new record. I add the phone number and click an Add button. This is
the awkward code I need to use (explanation of fVendID and fFormName to
follow):

Dim lngID As Long
Dim strRow As String

If Me.NewRecord Then
lngID = fVendID
Me.VendorID = lngID
strRow = "SELECT Phone FROM tblPhone WHERE VendorID = " & _
lngID & " ORDER BY MainPhone"
If fFormName = "frmMain" Then
With Forms!frmMain
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
ElseIf fFormName = "frmLimited" Then
With Forms!frmLimited
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
End If
End If

fFormName is a function in the frmAddPhone module

First, why incur the overhead of calling fFormName multiple times? I'd get
that value in a variable and use that variable in the rest of the code,
rather than calling the function twice.

However, it seems I should be able to do:

If Me.NewRecord Then
lngID = fVendID
Me.VendorID = lngID
strRow = "SELECT Phone FROM tblPhone WHERE VendorID = " & _
lngID & " ORDER BY MainPhone"
If fFormName = "frmMain" Then
With Forms!fFormName
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
End If
End If

You have to do it like this (leaving out the non-essentials):

Dim strFormName As String

strFormName = fFormName

With Forms(strFormName)
!lstPhone = -1
!lstPhone.RowSource = strRow
!txtVendorName.SetFocus
End With

Note the use of Forms(strFormName) instead of Forms!strFormName. You
probably should check to make sure you actually *have* a form name returned
from fFormName.

In a possibly related matter, when I run the Delete code the list box view
refreshes. That is, if there are two phone numbers, the list box (which
is one row high) shows up and down arrows. After deleting a phone number
the list box shows no arrows (just one number left). However, when I
*add* a phone number, the list box view does not refresh. If it had one
number it still has one number, and there are no up and down arrows. If I
move to another main form record and back, the list box contains the
information it should, and the up and down arrows appear as they should.
I can't figure out why the Delete code refreshes the list box and the Add
code does not. I have tried refreshing and repainting the form, but it
makes no difference.

My guess would be one of two things; either

(a) the "added" record hasn't actually been saved yet -- this could be
solved by executing "Me.Dirty = False" before resetting or requerying the
rowsource of the list box, or

(b) this code is only executed when Me.NewRecord = True. But if you're
executing the code only after you've saved the new record, then Me.NewRecord
will be False, because the record has been saved.

 

[BruceM]

Thanks for the speedy reply, and for getting to the heart of every
difficulty I was having. Everything now works as it should. I removed the
requery, used the parentheses syntax to call the form (the value passed with
OpenArgs), and saved the record as soon as I added VendorID (which caused
the list box to behave as it should regarding arrows and the list).
I had stepped through the code, so I knew the function was returning the
correct value.
Regarding the overhead of repeated function calls, should I so something
like declare the variables in the General part of the module, give them
values in the form's Load event, and use those variables thereafter in the
module? My reading has lately led me to become concerned about public
variables, but I may be taking that farther than I need to in that the
admonition about public variables being prone to lose their values in case
of an unhandled error is of concern mostly when passing variables between
code modules. In any case I add error handling to all code, so I probably
don't need to worry about that in this context.
Or would I do better to assign the function result to a variable within a
procedure, and use the variable thereafter in that procedure only (as you
did with the value from the fFormName function)?
I had fFormName return a string (I think):
Public Function fFormName() as String
It seems to work as it should to reference the correct form. Is this
approach going to work, or are there dangers lurking?

[...]

Me.lstPhone = -1
Me.lstPhone.RowSource = "SELECT Phone FROM tblPhone " & _
"WHERE VendorID = " & Me.VendorID & " ORDER BY MainPhone"
Me.lstPhone.Requery

This works as intended.

But FYI it's unnecessary to requery the list box after you have changed
its RowSource property. Changing the RowSource propery automatically
forces a Requery, so your code above is effectively running the query
twice.

So far so good. Now I click the New button on frmAddPhone, which takes
me to a new record. I add the phone number and click an Add button.
This is the awkward code I need to use (explanation of fVendID and
fFormName to follow):

Dim lngID As Long
Dim strRow As String

If Me.NewRecord Then
lngID = fVendID
Me.VendorID = lngID
strRow = "SELECT Phone FROM tblPhone WHERE VendorID = " & _
lngID & " ORDER BY MainPhone"
If fFormName = "frmMain" Then
With Forms!frmMain
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
ElseIf fFormName = "frmLimited" Then
With Forms!frmLimited
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
End If
End If

fFormName is a function in the frmAddPhone module

First, why incur the overhead of calling fFormName multiple times? I'd
get that value in a variable and use that variable in the rest of the
code, rather than calling the function twice.

However, it seems I should be able to do:

If Me.NewRecord Then
lngID = fVendID
Me.VendorID = lngID
strRow = "SELECT Phone FROM tblPhone WHERE VendorID = " & _
lngID & " ORDER BY MainPhone"
If fFormName = "frmMain" Then
With Forms!fFormName
!lstPhone = -1
!lstPhone.RowSource = strRow
!lstPhone.Requery
!txtVendorName.SetFocus
End With
End If
End If

You have to do it like this (leaving out the non-essentials):

Dim strFormName As String

strFormName = fFormName

With Forms(strFormName)
!lstPhone = -1
!lstPhone.RowSource = strRow
!txtVendorName.SetFocus
End With

Note the use of Forms(strFormName) instead of Forms!strFormName. You
probably should check to make sure you actually *have* a form name
returned from fFormName.

In a possibly related matter, when I run the Delete code the list box
view refreshes. That is, if there are two phone numbers, the list box
(which is one row high) shows up and down arrows. After deleting a phone
number the list box shows no arrows (just one number left). However,
when I *add* a phone number, the list box view does not refresh. If it
had one number it still has one number, and there are no up and down
arrows. If I move to another main form record and back, the list box
contains the information it should, and the up and down arrows appear as
they should. I can't figure out why the Delete code refreshes the list
box and the Add code does not. I have tried refreshing and repainting
the form, but it makes no difference.

My guess would be one of two things; either

(a) the "added" record hasn't actually been saved yet -- this could be
solved by executing "Me.Dirty = False" before resetting or requerying the
rowsource of the list box, or

(b) this code is only executed when Me.NewRecord = True. But if you're
executing the code only after you've saved the new record, then
Me.NewRecord will be False, because the record has been saved.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the speedy reply

You're welcome.

Everything now works as it should.
Excellent.

Regarding the overhead of repeated function calls, should I so something
like declare the variables in the General part of the module, give them
values in the form's Load event, and use those variables thereafter in the
module? My reading has lately led me to become concerned about public
variables, but I may be taking that farther than I need to in that the
admonition about public variables being prone to lose their values in case
of an unhandled error is of concern mostly when passing variables between
code modules.

Right. I wouldn't worry about that issue in this case; however, I also
wouldn't declare the variables at module level unless you need to use them
in various different procedures within the module, during the lifetime of
this instance of the form. It doesn't sound to me as though that's the
case. If it is, you can do that if you want, or decide not to bother
because the overhead of this particular function call is very small.

Or would I do better to assign the function result to a variable within a
procedure, and use the variable thereafter in that procedure only (as you
did with the value from the fFormName function)?

That's probably what I would do, but it depends on the particular
circumstances.

Is this approach going to work, or are there dangers lurking?

The approach should work. It may not be necessary in all cases, as you can
generally get the name of the "calling form" in the Open event of a "called"
form, using this method:

Dim mstrCallingForm As String

Private Sub Form_Open(Cancel As Integer)

mstrCallingForm = vbNullString
On Error Resume Next
mstrCallingForm = Screen.ActiveForm.Name

End Sub

That works because, in the called form's Open event, it is not yet the
active form -- the form that called it is (provided that form was in fact
active at the time).