Child Forms Using Main Form RecordSet

[OldBirdman]

I have a form with several bound textboxes. These are routinely updated by User. The table in RecordSource for this form currently has 8 fields not shown on this form, but which occasionally need to be changed by User. For this I have created an "Advanced" commandbutton, which opens a child form with controls to display the remaining fields.

I tried using recordsetclone to put the child form on the same record and recordset as the main form, but when the child form is closed, the clone is updated. Then the main form gets a write conflict when it tries to update the same record.

I found a sample code on another question which I have modified slightly and uploaded here. Original by FishVal I think.

Child form 1 seems to do what I need. Forms 2 and 3 pass references to a control, not the underlying data. As I have currently 8 fields, these seem inappropriate.

1) Open form = "frmmain", and move to record 2. Change Text2 to something else, i.e. "R 2aaa".
Move to record 3. Press the UnDo button, and the record is returned to record 2 and that is undone. But navagating to a new record should save the changes. Why wasn't record 2 saved when record 3 displayed?

2) Open form = "frmmain", and move to record 2. Click the Child Form 1 button. Change to something else, i.e. "Record 2ccc". Press submit. Press the UnDo button. Why isn't the command 'Undo' available now. The recordset for frmChild1 is the same as for frmMain, and the record has not been moved to another record, so why is the Undo unavailable?

3) The procedure "Private Sub txb_BeforeUpdate..." in the code for child1 saves .OldValue for this textbox in case the "Cancel" button is pushed. This should not be necessary, as .OldValue is the value in the record, and should remain until the record is updated. .Text would be lost after the textbox were updated. If I comment out this statement, and put a breakpoint in "Private Sub btnCancel_Click()". Me.txb.OldValue is the updated textbox value, not the recordset original value. Why?

4) If new record is selected and then child1 used, when I press "Submit" I get message "Update or CancelUpdate without AddNew or Edit." AllowAdditions = Yes on both frmMain and frmChild1, so why this message?

OldBirdman

[OldBirdman]

And now the Attachment...

[OldBirdman]

I'll get it right yet!
OldBirdman

[nico5038]

Without checking your sample, I do wonder or you considered to use the tab control ?
This "looks like" your additional [Advanced] button, but will allow you to have all fields on one form. You can even change the Tab into Buttons by changing the tab control's properties...

Idea ?

Nic;o)

[missinglinq]

I agree with Nic;o), tabbed pages would be the way I'd go with this, using the same table as the OP stated.

Linq ;0)>

[OldBirdman]

No, but I didn't consider a larger monitor either. A tab control with one tab (or button) doesn't seem right. I don't see where this saves space, unless the forms 50+ visible controls are on one tab, and the 8 advanced controls are on the other. What is a "OP"?

I have now found the source of the sample code in topic/access/insights/842978-forms-interaction, by FishVal. I don't know how to directly point to a specific post but, OK, there it is.

This small database with table & forms seems to be here to use as a tool. Download it, study it, modify it, use it. But I can't make it work as I want, so I ask a question.

