Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Record update with VBA code

Discussion in 'Information Technology' started by Mike W, Nov 3, 2005.

  1. Mike W

    Mike W
    Expand Collapse
    Guest

    I am trying to update records with the following code and continue to have
    problems getting this to work. I'd appreciate ideas on my syntax / command
    problems.

    Set rst = CurrentDb.OpenRecordset("CRData")

    rst.Index = "CRNumber"
    rst.Seek "=", rptSN
    rst.Edit
    rst.Fields("PartNumber") = rptPartNum
    rst.Update

    rst.Close
    Set rst = Nothing


    Thanks,
    Mike
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Why not simply:

    strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "' " & _
    "WHERE CRNumber = " & rptSN

    CurrentDb.Execute strSQL, dbFailOnError

    This assumes that PartNumber is a text field and that CRNumber is numeric.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Mike W" <MikeW@discussions.microsoft.com> wrote in message
    news:DB9E70B0-56EF-430A-99B4-DF8C3D300832@microsoft.com...
    > I am trying to update records with the following code and continue to have
    > problems getting this to work. I'd appreciate ideas on my syntax /

    command
    > problems.
    >
    > Set rst = CurrentDb.OpenRecordset("CRData")
    >
    > rst.Index = "CRNumber"
    > rst.Seek "=", rptSN
    > rst.Edit
    > rst.Fields("PartNumber") = rptPartNum
    > rst.Update
    >
    > rst.Close
    > Set rst = Nothing
    >
    >
    > Thanks,
    > Mike
     
  4. Mike W

    Mike W
    Expand Collapse
    Guest

    Would this work for a large number of other data? The PartNumber item is only
    one of list of items I need to update based on the CRNumber selected.

    In this application, I am creating a record of a customer repair in the
    CRData table. Based upon the "Type" of unit returned, I want to lookup test
    criteria in a separate table and append it to the appropriate customer repair
    order in the CRData table.

    This is the first time I've tried this sort of data manipulation and it's
    been frustrating. I'm sure there must be a simple way of doing this but I
    have yet to figure it out.

    Thanks
    Mike

    "Douglas J Steele" wrote:

    > Why not simply:
    >
    > strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "' " & _
    > "WHERE CRNumber = " & rptSN
    >
    > CurrentDb.Execute strSQL, dbFailOnError
    >
    > This assumes that PartNumber is a text field and that CRNumber is numeric.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Mike W" <MikeW@discussions.microsoft.com> wrote in message
    > news:DB9E70B0-56EF-430A-99B4-DF8C3D300832@microsoft.com...
    > > I am trying to update records with the following code and continue to have
    > > problems getting this to work. I'd appreciate ideas on my syntax /

    > command
    > > problems.
    > >
    > > Set rst = CurrentDb.OpenRecordset("CRData")
    > >
    > > rst.Index = "CRNumber"
    > > rst.Seek "=", rptSN
    > > rst.Edit
    > > rst.Fields("PartNumber") = rptPartNum
    > > rst.Update
    > >
    > > rst.Close
    > > Set rst = Nothing
    > >
    > >
    > > Thanks,
    > > Mike

    >
    >
    >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "', " & _
    "NumericField = " & lngNumericValue & ", " & _
    "DateField = " & Format(dtmDateValue, "\#mm\/dd\/yyyy\#") & ", " & _
    "Field2 = '" & txtOtherValue & "' " & _
    "WHERE CRNumber = " & rptSN

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Mike W" <MikeW@discussions.microsoft.com> wrote in message
    news:C13AC115-76BA-4BB8-8600-4AECEDD940C5@microsoft.com...
    > Would this work for a large number of other data? The PartNumber item is
    > only
    > one of list of items I need to update based on the CRNumber selected.
    >
    > In this application, I am creating a record of a customer repair in the
    > CRData table. Based upon the "Type" of unit returned, I want to lookup
    > test
    > criteria in a separate table and append it to the appropriate customer
    > repair
    > order in the CRData table.
    >
    > This is the first time I've tried this sort of data manipulation and it's
    > been frustrating. I'm sure there must be a simple way of doing this but I
    > have yet to figure it out.
    >
    > Thanks
    > Mike
    >
    > "Douglas J Steele" wrote:
    >
    >> Why not simply:
    >>
    >> strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "' " & _
    >> "WHERE CRNumber = " & rptSN
    >>
    >> CurrentDb.Execute strSQL, dbFailOnError
    >>
    >> This assumes that PartNumber is a text field and that CRNumber is
    >> numeric.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Mike W" <MikeW@discussions.microsoft.com> wrote in message
    >> news:DB9E70B0-56EF-430A-99B4-DF8C3D300832@microsoft.com...
    >> > I am trying to update records with the following code and continue to
    >> > have
    >> > problems getting this to work. I'd appreciate ideas on my syntax /

    >> command
    >> > problems.
    >> >
    >> > Set rst = CurrentDb.OpenRecordset("CRData")
    >> >
    >> > rst.Index = "CRNumber"
    >> > rst.Seek "=", rptSN
    >> > rst.Edit
    >> > rst.Fields("PartNumber") = rptPartNum
    >> > rst.Update
    >> >
    >> > rst.Close
    >> > Set rst = Nothing
    >> >
    >> >
    >> > Thanks,
    >> > Mike

    >>
    >>
    >>
     
  6. Mike W

    Mike W
    Expand Collapse
    Guest

    I tried a simplified version of your code below and am still not getting the
    update in my table. I'd appreciate more ideas.

    Set rsReport = New ADODB.Recordset

    rptTable = "SELECT * FROM CRData"
    rsReport.Open rptTable, cnCurrent, adOpenDynamic, adLockOptimistic

    strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "' " & "WHERE
    SerialNumber = " & rptSN

    cnCurrent.Execute strSQL

    Thanks,
    Mike


    "Douglas J. Steele" wrote:

    > strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "', " & _
    > "NumericField = " & lngNumericValue & ", " & _
    > "DateField = " & Format(dtmDateValue, "\#mm\/dd\/yyyy\#") & ", " & _
    > "Field2 = '" & txtOtherValue & "' " & _
    > "WHERE CRNumber = " & rptSN
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Mike W" <MikeW@discussions.microsoft.com> wrote in message
    > news:C13AC115-76BA-4BB8-8600-4AECEDD940C5@microsoft.com...
    > > Would this work for a large number of other data? The PartNumber item is
    > > only
    > > one of list of items I need to update based on the CRNumber selected.
    > >
    > > In this application, I am creating a record of a customer repair in the
    > > CRData table. Based upon the "Type" of unit returned, I want to lookup
    > > test
    > > criteria in a separate table and append it to the appropriate customer
    > > repair
    > > order in the CRData table.
    > >
    > > This is the first time I've tried this sort of data manipulation and it's
    > > been frustrating. I'm sure there must be a simple way of doing this but I
    > > have yet to figure it out.
    > >
    > > Thanks
    > > Mike
    > >
    > > "Douglas J Steele" wrote:
    > >
    > >> Why not simply:
    > >>
    > >> strSQL = "UPDATE CRData SET PartNumber = '" & rptPartNum & "' " & _
    > >> "WHERE CRNumber = " & rptSN
    > >>
    > >> CurrentDb.Execute strSQL, dbFailOnError
    > >>
    > >> This assumes that PartNumber is a text field and that CRNumber is
    > >> numeric.
    > >>
    > >> --
    > >> Doug Steele, Microsoft Access MVP
    > >> http://I.Am/DougSteele
    > >> (no e-mails, please!)
    > >>
    > >>
    > >> "Mike W" <MikeW@discussions.microsoft.com> wrote in message
    > >> news:DB9E70B0-56EF-430A-99B4-DF8C3D300832@microsoft.com...
    > >> > I am trying to update records with the following code and continue to
    > >> > have
    > >> > problems getting this to work. I'd appreciate ideas on my syntax /
    > >> command
    > >> > problems.
    > >> >
    > >> > Set rst = CurrentDb.OpenRecordset("CRData")
    > >> >
    > >> > rst.Index = "CRNumber"
    > >> > rst.Seek "=", rptSN
    > >> > rst.Edit
    > >> > rst.Fields("PartNumber") = rptPartNum
    > >> > rst.Update
    > >> >
    > >> > rst.Close
    > >> > Set rst = Nothing
    > >> >
    > >> >
    > >> > Thanks,
    > >> > Mike
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page