Update Null Fields with SQL in MS Access

Question

I'm trying to run some SQL commands in Access VBA to update the blank (null) fields in a column with the value obtained from a combobox in the form.

At the moment I'm receiving

Run time Error '3061' Too Few Parameters. Expected 1

but it appears to be properly formed.

The code I'm using is below. User will be passed as a string, e.g. - "Joe Bloggs".

Public Sub testSub(user as string)
Dim db as DAO.Database
Dim sqlstr as String
set db as CurrentDB

sqlstr = "UPDATE tTable1 SET Field1 = [" & user & "] WHERE Field1 IS NULL;"

db.Execute sqlstr

End Sub

Why those square brackets? Doesn't that imply a column name in this case? — jarlh
– jarlh, Commented Apr 6, 2016 at 14:30

Community · Accepted Answer · 2017-05-23 12:07:36Z

3

Consider a parameter query instead of concatenating a quoted string value into your UPDATE statement text. You don't need those quotes when using the parameter approach, and that also safeguards you from the issue Alex warned about (when the user string itself contains an apostrophe).

Public Sub testSub(ByVal pUser As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim sqlstr As String

    sqlstr = "UPDATE tTable1 SET Field1 = [which_user] WHERE Field1 IS NULL;"
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(vbNullString, sqlstr)
    qdf.Parameters("which_user").Value = pUser
    qdf.Execute sqlstr, dbFailOnError
End Sub

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Apr 6, 2016 at 14:53

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LiamH · Accepted Answer · 2016-04-06 14:33:12Z

1

I think you need to have user as a 'string' in your query using single quotes. Also, I have always used set db = currentdb instead of using as

Public Sub testSub(user as string)
Dim db as DAO.Database
Dim sqlstr as String
set db = CurrentDB

sqlstr = "UPDATE tTable1 SET Field1 = '" & user & "' WHERE Field1 IS NULL;"

db.Execute sqlstr

End Sub

EDIT: As @jarlh has mentioned I agree, I don't think that the square parentheses are required

edited Apr 6, 2016 at 14:33

answered Apr 6, 2016 at 14:23

LiamH

1,5123 gold badges22 silver badges35 bronze badges

Collectives™ on Stack Overflow

Update Null Fields with SQL in MS Access

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related