Subforms won't cooperate

[Paul H]

I have two subform that interact. The first one searches a table for using one or two strings to filter a list. This list is then display in a combo box for the user to pick the correct diagnosis which populates the code and description combo boxes. Once the user is happy with the selection, they click they click the select code button which populates another subform which is a continuous form displaying many records. As long as the focus is on the new/blank record this works perfectly, but if the focus is on an earlier record, that record is replaced with the new code and description. I want to make sure this one happens on the new blank record, but I can get the gotoRecord acNew command to work. I've tried this 10 different ways, so I'll only show you a few.

  Form_Clinical_Summary_ICD10.txtFromDate.SetFocus
            DoCmd.GoToRecord acDataForm, Form_Clinical_Summary_ICD10, acNewRec
            Form_Clinical_Summary_ICD10.cbo_ICD10 = Form_frmLookup_ICD10.cboICD10
            Form_Clinical_Summary_ICD10.cboICD10_Description = Form_frmLookup_ICD10.cboICD10.Column(1)

This fails on line 2. with this message.


So I tried it in quotes, with and without the Form_ prefix and get this.


I'm going round in circles and periodically crashing my program.

Any ideas or questions?

[RuralGuy]

You should *not* be using that particular syntax in your code but go through the Forms collection: http://access.mvps.org/access/forms/frm0031.htm

[Paul H]

RG, I appreciate the answer and I've tried to do this in any number of iterations without success. I don't normally do this, but if you can solve what I see is a small problem, I would greatly appreciate it. The two forms in question are both subforms of a larger form. The code copies the data from one subform to another. Before that happens I want to put the focus on the new record just in case the user has been touring around on some older records. These two lines, even in their bad syntax run perfectly well. I know this is essentially a repeat of my first post, but I'm ready to give up. I hate to depend on the user to put the focus on the new record, but I may have to.

 Form_Clinical_Summary_ICD10.cbo_ICD10 = Form_frmLookup_ICD10.cboICD10
            Form_Clinical_Summary_ICD10.cboICD10_Description = Form_frmLookup_ICD10.cboICD10.Column(1)

I even tried putting this, the same code from the Form_Load into the Form_LostFocus event, but it doesn't seem to even run a test message box.

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

Well, there you have it.

[ssanfu]

Paul,
What Allan (RG) meant was that using the prefix "Form_" in code to refer to forms is something you should never do. Access, internally and behind the scenes, adds a prefix "Form_" to the form name. You, the programmer, should refer to forms using the forms collection.

You have

Form_Clinical_Summary_ICD10.cbo_ICD10 = Form_frmLookup_ICD10.cboICD10

You should have something like

Forms!Clinical_Summary_ICD10.cbo_ICD10 = Forms!frmLookup_ICD10.cboICD10

But I always have problems with referring to subforms, so it takes me a while to get it right.

they click they click the select code button

Don't know if the button is on the main form or subform1 ("frmLookup_ICD10"???). If the code is on the main form, you might try this syntax:
Me!Subform2.Form!ControlName = Me!Subform1.Form!ControlName

So if the first subform is "frmLookup_ICD10" and the second subform is "Clinical_Summary_ICD10" you might try

Me!Clinical_Summary_ICD10.FORM!cbo_ICD10 = Me!frmLookup_ICD10.FORM!cboICD10

(you were not very specific: main form name or subform names, button location (main/subform) or the button code)

[Paul H]

The select code button is on the lookup subform and copies the data just fine. When I use the new syntax.

Forms!Clinical_Summary_ICD10.Form!cbo_ICD10 = Forms!frmLookup_ICD10.Form!cboICD10

I get this error.

While this works.

Form_Clinical_Summary_ICD10.cbo_ICD10 = Form_frmLookup_ICD10.cboICD10

Running my original code works fine. What I really want to do is move the focus of form Clinical_Summary_ICD10 to record acNewRec so that the data goes in the right place. Nothing I have tried seems to work.

[RuralGuy]

In your last post you stated that it works fine *and* you get "this" error. Very confusing.

[Paul H]

Using the original syntax it works fine. The new quoted syntax produces the error.

[RuralGuy]

I believe you have two SubForms. Those are displayed on your MainForm by the use of a SubFormControl. What is the name of your MainForm and what are the name of the two SubFormControls?

[Paul H]

Main Form: Clinical_Summary

The subform control is in bold. The actual form is in blue inside the parens. There is some inconsistency in the naming convention that I usually try to avoid. Both subforms are displayed simultaneously.

Subform1: subfrm_Clinical_Summary_ICD10 (Clinical_Summary_ICD10)
Subform2: subfrmLookup_ICD10 (frmLookup_ICD10) This has the button with the code which copies from Subform2 to Subform1

[RuralGuy]

Based on those names and the location of the button, this should be the code behind the button to copy the value.

   Me.Parent.subfrm_Clinical_Summary_ICD10.FORM.cbo_ICD10.SetFocus
   DoCmd.GoToRecord , , acNewRec
   Me.Parent.subfrm_Clinical_Summary_ICD10.FORM.cbo_ICD10 = Me.cboICD10

Please try it and let us know the results.

[Paul H]

I had high hopes for this one. It ran with no errors, but it did not perform the 2nd line.

DoCmd.GoToRecord , , acNewRec

It stayed right on the 3rd record back and updated that record. Nothing in the new record. Actually I get the same results if I only run line 3.

Me.Parent.subfrm_Clinical_Summary_ICD10.FORM.cbo_ICD10 = Me.cboICD10

I have noticed the focus never goes to subfrm_Clinical_Summary_ICD10.FORM.cbo_ICD10

When I run this line by itself, the focus stays on the button I just clicked.

Me.Parent.subfrm_Clinical_Summary_ICD10.Form.cbo_ICD10.SetFocus

I can set focus on anything on the Me. form, but not the destination form, so the DoCmd.GoToRecord has no effect.

I wish I could send this to you but it's full of sensitive information and has about 40 tables with a SQL Server back end.

I'm calling it a day. I'm grateful for all your assistance so far. I hate to give up on this, but I'm willing if your willing.

[RuralGuy]

Call it a day Paul. There are many ways to skin a cat. You might try:
Me.Parent.subfrm_Clinical_Summary_ICD10.SetFocus

and see what happens.

[Paul H]

And we have a winner!

 Me.Parent.subfrm_Clinical_Summary_ICD10.SetFocus
            DoCmd.GoToRecord , , acNewRec
            Me.Parent.subfrm_Clinical_Summary_ICD10.Form.cbo_ICD10 = Me.cboICD10
            Me.Parent.subfrm_Clinical_Summary_ICD10.Form.cboICD10_Description = Me.cboICD10.Column(1)

Would have gotten back to you sooner, but we had a snow day yesterday. Glad to bring this to a happy conclusion.

[RuralGuy]

Outstanding! Thanks for posting back with your success and solution.