A neater way to do the following, Looping through controls

I

Ian Mac

Registered User.
Local time
Today, 16:44
Joined
Mar 11, 2002
Messages
179
All,

Can someone please show me a nicer way to achieve the following:

Code: 
If IsNull([frm_UTC1_Subform]![TCUser]) Then
[frm_UTC1_Subform]![EmployeePid] = Null
Else
[frm_UTC1_Subform]![EmployeePid] = SystoPid([frm_UTC1_Subform]![TCUser])
End If

If IsNull([frm_UTC2_Subform]![TCUser]) Then
[frm_UTC2_Subform]![EmployeePid] = Null
Else
[frm_UTC2_Subform]![EmployeePid] = SystoPid([frm_UTC2_Subform]![TCUser])
End If

If IsNull([frm_UTC3_Subform]![TCUser]) Then
[frm_UTC3_Subform]![EmployeePid] = Null
Else
[frm_UTC3_Subform]![EmployeePid] = SystoPid([frm_UTC3_Subform]![TCUser])
End If

If IsNull([frm_STC_Subform]![TCUser]) Then
[frm_STC_Subform]![EmployeePid] = Null
Else
[frm_STC_Subform]![EmployeePid] = SystoPid([frm_STC_Subform]![TCUser])
End If

If IsNull([frm_Letter_DL15_1_Subform]![LetUser]) Then
[frm_Letter_DL15_1_Subform]![EmployeePid] = Null
Else
[frm_Letter_DL15_1_Subform]![EmployeePid] = SystoPid([frm_Letter_DL15_1_Subform]![LetUser])
End If

If IsNull([frm_Letter_DL15_2_Subform]![LetUser]) Then
[frm_Letter_DL15_2_Subform]![EmployeePid] = Null
Else
[frm_Letter_DL15_2_Subform]![EmployeePid] = SystoPid([frm_Letter_DL15_2_Subform]![LetUser])
End If

If IsNull([frm_Letter_Replies_Subform]![LetUser]) Then
[frm_Letter_Replies_Subform]![EmployeePid] = Null
Else
[frm_Letter_Replies_Subform]![EmployeePid] = SystoPid([frm_Letter_Replies_Subform]![LetUser])
End If

If IsNull([frm_HandOff_Subform]![HoUser]) Then
[frm_HandOff_Subform]![EmployeePid] = Null
Else
[frm_HandOff_Subform]![EmployeePid] = SystoPid([frm_HandOff_Subform]![HoUser])
End If

If IsNull([frm_CompTBS_Subform]![CoUser]) Then
[frm_CompTBS_Subform]![EmployeePid] = Null
Else
[frm_CompTBS_Subform]![EmployeePid] = SystoPid([frm_CompTBS_Subform]![CoUser])
End If

If IsNull([frm_CompPDCS_subform]![CoUser]) Then
[frm_CompPDCS_subform]![EmployeePid] = Null
Else
[frm_CompPDCS_subform]![EmployeePid] = SystoPid([frm_CompPDCS_subform]![CoUser])
End If

If IsNull([frm_CompAA_Subform]![CoUser]) Then
[frm_CompAA_Subform]![EmployeePid] = Null
Else
[frm_CompAA_Subform]![EmployeePid] = SystoPid([frm_CompAA_Subform]![CoUser])
End If

If IsNull([frm_CompCA_Subform]![CoUser]) Then
[frm_CompCA_Subform]![EmployeePid] = Null
Else
[frm_CompCA_Subform]![EmployeePid] = SystoPid([frm_CompCA_Subform]![CoUser])
End If

For info:
1) All of the subform apart form frm_PrintDate_Subform have an EmployeeID control
2) I can and will be changing the 'LetUser', 'TCUser' to simply 'OrginalOperator' on every table
i.e. my syntax for the above will be:
If IsNull([frm_CompAA_Subform]![OriginalOperator]) Then

I'm looking for the correct way to loop through the Subforms and achieve the above,

any further information needed I can provide it.

Thanks,
 
Is SystoPid a function you have written ? Why not incorporate the null check into that function ?
 
john471 said:
Is SystoPid a function you have written ? Why not incorporate the null check into that function ?
Click to expand...

the SystoPid is a Function I had help with :)

Code: 
Public Function SystoPid(SysID As Long)
Dim dbs As Database, qdf As QueryDef, rst As Recordset, strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT [tbl_CBO-TPO:EmployeeDetail].Pid, [tbl_CBO-TPO:EmployeeDetail].EmployeeID " & _
            "FROM [tbl_CBO-TPO:EmployeeDetail] " & _
            "WHERE ((([tbl_CBO-TPO:EmployeeDetail].EmployeeID)=" & SysID & "));"

Set qdf = dbs.CreateQueryDef("", strSQL)
Set rst = qdf.OpenRecordset()
SystoPid = rst!Pid.Value
Set dbs = Nothing

