Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: changing data in table

    Hi Guys,

    Ive been working with changing data in tables using code. I have gotten far as what is below. so adding new data to a table is ok. my question is, how abotut if i wanted to overwrite some information on a record in a table.
    What would be the best way.

    my code is below

    Code:
    Private Sub cmdAdd_Click()
     
    Dim strMsg As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("User")
        
    If DLookup("ID", "User", "[ID]='" & txtID.Value & "'") = True Then
       strMsg = "Record Exists"
       MsgBox (strMsg)
    Else
        rs.AddNew
        rs![ID] = Me.txtID.Value
        rs![FName] = Me.txtFName.Value
        rs![LName] = Me.txtLName.Value
        rs![PhoneNo] = Me.txtPhone.Value
        rs.Update
        Set rs = Nothing
        Set db = Nothing
        strMsg = "Added Record"
        MsgBox (strMsg)
    End If
    
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    aye yi yi
    way back when I was first introduced into concepts of data modelling I was told about things like naming coinvetnions...
    one fo the those rules was somethign ending in ID, or _ID or called ID should be reserved for numeric Identity columns (ie autonumber)
    something that is string/text/char etc should be called a type/code or so on.

    also you close your recordset (rs) & Db when you find nothign, but leave it open when find something. I'd suggest your db & rs should be closed outside the Dlookup test

    so to yur question
    in the same way that SQL allows you to SELECT records, it also allows you to UPDATE records

    eg UPDATE mytable set myNumericColumn=someValue, MyStringColumn='someText' where aNumericColumn=blah and aStringColumn='blah-di-blah'

    if you build the SQL as a variable
    strSQL = "UPDATE mytable set myNumericColumn=someValue, MyStringColumn='" & "someText" & "' where aNumericColumn=blah and aStringColumn='" & blah-di-blah & "'"
    ...replacing the relevent items such as myTable,MyxxxColumn, axxxxColumn and replacing someValue and someText and blah and blah-di-blah
    you can use values form controls, variables or hardcodeed values.

    you can then EXECUTE that SQL using docmd.RUNSQL (strSQL)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2006
    Posts
    178
    Healdem you VBA "Oracle", as ever thank you for your help.


    aboo

Posting Permissions

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