Noob help with executing SQL UPDATE

[Glo0]

Hi

Im new to access, made the plunge straight from php to oh what does that button do in access. ive figured out most of what i want to do but im stuck with how to run a sql update query from a buton click

I have no back ground and VBA but intend to start learning after i have this little project finished. the sql query i wish to run is

Update PaperAccounts
SET InvoiceNumber = (text box in form called txtBox1)
WHERE AccountNumber = (text box in form called txtBox2) AND InvoiceStatus = 0

The form the button is on is called "PaperAccountsInvoiceDetails"

I have tried running this code using the vba builder on the button click action but nothing....

From what i have read online i need some other code around the sql command.

If anyone could help me with this i would be most greatful and can jump into vba knowing something atleast.

Thanks in advance

Glynn

 

[JANR]

Refrencing controls on forms use this syntax:

Forms!yourformname!txtBox1

JR

 

[Glo0]

Thanks thats answered one part. But if i put

Private Sub Command61_Click()
Update PaperAccounts
Set InvoiceNumber = Forms!PaperAccountsInvoiceDetails!txtBox1
WHERE AccountNumber = Forms!PaperAccountsInvoiceDetails!txtBox2 AND InvoiceStatus = 0

End Sub

I get no change in my table.

I dont need to connect to the database do i or anything like that before i can run the UPDATE query.

I have even tried running just

Update PaperAccounts
Set InvoiceNumber = 1

and again nothing.

My WHERE argument is bigger aswell but aslong as i can use 2 vars i can use 3(or more)

{ also can anyone suggest any good vba books or sites }

 

[JANR]

Private Sub Command61_Click()
Dim strsql As String
 
    strsql = "Update PaperAccounts" & _
     "Set InvoiceNumber = Forms!PaperAccountsInvoiceDetails!txtBox1" & _
     "WHERE AccountNumber = Forms!PaperAccountsInvoiceDetails!txtBox2 AND InvoiceStatus = 0;"
    DoCmd.RunSQL strsql
End Sub

JR

edit: This is 1 way to do it using code, but if you are unsure of the syntax try and use the querybuilder to make your updateQuery and run the query using OpenQuery via the clickevent or a macro.

 

[Glo0]

Hey tried that and still i get nothing removed the 2 where clause and tried

Private Sub Command61_Click()
Dim strsql As String

strsql = "Update PaperAccounts" & _
"Set InvoiceNumber = Forms!PaperAccountsInvoiceDetails!txtBox1" & _
"WHERE AccountNumber = Forms!PaperAccountsInvoiceDetails!txtBox2"
DoCmd.RunSQL strsql
End Sub


But still nothing. im guessing theres something im missing. will go back to trying the querybuilder.

Thanks JANR for your help any tips on doing this in querybuilder ive been playing with that but to no avail

Thanks again

 

[MSAccessRookie]

Hey tried that and still i get nothing removed the 2 where clause and tried

Private Sub Command61_Click()
Dim strsql As String

strsql = "Update PaperAccounts" & _
"Set InvoiceNumber = Forms!PaperAccountsInvoiceDetails!txtBox1" & _
"WHERE AccountNumber = Forms!PaperAccountsInvoiceDetails!txtBox2"
DoCmd.RunSQL strsql
End Sub


But still nothing. im guessing theres something im missing. will go back to trying the querybuilder.

Thanks JANR for your help any tips on doing this in querybuilder ive been playing with that but to no avail

Thanks again

You are headed in the right direction, but there is one thing that you need to remember. JET does know how to parse VB Variables and Form Fields, so you have to include the VALUE to the string, not the Name.

Private Sub Command61_Click()
Dim strsql As String
 
strsql = "Update PaperAccounts" & _
"Set InvoiceNumber = [B][COLOR=red]'[/COLOR][COLOR=#ff0000]" & [/COLOR][/B]Forms!PaperAccountsInvoiceDetails!txtBox1 [COLOR=red][B]& "'[/B][/COLOR]" & _
"WHERE AccountNumber = [B][COLOR=#ff0000]'" & [/COLOR][/B]Forms!PaperAccountsInvoiceDetails!txtBox2[B][COLOR=red] & '[/COLOR][/B]"
DoCmd.RunSQL strsql
End Sub

Note that the above code assumes that the Form Fields are Text Fields. If either (or both) of the Fields are Number, then you will need to remove the surrounding "'" characters

 

[Glo0]

Right i think i have found out why this isnt working for me....

I have tried the final suggestion, thanks MSAccessRookie and it works if i try it on a new fresh database that has only 1 table and 1 form. But if i use it on my current project that has 10 tables nothing happens. I dont even get any runtiime errors which i did using the fresh DB.

Not sure if i done something to the settings to cause this problem the VBA wont debug either, well it didnt pick up the same errors the same code did in the frsh DB.

So im starting a fresh armed with what ive picked up along the way. Im very greatful for your help and you will no doubt be hearing more from me in the future in here.