Subform Blues - Data Entry setting itself to 'No' & Requerying 1 subform from another

[Keith Nichols]

Subform Blues - Data Entry setting itself to 'No' & Requerying 1 subform from another

2 Subform problems

I have a data entry subform that is only supposed to show an empty record ready to be populated, and a display records subform that is supposed to show all the records. The subforms are both on the same tab of a tab control on my main form.

Problem 1:
The data entry subform shows all the records rather than a blank record. Something on my main form is causing it to show the records when it should not. Any ideas? The Data Entry is set to Yes.

To try to isolate the problem, I created a new form and added the subform to it where it behaves properly

I then added Me.DataEntry = True to the form open to see if that would solve my problem but it still sets the data entry to no.

If I have the properties box open when in form view of my main form, I can set the data entry to Yes and it works fine until I move to the next record of the main form when it resets to no. Teraing my hair out here.

My final attempt was to search the entire project to see if there is a "DataEntry = False" somewhere but there isn't. What is setting this property? Any ideas where I should look?


Problem 2:

After entering data in the first subform (data entry form), I want to re-query the second subform but I just can't get the syntax right. I have wrestled with the "Syntax for subs" document downloaded from http://www.mvps.org/access/forms/frm0031.htm but to no avail.

My main form is called fdlgPrjDetails, the data entry is via fsubPrjCommentsUsersDataEntry and the subform I wish to requery is fsubPrjCommentsUsers.

None of the attempts below worked giving a cannot find control error.

Private Sub Form_AfterUpdate()
On Error GoTo ErrHandler

Me.Requery

'Me!fsubPrjCommentsUsers.Requery
'Me!fsubPrjCommentsUsers.Form.RecordSource.Requery
'DoCmd.Requery ([fsubPrjCommentsUsers])
'DoCmd.Requery [fsubPrjCommentsUsers]

ExitHere:
    Exit Sub
    
ErrHandler:
    MsgBox Err.Number & " - " & Err.Description & Chr(13) _
    & Chr(13) & "Error in fsubPrjCommentsUsersDataEntry: Err 003"
    Resume ExitHere
End Sub

Any Ideas?

Both problems have me stumped so I'll be grateful to anybody with a scoobie on this.

 

[RuralGuy]

Two forms of syntax for the requery assuming you are on the "other" SubForm:
Me.Parent!fsubPrjCommentsUsers.FORM.Requery
Forms!fdlgPrjDetails!fsubPrjCommentsUsers.FORM.Requery

Any chance you could post a stripped down version of your db with any sensitive data removed? There is something going on with the Data Entry that is not easy to determine without the db.

 

[Keith Nichols]

Thanks RG.

the referencing looks like the answer I need to that problem. One day I promise to understand all this sort of thing but it is hard when you are in the middle of a redevelopment and 1 change cascades mayhem throughout the db - well it seems that way anyway!

BTW, in your code snippets, you capitalized 'FORM'. Is this a protocol, or required, or a personal quirk of your coding? Just curious on that.

The stripped down DB sounds like the way to go with the other problem. The whole thing is fairly large and stripping down will be a struggle. It might even be that problem reveals itself during the process. I will try to create a small enough version with the relevant components & the error tomorrow.

Regards,

 

[RuralGuy]

If you can't get it shrunk down enough to post then you can send it to Rural Guy at Wild Blue dot Net if you want and I'll take a look at it. I capitalize FORM in many of my posts because that is an area that most people overlook. It is not necessary to capitalize but it draws attention to that area of the string as you noted.

 

[Keith Nichols]

Exporting the relevant bits to a new db was easier than I thought it would be and after striping out enough c*** to drop the size, I am, sort of, gratified that the problem persists


BTW, the DB throws an error when opening that it cannot change to the H drive. There is nothing on my H drive relevant to this database and so I do not know where this is coming from. It doesn't seem to cause any problems, but if you can spot the source of this behaviour, I'd be grateful.

The DB opens with the form in question. Go to the User Comments tab and you will see the data entry subform at the top and the display comments subform underneath.

Many thanks for your time, the good work, and the patience you show to many a struggling Accesser.

Regards,

 

[Keith Nichols]

referencing still giving errors

Hi RG,

I have given the referencing a go and still have problems. I checked the spelling of my subforms and the code lines I pasted in yet the 2 versions of referencing both threw up the same error:

2465 - EDT DB can't find the field 'fsubPrjCommentsUsers' refered to in your expression

Is it significant that the DB appears to be looking for a field rather than a subform? I would have expected the error to read "control" rather than "field" as a subform is not a field as such.

