Update table on report close 1

[SpandexBobcat]

Hi there,

I need to update a table when a report closes. The field 'paid' must change from 'No' to 'Yes' in my table OVERTIME. I was given this code to go in 'on close' but it doesnt seem to work, it asks for a parameter value for the report and everytime says it is about to update (0) fields. Here is the code:

UpdateTxt = "UPDATE OVERTIME SET OVERTIME.paid = -1 WHERE OVERTIME.Name = " & Me.Name
DoCmd.RunSQL UpdateTxt

Can anyone tell me what is going on? Name is not the primary key.....

Thanks in advance.

Simon.

 

[RoyVidar]

Hi!

Think you're having a challenge concerning naming convention.
When you refer to me.name the result is the report name, and I doubt you'll find it in the table;-)

I use some basic rules when creating tables, forms, reports:
* never use a reserved word (name, date, time... - if in doubt whether it's a reserved word, I use FName, ContactName or if I'm hit by a serious lack of creativity fldName)
* all controls on forms and reports that I wan't to refer to or use in calculations, I prefix (text controls txtMyControlName, Combos cboMyComboName...)

Having said that. You might be lucky with just renaming the control in the report (txtName) and refer to me!txtName in your code. If the primary key is available in the report, you should rather use that (in case of 2 Smith's), if you don't have it available, I'd recommend you to make it available.

Good Luck - Roy-Vidar

 

[SpandexBobcat]

Hi Roy,

I have changed the report so that it contains the primary key (Number) and the function kinda works... there are now a couple of problems... The update works but only on one entry at a time, I need it to update every entry in the report at once, secondly the report used to add the totals for each person together, now it just displays them as single entries. Any ideas as to how I can get round these problems?

Thanks in advance for any guidance given...

Simon

 

[SpandexBobcat]

Hi Roy-Vidar,

I have changed the report so that it contains the primary key (Number) and the function kinda works... there are now a couple of problems... The update works but only on one entry at a time, I need it to update every entry in the report at once, secondly the report used to add the totals for each person together, now it just displays them as single entries. Any ideas as to how I can get round these problems?

Thanks in advance for any guidance given...

Simon

 

[dhookom]

Personally, I think it is poor design to have a report do anything other than publish your data. I would add a msgbox and other code to update the records after the report has been run. You can not be sure the report ran successfully.

Duane
MS Access MVP

 

[SpandexBobcat]

Duane-

I see your point. What with the hassle this has caused your approach doesnt sound like a bad idea, however I am not very experienced at vb so dont really know where to start...

 

[dhookom]

Simon,
I can only assume your report displays a limited number of records from a query or SQL statement. You would need to create an update query based on the same records (or similar). Then, after the report has been successfully run, allow the user to click a button to run the update query.

Duane
MS Access MVP

 

[SpandexBobcat]

Duane,

A simple but effective solution.
I have created the update query which, when run, updates my fields correctly. All I need now is the code to 'on close' run the query. How do I get the update query to run, it is as straight forward as a normal one is it....

Simon

 

[dhookom]

You can use code in the On Close of the report:

If MsgBox("Do you want to update",vbYesNo+vbQuestion,"Update&quot = vbYes Then
DoCmd.OpenQuery "qupdYourQuery"
End If

Duane
MS Access MVP

 

[SpandexBobcat]

Duane,

Thanks for your help, I needed my train of thought changed and it now works a treat!

Simon