I have a number of forms that get data from tables or add data to tables. Each form needs its own subroutine to do that.
The column names and the corresponding form fields all use the same name. I pass the form name and the table row to the Get or Place sub.
Now when I change my table I have only one place to update the code.
This works but I have a clunky way of finding the user form name.
I can pass the form name to the sub. Is there a better way to do this.
Not every form uses every table column, so I just skip it if I do not need it.
The column names and the corresponding form fields all use the same name. I pass the form name and the table row to the Get or Place sub.
Now when I change my table I have only one place to update the code.
This works but I have a clunky way of finding the user form name.
I can pass the form name to the sub. Is there a better way to do this.
Not every form uses every table column, so I just skip it if I do not need it.
VBA Code:
Public Sub PutCustomer(fName As String, intRow As Integer)
Dim Obj As Object
For Each Obj In VBA.UserForms
If StrComp(Obj.Name, fName, vbTextCompare) = 0 Then
GoTo SetCustomer
End If
Next Obj
Exit Sub
SetCustomer:
On Error Resume Next
With lob_tblCustomer
.ListColumns("CustNum").DataBodyRange(intRow) = Val(Obj.CustNum)
.ListColumns("CustStatus").DataBodyRange(intRow) = Obj.CustStatus
.ListColumns("CustName").DataBodyRange(intRow) = Obj.CustName
.ListColumns("CustSpouse").DataBodyRange(intRow) = Obj.CustSpouse
.ListColumns("CustAdd1").DataBodyRange(intRow) = Obj.CustAdd1
.ListColumns("CustAdd2").DataBodyRange(intRow) = Obj.CustAdd2
.ListColumns("CustApt").DataBodyRange(intRow) = Obj.CustApt
.ListColumns("CustCity").DataBodyRange(intRow) = Obj.CustCity
.ListColumns("CustState").DataBodyRange(intRow) = Obj.CustState
.ListColumns("CustZIP").DataBodyRange(intRow) = Obj.CustZIP
.ListColumns("CustPrimePh").DataBodyRange(intRow) = Obj.CustPrimePh
.ListColumns("CustAltPh").DataBodyRange(intRow) = Obj.CustAltPh
.ListColumns("CustEmail").DataBodyRange(intRow) = Obj.CustEmail
.ListColumns("CustLookUp").DataBodyRange(intRow) = Obj.CustPrimePh
End With
On Error GoTo 0
End Sub