Can't figure out form controls / Command Buttons

[jim.thornton]

I've got a sub form (MembersSubForm) which is in HouseholdsForm.

I've got an "Add Member" button which opens a new form "MembersAddForm".

MembersAddForm has the following properties set:
Data Entry: True
Additions: True
Edits: False
Deletions: False

In the MembersSubForm I have added a command button "Edit" which opens the same form "MembersAddForm" and I have created a relationship to open a specific record. However, because of the properties (I think -- Data Entry), it isn't working. When the form opens it is an empty record and adds a blank record (if I don't type anything in).

I can't seem to find the VBA code that is attached to the command button, so I don't know where I can change the properties before the form "MembersAddForm" opens.

Can someone help me please?

[Gicu]

You need to change the form's properties to Data Entry=False, Edit =True then in the first button's click event add DoCmd.GoToRecord acDataForm, "MembersAddForm", acNewRec after the code you use to open it (Docmd.OpenForm "MembersAddForm").
For the Edit button use the WhereCondition argument of the OpenForm method: Docmd.OpenForm "MembersAddForm",,,"[MemberID]=" & Me.[MemberID] '[MemberID is the first field in your subform showing 13 for Bob and 14 for Mary.

Cheers

[jim.thornton]

Thank you... I found a good video that showed how to do it using macros, so I was able to get the functionality that I'm looking for except for a couple of odd things that I can't figure out:

1. In my main form (with my members subform visible), when I delete a member, it shows in the subform as DELETED in all the fields. I had to make a refresh button in the main form in order to get the subform to update.

2. When I add a member, same thing. It goes back to the main form, but doesn't show anything. However, when I click the refresh cmd button it updates and shows in the list.

3. I've got my ADD Member form opening as a dialog box. It allows me to save a blank form. It will then go back to the the main form and if I click the cmd button it refreshes and shows the blank row for the newly added blank member.

4. When I tab through all of the fields and the two buttons at the bottom (save and cancel) it goes to another blank form and it adds the member just by tabbing.

For #1-3: I would like this to happen automatically, but I can't seem to find the correct event to put the refresh command into.

For #4: I would like it so that when I tab through all the fields, it doesn't add the record but instead go back to the top of the form fields.

[June7]

By dialog box do you mean a form? Show your code.

For 4, maybe you should set the form Cycle property to Current Record.

[jim.thornton]

Thanks for the cycle property tip. 2, 3 & 4 are all solved. #1 is still a problem, showing DELETED after. I can't seem to get the main form to refresh without clicking the button manually.

I've attached 3 screenshots.
1. Shows what it looks like after I click delete.
2. Shows the code for the cmdDeleteMember button OnClick event
3. Shows the bottom part of the code

It's not "Code", I used macros to do it.

I just don't know what event is triggering when I confirm delete of a Member record and then it goes back into the Main Form.

[June7]

Dang, I don't use macros, only VBA.

Can't you do a Requery of main form after the Delete command? In VBA like:

Forms!mainformname.Requery

Or if need to requery a subform:

Forms!mainformname.subformcontainername.Requery

Not seeing code that opens form. In VBA I would use:

DoCmd.OpenForm "MembersAddEditForm", , , , acDialog
Me.Requery

[jim.thornton]

OK... I've got it updating. For the life of me I could not find an event that executed, so I ended up using the on timer event. I set the interval to 500 (which I think is 500 ms).

Every 500ms I have it checking if not isnull([TempVars]![tmpMembersUpdated]) and then if it is true, requery.

Then, in the Add/Edit Member form, when Delete is clicked I inserted SetTempVar to tmpMembersUpdated=True. Same thing for the Save cmd button.

It works. Is that the "correct" way to go about it or is it wrong?

[June7]

It's correct if it works to your satisfaction.