Deleting a Field Entry using Cmd button on a different Form

[Rek 1969]

Hello Everyone,

First post, so please be gentle

A quick introduction - I am Group Maintenance Manager for a circuit board manufacturer. I have been building a database from scratch for the last year now. This database records all maintenance activity, expenditure, PAT testing, and most recently a preventative maintenance form.

The database has been split, the back end on a server at one facility, the front ends on 5 PCs at 3 different sites, and on my laptop. Back-end is secure - front-ends are secure (log-in page, minimal interaction with software, and bypass key inactive unless I log in using my password etc.).

Upon login, the user is presented with the switchboard that allows them to select which site they are at, then the appropriate tabbed form opens, a tab for each of the above (activity, expenditure, PAT test etc).
Each of the forms allows the user to enter data, but each also has various cmd buttons, so they can for example search for what is due in each month for PAT test, or see how many man hours were spent on each machine, which machine was the most expensive etc.

The newest form is the Preventative maintenance form. This is non editable by the user - each machine has its own record, a hyperlink to the check sheet that they print off, and a 'Whats Due' button (asks for mm/yyyy) and a 'Whats Overdue' button.

Bear with me, nearly there ...

The actual activity data is entered into the Maintenance Activity Form - when Preventative maintenance is selected from combo box, some hidden fields appear, where the user enters "Date of Service" - this then autofills a "Next Service Due" field with same date, then a "Frequency of Service" combo box is used to select weekly, monthly, quarterly etc. Upon selection, the "Next Service Date" auto adds that time period to date.

So, when the user selects "Whats Due" on Preventative Maintenance Form, the query is looking at this date, and lists.

So, here comes my issue - all historical MRFS will appear in "Whats Overdue", because there is data in the "Next Service Due" field. This can be overcome by the User just going back to that record and deleting date from that field, but I was hoping for an easier way, working as follows ....

On the Preventative maintenance Form, I have a "reset" cmd button - when the User clicks it, parameter box asks for "MRF Number" (Maintenance Request Form - primary Key for Activity Form) - User enters this number, Access goes to that Record, and deletes the date from "Next Service Due" field, thereby removing that historical activity from query results.

So, is this possible - I am expecting some complicated VBA or Macro?

I have no formal training with Access, I learnt how to split database and secure from Steve Bishops videos on Youtube - worked wonders for me.

Apologies for long read, and I hope I explained things ok.

Good to meet you all,

Rek

[pbaldy]

I'm not sure I understand the design, but certainly what you ask is possible, and not hard. You can run an update query using the input in the criteria. I'd have them enter the MRF number on a form, and then execute SQL behind your button. It could be a saved query or

CurrentDb.Execute "UPDATE TableName SET FieldName = Null WHERE KeyFieldName = " & Forms!FormName.TextboxName

if the key field is text:

CurrentDb.Execute "UPDATE TableName SET FieldName = Null WHERE KeyFieldName = '" & Forms!FormName.TextboxName & "'"

[Rek 1969]

Thanks pbaldy for your reply.

A couple of questions ...

I have entered code as on click event for button as follows,

CurrentDb.Execute "UPDATE Tbl_Spirit Maintenance Activity SET DATEDateOfNextService = Null WHERE TxtLastWeekMRFNo = " &Forms!Tbl_Spirit Maintenance Activity.MRF Number &"'"

Initially I get an error saying that expected end of execute was the word Maintenance closest to the end.

Before I execute this, I have on the Preventative Maintenance Form added a text box for each frequency of service (so that user can add MRF Number), and adjacent to each a button - the first of which is called TxtLastWeekMRFNo as above.

So, I need the code behind the button/s (there will be five in total, Weekly, one for each frequency of service - weekly, monthly, quarterly, 6 monthly, annual) to look at the number entered into the text box on Prev Maint Form, and go to the Record with that same number on the Spirit Maintenance Activity Form. The MRF is an autonumber so the MRF number is the same as the Record number. Once it selects the record, it needs to make the field (DATEDateOfNextService) = NULL.

Can we just confirm this is what happens when I execute the above?

Thanks

Rek

[Rek 1969]

Private Sub CmdWeeklyReset_Click()
CurrentDb.Execute "UPDATE Tbl_Spirit Maintenance Activity SET DATEDateOfNextService = Null WHERE TxtLastWeekMRFNo = " &Forms!Tbl_Spirit Maintenance Activity.MRF Number &"'"




End Sub

I get a syntax error - the first line is highlighted yellow, the rest in red

[pbaldy]

