Help to get syntax correct 1

[Moss100]

Hello, spent too many hours at the screen today.

I wonder if someone could help.

I have the line:

CurrentDb.Execute "Update [tbl_Invoice] SET [Inv_Scan_FilestoreID] = '" & Me.[txt_Gemini_Prop_Deduct_Lan_Ref] & "' WHERE Inv_ID = " & Me.txt_100

I want to replace the part where I refer to the textbox: Me.[txt_Gemini_Prop_Deduct_Lan_Ref] with a Variable I have defined as: fileStoreID

I'd much appreciate if someone could provide the correct line. Thank you very much Mark

 

[FancyPrairie]

It's been quite a few years since I messed with Access. If fileStoreID is a number, then you don't need the single quotes around it. If you haven't looked at your syntax using debug, then I suggest you try it.

Modify your code to look like this:
Dim strUp as string
Dim fileStoreID as long

stop
fileStoreID = 123
strUp = "Update [tbl_Invoice] SET [Inv_Scan_FilestoreID] = " & fileStoreID & " WHERE Inv_ID = " & Me.txt_100

When your code executes the stop statement, the code will stop at that statement and the debug Immediate window will appear. Press the F8 button to step through your code 1 line at a time. When you step pass the strUp=... line, type ?strUp in debug's Immediate window. It will display the contents of strUp. This is a simple way to check your syntax. Note, press F5 to execute your code to the end or to the next stop statement.

 

[Moss100]

Mnay thanks Mark

 

[Andrzejek]

Over all, it is a good idea (IMHO) to assign your statement to a variable before you execute it, so you can see what you are doing.

[blue]Dim strUp as string[/blue]
Dim fileStoreID as long

stop
fileStoreID = 123
[blue]strUp[/blue] = "Update [tbl_Invoice] SET [Inv_Scan_FilestoreID] = " & fileStoreID & " WHERE Inv_ID = " & Me.txt_100
Debug.Print [blue]strUp[/blue]
CurrentDb.Execute [blue]strUp[/blue]

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Andy,
I whole heartedly agree. Plus, I wouldn't settle for txt_100. I couldn't sleep at night.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

On one hand we have nice, fully descriptive [tt]txt_Gemini_Prop_Deduct_Lan_Ref[/tt] A little long for my teste, but nothing that Ctrl-Space wouldn't fix (read: type), and on the other hand we have cryptic [tt]txt_100[/tt] :-(


---- Andy

There is a great need for a sarcasm font.