End Function

I'm not entirely sure I can do that, but certainly hope so.
 
When (in which event) would you want to run the code. This doesn't look to me like something you should be doing via a form. Forms are ment to be used interactively not as containers for batch updates. Are you aware that the If statements are only modifying the EmployeePid value for the single current record of each subform, Not all rows in the subforms' RecordSources?
 
Pat Hartman said:
When (in which event) would you want to run the code. This doesn't look to me like something you should be doing via a form. Forms are ment to be used interactively not as containers for batch updates. Are you aware that the If statements are only modifying the EmployeePid value for the single current record of each subform, Not all rows in the subforms' RecordSources?
Click to expand...

Sorry Pat, I haven't explained it fully,

The EmployeePid is an Unbound Control for display purposes only, (and update later on, PidtoSys Function)

When the user clicks Search (My own button, not an Access thing)
If there is a value in OriginalOperater then update the EmployeePid with the function to the visual.

The infomation for this comes from a Seperate Employee DB I have.

Here's the entire code for the button:

Code: 
Private Sub cmb_SearchRec_Click()

'Declare varibles for multi-record test
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

'Set the NINO value in the function to the NINOCrn Textbox
SetNINOval (Me!NinoCrn.Value)

'Set the test for multi-records
Set rst = dbs.OpenRecordset("qry_DC:Main")

'Application.Echo False

If rst.RecordCount = 0 Then
        If NINOval = "" Then
            'If the number of records is 0 then
            'set the focus back to the NINOCrn textbox
            'for the user to enter a value
            'IF they haven't entered a value in NINOCrn
            Me!NinoCrn.SetFocus
            Exit Sub
                Else
            '------------------------------------------------------
            'If the NINOCrn has a value in
            'but the records returned are 0
            'Then let the user know they have made an error
            'clear the NINOCrn and set the focus back to it
            '------------------------------------------------------
            MsgBox "The NINO you have enter does not exist on the System," & _
            Chr(13) & Chr(10) & "please re-enter the NINO again", vbCritical + vbOKOnly, "Error"
            Me!NinoCrn.Value = ""
            Me!NinoCrn.Requery
            Me!NinoCrn.SetFocus
            Exit Sub
        End If

Else
rst.MoveLast
If rst.RecordCount > 1 Then
        Me!bx_Next.Visible = True
        Me!bx_Outer.Visible = True
        Me!bx_Prev.Visible = True
        Me!lbl_Next.Visible = True
        Me!lbl_Prev.Visible = True
        Me!cmd_Next.Visible = True
        Me!cmd_Previous.Visible = True
        
        MsgBox "There are " & rst.RecordCount & " records with this NINO," & Chr(10) & Chr(13) & _
        "Please use the navigation buttons on the left to find the record you need", vbOKOnly, "Choose Record"
        'DoCmd.OpenForm "frm_MultiRecords"
        'Exit Sub
End If

Me.Requery

'--------------Set the values for Orginal OP------------------
'Each part tests the value in Original Operator field of the Subform
'Then returns the corresponding Pid from the Employee Table
'Uses the Function SystoPid()

If IsNull([frm_UTC1_Subform]![TCUser]) Then
[frm_UTC1_Subform]![EmployeePid] = Null
Else
[frm_UTC1_Subform]![EmployeePid] = SystoPid([frm_UTC1_Subform]![TCUser])
End If

If IsNull([frm_UTC2_Subform]![TCUser]) Then
[frm_UTC2_Subform]![EmployeePid] = Null
Else
[frm_UTC2_Subform]![EmployeePid] = SystoPid([frm_UTC2_Subform]![TCUser])
End If

If IsNull([frm_UTC3_Subform]![TCUser]) Then
[frm_UTC3_Subform]![EmployeePid] = Null
Else
[frm_UTC3_Subform]![EmployeePid] = SystoPid([frm_UTC3_Subform]![TCUser])
End If

If IsNull([frm_STC_Subform]![TCUser]) Then
[frm_STC_Subform]![EmployeePid] = Null
Else
[frm_STC_Subform]![EmployeePid] = SystoPid([frm_STC_Subform]![TCUser])
End If

If IsNull([frm_Letter_DL15_1_Subform]![LetUser]) Then
[frm_Letter_DL15_1_Subform]![EmployeePid] = Null
Else
[frm_Letter_DL15_1_Subform]![EmployeePid] = SystoPid([frm_Letter_DL15_1_Subform]![LetUser])
End If

If IsNull([frm_Letter_DL15_2_Subform]![LetUser]) Then
[frm_Letter_DL15_2_Subform]![EmployeePid] = Null
Else
[frm_Letter_DL15_2_Subform]![EmployeePid] = SystoPid([frm_Letter_DL15_2_Subform]![LetUser])
End If