Two problems. Because of the inadvisable space, the table name needs to be bracketed, as does the control in the form reference. Second, you either need the single quote on both sides of the value or neither; you've got one at the end but not the start. Look at my example again.

[Rek 1969]

Ok yes I see my mistake regarding the quotes.

I also changed the Tbl_Spirit Maintenance Activity at the end to the Form name so it now looks like this...

Private Sub CmdWeeklyReset_Click()
CurrentDb.Execute "UPDATE [Tbl_Spirit Maintenance Activity] SET DATEDateOfNextService = Null WHERE TxtLastWeekMRFNo = " & Forms![Spirit Maintenance Activity Log].[MRF Number]










End Sub


So, if I forget about the tabbed form that these foms are on for a sec, and just open the Prev Maints Form and the Maints Activity Form, and hit reset (I have put an MRF number into the field that corresponds with a record on Maints aActivity that has a Next Service Due Date entered), I get the following runtime error,

3061.
Too few parameters, expected 2.

If I hit Reset without the Maints Activity Form open I get the following runtime error,

2450
MA cannot find the referenced form 'Spirit Maintenance Activity Log'

Any ideas?

[pbaldy]

The parameters error implies that 2 things are spelled wrong. Double check the table and field names in the SQL. Can you post the db here?

[Rek 1969]

I have checked spellings, and indeed copy and pasted from properties to make sure they are correct.

Posting the db here wouldnt work as its split? the Back end is on a server at another factory. i access with vpn connection?

I may have an old unsplit db somewhere that would give a small idea of design - how would i post it if i can find?

[pbaldy]

Compact, zip and then attach in the Go Advanced area. I just want to be able to recreate and thus hopefully solve the problem.

[Rek 1969]

Im having trouble finding a copy at the moment - will resume search tomorrow. I may have to copy out some relevant forms etc into another much earlier database.

I have managed to reduce the expected parameters to 1 though - the SET DATEDateOfNextService is the name of the text box, but in the table its called Date of Next Service, so I copy and pasted that, put square brackets around it and hey presto, down to one. I then tried it with the TxtLastWeekMRFNo, changing to Last Weekly MRF Number, as the feild is called in the Prev Maints table, but still came up with error message.

I really appreciate your help with this.

Rek

[pbaldy]

You bracketed both I assume? What is the code now? You might try using a variable for the SQL and this, which may give you a better error:

BaldyWeb-Immediate window

[Rek 1969]

It looks like this now

Private Sub CmdWeeklyReset_Click()




CurrentDb.Execute "UPDATE [Tbl_Spirit Maintenance Activity] SET [Date of Next Service] = Null WHERE [Last Weekly MRF Number] =" & Forms![Spirit Maintenance Activity Log].[MRF Number]








End Sub

The bracketed part after the WHERE (last weekly mrf number] doesnt seem to make any difference.

Ref the code in the link, I'm not sure what to do there - do I need to copy the dimdb, dim rs bits etc, or just the print bit that is circled?

Apologies, treading new ground here

Rek

[pbaldy]

It would look like:

Dim strSQL As String

strSQL = "UPDATE [Tbl_Spirit Maintenance Activity] SET [Date of Next Service] = Null WHERE [Last Weekly MRF Number] =" & Forms![Spirit Maintenance Activity Log].[MRF Number]

Debug.Print strSQL

CurrentDb.Execute strSQL

that would print the SQL out to the Immediate window. Copy/paste it from there into a new query and try to run it.

[Rek 1969]

This is the message in the Immediate Window

UPDATE [Tbl_Spirit Maintenance Activity] SET [Date of Next Service] = Null WHERE [Last Weekly MRF Number] =1
UPDATE [Tbl_Spirit Maintenance Activity] SET [Date of Next Service] = Null WHERE [Last Weekly MRF Number] =1

and

CurrentDb.Execute strSQL

is highlighted yellow

Copied and pasted into new query (didnt select table or form from list, just closed that and went into SQL view, then changed SELECT; to UPDATE; and pasted code in. ).

looks like this ...

update
Dim strSQL As String




strSQL = "UPDATE [Tbl_Spirit Maintenance Activity] SET [Date of Next Service] = Null WHERE [Last Weekly MRF Number] =" & Forms![Spirit Maintenance Activity Log].[MRF Number]


Debug.Print strSQL
CurrentDb.Execute strSQL



I get a syntax error saying error in UPDATE statement. The word STRING is highlighted.

[pbaldy]

No, paste this into a query:

UPDATE [Tbl_Spirit Maintenance Activity] SET [Date of Next Service] = Null WHERE [Last Weekly MRF Number] =1