Deleting a Field Entry using Cmd button on a different Form

[Rek 1969]

Ok so that sort of worked in that there was no fault, but I dont think it actually did anything

When I ran the query, the Update Query warning came up, then a Parameter Box, asking for MRF number. This was folled by other warning saying that you are about to modify a row that you cannot undo etc. Ok'd that, then went to the Activity form, and the date was still there.



Also, the Parameter box was something I was hoping to avoid. I was hoping that the query would look at the number in the box next to the reset button, go to that record on the Activity form, and delete the date.

Does Access treat the date as a number or as text? (its a date field entry I am trying to delete) Would that make a difference?

Please see attached pictures, hopefully they will give you an idea od form design.

And thank youi for your help last night, much appreciated.

Rek

[pbaldy]

The parameter prompt is Access telling you it can't find something, so double check the form and textbox spellings. Dates are stored as numbers (double). You should be able to set the field to Null but try a literal date surrounded by #, like:

#1/1/2000#

can you post the db here, or a representative sample?

[Rek 1969]

Adding PreventMaints to SPIRIT Activity Form.zipAdding PreventMaints to SPIRIT Activity Form.zip

This is probably going to be the closest I have for - hopefully it works.
One thing that will be missing from the Preventative Maintenance Tab is are the text boxes and reset buttons.
Could I ask that once you have it could you remove it from the post please?

Thank you

[Rek 1969]

You will notice that the little service date section on Maints activity tab only appears when you select 'preventative maintenance' from the 'Type of Activity' combo box.

[pbaldy]

I'm not seeing the code that resets the value. What form is it on?

[Rek 1969]

Adding PreventMaints to SPIRIT Activity Form.zip

Ive just quickly added to the Frm prevMaints a representative text box (for MRF number of activity) and a reset button. This runs the query Qry_SpiritResetWeeklyPMMRFNumber.

I havent entered any vba code as your very first suggestion.
This database has hardly any data in it, but hopefully will have enough in it to work with.

It also has no switchboard or login form.

The Maintenance Activity form is the one for Spirit, or the Spirit tabbed form has it on also.

Thanks

Rek

[pbaldy]

Try changing the query to:

UPDATE [Tbl_Spirit Maintenance Activity] SET [Tbl_Spirit Maintenance Activity].[Date of Next Service] = Null
WHERE ((([Tbl_Spirit Maintenance Activity].[MRF Number])=[Forms]![SPIRIT Tabbed Form]![Tbl_PrevMaints].[Form]![TxtLastWeeklyPMMRF]));

You can use SetWarnings in the macro to turn the warnings off and then back on after the query is run.

[Rek 1969]

Obviously the idea is that the user identifies which equipment is due for PM via the Prev Maints Form, then enter the details onto the Maintenance Activity Form. The Maintenance Activity Number is then entered into the tex box on the Prev Maints Form (adjacent to Reset button). Then when they carry out the next PM of the same frequency they hit the Reset button to delete the Next Service Due Date on the Maints Activity Form.

This then removes that MRF from the Overdue search results.

Phew, sounds a bit around the houses I know, but would be a good way of doing it, hopefully!

[Rek 1969]

It WORKS!!

But, is there a way for the Query to see what number is in the text box and go to the corresponding MRF without the parameter box?

Also, could you give me a very brief and simplistic explanation of how you got it working please?

Great work, thank you

Rek

[pbaldy]

The query I posted ran without parameter prompts in the sample (I had it on the SPIRIT tabbed form on the preventative maintenance tab). The field in the query wasn't the MRF field, so I changed it to that, and then added the form reference to the criteria.

[Rek 1969]

Sorted!

Worked wonders.

when you mention the setwarnings in the macro - where and how do i do that?

[pbaldy]

Excellent! Before and after the OpenQuery in the macro behind your button. Basically

SetWarnings No
OpenQuery
SetWarnings Yes

[Rek 1969]

Thank you very much,
Now all I need to do is repeat that for each frequency, for each site!

I do have another issue with a subform (will pale in comparison to this one) ... but I think you have earned a weekend off haha.

I will save it for tuesday

Thanks again

Rek

[pbaldy]

Happy to help! I don't think I get the weekend off though. I'm slamming in new functionality that won't be used until tomorrow. I'm quite sure something will break, given the complete lack of testing.

[Rek 1969]

Good Luck!

If you need a hand, give me a shout haha