Dynamic Reference to a form

[pcdaveh]

Situation: The code below is from a code module. Various form modules will call this code and feed the data back to the appropriate form. My hope is to be able to do this dynamically. You my or may not agree on why / how I'm doing this but I do have my reasons. Just not enough time to go into alot of detail. I'm trying to dynamically set a reference to the form that is calling this procedure with out hard coding the name of the form and setting up a bunch of IF Statements. I know how to do that.

Each form has a procedure that calls this routine. There is a public variable that contains the name of the form that is calling this routine: strFormName = Me.Name


I'm trying to dynamically substitue strFormName into
Set Form_Name = Forms!Reports_Form_Wizard<----- This part of this statement. You can't do this as a string. But can you do this a dynamically as an object?

I'm doing this because I have to preface this before the name of each text box in this code module to populate the correct form textboxes. You will find the place I'm refering to highlighted in red below.

Option Compare Database
Option Explicit

Public Sub btnPopulate()
On Error GoTo Err_btnPopulateForm_Click

Dim strUsername As String
Dim strPassword As String

Dim dbs As ADODB.Connection
Dim rsdata As ADODB.Recordset

Dim dbs0 As ADODB.Connection
Dim rsData0 As ADODB.Recordset

Dim dbs1 As ADODB.Connection
Dim rsData1 As ADODB.Recordset

Dim strSql As String
Dim strSQL_Create_Table As String
Dim strSelect As String
Dim strSelect_Again As String
Dim strInsert As String
Dim strClient As String
Dim strInitial As String
Dim lngCommaPosition As Long
Dim lngClient_Length As Long
Dim lngInitial_Length As Long
Dim lngQuery_Case As Long
Dim lngAmpersandPosition As Long
Dim lngPeriodPosition As Long
Dim lngUnwantedChar As Long
Dim blnSplit_This As Boolean
Dim lngPrimary_Second As Long

' Dim strplanner As String
' Dim strSSN As String
' Dim strAccountno As String

Dim txtplanner As String 'done
Dim txtprimarylast As String
Dim txtprimaryfirst As String
Dim txtPrimaryPrefix As String
Dim txtsecondarylast As String
Dim txtsecondaryfirst As String
Dim txtsecondaryprefix As String
Dim txtAccountNumber As String 'done
Dim txtSSN As String 'done
Dim txtAddress1 As String
Dim txtAddress2 As String
Dim txtAddress3 As String
Dim txtCity As String
Dim txtState As String
Dim txtZip As String
Dim txtPrimarySSN As String
Dim txtSecondarySSN As String
Dim txtPrimaryDOB As String
Dim txtSecondaryDOB As String
Dim txtPrimaryMiddle As String
Dim txtCityStateZip As String
Dim Form_Name As Form
Dim ThisProject As CurrentProject

Set Form_Name = Forms!Reports_Form_Wizard

'DoCmd.SetWarnings (0)

strUsername = &quot;sa&quot;
strPassword = &quot;&quot;

' Open DB connection
Set dbs = New ADODB.Connection
dbs = &quot;PROVIDER=SQLOLEDB;DATA SOURCE=GOLDMINE;DATABASE=
dbs.Open dbs, strUsername, strPassword

strSql = &quot;SELECT CONTACT1.KEY4 AS planner, CONTACT1.LASTNAME AS primarylast,CONTACT2.UMAINMNAM AS PrimaryMiddle, CONTACT2.UMAINFNAME AS primaryfirst, &quot;
strSql = strSql & &quot;CONTACT2.UCLIPRF1 AS PrimaryPrefix,CONTACT2.UNAMELNAM2 AS secondarylast, CONTACT2.UNAMEFNAM2 AS secondaryfirst, CONTACT2.UCLIPRF2 AS SecondaryPrefix, CONTACT1.ACCOUNTNO AS AccountNumber, &quot;
strSql = strSql & &quot;CONTACT2.UCLISSNUM1 AS SSN, CONTACT1.ADDRESS1 AS Address1, CONTACT1.ADDRESS2 AS Address2, CONTACT1.ADDRESS3 AS Address3, &quot;
strSql = strSql & &quot;CONTACT1.CITY AS City, CONTACT1.STATE AS State, CONTACT1.ZIP AS Zip, CONTACT2.UCLISSNUM1 AS PrimarySSN, &quot;
strSql = strSql & &quot;CONTACT2.UCLISSNUM2 AS SecondarySSN, CONTACT2.UCLIDOB AS PrimaryDOB, CONTACT2.UCLIDOB2 AS SecondaryDOB &quot;
strSql = strSql & &quot;FROM CONTACT1 INNER JOIN CONTACT2 ON CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO &quot;
strSql = strSql & &quot;WHERE CONTACT1.ACCOUNTNO = '&quot; & Form_Name.txtAccountInput & &quot;'&quot;
'strSQL = strSQL & &quot;WHERE CONTACT1.ACCOUNTNO = '&quot; & Me.cmbAccount_select & &quot;'&quot;

