Losing value of variable

  • Thread starter Thread starter jim
  • Start date Start date
J

jim

Here's my code in a pop-up form:
Option Compare Database
Option Explicit

Private Sub ContactName_AfterUpdate()
Dim txtCustomerName As String
Dim txtBuildingNo As Integer

If NewRecord Then 'When a new contact
is added assign next Contact Number for Building
If DCount("ContactNo", "tblClientBuildContacts", "[CustomerName] =
""" & txtCustomerName & """ AND [BuildingNo] = " & txtBuildingNo) = 0 Then
'"[CustomerName] = """ & Me!CustomerName & """ And [BuildingNo]
= " & Me!BuildingNo
Me.txtContactNo = 1
Else
Me.txtContactNo = DMax("ContactNo", "tblCustomerContacts",
"[CustomerName] = """ & txtCustomerName & """ AND [BuildingNo] = " &
txtBuildingNo) + 1
End If
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
Dim txtCustomerName As String
Dim txtBuildingNo As Integer

If IsNull(OpenArgs) = False Then
Dim arrArgs() As String
arrArgs = Split(Me.OpenArgs, ",")
txtCustomerName = arrArgs(0)
txtBuildingNo = arrArgs(1)
End If
End Sub

And in the calling form I have:
Private Sub Option96_Click()
Dim strOpenArgs As String
strOpenArgs = Me.txtCustomerName & "," & Me.txtBuildingNo

DoCmd.OpenForm "sfrmClientBuildContacts", , , "[CustomerName] = """ &
Me!CustomerName & """ And [BuildingNo] = " & Me!BuildingNo, , , strOpenArgs
.....which is working very good.

The problem is in the Sub ContactName_AfterUpdate. Somehow the variable
txtCustomerName is lost and shows null in debug mode. The other variable,
txtBuildingNo is fine. So when I try to add a record it fails because
CustomerName is a required field. Why is the variable lost? I tried
defining them twice but this also does not work.
 
Would you please explain exactly what you are trying to do, as I can't quite
follow it from the code.
I assume the popup form is a bound form because you are using
Me!CustomerName
It looks like you are getting the value for an unbound textbox called
txtCustomerName from the OpenArgs?

I'm not following the bit where if there are no contacts with the customer
name passed in as open args, then you want the form to set the value for
CustomerName to the form's CustomerName.
If you could explain this bit, it would help us to help you.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
You need to make the variables txtCustomerName and txtBuildingNo global
within the form. Move both dim statements you have in the Sub
ContactName_AfterUpdate and place it below the Option Explicit statment. This
way the variables are accessible to all the routines/functions within the
form. Do the following:


Option Compare Database
Option Explicit
Dim txtCustomerName As String
Dim txtBuildingNo As Integer
 
Back
Top