The code is in the after update event of the data entry subform as shown below (not in the sample db I posted, the master back at my PC)

Private Sub Form_AfterUpdate()
On Error GoTo ErrHandler

Me.Requery

Me.Parent!fsubPrjCommentsUsers.Form.Requery
'Error 2465 - EDT DB can't find the field 'fsubPrjCommentsUsers' refered to in your expression

Forms!fdlgPrjDetails!fsubPrjCommentsUsers.Form.Requery
'Error 2465 - EDT DB can't find the field 'fsubPrjCommentsUsers' refered to in your expression


ExitHere:
    Exit Sub
    
ErrHandler:
    MsgBox Err.Number & " - " & Err.Description & Chr(13) _
    & Chr(13) & "Error in fsubPrjCommentsUsersDataEntry: Err 003"
    Resume ExitHere
End Sub

More confused than usual today

 

[RuralGuy]

Hi Keith,
The generic syntax for the reference you want is:

Me.Parent!SubFormControl.FORM.Requery
or
Forms!MainForm!SubFormControl.FORM.Requery

Forms are displayed on other form by means of a SubFormControl. This SubFormControl name defaults to the name of the form it displays, but it is not manditory. In your case, the SubFormControl is named Child742 and *not* the name of the form it displays! Therefore, the actual syntax that works from the fsubPrjCommentsUsersDataEntry form is:

Me.Parent!Child742.Form.Requery
or
Forms!fdlgPrjDetails!Child742.Form.Requery

As for the DataEntry property that gets reset, I’ve commented out with ‘$$$ sections of code that are causing this to happen in your FilterForm subroutine. It has to do with your filters in some way and I will leave it to you to pin down the issue. You’ll see I have a Diagnostic MsgBox display the state of the DataEntry property. The MsgBox is in the Current Event of the fsubPrjDataEntryCommentsUsers form. You may wish to explore why you get so many Current events when there is a problem.

I’m pretty sure that setting the RowSource of a ComboBox will cause it to Requery making the .Requery lines redundant. You may wish to verify that.

I’ve put some additional notes behind ‘## tags in your code. Hopefully you will find these notes useful and you will be able to pin down your problem with the DataEntry property.

I didn’t really study what you were doing with all of those string manipulations for your filters but setting filters causes requeries and I’ve tried to avoid them when I code.

 

[Keith Nichols]

Thanks RG.

The time spent is very much appreciated and the comments will be useful.

It may be that the structure of my form is clumsy in the it uses 5 comboboxes with some degree of cascading along with a frame select group. This give the form a high degree flexibility for quickly getting to the project you need from a number of different starting points (department, project status, project stage, etc), but to code for that, I needed to go round in many circles. It is certainly well beyond the remit of this query (or even this forum!) to look into that.

Thanks for the subform pointer. I should have spotted that one as I am aware of the control name verses the subform name. I usually set these the same, but in a recent re-build, I missed this step out.


Once again, many many thanks.

 

[RuralGuy]

Hi Keith,
I would recommend that the SubFormControl *not* be named the same as the form it displays; it is then very obvious what control is being referenced. I also highly recommend that all controls *not* be named the same as the field to which they are bound for the same reason. I would adopt one of the common naming conventions. The code is *much* easier to read. Good luck with the rest of the project.

 

[Keith Nichols]

Hi RG,

Having worked thorugh your comments, notes and corrections, I can see you spent a considerable time on it so many many thanks for that.

I can also see that, even though I have created something useful and flexible that is being appreciated by the users, I have a long way to go before I understand all (or enough at least!) of the subtelties and conventions of Access.

I take the point about the subform being named differently from the control on the form and will look into that.

The number of set focus and similar repeated comands was due to single snippets of code being copied for multiple uses where they will runb sequentially. I have deleted the unrequired setfocus and requery lines from the code.

The form has a lot on it and, at 22 routines, is probably a bit on the unwieldy side of things. However, with thanks for your input, it now works correctly.

I haven't the time right now to attempt a more rational rebuild (I'm actually doing that for the whole database at the moment and on a deadline) so to get round the data entry setting itself to 'No', I used me.dataentry = true in the subform on current event. As noted, this works but is a fudge around the problem rather than a proper solution. As my experience progresses, I am sure that I will resolve this issue in a more suitable way.

Once agan, thanks.

 

[RuralGuy]

Thanks for posting back Keith. Lots of luck with the system and have fun. I have also been known to use such "patches" to move a project along.