' Open the Recordset
Set rsdata = New ADODB.Recordset
rsdata.Open strSql, dbs, adOpenDynamic, adLockOptimistic
'txtplanner
'txtprimarylast
'txtprimaryfirst
'txtsecondarylast
'txtsecondaryfirst
'txtAccountNumber
'txtSSN
'txtAddress1
'txtAddress2
'txtAddress3
'txtCity
'txtState
'txtZip

If Not rsdata.EOF Then

If Len(Trim(rsdata(&quot;planner&quot)) > 0 Then
txtplanner = Trim(rsdata(&quot;planner&quot)
Form_Name.txtplanner = txtplanner
Else
txtplanner = &quot;&quot;
Form_Name.txtplanner = txtplanner
End If

If Len(Trim(rsdata(&quot;primarylast&quot)) > 0 Then
txtprimarylast = Trim(rsdata(&quot;primarylast&quot)
Form_Name.txtprimarylast = txtprimarylast
Else
txtprimarylast = &quot;&quot;
Form_Name.txtprimarylast = txtprimarylast
End If

If Len(Trim(rsdata(&quot;primaryfirst&quot)) > 0 Then
txtprimaryfirst = Trim(rsdata(&quot;primaryfirst&quot)
Form_Name.txtprimaryfirst = txtprimaryfirst
Else
txtprimaryfirst = &quot;&quot;
Form_Name.txtprimaryfirst = txtprimaryfirst
End If

If Len(Trim(rsdata(&quot;PrimaryMiddle&quot)) > 0 Then
txtPrimaryMiddle = Trim(Left(rsdata(&quot;primaryMiddle&quot, 1))
Form_Name.txtPrimaryMiddle = txtPrimaryMiddle
Else
txtPrimaryMiddle = &quot;&quot;
Form_Name.txtPrimaryMiddle = txtPrimaryMiddle
End If

If Len(txtprimaryfirst) > 0 Then
If Len(txtprimarylast) > 0 Then
Form_Name.txtName = txtprimarylast & &quot;,&quot; & &quot; &quot; & txtprimaryfirst
Else
Form_Name.txtName = txtprimaryfirst
End If
Else
If Len(txtprimarylast) > 0 Then
Form_Name.txtName = txtprimarylast
Else
Form_Name.txtName = &quot;Please Supply a Name&quot;
End If
End If

If Len(Trim(rsdata(&quot;secondarylast&quot)) > 0 Then
txtsecondarylast = Trim(rsdata(&quot;secondarylast&quot)
Form_Name.txtsecondarylast = txtsecondarylast
Else
txtsecondarylast = &quot;&quot;
Form_Name.txtsecondarylast = txtsecondarylast
End If

If Len(Trim(rsdata(&quot;secondaryfirst&quot)) > 0 Then
txtsecondaryfirst = Trim(rsdata(&quot;secondaryfirst&quot)
Form_Name.txtsecondaryfirst = txtsecondaryfirst
Else
txtsecondaryfirst = &quot;&quot;
Form_Name.txtsecondaryfirst = txtsecondaryfirst
End If

If Len(Trim(rsdata(&quot;AccountNumber&quot)) > 0 Then
txtAccountNumber = rsdata(&quot;AccountNumber&quot
' point this where you will
End If

If Len(Trim(rsdata(&quot;SSN&quot)) > 0 Then
txtSSN = Trim(rsdata(&quot;SSN&quot)
Form_Name.txt_SSN1_Before = txtSSN
Call Parse_SSN(txtSSN)
Form_Name.txtSSN = txtSSN
Else
txtSSN = &quot;&quot;
Form_Name.txtSSN = txtSSN
End If

If Len(Trim(rsdata(&quot;Address1&quot)) > 0 Then
txtAddress1 = Trim(rsdata(&quot;Address1&quot)
Form_Name.txtAddress = txtAddress1
Else
txtAddress1 = &quot;&quot;
Form_Name.txtAddress = txtAddress1
End If

If Len(Trim(rsdata(&quot;Address2&quot)) > 0 Then
txtAddress2 = Trim(rsdata(&quot;Address2&quot)
Form_Name.txtAddress2 = txtAddress2
Else
txtAddress2 = &quot;&quot;
Form_Name.txtAddress2 = txtAddress2
End If

If Len(Trim(rsdata(&quot;Address3&quot)) > 0 Then
txtAddress3 = Trim(rsdata(&quot;Address3&quot)
Form_Name.txtAddress3 = txtAddress3
Else
txtAddress3 = &quot;&quot;
Form_Name.txtAddress3 = txtAddress3
End If

If Len(Trim(rsdata(&quot;City&quot)) > 0 Then
txtCity = Trim(rsdata(&quot;City&quot)
Form_Name.txtCity = txtCity
Else
txtCity = &quot;&quot;
Form_Name.txtCity = txtCity
End If

If Len(Trim(rsdata(&quot;State&quot)) > 0 Then
txtState = Trim(rsdata(&quot;State&quot)
Form_Name.txtState = txtState
Else
txtState = &quot;&quot;
Form_Name.txtState = txtState
End If

If Len(Trim(rsdata(&quot;Zip&quot)) > 0 Then
txtZip = Trim(rsdata(&quot;Zip&quot)
Form_Name.txtZip = txtZip
Else
txtZip = &quot;&quot;
Form_Name.txtZip = txtZip
End If

If Len(txtCity) > 0 Then
If Len(txtState) > 0 Then
If Len(txtZip) > 0 Then
Form_Name.txtCityStateZip = txtCity & &quot;, &quot; & txtState & &quot; &quot; & txtZip
Else
Form_Name.txtCityStateZip = txtCity & &quot;, &quot; & txtState
End If
Else
If Len(txtZip) > 0 Then
Form_Name.txtCityStateZip = txtCity & &quot;, State Unknown &quot; & txtZip
Else
Form_Name.txtCityStateZip = &quot;City Unknown&quot; & &quot;, State Unknown &quot; & txtZip
End If
End If
Else
If Len(txtState) > 0 Then
If Len(txtZip) > 0 Then
Form_Name.txtCityStateZip = &quot;City Unknown&quot; & &quot;, &quot; & txtState & &quot; &quot; & txtZip
Else
Form_Name.txtCityStateZip = &quot;City Unknown&quot; & &quot;, &quot; & txtState & &quot; ZIP Code Unknown&quot;
End If
Else
If Len(txtZip) > 0 Then
Form_Name.txtCityStateZip = &quot;City Unknown&quot; & &quot;, State Unknown &quot; & txtZip
Else
Form_Name.txtCityStateZip = &quot;City Unknown&quot; & &quot;, State Unknown &quot; & &quot;ZIP Code Unknown&quot;
End If
End If

End If

If Len(Trim(rsdata(&quot;PrimarySSN&quot)) > 0 Then
txtPrimarySSN = Trim(rsdata(&quot;PrimarySSN&quot)
txtSSN = txtPrimarySSN
Call Parse_SSN(txtSSN)
txtPrimarySSN = txtSSN
Form_Name.txtPrimarySSN = txtPrimarySSN
Else
Form_Name.txtPrimarySSN = &quot;&quot;
End If

If Len(Trim(rsdata(&quot;SecondarySSN&quot)) > 0 Then
txtSecondarySSN = Trim(rsdata(&quot;SecondarySSN&quot)
txtSSN = txtSecondarySSN
Form_Name.txt_SSN2_Before = txtSSN
Call Parse_SSN(txtSSN)
txtSecondarySSN = txtSSN
Form_Name.txtSecondarySSN = txtSecondarySSN
Else
Form_Name.txtSecondarySSN = &quot;&quot;
End If

If Len(Trim(rsdata(&quot;PrimaryDOB&quot)) > 0 Then
If IsDate(CVDate(Trim(rsdata(&quot;PrimaryDOB&quot))) Then
txtPrimaryDOB = Trim(rsdata(&quot;PrimaryDOB&quot)
Else
txtPrimaryDOB = Trim(rsdata(&quot;PrimaryDOB&quot) & &quot;: Invalid DOB&quot;
End If
Form_Name.txtPrimaryDOB = txtPrimaryDOB
Else
Form_Name.txtPrimaryDOB = &quot;&quot;
End If


If Not IsNull(rsdata(&quot;SecondaryDOB&quot) Then
If IsDate(CVDate(Trim(rsdata(&quot;SecondaryDOB&quot))) Then
txtSecondaryDOB = Trim(rsdata(&quot;SecondaryDOB&quot)
Else
txtSecondaryDOB = Trim(rsdata(&quot;SecondaryDOB&quot) & &quot;: Invalid DOB&quot;
End If
Form_Name.txtSecondaryDOB = txtSecondaryDOB
Else
Form_Name.txtSecondaryDOB = &quot;&quot;
End If

' rsData.MoveNext
End If

If Len(Trim(rsdata(&quot;PrimaryPrefix&quot)) > 0 Then
txtPrimaryPrefix = Trim(rsdata(&quot;PrimaryPrefix&quot)
Select Case UCase(txtPrimaryPrefix)
Case &quot;MR.&quot;
Form_Name.chkPrimaryMr = -1
Form_Name.chkPrimaryMrs = 0
Form_Name.chkPrimaryMs = 0
Case &quot;MR&quot;
Form_Name.chkPrimaryMr = -1
Form_Name.chkPrimaryMrs = 0
Form_Name.Form_Name.chkPrimaryMs = 0
Case &quot;MRS.&quot;
Form_Name.chkPrimaryMrs = -1
Form_Name.chkPrimaryMr = 0
Form_Name.chkPrimaryMs = 0
Case &quot;MRS&quot;
Form_Name.chkPrimaryMrs = -1
Form_Name.chkPrimaryMr = 0
Form_Name.chkPrimaryMs = 0
Case &quot;MISS&quot;
Form_Name.chkPrimaryMs = -1
Form_Name.chkPrimaryMr = 0
Form_Name.chkPrimaryMrs = 0
Case &quot;MISS.&quot;
Form_Name.chkPrimaryMs = -1
Form_Name.chkPrimaryMr = 0
Form_Name.chkPrimaryMrs = 0
Case &quot;MS.&quot;
Form_Name.chkPrimaryMs = -1
Form_Name.chkPrimaryMr = 0
Form_Name.chkPrimaryMrs = 0
Case &quot;MS&quot;
Form_Name.chkPrimaryMs = -1
Form_Name.chkPrimaryMr = 0
Form_Name.chkPrimaryMrs = 0
Case Else
Form_Name.chkPrimaryMs = 0
Form_Name.chkPrimaryMr = 0
Form_Name.chkPrimaryMrs = 0
End Select
Else
Form_Name.chkPrimaryMs = 0
Form_Name.chkPrimaryMr = 0
Form_Name.chkPrimaryMrs = 0
End If

Form_Name.Repaint

rsdata.Close
Set rsdata = Nothing
strFormName = &quot;&quot;
Exit_btnPopulateForm_Click:
Exit Sub

Err_btnPopulateForm_Click:
MsgBox Err.Description
Resume Next
Resume Exit_btnPopulateForm_Click

End Sub

 

[oharab]

With all your Dims at the top, add:

Dim Form_Name as Form

Then replace
Set Form_Name = Forms!Reports_Form_Wizard
with

Set Form_Name = Forms(strFormName)

now Form_Name will be whatever form strFormName is.

HTH

B
----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[pcdaveh]

Thanks!