update query question

[dubiic]

Hello again!

I just faced task which seems to be quite difficult for me.
I will make this a bit more simple then it is, so everyone could understand.
So I have table with 3 fields for example: ID ( which is key ), Date, quantity
ID is 8 digit number. I have like 1000 rows where there is just ID. For some of these rows it is necessary to add date and quantity. And it is so not handy to look for right ID and then fill these rest 2 values by hand. Cause these 2 fields might be similar for multiple records. Since now users have worked in excel using auto filter which was so easy, you were able to update like 100 records at the same time without any problem.

What I have in mind, It is necessary to build form based on update query.
It should have like 10 empty text boxes where user can just paste these required ID's in. Then there would be 2 fields "Date" and "Quantity".
Nice and fat button "update" which would update all these rows containing right "ID's" with the same Date and Quantity.

My idea seems to be possible, but still in blur for me. Could you confirm that it is possible, or maybe there are other suggestions how to make custom updates for multiple rows at once.

Thanks a lot for reading, sorry it might look a bit messy.
Best regards.

 

[namliam]

Yes possible. Atleast... The idea is possible. It is however impossible to construct a form upon an update query... but the concept... possible...

Suggestion:
Make the 10 textboxes one listbox for all IDs that still need information. Then they can click on which IDs they want updated.

Then in the update button run a loop for each selected ID and update the records in the table.

 

[dubiic]

Hmm listbox probably will die cause there are over 5000 Id's
Hmm so I should make it based on just normal form then.

 

[namliam]

5000 ids is not the problem IIRC it is 32000 characters, so if you have 4 didgets/id you should be OK. Offcourse you can run into trouble!
Yes Normal form with "normal things", just updates in code

Another alternative could be to have a "tabular" form, then have the user enter data in the header in some text boxes and click an update button on the tabular subform. Tho this does mean finding the ids manually

Also I can imagine maybe the users want some function like: ID 101 - 120

Maybe an alternative still is to export to excel, allowing users free hand of updating data as they see fit and desire. Then reimport the editted excel file.

 

[dubiic]

Hmm subform would mean same horrible effort with updating them im afraid.
You see my "ID" like I called it just to explane - actually are VIN - car chassis numbers ( 17 digits ), so range is not what we are looking for this time.
It will be like 10 car update "combo" per hour so export import wont work either

 

[namliam]

Just throwing options out there, trying to help you... If the options are invalid, feel free to discard them...
17 didgets would mean trouble for the listbox IIRC... Still entering 17 didget numbers is allmost asking for data entry problems.

What if you make it combo's?

 

[dubiic]

I know
I am at the same state, You see entering process is just copy paste from another system. What if there would be form with my 3 fields and 1 button. I write values what i want for date and quantity. User just paste ID ( VIN whatever how we call that scary number ) and presses update, in that moment record for that ID updates and were happy, just paste next one and since form will renember date and quantity we can just hit update button again, without much effort, paste, click, paste click

 

[neileg]

I think your last post is the best idea but I would also use a combo.

So on your form you have a combo box that is based on a query that returns the records that have the two blank fields. You also have two unbound text boxes, one for Date and one for Quantity. You then need a button that has code that builds your update query as string SQL referencing the the combo and the two text boxes and then runs the SQL and refreshes the query for the combo.

 

[namliam]

You could even Paste the data or read it from the clipboard with a little more effort, limiting the user actions even more

 

[dubiic]

I can see how it works, but I'm afraid that my skills won't be good enough for that update query button, sounds like Chinese to me

 

[namliam]

Do you know how to make an update query in the designer and retrieve the SQL from that?

 

[dubiic]

Yep, I could do that. Well you mean just make empty update query with related fields, since update part should be custom from code

 

[namliam]

Yes empty... Or with fake values anyway as the values will have to come of your form.

Take that SQL, now go into your "update buton"
Add:
Currentdb.execute "updatequeryhere"

Now edit out all the empty/fake values and replace them by the values on the form... and you are done...

To reference a textbox on the form:
Me.Textbox

 

[dubiic]

Ok, i thought under query is something more complicated

