Sub-form data entry

[GMontreal]

I am new in this forum. Before posting I searched for similar topic and I could find any information about my specific issue. If I missed it I apologize.

I have a single form (customer orders) with its default view set to "single form", containing a sub-form (customer order details) whose default view is set to "continuous form" in order to view multiple records. I manage this combination form in access (.accdb) 2007 via visual basic with combo boxes and form buttons.

When I want to enter new data into the sub-form via "DoCmd.GoToRecord , , acNewRec" command from visual basic then I get a new row in order to add/select data plus an additional blank row underneath. This blank row confuses my users.


Is there a way to prevent the blank row from appearing? Alternatively can I switch, via VB, the default view of my sub-form to “Datasheet” just before entering the data (this eliminates the empty row) and then switch, via VB, back to “Continuous form” right after saving the data?
I also found information about using a split form, which I find very useful for certain cases, but this, as I understand it, cannot be used as sub-form presently.

Thanks for reading my post. Your input will be greatly appreciated

 

[KenHigg]

You may try manipulating the 'data entry' property of the subform.

 

[GMontreal]

Thanks for answering.
I have the 'data entry' of the sub-form set to off and manipulate it via VB. If I set the data entry to Yes I still see the second empty row.
George

 

[KenHigg]

There are several form properties around the data entry property. I'm thinking experimenting with them should yield a solution...

 

[GMontreal]

I have tried modifying the data entry property of the sub-form. Unfortunately this did not solve my problem.
George

 

[Steve R.]

A potential approach.
1. Lock the subform, set the "allow addition" property to "no". That will eliminate the blank line.
2. Make a pop-form that is activated by either a command button or double clicking.
3. On the "close event" of the pop-up form execute the refresh event to the underlying form. It would look something like this "Forms("NameOfForm").Refresh"

PS: The underlying form and the pop-up form cannot utilize the same data source; that will create a "write" error.

 

[GMontreal]

Thanks to all for replying. Back to books and reading forums in search of an answer
George.

 

[Steve R.]

The approach I outlined above can be improved through the use of unbound controls in the pop-up form. To my knowledge this eliminates the potential for a "write" error. The examples below constitute a basic outline. That means supplementary code may be needed to get full functionality.

1. On the main form's "current" or "activate" events have code that disables additions to the subform. To a degree this code could be considered redundant.

Private Sub Form_Current()
    Me.Child1.Form.AllowAdditions = False
End Sub

2. Choose a field on the subform for double clicking (or have a command button on the main form) to activate the pop-up form. This code could also be modified to allow editing of existing records in the subform.

Private Sub RepsNameIDnum_DblClick(Cancel As Integer)
    Me.Parent.Child1.Form.AllowAdditions = True
    DoCmd.GoToRecord , , acNewRec
    DoCmd.OpenForm "NewData_PopUP", acNormal, , , acEdit
End Sub

3. After data entry is completed for a control, the code below will transfer it to the subform. This code can also be in the "close" event of the pop-up form. "Me.Text0" is a control on the pop-up form.

Private Sub Text0_AfterUpdate()
    Forms!mainform!Child1.Form.RepsName = Me.Text0
End Sub

4. Pressing "close" on the pop-up form disables the ability to add new data to the subform and then closes the form.

Private Sub Command2_Click()
    Forms!mainform!Child1.Form.AllowAdditions = False
    DoCmd.Close
End Sub

Using a pop-up form is also easier for data entry than playing with the fields in a subform.

 

[GMontreal]

Thanks Steve, I am eager to try out your proposal. It will take some time though, as I am leaving soon for holidays. I will be back on the 26th of November, but I will be following the forum and taking note of any suggestions. Thanks to ALL
George

 

[GMontreal]

Thank you Steve R. and sorry to reply so late! I appreciate your support which helped me get started.

My original setup was a main (single) form containing a customer order with a (continuous form) sub form containing all the products relating to that particular order of the particular customer. This sub form was used for both data entry and data edit functions. The problem with this setup was that during data entry into the sub-form via "DoCmd.GoToRecord , , acNewRec" command from visual basic I used to get a new row in order to add/select data plus an additional blank row underneath. This blank row confused my users.

The following is what I have done:
The main (single) form was not changed. The (continuous) sub form’s code was changed and it now has four command buttons. One button is for consulting available product inventory (other than those already selected), the second button is to perform deletion of a selected product, the third button is for data entry and the fourth for data edit. The third and the fourth command buttons are the ones that have been changed.
The (third) command button, which is, for data entry disables all controls on the main and the sub form plus it opens a (data entry dedicated) single, pop-up form having ‘data entry’ property set to ‘yes’ and ‘allow additions’, ‘allow deletions’ and ‘allow edits’ properties set to ‘no’. This way the opened dedicated pop-up form has now only one row for data entry and three command buttons, one for consulting available product inventory (other than those already selected), one for cancelling data entry and one for saving the new data. All functions are re-written in VB to open and close this dedicated data entry pop-up form.
The (fourth) command button, which is, for data editing disables all controls on the main and the sub form plus it opens a (data editing dedicated) single, pop-up form having ‘data entry’ , ‘allow additions’, ‘allow deletions’ and ‘allow edits’ properties set to ‘no’. This way the opened dedicated pop;-up form has now only one row for data editing and two command buttons, one for cancelling data editing and one for saving the edited data. All functions are re-written in VB to open and close this dedicated data entry pop-up form.

These are the very general lines I followed with a lot of VB code changes. Now the Customer Orders module works to my satisfaction. Thanks again to Steve R. and all others for contributing.