Query Wont Run from Button

[Paul_birm]

I have a Form with a Button on. The form is a text input form that updates data on a table.

The data i have pulled in to view on the form is held in a temporary table and the query i am trying to run, takes the updated data and puts it into the original table.

temp table name - tbl_TempEdit
original table name - tbl_SubmissionData

the query name is: QryUpdateSubmissionDatafromTempEdit

the query runs when i manually edit the data in the tbl_TempEdit table, then run the query. BUT when i click the button on the form, it doesnt update the data on the tbl_SubmissionData table

here is the VBA code from the button on the form.

Private Sub Update_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_UpdateSubmissonDatafromTempEdit", acViewNormal
DoCmd.SetWarnings True
DoCmd.Close acDefault
End Sub

Please, Please Please, help me to point out WHERE i have gone wrong.
Thanks
Paul

 

[DCrake]

Try removing the set warnings and see if any records are affected.

 

[Paul_birm]

DC - thanks for the advice. think i had already tried that, but did it again!


I got the "you are about the run an update query" message and then the message saying "you are about to update 1 row" which was correct

but when i closed the application down and go back into the dbase with the SHIFT key held down, the data on the tbl_SubmissionData is STILL showing the OLD version.

so, it appears to be working, but doesnt actually UPDATE the tbl_SubmissionData table record.

for info only, the table tbl_TempEdit still holds the Updated record when you restart the dbase and a query clears it out prior to the button being pressed.

so am I Missing something with the Update or Re-query ???

 

[gemma-the-husky]

change or copy the query, to make it a SELECT query. then your code will show you the query details - and you may be able to see what is wrong.

here's a thought though - maybe when you click the button, the temp data hasn't yet been written/finalised to the "source" table for the update - so the update isn't changing anything. Maybe you need something to "force" the completion of the update first.

That's why I suggested using a select query, so you can see what is actually happening.

 

[Paul_birm]

this is a Screen Grab of the Update Query - can anyone see what is wrong

it SHOULD take the data out of tbl_TempEdit and update the table tbl_SubmissionData

thanks
Paul

 

[Paul_birm]

thanks Dave / Gemma-the-husky

it looks like its SELECTING the data from the tbl_SubmissionData table rather than the tbl_TempEdit

 

[gemma-the-husky]

have you sorterd this, Paul - I can't see the screenshot

 

[Paul_birm]

hi - no not sorted this yet, but looks like i MAY have the tables transposed, so i will TRY that, then i will let you all know

cheers

Paul

 

[Paul_birm]

OK guys thanks for the advice above BUT

1) i have NOW prooved that the Query Works on its own - independantly

it just doesnt work when i invoke the query from the Button on the form

2) i have commented out the warnings bits and YES i DO get the warning and the " you are updating 1 row" - again that works to - that works in both Query format AND on via the Button on the FORM

just as extra info, when the query hasw run and the has 'updated' the original table, i then close the edit form (where the button was) should i be putting some kind of UPDATE command ? or requery command? if i am.. .whats the syntax please ???

just a random thought !!!

thanks for looking at this and having a think about it.

Paul

 

[gemma-the-husky]

can you post the SQL of the update query - one of the qry view options on the design icon at the top will be a SQL view.

I think you can right-click the query and get the SQL view also.

 

[Paul_birm]

Hi Dave,
here is the SQL View of the Update Query (although, suprisingly, it doesnt verify the syntax in SQL Query Analyser!)

UPDATE tbl_SubmissionData INNER JOIN tbl_TempEdit ON tbl_SubmissionData.ID = tbl_TempEdit.ID SET tbl_SubmissionData.Application = [tbl_TempEdit.Application], tbl_SubmissionData.Project = [tbl_TempEdit.Project], tbl_SubmissionData.Valve = [tbl_TempEdit.Valve], tbl_SubmissionData.Title = [tbl_TempEdit.Title], tbl_SubmissionData.FieldText = [tbl_TempEdit.FieldText], tbl_SubmissionData.Approved = [tbl_TempEdit.Approved]
WHERE (((tbl_SubmissionData.ID)=[tbl_SubmissionData].[ID]));


fyi - the data is on the tbl_SubmissionData table and then copied (via a query) on to the tbl_TempEdit table. the tempedit table then is the basis for the form that allows the text editing - so when the user has finished text editing, it needs to update the tbl_submissionData entry

hope that helps - any questions, just fire away !

thanks in advance

 

[Paul_birm]

ok i think i have kind of sussed whats happening here (or not)

1) there is a query that copies the requested (by the user) record no (only 1) from table a to table b
2) the edit form uses the contents of table b as its control source
3) the user updates the text in real time
THEN clicks UPDATE button
4) then the contents of table b are updated on to table a
i DONT THINK that the contents of Table B have been updated with the NEW Text entered by the user
5) then the form closes

Q - how do i get the updated text on the form to update to table b BEFORE the query runs that copys the contents of table B over to table A

well at least i THINK that, that is whats happening (or not!!)

thanks in advance

 

[Paul_birm]

Problem sorted thanks.

Solution is that I had to close the update form first (which then saved the NEW data) and then run the Query that copies the revised data to the Original table

here is the VB from the Update Button :-

DoCmd.Close acDefault
DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_QueryName"
DoCmd.SetWarnings True

Thanks to all who offered help, advice and words of wisdom !
regards
Paul