Solved Syntax Error while updating

[SachAccess]

Hi,
I am getting a Syntax Error while trying to update a table with VBA code.
Please note, I have changed all the names from the below example.

VBA code is getting stuck at below line.
CurrentDb.Execute "Update MyTable set ShortInfo='" & Form_Frm_ABC.Remarks & "', QAZ='" & Form_Frm_ABC.QAZ_ID & "', STATUS=" & Form_Frm_ABC.MyType & " Where MyID=" & Form_Frm_EDC.MyID

Please let me know if you want me to post entire ‘Sub’ here. However, code is getting stuck at this line only.
Can anyone please help me this.
PS - I might revert to this thread on 17-Nov-2022.

Me.Dirty = True
If Form_Frm_ABC.Remarks <> "" Then
MyComm = ""
MyComm = Form_Frm_ABC.Remarks
End If

CurrentDb.Execute "Update MyTable set ShortInfo='" & Form_Frm_ABC.Remarks & "', QAZ='" & Form_Frm_ABC.QAZ_ID & "', STATUS=" & Form_Frm_ABC.MyType & " Where MyID=" & Form_Frm_EDC.MyID

 

[MajP]

dim strSql as string
strSql = "Update MyTable set ShortInfo = '" & Form_Frm_ABC.Remarks & "', QAZ = '" & Form_Frm_ABC.QAZ_ID & "', STATUS = " & Form_Frm_ABC.MyType & " Where MyID = " & Form_Frm_EDC.MyIDd
debug.print strSql
'Post back what is printed
currentdb.execute strSql

I would add some spaces between ='

 

[The_Doc_Man]

Usually the easiest way to handle problems like this is to separately build the string first and then execute it as a distinct step. Since you have a lot of concatenation, you have a lot of opportunity to misplace a quote-mark or apostrophe or something. You do this in two steps because it gives you the opportunity to set a breakpoint on the execution so that you can issue a Debug.Print on the string to see what it is that you are trying to execute. Doing it in-line with the .Execute denies you the ability to double-check that you built the string you intended to build.

 

[MajP]

Also is status numeric?

 

[ebs17]

debug.print strSql
'Post back what is printed

SqlDebugPrint
With this add-in you can speed up and improve the display and checking of the generated SQL statement. In the case of action queries, a transaction ensures that no data in the table is changed as a result of the check process. Use in code after installation:

SqlDebug strSQL

However, one could also use real parameter queries and thus avoid such formatting problems.

 

[CJ_London]

is your form really called

Form_Frm_ABC

suspect this needs to be

Forms!Frm_ABC

or if this is the name then

forms!Form_Frm_ABC

and if the code is in this form, you don't need it at all, just Me.

 

[SachAccess]

Thanks everyone for the help! I will revert in some time. Have a nice day ahead.

 

[SachAccess]

is your form really called

Form_Frm_ABC

suspect this needs to be

Forms!Frm_ABC

or if this is the name then

forms!Form_Frm_ABC

and if the code is in this form, you don't need it at all, just Me.

Hi @CJ_London thanks a lot for the help. I tried changing Form_ to Form!
However, this is giving me different bug. Please see below code if you get time.

Run-time error 2465
My Tool Name can't find the field 'frm_Second' referred to in your expression.
Checked manually, this form is present in the file.

Private Sub Q123_AfterUpdate()
    Me.Dirty = True
    If Me.Q123 <> "" Then
        MyComm = ""
        MyComm = Me.Q123
    End If
    
    CurrentDb.Execute "Update MyTabe set ShortInfo='" & Me.Q123 & "', QAZ='" & Me.QAZ_ID & "', MyStatus=" & Me.WSX & " Where MyID=" & Form!frm_Second.MyID
    
    Forms!frm_MainForm.Requery
    Forms!frm_Second.Requery
    DoCmd.RunCommand acCmdSaveRecord
    Me.Dirty = False
    Forms!frm_Second.Requery
End Sub

 

[ebs17]

Forms!frm_Second.MyID

The form should also be open.

 

[SachAccess]

Forms!frm_Second.MyID

The form should also be open.

Hi @ebs17 thanks for the help. Tried it, not working, now giving error as Syntax Error.
Please give me some time, I am checking all the values in IW and revert with details.
Have a nice day ahead.

 

[ebs17]

now giving but as Syntax Error

Eliminating one bug makes room for the display of the next...

 

[CJ_London]

My Tool Name can't find the field 'frm_Second' referred to in your expression.

you really need to pay attention to the responses - I said 'forms!' you have used 'form!' and perhaps check you're coding before coming back with an error.

If you still get an error, show what you now have, we can only guess that you have corrected it as advised.

And to be clear is frm_second open on it's own or as a subform (perhaps to mainform?)

and what happened to frm_ABC?

 

[SachAccess]

Hi @CJ_London, thanks for the help and sorry for the mistake. Will not repeat. Please give me some time to revert with correct and updated details.

 

[SachAccess]

