Hi all,
I’m looking for help to correct a clumsy VB code written by me.
I have a Form named "4_PL WS2 FRM" containing 20 Batch No. from 21 to 40 viz. field [BatchNo21]… [BatchNo40].
"4_PL WS2 FRM" is a worksheet to update the Batch No. of other 2 forms, "4_PL P2P2 FRM" and "4_PL P3P3 FRM" which I wish to open to further edit basing on the value of the Batch No fields from the worksheet.
I created a Command Button named “Upto40” to open either "4_PL P2P2 FRM" or "4_PL P3P3 FRM". The conditions were :
1) If value of Batch No. = 21 to 26, then open "4_PL P2P2 FRM" and close "4_PL WS2 FRM"
2) If value of Batch No. = 27 to 40, open "4_PL P3P3 FRM" and close "4_PL WS2 FRM"
Below is the clumsy code by attempting to recognise which Form to open if [BatchNo27] to [BatchNo40] is not Null. To my frustration, the outcome always open up "4_PL P3P3 FRM" regardless whether the field is Null or not Null. Grateful if someone can help to simplify & modify the code. Thanks a lot !
==================================================
Private Sub PL_Upto40_Click()
On Error GoTo Err_PL_Upto40_Click
'* SAVE CURRENT RECORD
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim stDocName1 As String
Dim stDocName2 As String
Dim stLinkCriteria As String
stDocName1 = "4_PL P2P2 FRM"
stDocName2 = "4_PL P3P3 FRM"
stLinkCriteria = "[SalesID]=" & Me![SalesID]
If Not IsNull(Me![BatchNo27]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo28]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo29]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo30]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo31]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo32]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
If Not IsNull(Me![BatchNo33]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo34]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo35]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo36]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo37]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo38]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo39]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
ElseIf Not IsNull(Me![BatchNo40]) Then
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName2, , , stLinkCriteria
End If
Else
'* CLOSE FORM
DoCmd.Close acForm, "4_PL WS2 FRM", acSaveYes
'* OPEN FORM
DoCmd.OpenForm stDocName1, , , stLinkCriteria
End If
Exit_PL_Upto40_Click:
Exit Sub
Err_PL_Upto40_Click:
MsgBox Err.Description
Resume Exit_PL_Upto40_Click
End Sub