Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Mount Wolf, PA

    Arrow Unanswered: Find and Replace

    I am trying to update the field FullName in tbl_Input based on the criteria selected on frm_1WorkEntry, which matches what is in the table. Data is entered into this table from this form in bulk. The work is later assigned to the staff. Work is assigned to staff (cboFullName) based on the JobCode and the check values on the form that match what is in the table.

    If I want to assign all work that is Misc and is a rush and a project to John Doe, I choose John Doe's name, choose the Misc job code, click rush, click project and press the command button. Although what is chosen on the form matches what is in the table no records are found. I have tried many ways to manipulate this code and settled on submitting the following:

    Dim db As Database, RS As Recordset
    Dim JobCode As String, EmpName As String, Premier As Boolean, Rush As Boolean
    Dim Canada As Boolean, Project As Boolean, Reject As Boolean

    Set db = CurrentDb()
    Set RS = db.OpenRecordset("tbl_Input", dbOpenDynaset)

    ' Set the focus of the cursor away from tbl_Input
    ' Error message if trying to update table with no records.
    If RS.RecordCount = 0 Then
    MsgBox "There are no records to update.", vbCritical, "No Updateable Records"
    Exit Sub
    End If

    ' Verify name has value.
    If IsNull(cboFullName.Value) Then
    MsgBox "Please choose a name.", vbCritical, "Choose Name"
    GoTo Exit_cmdJobCodeName_Click:
    End If

    ' Verify job code value.
    If IsNull(cboJobCode.Value) Then
    MsgBox "To update rows without a name, you must choose a job code from the drop down menu which matches the table below.", vbCritical, "Choose Name"
    GoTo Exit_cmdJobCodeName_Click:
    End If

    JobCode = cboJobCode.Value
    Premier = chkPremier.Value
    Rush = chkRush.Value
    Canada = chkCanada.Value
    Project = chkProject.Value
    Reject = chkReject.Value

    RS.FindFirst "JobCode " & "='" & JobCode & "' " & RS("Premier").Value = Premier And RS("Rush").Value = Rush And RS("Canada").Value = Canada And RS("Reject").Value = Reject And RS("Project").Value = Project

    If RS.NoMatch = False Then
    Do While Not RS.EOF = True
    RS("FullName").Value = cboFullName.Value
    MsgBox "No records found using this job code.", vbInformation, "No Match"
    End If

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    could you not get there more directly with something like

    dim strSQL as string
    dim lngRaf as long

    strSQL = "UPDATE tbl_Input SET FullName = '" & cboFullName & "' WHERE" _
    & " JobCode = '" & cboJobCode & "'" _
    & " And Premier = " & chkPremier _
    & " And Rush = " & chkRush _ ....and so on for the other chks

    currentdb.execute strSQL
    lngRaf = currentdb.recordsaffected
    if lngRaf = 0 then
    msgbox "Nothing happened"
    msgbox lngRaf & " records were updated"


    LATER - sorry, forgot the Ands
    LATER STILL - sorry again, added one too many And
    Last edited by izyrider; 03-21-06 at 12:27.
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2006
    Mount Wolf, PA

    Thanks for the help. It now works!

Posting Permissions

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