What I don't understand is how can i bound this query to row which i have to change, As i understand i made combobox bounded to table with all 3 my interesting fields. Ok, I understand my 2 field values in code, but what should i write in my VIN part ? Now it is my fake value "1" Can't understand how can i reference to that row.
I came up with:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Currentdb.execute "UPDATE body SET body.VIN = "1", body.CMR = me.updatecmr, body.[TR rekina datums] = me.updatedatums;"

End Sub

so far

 

[namliam]

Good try

To substitute in variables you have to get them outside the query string and in the code using & to concatinate the query string.

Something like:

Currentdb.execute "UPDATE body SET body.VIN = 1, body.CMR = " & me.updatecmr & ", body.[TR rekina datums] = " & me.updatedatums & ";"

Now lets try and make it a little more readable for future updates....
To concatinate to the next line use "& _"

Currentdb.execute "UPDATE body SET body.VIN = 1, " & _
                                 " body.CMR = " & me.updatecmr & "," & _
                                 " body.[TR rekina datums] = " & me.updatedatums & ";"

Now one final thing.... When using strings you need to enclose them in quotes like so: "string"
Dates in hashes, like so: #02/28/2008# NOTE the format, this is manditory!
Numbers dont need to be enclosed

So the final idea would probably be something like:

Currentdb.execute "UPDATE body SET body.VIN = 1, " & _
                                 " body.CMR = """ & me.updatecmr & """," & _
                                 " body.[TR rekina datums] = #" & me.updatedatums & "#;"

But you have to finalize it offcourse as I am just stabing a little in the dark here.

Good Luck !

 

[dubiic]

Nice

But what about that bound to VIN ? I just cant understand how to target right row, just found out that VIN searching will be by wildcards, last 6 digits or so.

Thank God my working day is over, I will continue tomorrow.
Mailman, Your effort is realy appreciated again. +100

 

[namliam]

Well how does it work when you put criteria in the SQL window?? And how does the SQL look there???

You get a "where clause" appended to the update query.... Try and work it out, post back any questions.

 

[dubiic]

I Was away from work this week, so now I will try to finish this
For what I am not still clear is how to manage that VIN search, Just combobox or simple textbox. Input method will be paste it in. And how to point right row where to put these 2 field changes in.

 

[dubiic]

My situation for now: Just form with 3 textboxes
* "updatevin" ( Key in table). All fields are filled with this value. This value will be just pasted in to somehow point at needed row in which one I am interested in.
* "updatecmr" just number which will be similar for multiple VIN's
* "updatedatums" just date which will be similar for multiple VIN's

Since I still can't figure out how to point at row where these changes has to be made each time, I would like to overwrite VIN entry then. Effect should be the same.

I made such button, but there are couple of problems in it, maybe you can comment it. Visual basic compilator says "compile error, expected end of statement" and marks "updatecmr" part.

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

CurrentDb.Execute "UPDATE body SET body.[VIN] = "" & Me.updatevin & "," & _
" body.CMR = "" & Me.[updatecmr] & "," & _
" body.[TR rekina datums] = #" & Me.updatedatums & "#;"

End Sub

I would be realy glad for any help.
Thanks in advance!

 

[namliam]

"updatedatums" => You are dutch ... LOL
Greets from amsterdam

CurrentDb.Execute "UPDATE body SET body.[VIN] = "" & Me.updatevin & "," & _
                  " body.CMR = "" & Me.[updatecmr] & "," & _
                  " body.[TR rekina datums] = #" & Me.updatedatums & "#;"

Just like you put a # in front and behind the updatedatums, you need to put quotes around updatecmd and updatevin, not just in front.

Also putting in a single quote is not going to work, as a single quote will mark the beginning and/or end of a string of the execute statement. You have to "escape" the quotes by doubling them up, so if you want "123" in your query you have to make it like "SomeField = """ & variable & ""","
The double quotes end up beeing single ones in your query: Somefield = "Variable",

All in all this is what you are looking for:

CurrentDb.Execute "UPDATE body SET body.[VIN] = """ & Me.updatevin & """," & _
                  " body.CMR = """ & Me.[updatecmr] & """," & _
                  " body.[TR rekina datums] = #" & Me.updatedatums & "#;"