If my actual code is necessary, I will post it (you don't want to see it). If a few lines of code can demonstrate a problem, I prefer to post that. It is easier to understand, and might be easier for other users of this forum to apply to their problems. In developing a simple example, I often solve my own problems.

Of course there are other solutions to this problem. I could make all my controls smaller, with smaller fonts. I can't make the form larger, or it won't fit on my laptop screen. But if the controls are smaller, I can't read the data when the laptop is in my lap, because I'm getting old, and my bifocals are for close-up when looking down, and my lap is too far away, but my far reading means looking over the top of the bi-focal, and is a strain on my eyes, neck, and back. I could put the laptop in a place other than my lap, but then I could not take it with me, so I would just need the desktop. Also, as I get older, I want larger buttons and arrows to click. The motor skills to move the mouse/cursor aren't as good as they used to be. Microsoft recognizes this and addresses it in the control panel. But to make the mouse less responsive means the desktop computer, with a 22 inch monitor, needs too many passes over the mousepad to get from one edge of the monitor to the other.

If my question is inappropriate to this forum, please let me know. Otherwise, I would like to know how to open a child form, bind it to the same recordset as the parent form, and be able to cancel while the child form is open, or after it is closed but before the current record on the parent form is saved. I would also like it to work if the "New" button pressed, therefore creating a new record.

OldBirdman

[FishVal]

Hello, OldBirdman.

As soon as it was me to write this howto I'm expected to take responsibility. :D

@OldBirdman
Though I don't use menu undo command, I have a feeling that it is not a bug it is a feature. :)
Really, its behaviour is quite consistent. When current record is dirty it undoes changes in current record, otherwise it undoes last saved record.

2) Open form = "frmmain", and move to record 2. Click the Child Form 1 button. Change to something else, i.e. "Record 2ccc". Press submit. Press the UnDo button. Why isn't the command 'Undo' available now. The recordset for frmChild1 is the same as for frmMain, and the record has not been moved to another record, so why is the Undo unavailable?

As soon as frmChild1 is closed record it was pointed to is being automatically saved. Undo action is not available in main form because the record wasn't edited there. This makes sense if we will assume that particular form hold a "log" to roolback last edited record and Undo action uses this feature.

3) The procedure "Private Sub txb_BeforeUpdate..." in the code for child1 saves .OldValue for this textbox in case the "Cancel" button is pushed. This should not be necessary, as .OldValue is the value in the record, and should remain until the record is updated. .Text would be lost after the textbox were updated. If I comment out this statement, and put a breakpoint in "Private Sub btnCancel_Click()". Me.txb.OldValue is the updated textbox value, not the recordset original value. Why?

This was done to simulate Undo, when bound recordset will be updated on frmChild1 close.

4) If new record is selected and then child1 used, when I press "Submit" I get message "Update or CancelUpdate without AddNew or Edit." AllowAdditions = Yes on both frmMain and frmChild1, so why this message?

OldBirdman

Actually, I couldn't reproduce this behavior. However, there is another flaw - when input is cancelled in frmChild1 a new record in main form is being created all the same.

Frankly speaking I don't like this Method #1 because together with its "simplicity" it triggers too many automatic actions in a manner Access doesn't always expect.
I prefer to use Method#2 or, in special cases Method#3. As in examples from Absolute Position of a Control.

Regards,
Fish

[nico5038]

A tab control is used in general with two or more tabs.
Each tab "duplicates" the space of the form.
I normally "group" fields in logical units and place the group name on top.
This enables a user to navigate easily through the fields.
One form with 50 fields is certainly "too much" to grasp for a normal user and will probably require scrolling. By using a tab control and placing some 10 fields per tab will save the form scrolling and give an opportunity to "organize" the fields logically.

Just check the sample.

Nic;o)

[OldBirdman]

First to my original 4 questions:
1) Yes, I now see that there is consistancy here. According to Access Help, moving to another record saves any changes before the move. Therefore, I wrongly assumed that my changes were complete and secure.
Even without an Undo button, a user can press cntl+Z and undo a previously saved record.

The question for #1 should now be: How do I write my changes to the table and prevent those changes from being inadvertently undone?

2) If form is Dirty, UnDo obviously should undo the recent, unsaved changes. If form is not Dirty, then for consistancy with #1 above, the last saved record should be undone. In this case, nothing is done. Not consistant.

3) I think this is not needed code. First, the way this sample code was written, it can't be used. The BeforeUpdate only fires when the form is closed, either by "Submit" or "Cancel". No code can use this held value. Adding another control to receive the focus can trigger BeforeUpdate without closing the form. Now varOldValue can be assigned a value. However, the statement "Me.txb = Me.txb.OldValue" in "Sub btnCancel_Click" will do the same thing.

4) I can't reproduce it either, NOW. But I did for several days, thru several reboots.


