Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Records (Access 2003)

    I have a form that has a command button; when I click on the button I want the table to be field in the table to be updated. Currently when I click the button only one record get updated on the table. How do I get the whole table to be looped?

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Update Records (Access 2003)

    Sound like you need to get the button to run an update wuery to me.

    BUT., I reckon you need to explain this in a bit more detail.

    Do you want a field to be updated to the same value throughout the entire table.
    Where does the new value come from.

    Is the table the recordsource of the form?

    etc
    Andrew

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Records (Access 2003)

    The following code is on the 'On Click' Event


    If [ReportingUnit] > "009" And [ReportingUnit] < "045" Then
    [Org] = "EXEC"
    [Office] = "EXECUTIVE"
    ElseIf [ReportingUnit] > "060" And [ReportingUnit] < "078" Then
    [Org] = "ADMN"
    [Office] = "ADMINISTRATION"
    ElseIf [ReportingUnit] > "080" And [ReportingUnit] < "082" Then
    [Org] = "MRKT"
    [Office] = "MARKETING"
    ElseIf [ReportingUnit] > "083" And [ReportingUnit] < "085" Then
    [Org] = "EXEC"
    [Office] = "EXECUTIVE"
    ElseIf [ReportingUnit] > "090" And [ReportingUnit] < "098" Then
    [Org] = "PAED"
    [Office] = "PLNG ACQ ENVIR DESIGN"
    ElseIf [ReportingUnit] > "500" And [ReportingUnit] < "559" Then
    [Org] = "OHMV"
    [Office] = "OFF HWY MOTOR VEHICLE"
    ElseIf [ReportingUnit] > "629" And [ReportingUnit] < "712" Then
    [Org] = "NORT"
    [Office] = "NORTHERN"
    Else
    [Org] = "UNDF"
    [Office] = "OTHERS"
    End If

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Update Records (Access 2003)

    Not sure if this is what you are after, but something like this ought to work assuming you want to run this
    against EVERY record in the forms recordset

    Dim rst as DAO.recordset

    set rst=Me.RecordSetClone
    If not rst.RecordCount=0 then
    rst.Movefirst
    Do Until rst.EOF
    If rst("ReportingUnit") > "009" And rst("ReportingUnit") < "045" Then
    rst("Org") = "EXEC"
    rst("Office") = "EXECUTIVE"
    ElseIf rst("ReportingUnit") > "060" And rst("ReportingUnit") < "078" Then
    rst("Org") = "ADMN"
    rst("Office") = "ADMINISTRATION"
    ElseIf rst("ReportingUnit") > "080" And rst("ReportingUnit") < "082" Then
    rst("Org") = "MRKT"
    rst("Office") = "MARKETING"
    ElseIf rst("ReportingUnit") > "083" And rst("ReportingUnit") < "085" Then
    rst("Org") = "EXEC"
    rst("Office") = "EXECUTIVE"
    ElseIf rst("ReportingUnit") > "090" And rst("ReportingUnit") < "098" Then
    rst("Org") = "PAED"
    rst("Office") = "PLNG ACQ ENVIR DESIGN"
    ElseIf rst("ReportingUnit") > "500" And rst("ReportingUnit") < "559" Then
    rst("Org") = "OHMV"
    rst("Office") = "OFF HWY MOTOR VEHICLE"
    ElseIf rst("ReportingUnit") > "629" And rst("ReportingUnit") < "712" Then
    rst("Org") = "NORT"
    rst("Office") = "NORTHERN"
    Else
    rst("Org") = "UNDF"
    rst("Office") = "OTHERS"
    End If
    rst..MoveNext
    Loop
    Endif
    rst.close
    set rst=Nothing

    Obvously you will need to check it out against you data
    Andrew

  5. #5
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Records (Access 2003)

    Got a error;
    Update or CancelUpdate without AddNew or Edit. (Error 3020)
    On a Microsoft Jet database, you called the Update or CancelUpdate method but did not use the AddNew or Edit method before writing data to a record.
    On an ODBCDirect database, this error occurs when you attempt to write data to a record without first calling AddNew or Edit.

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Update Records (Access 2003)

    OOPS Sorry, I forgot a couple of lines

    Just After the Start of the Loop you need

    Do Until rst.EOF

    rst.Edit

    If rst("ReportingUnit") > "009" And rst("ReportingUnit") < "045" Then

    Then at the other end of the loop

    Just before the rst.MoveNext line you need

    End If

    rst.Update

    rst..MoveNext
    Loop


    Apologies, I just forgot to put them in.
    Andrew

Posting Permissions

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