Edit data on table with VBA

[XXD]

Hi

I am interested on press of a button to add a value from a textbox to a specific table on a specific column on a specific row. Is this possible?

regards
xxd

 

[WayneRyan]

XXD,

That's a pretty general question, it depends on your datatypes and
how you uniquely identify records in your table:

CurrentDb.Execute "Update YourTable " & _
                  "Set    SomeField = '" & Me.YourTextBox & "' " & _
                  "Where  SomeOtherField = 'SomeValue'"

Wayne

 

[XXD]

Can you explain to me what SomeField, SomeOtherField and SomeValue are?
thanks in advance
xxd

 

[WayneRyan]

XXD,

They're just "guesses". I have no idea what your table is named, or the
columns, or what the primary keys are. The sample is just one command
you can issue to achieve what you want, you'll have to substitute the
names with your actual names ... or provide more words in your original
question.

Secondly, datatypes play a role here, in the example, I'm setting a string
value and need to surround it with the single-quotes. Date values and
numerics are treated differently also.

Wayne

 

[XXD]

Yes I know that they are just examples but of what?
Somefield is A column on my table, not primary?
SomeOtherField is My row?
SomeValue = ?

 

[WayneRyan]

XXD,

You have to supply that information.

Example --> I'd like to change the HourlyRate for Programmer 'Jones' to
$50.00 in my Salary Table.

Update [B]Salary[/B]
Set    [B]HourlyRate [/B]= 50.0
Where  [B]Programmer [/B]= 'Jones'

Wayne

 

[georgedwilkinson]

Why don't you just bind the table to the form and the textbox to the column. Access will take care of the rest.

And I believe Wayne did an excellent job trying to answer your question with no information.

 

[XXD]

So your table looks like this

Table = Salery
Id_|_HourlyRate_|_Programmer
1 50.0 Jones

 

[WayneRyan]

XXD,

Yeah, but what does YOUR table look like?

Also, why aren't you updating the data with a form?

Wayne

 

[XXD]

My table looks like yours. I need to use it like this, I just need to.
I'm getting a syntax error on this code:

CurrentDb.Execute "Update Beställda-Produkter " & _
"Set Enhetspris = '" & Me.Text45 & "' " & _
"Where Produkt nummer = '"text51.Value"' "

 

[georgedwilkinson]

You left out the concatenation symbols in your where clause. Try:

CurrentDb.Execute "Update Beställda-Produkter " & _
"Set Enhetspris = '" & Me.Text45 & "' " & _
"Where Produkt nummer = '" & text51.Value & "';"

 

[WayneRyan]

Darned spaces ...

CurrentDb.Execute "Update Beställda-Produkter " & _
"Set Enhetspris = '" & Me.Text45 & "' " & _
"Where [B][SIZE="3"][[/SIZE][/B]Produkt nummer[B][SIZE="3"]][/SIZE][/B] = '" & text51.Value & "';"

Wayne

 

[XXD]

Now I get syntax error on Update statement :S
Error 3144

edit:
I'm using access 2007 if it is to any help.

 

[WayneRyan]

XXD,

You have to reference it as [Produkt nummer] because it has a space in
the name. It's easier in the long run to just name it ProduktNummer and
avoid that issue.

Wayne

 

[XXD]

I've tried with the [] and Porduktnummer without space but still same error. Can it be something with my settings?

 

[WayneRyan]

XXD,

You can't just change the reference, you'd have to change its name in
your table.

Can you post a sample database?

Wayne

 

[XXD]

I know that I have to change the name table and the on the code but still not working. I have attached a picture showing my table.

When I press the button on my form, lets say the Text51.Value = 7240, then on my table field, "Enhetspris" a value that Text45 have should be added in the "Enhetspris" where "Produktnummer" is 7240

button code:
CurrentDb.Execute "Update Beställda-Produkter" & _
"Set Enhetspris = '" & Text45.Value & "' " & _
"Where Produktnummer = '" & Text51.Value & "';"

thanks for all help Wayne
xxd

 

[Rabbie]

I know that I have to change the name table and the on the code but still not working. I have attached a picture showing my table.

When I press the button on my form, lets say the Text51.Value = 7240, then on my table field, "Enhetspris" a value that Text45 have should be added in the "Enhetspris" where "Produktnummer" is 7240

button code:
CurrentDb.Execute "Update Beställda-Produkter" & _
"Set Enhetspris = '" & Text45.Value & "' " & _
"Where Produktnummer = '" & Text51.Value & "';"

thanks for all help Wayne
xxd

Try changing your code as follows

button code:
dim strQuery as String
strQuery = "Update Beställda-Produkter " & _
"Set Enhetspris = '" & Text45.Value & "' " & _
" Where Produktnummer = '" & Text51.Value & "';"
debug.print strQuery
CurrentDb.Execute strQuery

This will let you see the actual SQL string you are generating so you check it is as expected.

 

[XXD]

error on:
CurrentDb.Execute strQuery

 

[Rabbie]

error on:
CurrentDb.Execute strQuery

Then there is something wrong with strQuery. What was the diagnostic printout of strQuery. You need to give us all the relevant information if you want us to help you