Results 1 to 4 of 4

Thread: How do I ...

  1. #1
    Join Date
    Sep 2006

    Red face Unanswered: How do I ...

    how do I use unbound controls on a form to update a record in a table.

    this is what have I have so far but it only seems to update the first record in the table.



    Dim db As Database, rst1 As Recordset
    Dim rst2 As Recordset
    Set db = CurrentDb
    Set rst1 = db.OpenRecordset("Select * from tbldesign")
    rst1.Filter = vardesign_num
    Set rst2 = rst1.OpenRecordset

    With rst2
    !design_name = Me.design_name.Value
    !size = Me.size.Value
    !rack = Me.rack.Value
    !pocket = Me.pocket.Value
    !pocket_ref = Me.pocket_ref.Value
    !updates = Me.updates.Value
    End With

    Set rst2 = Nothing: Set rst1 = Nothing
    Set db = Nothing

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    i can't help you with your filter since i dont use them.
    i'm confused by your need for two rst so i can't help there either

    anyhow - my approach would be to forget the rst and use something like:

    dim strSQL as string
    strSQL = "UPDATE tbldesign SET" _
    & " name = '" & me.design_name _
    & "', size = " & me.size _
    & ", updates = " & me.updates _
    & " WHERE SomeFieldInYourTable = " & vardesign_num & ";"
    currentdb.execute strSQL

    keep control over your spaces (excess are ignored, one missing is fatal)
    ...text with ' as in name above (make sure you noticed both of the ')
    ...dates with # (and in US format)
    ...numbers with nothing as in size, updates, vardesign_num above

    Dim db As Database, rst1 As Recordset
    Dim rst2 As Recordset
    is unsafe from Access-2000 onwards. if you still want to go the rst route, use:
    Dim db As DAO.Database, rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset

    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2006
    Thanks Izy
    I will give it a go !

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    If the code is updating the first record only, I'm guessing you don't have a RecordID (autonumber)/Primary Key or some field to uniquely identify which record you are on or are not using it in your select statement. Usually in your "Select * tbldesign" would be replaced by something like "Select * from MyTable where RecID = " & Forms!MyForm!RecID & "". Every table should normally have a field which uniquely designates that record (usually an autonumber field as numbers work quickly to parse through and are convenient for quick returns when utilizing relationships on multiple tables).
    Last edited by pkstormy; 09-12-06 at 16:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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