Setting subform recordsource.

[Lol999]

Okay, so I'm trying to set the record source of a subform on load event of the main form. I have tried many permutations, some that supposedly work.
main form is called frmBooking, subform is called frm_Location
Here is what I have and I'm still getting errors:
"The expression you entered refers to an object that is closed or doesn't exist"

Private Sub Form_Load()
Dim PartNumber As String
Me!txtPart_Number.SetFocus
PartNumber = Me!txtPart_Number.Text
Dim SQL1 As String
SQL1 = " SELECT Tbl_Location.Location_Category, Tbl_Location.Contract_Number, Tbl_Location.Contract_Details, Tbl_Location.Van_Reg, Tbl_Product.Part_No, Tbl_Current_Location.Date_Loaned, Tbl_User.Details, Tbl_Current_Location.Comments " & _
" FROM Tbl_User INNER JOIN (Tbl_Location INNER JOIN (Tbl_Product INNER JOIN Tbl_Current_Location ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product) ON Tbl_Location.ID_Location = Tbl_Current_Location.ID_Location) ON Tbl_User.ID_User = Tbl_Current_Location.ID_User " & _
" WHERE (((Tbl_Current_Location.ID_Product)=[Tbl_Product].[ID_Product]) AND (Tbl_Product.Part_No)=""" & PartNumber & """);"

    With Forms![frmBooking]![frm_Location]
      .Form.RecordSource = SQL1
         .Requery
    End With
End Sub

 

[Minty]

Close - but not quite the syntax from here http://access.mvps.org/access/forms/frm0031.htm is Me!Subform1.Form.RecordSource

So it should be
Me!frm_Location.Form.RecordSource = SQL1

 

[Lol999]

Hi Minty and thanks for the reply but I'm still getting the same warning using this:
Me!frm_Location.Form.RecordSource = SQL1
Me!frm_Location.Form.Requery

Thanks, Lol

 

[bastanu]

The subform will load before the main form therefore the error. See this link for more info:https://support.office.com/en-us/ar...ects-e76fbbfe-6180-4a52-8787-ce86553682f9#bm3.

Why isn't the subform bound to your recordsource?

Cheers,
Vlad

 

[Lol999]

I'm trying to set the record source of the subform to an sql statement which filters dependant upon the value in a text box.
Since the subform loads first I have set the procedure to run from the afterupdate event of the text box the sql takes the value from.

Hence I am using:

Me!frm_Location.Form.RecordSource = SQL1
Me!frm_Location.Form.Requery

and still getting nowhere :banghead:

 

[Minty]

Set a default value in your initial recordset, then change it in the after update of the text box.

You do know that you can use more than one field in the Child/Parent properties of the Sub form ?

Which would probably remove the need for the VBA...

 

[Lol999]

In truth Minty I wouldn't know where to start. This project is going to be the death of me.

 

[bastanu]

Save your SQL1 statement without the WHERE clause as a query.
Set the subform's record source to that query (that would be the default MInty mentioned). Remove any code you might have in the form load event that attempts to do anything with the said subform.

Now on the AfterUpdate event of the textbox (I assume it holds a part number) you can reset the recordsource of the subform.

A better approach would be to start with the proper query that references your text box control and simply issue a Requery in the afterupdate event:

SELECT Tbl_Location.Location_Category, Tbl_Location.Contract_Number, Tbl_Location.Contract_Details, Tbl_Location.Van_Reg, Tbl_Product.Part_No, Tbl_Current_Location.Date_Loaned, Tbl_User.Details, Tbl_Current_Location.Comments " & _
" FROM Tbl_User INNER JOIN (Tbl_Location INNER JOIN (Tbl_Product INNER JOIN Tbl_Current_Location ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product) ON Tbl_Location.ID_Location = Tbl_Current_Location.ID_Location) ON Tbl_User.ID_User = Tbl_Current_Location.ID_User " & _
" WHERE (((Tbl_Current_Location.ID_Product)=[Tbl_Product].[ID_Product]) AND (Tbl_Product.Part_No)=""" & Forms!YourFormName!PartNumber & """)

Cheers,
Vlad

 

[Lol999]

Thanks Vlad I'll try it later.

 

[Cronk]

I think the last line should be

" WHERE (((Tbl_Current_Location.ID_Product)=[Tbl_Product].[ID_Product]) AND (Tbl_Product.Part_No)=""" & Forms!YourFormName!PartNumber & "")"

ie the last bracket moved inside the last quote

 

[JHB]

Going back to the first code you posted, it should be okay, so in exactly which code line do you get the error message?
Are you sure the object container for the subform is called "frm_Location"?
Else post your database with some sample data.

 

[Lol999]

Let's mark this closed thanks I have taken a different tack.