Hi,
I am aware, I am giving in-complete information most of the times. I will try my level best not to waste experts time on my in-complete posts.
Will definitely try to improve myself going forward. Thanks everyone for helping me each time.
I am not allowed to upload anything and am scared that I should not disclose real name of the objects.
That is why I try to replace all the real names with dummy names, that is where I made mistake of not keeping same dummy names through the thread.


Will try to explain what am trying to achieve. That might be more helpful to understand my issue.
I have an existing MS Access DB. This DB is a Front End. All the tables are linked from the Back End.
On my FE, I have a form.
With this form, am trying to add a new entry in the table.
My Form has multiple Combo Box and few date fields and few free text box.
All the options, dates, free text updated on the form for new entry should replicate on the table as new entry.

I am facing issue here.
I have one form, let us say Form 1.
This form has few options to select.
On clicking one button of Form 1, Form 2 gets opened.
On this Form 2, there is free text box.
For this text box on Form 2, there is a AfterUpdate VBA code linked.
Form 1 is already closed by VBA code.
Now only Form 2 is open.
Part that am not able to understand, if I check on ‘Me.Name’ in IW, it gives me name of another form, let us say Form 3.
This Form 3 is not open at this moment.

And am getting Syntax Error in update statement.
Here, Me.Name = Form 3, this is not Form 1, Form 1 is already closed. This is not Form 2 also.
frm_Second from the code example is Form 2.
frm_Second or Form 2 is open.
AfterUpdate VBA code is linked to Form 2.

Private Sub Q123_AfterUpdate()
    Me.Dirty = True
    If Me.Q123 <> "" Then
        MyComm = ""
        MyComm = Me.Q123
    End If

    CurrentDb.Execute "Update MyTabe set ShortInfo='" & Me.Q123 & "', QAZ='" & Me.QAZ_ID & "', MyStatus=" & Me.WSX & " Where MyID=" & Form!frm_Second.MyID

    Forms!frm_MainForm.Requery
    Forms!frm_Second.Requery
    DoCmd.RunCommand acCmdSaveRecord
    Me.Dirty = False
    Forms!frm_Second.Requery
End Sub

Now, am trying to write a new code in new sub which will update new entry in the table based on the selection done on the form.
This Form 1 and Form 2 combination is confusing for me too.
Trying to club all the options in Form 1 and write in new Sub.
I hope, this approach will work for me.
Thanks once again.

 

[Minty]

Firstly - Me.AlmostAnything in the Immediate window won't work or give you bogus data, as it has no knowledge of the current forms module and controls.

Your code has some very strange methods, I'm pretty sure you can't set Me.Dirty to True?
What do you think that is doing? The forms Dirty property is set to true by the database engine if a field is updated. You can't decide to do that yourself.

It would help enormously if you gave your controls and forms meaningful names. Is MainForm your first form?

Maybe post up a copy of your database with enough dummy data to let someone work out the process and where you are going wrong.
There is nothing in a few forms with some dummy data that is confidential enough to worry anyone here that might look at it.

 

[SachAccess]

Hi @Minty thanks a lot for the help.
Not giving an excuse here, however, am trying to edit the existing code.
Lines like 'set Me.Dirty to True' are written by someone else.
I will try to create something dummy on personal computer and will upload (over weekend).

'meaningful names', sorry for this, was replacing real names with dummy names.
However, will keep this point in mind.

'Now, am trying to write a new code in new sub which will update new entry in the table based on the selection done on the form.'
Will it be better if I do this, rather than editing original code. I guess that would be better for my own understanding too.

Have a nice day ahead.

PS - Will reply on 18-11-2022.

 

[Minty]

Always use the real names for your fields, forms and tables.
Copy and paste the code exactly don't edit it.

We can't spot typo's etc if they are there if it's not the EXACT code you are running.

 

[CJ_London]

I am not allowed to upload anything and am scared that I should not disclose real name of the objects.

Suggest talk to your bosses and clarify, can understand data but cannot see why they would object to object and field names.

The problem is if you have a problem and it is related to spelling or object type and you say you have a problem and give a different spelling - or create typos because you don't copy paste, that just wastes everyone's time because the focus is on that and not the real issue.

The other problem is because you change names - you started with Form_Frm_ABC, then changed to frm_MainForm and then you say things like 'Me.Name = Form 3, this is not Form 1'.

 

[Pat Hartman]

Did you fix the Me.Dirty = True to = False?
What is the purpose of the If that follows? You are checking Me.Q123 and setting MyComm to two different values. Obviously only the second value remains
Why are you storing the same data in multiple tables?

 

[SachAccess]

Suggest talk to your bosses and clarify, can understand data but cannot see why they would object to object and field names.

The problem is if you have a problem and it is related to spelling or object type and you say you have a problem and give a different spelling - or create typos because you don't copy paste, that just wastes everyone's time because the focus is on that and not the real issue.

The other problem is because you change names - you started with Form_Frm_ABC, then changed to frm_MainForm and then you say things like 'Me.Name = Form 3, this is not Form 1'.

Thanks for the help.
Will reply in details tomorrow.
Replying from mobile.

Have a nice day ahead.