If IsNull([frm_Letter_Replies_Subform]![LetUser]) Then
[frm_Letter_Replies_Subform]![EmployeePid] = Null
Else
[frm_Letter_Replies_Subform]![EmployeePid] = SystoPid([frm_Letter_Replies_Subform]![LetUser])
End If

If IsNull([frm_HandOff_Subform]![HoUser]) Then
[frm_HandOff_Subform]![EmployeePid] = Null
Else
[frm_HandOff_Subform]![EmployeePid] = SystoPid([frm_HandOff_Subform]![HoUser])
End If

If IsNull([frm_HandOff_Subform]![HoTID]) Then
[frm_HandOff_Subform]![HandOffTo] = Null
Else
[frm_HandOff_Subform]![HandOffTo] = HOSYStoHOV([frm_HandOff_Subform]![HoTID])
End If

If IsNull([frm_HandOff_Subform]![HoRID]) Then
[frm_HandOff_Subform]![HandOffReason] = Null
Else
[frm_HandOff_Subform]![HandOffReason] = HORSYStoHORV([frm_HandOff_Subform]![HoRID])
End If

If IsNull([frm_CompTBS_Subform]![CoUser]) Then
[frm_CompTBS_Subform]![EmployeePid] = Null
Else
[frm_CompTBS_Subform]![EmployeePid] = SystoPid([frm_CompTBS_Subform]![CoUser])
End If

If IsNull([frm_CompPDCS_subform]![CoUser]) Then
[frm_CompPDCS_subform]![EmployeePid] = Null
Else
[frm_CompPDCS_subform]![EmployeePid] = SystoPid([frm_CompPDCS_subform]![CoUser])
End If

If IsNull([frm_CompAA_Subform]![CoUser]) Then
[frm_CompAA_Subform]![EmployeePid] = Null
Else
[frm_CompAA_Subform]![EmployeePid] = SystoPid([frm_CompAA_Subform]![CoUser])
End If

If IsNull([frm_CompCA_Subform]![CoUser]) Then
[frm_CompCA_Subform]![EmployeePid] = Null
Else
[frm_CompCA_Subform]![EmployeePid] = SystoPid([frm_CompCA_Subform]![CoUser])
End If
            
'Once the form has populated set the focus on the first box in the Subform
Me!frm_UTC1_Subform.SetFocus
Me!frm_UTC1_Subform.Form!Date.SetFocus

End If

Me!NinoCrn.Value = NINOval
'---------------------------------------------------------------------

'Application.Echo True

rst.Close
Set dbs = Nothing
End Sub

the NINOVal is:

Code: 
Global NINOval As String
Global PidVal As Long

Public Sub SetNINOval(myNINO As Variant)
    
    If IsNull(myNINO) Then
        NINOval = ""
    Else
        NINOval = myNINO
    End If

End Sub

Public Function GetNINOval()

    GetNINOval = NINOval
    
End Function

For info

Cheers,
 
Perhaps....

Code: 
Public Function varSystoPid(varSysID As variant) as variant
Dim dbs As Database, qdf As QueryDef, rst As Recordset, strSQL As String, lngSysId as Long


if isnull(varSysID) then
   varSystoPid = null
else
   lngSysId = clng(varSysID)
   
   'Have you considered using dLookup here ?

   Set dbs = CurrentDb
   strSQL = "SELECT [tbl_CBO-TPO:EmployeeDetail].Pid, [tbl_CBO-TPO:EmployeeDetail].EmployeeID " & _
               "FROM [tbl_CBO-TPO:EmployeeDetail] " & _
               "WHERE ((([tbl_CBO-TPO:EmployeeDetail].EmployeeID)=" & lngSysID & "));"

   Set qdf = dbs.CreateQueryDef("", strSQL)
   Set rst = qdf.OpenRecordset()
   SystoPid = rst!Pid.Value
   Set dbs = Nothing
endif
End Function

(Of course you should also put in some error handling)

Then

Code: 
[frm_UTC1_Subform]![EmployeePid] = varSystoPid([frm_UTC1_Subform]!
[TCUser])
'etc


N.B. Untested code, entered directly into the web-browser; Caveat Emptor !
 
You must log in or register to reply here.

Similar threads

A
Solved Referring to a control on a subform from the parent form.
Replies
3
Views
384
AlliCarr
A
T
Iterating through dates in a For loop
Replies
19
Views
1,458
tcneo
T
A
Loop through current records to find null value in the Subform
Replies
8
Views
1,192
Pat Hartman
P
Z
Looping through form and subform controls
Replies
13
Views
2,122
zeroaccess
Z
A
Using a check box on a bound form to control the NotinList combo box event to take 2 controls from form, inserting into 2 fields in another table
2
Replies
24
Views
2,480
AccessAllAreas
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom