All,

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


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 ?

Is SystoPid a function you have written ? Why not incorporate the null check into that function ?

the SystoPid is a Function I had help with :)


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?

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?

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:


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:


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....


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


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



N.B. Untested code, entered directly into the web-browser; Caveat Emptor !