CODEWITHSUNDEEP
vbams-access
Leon
Leon

Reputation: 69

How to search if certain field of table already contain specific value using VBA in ms access

I would like to do something like,

if field table "name" of table "listuser" already contain "john",
   update the "id" field to 1234.
else - create a new record with name "john" 
   and update the id field to 1234.

how to write the "field table "name" of table "listuser" already contain "john"" part in vba?

Upvotes: 0

Views: 322

Answers (1)

Brad
Brad

Reputation: 12245

A combo of VBA and SQL seems like it can handle this

If we define two queries we can use them in VBA

UpdateListUser:

PARAMETERS newId Long, userName Text ( 255 );
UPDATE listuser SET listuser.id = iif(newId is null, listuser.id, newId)
WHERE (((listuser.[name])=userName));

and InsertNewUser:

PARAMETERS newId Long, userName Text ( 255 );
INSERT INTO Listuser ( id, name )
VALUES (newId, username);

You would make and save these using the Create > Query Design > SQL View window in the regular Access interface.

Then to use them in VBA you can have a function like this

Dim id As Integer, username As String
id = 1234
username = "John"

Dim cmd As New DAO.QueryDef
Dim db As DAO.Database
Set db = CurrentDb

Dim updListUser As DAO.QueryDef
Set updListUser = db.QueryDefs("UpdateListUser")
updListUser.Parameters("newId") = id
updListUser.Parameters("UserName") = username
updListUser.Execute

Debug.Print updListUser.RecordsAffected

If updListUser.RecordsAffected = 0 Then
    Dim insertNewUser As DAO.QueryDef
    Set insertNewUser = db.QueryDefs("InsertNewUser")
    insertNewUser.Parameters("newId") = id
    insertNewUser.Parameters("UserName") = username
    insertNewUser.Execute
End If

Upvotes: 1

Related Questions