CODEWITHSUNDEEP
ms-accessms-access-2007vba
Arlen Beiler
Arlen Beiler

Reputation: 15866

Adding or changing record in access table

I am making a rather simple inventory tracking database. And I want to retrieve the record by ID, and add or remove the specified number to the amount. If it doesn't exist I want to add it. Is it even possible to do this without binding to a table?

Upvotes: 1

Views: 18245

Answers (2)

HansUp
HansUp

Reputation: 97101

Sounds like you have a candidate ID value. Maybe it's contained in a numeric variable named MyID. And you have another numeric value, MyAmtChange, which is to be added to the value in a field named amount in your table for the row where the ID field value matches MyID.

A complication is there may be no row in your table whose ID value matches MyID. In that case, you need to add a row for it.

If that's correct, INSERT a row for MyID when one doesn't exist. Then you can simply UPDATE the amount in the row which matches MyID.

Dim strInsert As String
Dim strUpdate As String
Dim db As DAO.Database

Set db = CurrentDb
If DCount("*", "YourTableNameHere", "ID = " & MyID) = 0 Then
    strInsert = "INSERT INTO YourTableNameHere (ID)" & vbCrLf & _
        "VALUES (" & MyID & ");"
    Debug.Print strInsert
    db.Execute strInsert, dbFailOnError
End If

strUpdate = "UPDATE YourTableNameHere" & vbCrLf & _
    "SET amount = Nz(amount, 0) + " & MyAmtChange & vbCrLf & _
    "WHERE ID = " & MyID & ";"
Debug.Print strUpdate
db.Execute strUpdate, dbFailOnError
Set db = Nothing

If this guess is reasonably close, add an error handler block to that code to deal with any issues surfaced by dbFailOnError ... or any other errors.

Upvotes: 1

Ould Abba
Ould Abba

Reputation: 843

I don't know what do you want exactly, but this code show how to manipulate data with VB-Access. 

Sub fnStudent()
    On Error GoTo insertError
    Dim studentQuery As String
    'INSERTING INTO TABLE
        studentQuery = "INSERT INTO Students values ('10','YAHYA','02/10/2012')"
        CurrentDb.Execute studentQuery, dbFailOnError
    'UPDATING
        studentQuery = "UPDATE Students Set name='YAHYA OULD ABBA' WHERE stdID='10'"
        CurrentDb.Execute studentQuery, dbFailOnError
    'LISTING VALUES
    Dim studentsRS As Recordset
    Set studentsRS = CurrentDb.OpenRecordset("SELECT * FROM Students WHERE upper(name) like '%YAHYA%';")
    Do While Not studentsRS.EOF
        MsgBox "ID : " & studentsRS.Fields(0) & "Name : " & studentsRS.Fields(1) & "Birth Date : " & studentsRS.Fields(2)
        studentsRS.MoveNext
    Loop
    'DELETING
    studentQuery = "DELETE FROM Students WHERE stdID='10'"
    CurrentDb.Execute studentQuery, dbFailOnError
    Exit Sub 'exit if there was no error
    'UPDATE:
errorHandler:
    If Err.Number = 3022 Then
            MsgBox "Can't have duplicate key; index changes were unsuccessful",     vbMsgBoxRtlReading + vbCritical, "Error " & Err.Number
    Else : MsgBox "Error" & vbCrLf & Err.Description, vbMsgBoxRtlReading + vbCritical, "Error " & Err.Number
    End If
End Sub

here you find a list of vba errors http://www.halfile.com/vb.html

Upvotes: 1

Related Questions