Second, Tabbed Forms:
This is really off-topic here. The sample file assumes that the 3 child forms are for different data. They are actually 3 ways to accomplish the same thing, and I don't believe a developer would give this choice to a user.
I find tab controls to be useful under two conditions. One is where the tabs have completely different controls, but all relate to a common theme. "Options" is an example here. Different tabs for different sets of options.
The other use mimics a ComboBox. Each tab presents the same data, but the underlying table is filtered according to the tab selected. A car dealer could have a tab for each brand sold, and therefore selecting "Toyota" would show a list of Toyotas in stock, and the "Scion" tab would similarly display a list of in-stock.
Tab controls can present their own problems, however. I tried to mimic a tab control on a tab control (Primary tab to display brand, secondary to select and display model in-stock). In DesignView the tab controls were not in the final position, so they could be selected. Moving the controls using VBA presented problems. Access doesn't store the controls relative to the tabcontrol, but to the form. When the tab control is moved, it is resized to accomodate the controls original position. I did not pursue this.

Third, Number of Controls on Form:
There are 2 variables here, controls on a form, and number of mouse clicks or keypresses to do the common tasks. The trick is to find the ideal point. A table of 10K records using a native language to identify the correct record would require an average of 2,500 records displayed using the Access navigation buttons(First, Previous, Next, Last). Guessing by selecting the record number, typing over it, and pressing Enter would lessen the records viewed, but is clumsy, involving both the mouse and the keyboard. Having an easy selection process always visible takes space but makes the task easier. Having to click the "Select" tab, do whatever is necessary here, click the "View" tab to see if selection was successful, and repeat if necessary is labor intensive. I work with names (people & business), titles (movies, books, music albums), and bird species. All these may have duplicates and/or alternate names.
Now that the correct record is displayed, I want to view/modify fields not displayed. The sample, "Child Form Same Recordset.zip", seemed to have the solution to modifying the record, but I had questions. That is what this thread is about, I hope.

OldBirdman

[nico5038]

I see you're not grasping the way a tab-control can be used, so I rest my case.

Success with your application.

Nic;o)

[FishVal]

@OldBirdman
@2
Form cannot undo changes in record because changes were made in another form. As I've posted before, it is most likely that form holds some kind of changes log allowing undo one previuos record.
This, BTW, solves your question #1. :)

3) I think this is not needed code. First, the way this sample code was written, it can't be used. The BeforeUpdate only fires when the form is closed, either by "Submit" or "Cancel". No code can use this held value. Adding another control to receive the focus can trigger BeforeUpdate without closing the form. Now varOldValue can be assigned a value. However, the statement "Me.txb = Me.txb.OldValue" in "Sub btnCancel_Click" will do the same thing.

4) I can't reproduce it either, NOW. But I did for several days, thru several reboots.

Well, you are right. It is better to cancel changes on Form_BeforeUpdate to ensure the whole record remains intact after user has cancelled input. Additionally, this prevents creating an empty record when user has cancelled input in child form pointed to new record.

So frmChild1 module will be something like the following

Option Compare Database
 
'Flag to determine whether the record has to be saved or cancelled
'blnCancel=True record saving is not allowed
'blnCancel=False record saving is allowed
Private blnCancel As Boolean
 
Private Sub btnCancel_Click()
    CloseMe
End Sub
 
Private Sub btnSubmit_Click()
    blnCancel = False 'allow the record to be saved
    CloseMe
End Sub
 
Private Sub CloseMe()
    DoCmd.Close acForm, Me.Name
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = blnCancel
End Sub
 
Private Sub Form_Load()
    'all attempts to save record will be cancelled
    blnCancel = True
End Sub
 

Regards,
Fish.

[NeoPa]

@OldBirdman
"OP" is the initials for "Original Poster" (or sometimes "Original Post" - context informs which).

This is a general forum term used to refer to the member who originates, and therefore is the driving force behind, a particular thread. In general, the focus of a thread should be towards the stated needs of the OP.

Hope that helps.