Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Help with Code (2003)

    I have the following code in a database:-

    Public Sub FillInFields2(strCofNo As String, _
    Optional intIndex As Integer = 1)
    On Error GoTo err_FillInFields2

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim intResponse As Integer


    Set db = CurrentDb
    strSQL = "SELECT COF1_code,MOB1_code,orderno,stock,descline1,descli ne2,drawing,name " & _
    " FROM Table1 WHERE COF1_code = '" & strCofNo & "'"
    Set rs = db.OpenRecordset(strSQL)
    If Not rs.BOF Then
    ' fill in fields on form
    Select Case intIndex
    Case 1
    Forms!frmCofCnew![Cof No 1] = rs("COF1_code")
    Forms!frmCofCnew![Mob 1] = rs("MOB1_code")
    Forms!frmCofCnew![Customer Order No 1] = rs("orderno")
    Forms!frmCofCnew![Stock Code No 1] = rs("stock")
    Forms!frmCofCnew![Description 1] = rs("descline1")
    Forms!frmCofCnew![Ext Description 1] = rs("descline2")
    Forms!frmCofCnew![DWG No 1] = rs("drawing")
    Forms!frmCofCnew![Issue 1] = rs("drawing")
    Forms!frmCofCnew![Customer Name] = rs("name")
    Case 2
    Forms!frmCofCnew![Cof No 2] = rs("COF1_code")
    Forms!frmCofCnew![Mob 2] = rs("MOB1_code")
    Forms!frmCofCnew![Customer Order No 2] = rs("orderno")
    Forms!frmCofCnew![Stock Code No 2] = rs("stock")
    Forms!frmCofCnew![Description 2] = rs("descline1")
    Forms!frmCofCnew![Ext Description 2] = rs("descline2")
    Forms!frmCofCnew![DWG No 2] = rs("drawing")
    Forms!frmCofCnew![Issue 2] = rs("drawing")
    Case 3
    Forms!frmCofCnew![Cof No 3] = rs("COF1_code")
    Forms!frmCofCnew![Mob 3] = rs("MOB1_code")
    Forms!frmCofCnew![Customer Order No 3] = rs("orderno")
    Forms!frmCofCnew![Stock Code No 3] = rs("stock")
    Forms!frmCofCnew![Description 3] = rs("descline1")
    Forms!frmCofCnew![Ext Description 3] = rs("descline2")
    Forms!frmCofCnew![DWG No 3] = rs("drawing")
    Forms!frmCofCnew![Issue 3] = rs("drawing")
    Case 4
    Forms!frmCofCnew![Cof No 4] = rs("COF1_code")
    Forms!frmCofCnew![Mob 4] = rs("MOB1_code")
    Forms!frmCofCnew![Customer Order No 4] = rs("orderno")
    Forms!frmCofCnew![Stock Code No 4] = rs("stock")
    Forms!frmCofCnew![Description 4] = rs("descline1")
    Forms!frmCofCnew![Ext Description 4] = rs("descline2")
    Forms!frmCofCnew![DWG No 4] = rs("drawing")
    Forms!frmCofCnew![Issue 4] = rs("drawing")

    Case Else
    MsgBox "You have not entered a valid index number."
    End Select
    Else
    ' insert new data into CertNos table
    intResponse = MsgBox("You must enter a valid Cof Number!", vbExclamation)

    End If

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

    exit_FillInFields2:
    Exit Sub

    err_FillInFields2:
    MsgBox Err.Description
    Resume exit_FillInFields2

    End Sub


    which fills in fields automatically. I have been asked to change it so if Stock Code No 1 begins with 5R, to leave Mob_1 empty, and also repeated for the other stock codes. This is beyond my scope. Can anyone please advice. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Code (2003)

    Try this version, I streamlined it and added code to test whether the stock code begins with 5R:
    <code>
    Public Sub FillInFields2(strCofNo As String, Optional intIndex As Integer = 1)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    On Error GoTo err_FillInFields2
    Set db = CurrentDb
    strSQL = "SELECT COF1_code, MOB1_code, orderno, stock, descline1, descline2, " & _
    "drawing, name FROM Table1 WHERE COF1_code = '" & strCofNo & "'"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rs.BOF Then
    ' fill in fields on form
    Select Case intIndex
    Case 1 To 4
    With Forms!frmCofCnew
    .Controls("Cof No " & intIndex) = rs("COF1_code")
    .Controls("Customer Order No " & intIndex) = rs("orderno")
    .Controls("Stock Code No " & intIndex) = rs("stock")
    If Not rs("stock") Like "5R*" Then
    .Controls("Mob " & intIndex) = rs("MOB1_code")
    End If
    .Controls("Description " & intIndex) = rs("descline1")
    .Controls("Ext Description " & intIndex) = rs("descline2")
    .Controls("DWG No " & intIndex) = rs("drawing")
    .Controls("Issue " & intIndex) = rs("drawing")
    If intIndex = 1 Then
    .Controls("Customer Name") = rs("name")
    End If
    End With
    Case Else
    MsgBox "You have not entered a valid index number."
    End Select
    Else
    MsgBox "You must enter a valid Cof Number!", vbExclamation
    End If

    exit_FillInFields2:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

    err_FillInFields2:
    MsgBox Err.Description
    Resume exit_FillInFields2
    End Sub</code>

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help with Code (2003)

    Thanks Hans. Sorry to be a pain, but I've since been asked, could I also make it for 5D also. Just a guess, but could I add it to the statement you've already created using OR?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Code (2003)

    Yes, you can use

    If Not rs("stock") Like "5R*" Or Not rs("stock") Like "5D*" Then

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help with Code (2003)

    Thanks Hans, that's excellent. Thanks again.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help with Code (2003)

    Spoke too soon. The Mob numbers are still being filled in. Is there something I should look for? I copied the code exactly as you sent it. Could it be the code I have in the AfterUpate event of each COF No. This is:-

    On Error GoTo err_Cof_No_1_AfterUpdate

    f_intIndex = 1
    If Len([Cof No 1].Text) > 0 Then
    Call FillInFields2([Cof No 1].Text, f_intIndex)
    End If

    exit_Cof_No_1_AfterUpdate:
    Exit Sub

    err_Cof_No_1_AfterUpdate:
    MsgBox Err.Description
    Resume exit_Cof_No_1_AfterUpdate
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Code (2003)

    Oops, my bad. It should have been

    If Not rs("stock") Like "5R*" And Not rs("stock") Like "5D*" Then

    Sorry about that!

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help with Code (2003)

    Thanks Hans. Working perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •