Form OnCurrent event bug?

[morsagmon]

Hello all,
I have a frmMain with two peer subforms: sfrmSupplements and sfrmProducts.
sfrmProducts shows the "many" records per the "one" current record selected in sfrmSupplement.
In order to establish this relationship, I have the following event code of sfrmSupplements:

Private Sub Form_Current()
    
On Error GoTo Form_Current_Error

    Dim strSql As String
    
    strSql = "SELECT tblProducts.Product_Code, tblProducts.Supplement_Code, tblProducts.Units_Code, tblProducts.Number_of_Units, tblProducts.Bottles_in_Stock, tblProducts.Stock_Date, tblProducts.Product_Price, tblProducts.Comments_Medium FROM tblProducts WHERE tblProducts.Supplement_Code = '" & Me.Supplement_Code & "'"

    Me.Parent.Form!sfrmProductsInSupplements.Form.RecordSource = strSql
    Me.Parent.Form!sfrmProductsInSupplements.Form.Requery
    
Form_Current_Exit:
    Exit Sub
    
Form_Current_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Current of VBA Document Form_sfrmFoodSupplements"
    Resume Form_Current_Exit
    Resume
End Sub

The above code works fine and does the trick - except for the first time when the form is opened. Upon opening the form, I get this error message:

Runtime error 2455:
You entered an expression that has an invalid reference to the property Form/Report.

The error is captured on this line:

Me.Parent.Form!sfrmProductsInSupplements.Form.RecordSource = strSql

Ignoring the error (or bypassing it with On Error Resume Next) is possible, but then the sfrmProducts records are not bound to the first record of sfrmSupplements that receives the focus upon entry. Moving away to any other record clears the problem and the whole thing works fine.

Any suggestions?

Thank!
Mor

 

[KenHigg]

For starters I'm not sure I'd do a new sql statement/ requery thing everytime, why not just use the code as a param or filter... ?

 

[JamesMcS]

Is it the last form. you've got in that line?

 

[JamesMcS]

I'm pretty sure it is, thinking about it... I found a thread a while back that had a link to a word doc, with the exact syntax for referring to other forms and objects depending on where you are. Might be worth a search....

 

[morsagmon]

Ken and James, thank you for your prompt response.
Ken, I'm not sure I understand your suggestions. Can you please be more specific?
James, I guess if I had a syntax error, it would fail every time, not just on the first run. Or have I misunderstood you? I did find http://www.mvps.org/access/forms/frm0031.htm for exact syntax between forms.

Thanks!!
Mor

 

[SOS]

Well, for one - you don't need the !Form after the .Parent part. If you are referring to another subform on the parent from a subform you would use:

Me.Parent.sfrmProductsInSupplements.Form.RecordSource = strSql

 

[morsagmon]

SOS,

Thank you! I corrected my syntax.
No change in the above described error, though.

I though I was trying to achieve a very basic and popular task: two subforms with one-to-many relationship. How tough does it have to get?

Thanks!

 

[SOS]

Okay, one more thing that may help in the fixing of this, or something.

Subforms are loaded BEFORE the main forms. So, if you are setting something based on something in the MAIN form's recordset, then it can't get it and hence the error because it hasn't loaded yet at the time the subform is trying to get it. You should set the subform to have a valid recordsource in design view, and then set the thing in the main form's load event so that it can load without a problem and then reset itself.

 

[morsagmon]

SOS,

But my main form has no recordset. It is just a blank form to accommodate several subforms on it.
From your advice I suspect that the path between the two subforms (going "up" through the ".parent") when the mainform (parent) has not yet loaded, causes this error. In this case, perhaps I can insert a little delay at the beginning of the Form_Current sub? any other ideas on how to handle this?

Anyway, am I the first person that's using a blank main form to accommodate communicating subforms on it?? Has this simple task never been done before in Access? I feel as if we're inventing the wheel here. What am I missing?

Many thanks!!

 

[JamesMcS]

Is there any way to base the main form on some kind of 'global table' that contains all your link data? That would get around the subform-loads-before-main-form problem, and you could just use a combo box to select the record on the main form, and the subforms would both update OK.

 

[morsagmon]

James,

Thank you for the idea.
This would cause more overhead than it would help.

The workaround I found is:
On the open event of the main form, I'm re-calling the OnCurrent sub of the sub-form (through a function).

Thanks!
Mor

 

[JamesMcS]

Good stuff, that's a cunning one ]

Have a fun filled week!

 

[gemma-the-husky]

I think I do something similar

Rather than set a field in the parent form, though, i tend to use a global variable, so I dont get the problem you describe. You can also address the other subform form one of the subforms with a simpler syntax:

parent!othersubformcontrolname.requery


ie use the name of the control on the parent form.

 

[morsagmon]

Gemma, thanks.

Point is, your suggestion also travels through the "parent" form, and if it not yet loaded - it fails the same way.

Thanks!
Mor

 

[gemma-the-husky]

so it does

i thought i had better look and see exactly what i did do, in view of your comment, rather than guess!

so, I am doing something very similar to what you are doing

i have no problems referencing the parent form from the subform, but I do have problems with the other subform, the first time.

in the current event for the first subform I do this to display the other subform, but as you say - you need to trap the startup error, hence the error trap


On Error Resume Next
Me.Parent.subdetails.Requery

and I actually requery the subdetails form in the open event of the main form - which does the same as you are doing (in the current event) - because ther main form isnt bound, and therefore it only fires once.

I suspect your code is probably better, as it does exactly the same thing each time, doesnt it!

--------------
just one other point - not sure if its germane to you.

I actually use the parent container, to change dates, and things like that, which causes the data in the "first" subform to change. Now, if there are no items in the "first" subform (which could happen in my case, if i go to an unusual date) then the current event in the first subform doesnt fire, and the "right" subform doesnt get refreshed, so I had to do some fiddling in the parent to check whether there were any items in the "first" sub, and if not clear the "second" sub.

Hope this makes sense - not sure if you have this issue.

 

[morsagmon]

Hey Dave.

Thanks.
I don't (yet) have your issue of empty subform.
I rather am satisfied with the way I got this to work.
Now I'm hit by another wall - see my new post on the matter:
http://www.access-programmers.co.uk/forums/showthread.php?p=956777#post956777

Take care!
Mor