Edit data on table with VBA

XXD

Registered User.
Local time
Yesterday, 19:44
Joined
Mar 11, 2008
Messages
68
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
 
XXD,

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

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

Wayne
 
Can you explain to me what SomeField, SomeOtherField and SomeValue are?
thanks in advance
xxd
 
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
 
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 = ?
 
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.

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

Wayne
 
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.
 
So your table looks like this

Table = Salery
Id_|_HourlyRate_|_Programmer
1 50.0 Jones
 
XXD,

Yeah, but what does YOUR table look like?

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

Wayne
 
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"' "
 
You left out the concatenation symbols in your where clause. Try:
Code:
CurrentDb.Execute "Update Beställda-Produkter " & _
"Set Enhetspris = '" & Me.Text45 & "' " & _
"Where Produkt nummer = '" & text51.Value & "';"
 
Darned spaces ...

Code:
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
 
Now I get syntax error on Update statement :S
Error 3144

edit:
I'm using access 2007 if it is to any help.
 
Last edited:
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
 
I've tried with the [] and Porduktnummer without space but still same error. Can it be something with my settings?
 
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
 
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
 

Attachments

  • table.jpg
    table.jpg
    36.6 KB · Views: 93
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

Code:
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.
 
error on:
CurrentDb.Execute strQuery
 
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
 

Users who are viewing this thread

Back
Top Bottom