Help with subform on load

[Lol999]

Hi all, for those of you who experienced my previous horrors for equipment control I've gone back to the drawing board, cut out all the inessentials and got a much simpler design going.
I have on the form a couple of cascading comboboxes which filter the products to display dependant upon category for the user to choose from.
These work great in themselves in that when I choose an item the "loan" history for that item appears in the subform.
The problem I have is that when the form is first loaded the subform is displaying the history for the first item in the Product table.
I would much rather the subform displayed nothing only empty fields.

I've tried a number of solutions and none worked. Anyone have any ideas?

I have attached the offending article for your perusal.

Many thanks, Lol

 

[isladogs]

Hide the subform until the main form controls are populated

Please mark the previous thread as solved

 

[Lol999]

Hi Pat, I've just made another post which addresses other problems I suspect are overlapping this one.

Thanks, Lol

 

[Lol999]

Hi Pat, what I discovered was that all the examples I have sen on the internet have the subform recordsource being set in the AfterUpdate event of the first combobox when in actual fact it needs to be done in the AfterUpdate event of the SECOND combobox!

That and a few syntax errors now resolved and it works.

The code is below for anyone interested:

Private Sub cboCategory_AfterUpdate()
Me.cboCategory.SetFocus
    ' Set source records for use to choose product based upon category selected in cboCategory
     Me.cboProduct_Picker.RowSource = "Select Tbl_Product.ID_Product, Tbl_Product.Part_No, Tbl_Product.Details From Tbl_Product Where Tbl_Product.Tool_Category='" & Me.cboCategory.Text & "';"
     Me.cboProduct_Picker.Requery
    
    
End Sub




'------------------------------------------------------------
' cboProduct_Picker_AfterUpdate
'
'------------------------------------------------------------
Private Sub cboProduct_Picker_AfterUpdate()
On Error GoTo cboProduct_Picker_AfterUpdate_Err
Me.txtProduct_Details.Value = Me.cboProduct_Picker.Column(2)
Me.cboProduct_Picker.SetFocus
      Debug.Print Me.cboProduct_Picker.Value, ID_Product
    
If IsNull(Me.cboProduct_Picker.Value) Then
   Me.frm_Current_Location_Subform.Form.Filter = ""
   Me.frm_Current_Location_Subform.Form.FilterOn = False
Else
  Me.frm_Current_Location_Subform.Form.Filter = "[ID_Product] = " & Me.cboProduct_Picker.Value
  Me.frm_Current_Location_Subform.Form.FilterOn = True
End If
Me.frm_Current_Location_Subform.Form.Recordset.MoveLast
   Exit Sub

    ' DoCmd.SearchForRecord , "", acFirst, "[ID_Product] = " & Str(Nz(Screen.ActiveControl, 0))


cboProduct_Picker_AfterUpdate_Exit:
    Exit Sub

cboProduct_Picker_AfterUpdate_Err:
    MsgBox Error$
    Resume cboProduct_Picker_AfterUpdate_Exit

End Sub

 

[Lol999]

Hi Pat, what can I say, it works. Perhaps I didn't explain myself properly but the recordset seemed to be a "selection" behind where it should be, and as such the subform was filtering a "move" behind.
I "think" it was because my comboboxes were unbound, and what I should have done was bound my table to the "many" table rather than the "one".

However, it works and will do what I want it to so I live and learn, albeit slowly.

I've attached the latest version, unfinished but functional for you to look at if you're interested.

Thanks, Lol

 

[Lol999]

Evening Pat, to a degree you are right, something is not recording properly.

I'll look at it tomorrow evening, too tired today.

 

[Lol999]

This direction abandoned.