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

    Module problem (2003)

    I have been asked to amend a database that was written a few years ago, and the code below is contained in a module

    Public Sub FillInFields(strCastNo As String, _
    Optional intIndex As Integer = 1)
    On Error GoTo err_FillInFields

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

    Set db = CurrentDb
    strSQL = "SELECT CAST_No, ALLOY_MASTER_MELT_No, CHEM_ANALYSIS_REPORT, MECH_TEST_REPORT_No " & _
    " FROM CertNos WHERE CAST_No = '" & strCastNo & "'"
    Set rs = db.OpenRecordset(strSQL)
    If Not rs.BOF Then
    ' fill in fields on form
    Select Case intIndex
    Case 1
    Forms!Certificate_of_Conformity.[Cast No 1] = rs("Cast_No")
    Forms!Certificate_of_Conformity.[Alloy Master Melt No 1] = rs("ALLOY_MASTER_MELT_No")
    Forms!Certificate_of_Conformity.[Chemical Analysis Report No 1] = rs("CHEM_ANALYSIS_REPORT")
    Forms!Certificate_of_Conformity.[Mech Test Report No 1] = rs("MECH_TEST_REPORT_No")
    Case 2
    Forms!Certificate_of_Conformity.[Cast No 2] = rs("Cast_No")
    Forms!Certificate_of_Conformity.[Alloy Master Melt No 2] = rs("ALLOY_MASTER_MELT_No")
    Forms!Certificate_of_Conformity.[Chemical Analysis Report No 2] = rs("CHEM_ANALYSIS_REPORT")
    Forms!Certificate_of_Conformity.[Mech Test Report No 2] = rs("MECH_TEST_REPORT_No")
    Case 3
    Forms!Certificate_of_Conformity.[Cast No 3] = rs("Cast_No")
    Forms!Certificate_of_Conformity.[Alloy Master Melt No 3] = rs("ALLOY_MASTER_MELT_No")
    Forms!Certificate_of_Conformity.[Chemical Analysis Report No 3] = rs("CHEM_ANALYSIS_REPORT")
    Forms!Certificate_of_Conformity.[Mech Test Report No 3] = rs("MECH_TEST_REPORT_No")
    Case 4
    Forms!Certificate_of_Conformity.[Cast No 4] = rs("Cast_No")
    Forms!Certificate_of_Conformity.[Alloy Master Melt No 4] = rs("ALLOY_MASTER_MELT_No")
    Forms!Certificate_of_Conformity.[Chemical Analysis Rep No 4] = rs("CHEM_ANALYSIS_REPORT")
    Forms!Certificate_of_Conformity.[Mech Test Report No 4] = rs("MECH_TEST_REPORT_No")
    Case Else
    MsgBox "You have not entered a valid index number."
    End Select
    Else
    ' insert new data into CertNos table
    intResponse = MsgBox("Do you wish to add Cast No " & strCastNo & " to the table Cert Nos?", vbOKCancel)
    If intResponse = vbOK Then
    MsgBox "Please enter values for the next three fields and click the 'Add' button."
    Forms!Certificate_of_Conformity.cmdAdd.Visible = True
    End If
    End If
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

    exit_FillInFields:
    Exit Sub

    err_FillInFields:
    MsgBox Err.Description
    Resume exit_FillInFields

    End Sub

    It fills in 3 fields on 4 rows when a cast number is entered.
    This has worked fine with no problems. I've been asked to change it so that it only fills the other fields in IF a field elsewhere on the form begins with '5D', but I'm not sure where to start.

    Regards,

    Rob

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

    Re: Module problem (2003)

    Rob,

    A phrase like "a field elsewhere on the form" is rather vague. Let's say that it is named "MyField".

    Between the lines starting with Dim and the line Set db = CurrentDb, insert the following code:

    If Not Left(Forms!Certificate_of_Conformity.[MyField], 2) = "5D" Then
    Exit Sub
    End If

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

    Re: Module problem (2003)

    I'm getting an error message 'Microsoft Office Access can't find the field 'l' referred to in your expression', but I don't know which expression it's referring to.

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

    Re: Module problem (2003)

    You are supposed to replace MyField in the code I posted with the name of the text box/field you need to check.

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

    Re: Module problem (2003)

    What gives you the impression I didn't?

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

    Re: Module problem (2003)

    Just trying to eliminate possible causes...

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

    Re: Module problem (2003)

    The field [My Field], is in a Subform. Can I still use this as a criteria.

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

    Re: Module problem (2003)

    You'll have to use something like

    If Not Left(Forms!Certificate_of_Conformity![NameOfSubform]![MyField], 2) = "5D" Then

    where NameOfSubform is the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window. Open the main form in design view and click once on the subform to see its name in the Properties window.

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

    Re: Module problem (2003)

    Thanks Hans. That's done the trick.

Posting Permissions

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