Very simple syntax issue (1 Viewer)

Dear Access experts,
excuse me for asking questions about primitive things, but this is really confusing when you only start using VBA.

I'm trying to do something totally simple:

Dim strString As String
strString = "TEXT"
"TABLE.Field1" = strString

I keep getting a syntax error. I guess there is somewhere I didn't put a quote or an ampersand but I cannot figure out where.

Could anyone kindly help me with this case and advise me a good concise guide to VBA syntax?

Thank you.

You can't update a table that way. Options include:

1) the Edit method of a recordset
2) executing an update query
3) changing the value of a textbox on a form bound to the table/field

VBA treats ANYTHING inside double quotes as a string, so what you're telling it to do in that third line is to take a string value, and then assign a variable to that, which can't be done.

You can update a field a few ways in VBA - you can assign a value to a control on a form, you can assign a value to the field directly (as long is you're in a form and the field was passed in the query), you can run an update query, or you can open a recordset and update the value directly.

So for a more useful answer, is this part of a form or a general module you're using?

Edit: Basically what Paul said.

Frothingslosh said:

Edit: Basically what Paul said.

Click to expand...

Fastest fingers in the west.

Fastest fingers in the west.

Click to expand...

So says pbaldy, Frontier Proctologist?

But seriously, Ishim, here is your syntax issue.

ANYTHING in quotes - either " " or ' ' - is a CONSTANT STRING. You cannot change the contents of a constant because... well... it's a CONSTANT.

The advice given earlier is correct. You MUST have a recordset open to update a field value in a table.

Paul's suggestions were:

1) the Edit method of a recordset - which implies that you OPENED a recordset with appropriate syntax e.g.

SET recordset-variable = CurrentDB.OpenRecordset( "recordset-source-name", open-style-parameter ) plus some other parameters on locking and mode of record scanning.

This is a piecemeal method because you must find the desired record to be updated before you use the recordset-variable.Edit syntax to allow you to update the current record in the set.

2) executing an update query - which works because SQL will open a recordset for your based on the table name in the query. This is a WHOLESALE method that potentially could affect the field in every record in the table, depending on filtration query specified in your WHERE clause.

3) changing the value of a textbox on a form bound to the table/field - which works because a bound FORM has an implied recordset that was opened when you opened the form. This is a piecemeal action that can ONLY affect the record currently selected by the form, period. Note further that if the form is unbound, then you cannot use this method at all.

Dear All, I can't even express how I am thankful to you all for your incredible help! That was quite a lot of information for me to digest )))) But I made it! I ran an UPDATE query and it worked! Thank you for giving me this little triumph!