Refreshing an Open Form Aftera Pop Up/Modal Form is Closed

[Sa'El]

I have a main form that list all the records and on click opens the detail of a given record via a Modal window form. Through the wizard I've added a delete record button that additionally closes the modal form and returns to the (still opened) form list. I want the main list to be refreshed/requeried when the form is closed, as I see #Deleted.



I'd rather not do some work-around where the form is closed and opened again to get the same result. How would I go about doing it? I am new to VBA, so I can follow code as long as it comes with a little hand-holding. Any help would be appreciated.


Thanks.

[RuralGuy]

OPen the Form with the acDialog argument and then add a Me.Requery on the line after the FormOpen command. It will not execute until the next form is closed or made invisible.

[Sa'El]

The Open Form was originally a macro, but I decided to go for the VBA route and see if I can make it work, and it did! Thanks a lot RG, but I have a curious question as I want to be more of a programmer about this.

The form is opened via a click on the datasheet portion of a split form (the single-record view is hidden). So I tested the above with one field, it worked, and so from here I can copy and paste the code and then just paste the field name. But I know there's more of a programmatical way of doing it, as all the different fields are executing the same code.

Trying to remember my programming class from like 5 years ago, I'm thinking you may have to declare all possible controls (field names) that will would be clicked and on a given click the program will loop through these controls until it matches the right control and then executes, which in this case is, the open form in acDialog and then add me.requery.

Am I on the right track at least (although that may be just one way to do it programmatically). But how would I go about the syntax?

[RuralGuy]

What do you have as the OpenForm line? Are you using the WhereCondition argument?

[Sa'El]

This is the code:

Private Sub ID_Click()


DoCmd.OpenForm "Patient Detail", , , "[ID]=" & ID, , acDialog
Me.Requery


End Sub

[RuralGuy]

Is ID a variable?

[Sa'El]

Yes it is. It is the autonumber primary key.

The only thing that would change if I were to copy and paste the code is the control name on top of the procedure. So the "ID" in ID_Click() and the filter containing the "ID" is coincidental.

[RuralGuy]

I'm trying to understand your form a bit better. Why, for example, do you use a "split form (the single-record view is hidden)" here when a continuous form would do? Where do you load the ID variable with the value of the current record?

[Sa'El]

I’m sorry that this is a bit ambiguous. When I started working on Access projects a couple of years ago I looked at MS Office sample databases for visual design ideas. I came across a form that had the benefit of a datasheet lists multiple records in a simple manner, but also has had the visual form header to add some aesthetic to the form. So that form pulled up the splitter bar all the way to the form header (and then disable the form splitter bar property) so that the single-record view was hidden, leaving just the datasheet and a visually pleasing form header. I have attached a screenshot.

I thought that was clever and that type of design has been useful to me since often I have had end users that have a lot of data to enter but may want to see a few key fields on a high level and then click that record on the datasheet (as hyperlinks) to view/enter detailed information (Pop-up/Modal form) for that record. Also, adding “Show/Hide” button they can add/remove fields on the fly without messing up the overall design of the form as they are adding to a field (column) to a database, leaving them to simply adjust the column width, if necessary.

For the record, just this weekend I saw a video about continuous forms (always heard about them) and I see why you would recommend this as it is a similar blend of the aesthetic I described. But the simplicity of the datasheet portion as a high-level search, the flexibility to add/remove fields by the end user that won’t disrupt the design of the form, and the ability/real estate to place up to several menu command buttons in the form header (feature of the split-form) for facilitated navigation makes this option well-rounded. Although, I will look at continuous forms in more depth as I’ll likely find a scenario to use them (maybe as subforms, as I’ve obviously only used datasheet or single forms as subforms up until now)

Hopefully this explanation and the screenshot makes sense.

[Sa'El]

Forgot Screenshot

[Sa'El]

Now for the next question:

That’s a good question, and to be honest, technically the Patient MR# should be the filter, which I was going to change anyway, as that is the field connecting the one-one relationships of the forms. And the reason why I have one-ones is that I am converting a questionnaire that has a lot of questions, so to normalize the database most of the tables I created are sub-sections of the same record.

Finally, if you’re wondering why ID is there in the first place, it was because when I didn’t go the VBA route and was using the macro that was in the sample database to open a specific record, they use the ID number field. So for simplicity’s sake I kept using ID, but now that I’ve grown up a little, I can shed that.

I appreciate your patience through all this.

[RuralGuy]

Frankly, I've not used split forms before and I'm not sure how they work. On a form in Continuous View or Datasheet View, every time you place the cursor in a different record you get a Current Event. The Split Form may also work this way. As such, putting your code in the Current Event should cause it to open your other form regardless of which control they select. Give it a try and see what happens.

[Sa'El]

A split form is a conglomerate of a Single Form and a Datasheet form. The default set-up is that on top is the single form and below is the datasheet form, separated by a moveable "Splitter Bar" to give one form more screen real estate than the other (as aforementioned, the splitter bar can be disabled in order to "lock" the split). So whatever record you click on the datasheet, the above single form will display the corresponding record. The additional benefit is that you can specify which fields you want to display in either form, thus it's likely that you'll use the datasheet for basic information, and the single form for detailed information. Conceptually this is similar to a continuous form, but a split form does this as a conglomerate whereas the continuous form does this like a hybrid.


I inputted this code, and although it compiled, nothing effectively happened:

Private Sub Current()


DoCmd.OpenForm "Patient Detail", , , "[ID]=" & ID, , acDialog
Me.Requery

End Sub

[ItsMe]

Did not read the whole thread but...

Split Form? Perhaps a Navigation Control/Form is being used here.

[Sa'El]

Hey there.

Here's a quick way to navigate where we're at.

Post #5 by me is the answer with RG's help to the question in post #1.

What we're working on is the latter half of post #3. The split form aspect was a bit of a tangent discussion.