Results 1 to 2 of 2
  1. #1
    gykiang is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2013
    Posts
    47

    VB Code not working

    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

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What type of value (other than Null) might one find in the BatchNo27 field?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Code not working
    By jj1 in forum Access
    Replies: 1
    Last Post: 12-22-2014, 01:38 PM
  2. VBA Code not working
    By data808 in forum Forms
    Replies: 31
    Last Post: 02-06-2014, 08:55 AM
  3. Replies: 2
    Last Post: 06-28-2013, 12:58 PM
  4. VB code not working
    By cwwaicw311 in forum Programming
    Replies: 17
    Last Post: 04-26-2010, 07:02 PM
  5. VBA Code for working with IE
    By smikkelsen in forum Programming
    Replies: 3
    Last Post: 04-15-2010, 01:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums