How best to show/hide main form buttons when clicking into a subform

[popguru]

Hi everybody,
Newbie here. I'm currently building a database in Access 2013. I've used Access many years ago, and not really in anger since maybe 2003, so I've been re-learning quite a lot. Here's an overview of my scenario, and issue:

The main purpose of my database is to allow engineers to log various bits of information when building, servicing and testing manufactured products for offshore exploration. Because some of the engineering team aren't so great with data entry etc, I've decided that, rather than deleting data, I'm going to use a 'deleted' flag: user clicks what he considers to be a delete button, but in reality an update query will simply update the flag and, because the form is built on a query which includes a 'where deleted = 0' filter, it'll no longer be shown. This will allow us more control over the data and will also allow me to check that everything is running okay.

My main forms include a number of subforms, including one which includes a second tier of nested form: Pin Maps for (electrical) Connectors.

What I need to achieve is fairly straightforward: as the user navigates around the form, I'd like a 'delete' button which is specific to that subform to become visible and for any other 'delete' buttons to become hidden. Once tested, the buttons would be 'stacked' in the same position at the top of the main form, so the user just thinks they're deleting the relevant record in the sub, or main, form.

I think I'm okay creating the VBA for this, but which event(s) should I attach the VBA to? Is there a defacto, tried and tested method?

Thanks in advance for any help. As you can probably see, my interface design head is once again making promises that my coding fingers can't keep!

Martin

 

[CJ_London]

why not just have one button with slightly more complex code behind it that will refer to the appropriate subform.

so the code might be

currentdb.execute("UPDATE " & curTable SET fldDeleted = true WHERE ID = " & curID)

and in the oncurrent event of each of your subforms you put

Parent.curTable="Table1" 'set to name of the table you want to update
Parent.curID=me.ID 'or whatever you are using for your autonumber

You need to create two unbound hidden controls on the form called curTable and curID

If each of your tables has varying names for ID and flDeleted then the above code can easily be modified to allow for this

 

[popguru]

Beautiful! Thanks, CJ_London. That's exactly the sort of thing that I wish I could train my brain to come up with. I'll give it a go now and report back shortly.

 

[CJ_London]

sorry - missed a quotation mark - tho' you probably spotted it

currentdb.execute("UPDATE " & curTable [COLOR=red]& "[/COLOR] SET fldDeleted = true WHERE ID = " & curID)

 

[popguru]

Yep, I did figure out the missing quotation mark once I'd realised that you were cunningly synthesising the SQL-esque statement. Very elegant indeed. I've just finished up some nav interface bits and pieces so, here goes.... :0)

 

[popguru]

I'm back :0)

I've almost cracked this. Firstly, I had to tweak the SQL statement to change 'fldDeleted' to 'Deleted' (as that was its name in my tables). I then had to add a third unbound field to my master form and to the oncurrent subform event to include the actual field name of the autonumbered subform field, which I called 'curPK', and then I had to tweak the synthesised/embedded SQL statement for the button to include curPK as follows:

CurrentDb.Execute ("UPDATE " & curTable & " SET Deleted = true WHERE " & curPK & " = " & curID)

My remaining task is to make the subform refresh so that the 'deleted' record is removed from the view and I'm there.

 

[CJ_London]

My remaining task is to make the subform refresh so that the 'deleted' record is removed from the view and I'm there.

Putting

me.requery

on the next line should do that

Note I deleted my last post if you rec'd it via email - I was talking gibberish!

 

[popguru]

Ha! That almost works perfectly, but has opened a (new) can of worms. Let's see if I can explain.

The form in question, Builds, relates to builds of a given Product. In general, a user will access this form via one of two methods: they'll either apply some filters in a 'Search Builds' form and then click through to the relevant record, or they'll click through to a Build from a list of Builds for that Product in a tab on a given Products record. So far, this has worked okay. If I apply the 'me.requery' statement above, naturally the Build form spits the dummy and asks for a contextual Build_ID based on where the user has arrived from, i.e.:

[Build_ID]=[Forms]![Product_Master_Core]![Builds_For_Products_subform].[Form]![Build_ID]

*or*

[Build_ID]=[Forms]![Search_Builds]![Sub].[Form]![s_Build_ID]

depending on where the user started from. I see that the relevant passthrough filter is displayed in the Build's Filter property in design mode, which is understandable, and I'm guessing that I could programmatically remove that so that, after requerying, the dialogue box asking for Build_ID doesn't appear. But then I'd lose the filter and, presumably, all Build records would be displayed and I'd then default to the first/last/whatever record based on the prevailing sort order. Is there a known workaround for this scenario?

Sorry to answer your solution with another question, I appreciate that you obviously have (paid) work to do.

Thanks,

Martin