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

    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
    cboFullName.SetFocus
    ' 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"
    cboFullName.SetFocus
    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"
    cboJobCode.SetFocus
    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.Edit
    RS("FullName").Value = cboFullName.Value
    RS.Update
    RS.MoveNext
    Loop
    Else
    MsgBox "No records found using this job code.", vbInformation, "No Match"
    End If
    RS.Close

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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"
    else
    msgbox lngRaf & " records were updated"
    endif

    izy


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

  3. #3
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21
    Izy,

    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
  •