UPDATE code wont work...

[Villarreal68]

Hello Everyone,

I have the following code that I think is good, but does not seem to work:

Dim db As Database
Set db = CurrentDb
db.Execute ("UPDATE PubStatus SET [PubCode]='" & PUB & "' WHERE [PubStatusID]=" & 1 & ";")
db.Execute ("UPDATE PubStatus SET [PubCode]='" & AUX & "' WHERE [PubStatusID]=" & 2 & ";")
db.Execute ("UPDATE PubStatus SET [PubCode]='" & REG & "' WHERE [PubStatusID]=" & 3 & ";")
db.Execute ("UPDATE PubStatus SET [PubCode]='" & ESP & "' WHERE [PubStatusID]=" & 4 & ";")
db.Close
Set db = Nothing

When i click on the button that runs it, nothing happens. I check the table that should get affected, and the updates don't get applied.

Could a new set of eyes check it and see if you can see what is missing or wrong?

The table name is: PubStatus
The field I'm trying to update is: PubCode
The Field I'm calling on is: PubStatusID

I feel it should work but it just doesn't. Any help would be appreciated.

Thanks!

René

 

[pbaldy]

Is PUB a variable somewhere, or a literal string you want the field set to?

 

[ajetrumpet]

the thing to do here Rene, is to write this:

debug.print "UPDATE PubStatus SET [PubCode]='" & PUB & "' WHERE [PubStatusID]=" & 1 & ";"

then check the immediate window to see your statement. I'm guessing you may find the problem if you look at it this way. Also, the syntax that you have used is only good for string variables. Any other types will not comply with the statement, and the syntax will probably be wrong in the end (like Paul kind of said), as the debug statement should tell you.

 

[JANR]

You can try this:

Dim db As Database
Set db = CurrentDb
db.Execute ("UPDATE PubStatus[COLOR=red].PubCode[/COLOR] SET [PubCode]='" & PUB & "' WHERE [PubStatusID]=" & 1 & ";")
db.Execute ("UPDATE PubStatus[COLOR=red].PubCode[/COLOR] SET [PubCode]='" & AUX & "' WHERE [PubStatusID]=" & 2 & ";")
db.Execute ("UPDATE PubStatus[COLOR=red].Pubcode[/COLOR] SET [PubCode]='" & REG & "' WHERE [PubStatusID]=" & 3 & ";")
db.Execute ("UPDATE PubStatus[COLOR=red].[/COLOR][COLOR=red]Pubcode[/COLOR] SET [PubCode]='" & ESP & "' WHERE [PubStatusID]=" & 4 & ";")
db.Close
Set db = Nothing

JR

 

[Villarreal68]

Hello Everyone,

Thank you for responding. Sorry it took so long to respond. i posted yesterday before I went home for the day and I just got in today.


Pbaldy:
Pub is a string. Basically is the abreviated code for that particular record.

ajetrumpet:
Thanks, I'll try that and see what it returns.

JANR:
I'll try the debug.print first and then I will try your code.

Thank you all!

René

 

[pbaldy]

Pbaldy:
Pub is a string. Basically is the abreviated code for that particular record.

If it's just a string, then try this:

db.Execute ("UPDATE PubStatus SET [PubCode]='PUB' WHERE [PubStatusID]=1")

 

[Atomic Shrimp]

Perhaps this is too obvious a suggestion, but if clicking the button is doing nothing at all, it's worth checking that the click event really is pointing at your code. It's possible to right-click and 'Build Event' without realising you've put the code into some event relating to the form, rather than the click event of the button on the form.

Select the button in design mode and follow the link to the code through the event in the properties box - does it go to the expected place?

 

[Villarreal68]

Hello Everyone,

Mike:
Thanks for the suggestion. I did double check and it was pointing to it. i even added a MsgBox to make sure it was working and it was, but any suggestion is appreciated.

Pbaldy:
I tried your suggestion (Today 08:05 AM) and it worked. I had too much junk in my code that I didn't need. Thanks a bunch!

ajetrumpet & JANR:
Thanks for your suggestions. I'll keep them in mind for the next "brain fart" and am clueless again!

Thanks all!

René