updating a subform recordsource

[Gary]

Hello,

I'm having some trouble getting a subform's recordsource to lock to a
specific #.

What I want to do is when the user enters the Order #, the form is called.
Sometimes we ship to multiple locations and we need to print tags for the
pallets with Order #, Customer's address, etc. I built a query with the info
I need, but it will return every single row in the database, rather than
just filtering on the one #. I can filter it in the query fine, but the form
is not liking it. Does anyone have any suggestions that might help. My
form's Form_Load code is as follows:

Private Sub Form_Load()
On Error GoTo err_FormLoad
Dim SQLstmt As String
Dim WOLookup As Long
Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("qryPalletTag")

SQLstmt = "SELECT dbo_PACKING_SLIPS.Order_No,
dbo_PACKING_SLIPS.SlipCount, dbo_PACKING_SLIPS.ShipTo_Name FROM
dbo_PACKING_SLIPS"
SQLstmt = SQLstmt & " WHERE dbo_PACKING_SLIPS.Order_No=" & glngWOLookup

[Forms]![40a_sfrmCreateATag].Form.RecordSource = SQLstmt
[Forms]![40a_sfrmCreateATag].Form.Requery

qdf.SQL = SQLstmt
Set qdf = Nothing
SQLstmt = ""

exit_FormLoad:
Exit Sub
err_FormLoad:
On Error Resume Next
GoTo exit_FormLoad

End Sub

Thanks!
Gary

 

[chakatheapeman]

Parent Child Relationship

Sounds like you just need to set up the parent child relationship in the sub form. This is handled by Access with out code. Click on the sub form on the main form to get to the properties of the sub form object. You don't want the sub form itself.

You could do what you proposing, however you will probably find it easier to do it the Access way.

 

[Guest]

In design view open the main form and on the menu click on VIEW -
Properties. Click on the upper left corner of the subform. Make sure the
Master and Child Links are set to the same field that relates the two tables.

 

[Gary]

Ahhh....brilliant! Thanks Karl!

In design view open the main form and on the menu click on VIEW -
Properties. Click on the upper left corner of the subform. Make sure the
Master and Child Links are set to the same field that relates the two tables.

Hello,

I'm having some trouble getting a subform's recordsource to lock to a
specific #.

What I want to do is when the user enters the Order #, the form is called.
Sometimes we ship to multiple locations and we need to print tags for the
pallets with Order #, Customer's address, etc. I built a query with the info
I need, but it will return every single row in the database, rather than
just filtering on the one #. I can filter it in the query fine, but the form
is not liking it. Does anyone have any suggestions that might help. My
form's Form_Load code is as follows:

Private Sub Form_Load()
On Error GoTo err_FormLoad
Dim SQLstmt As String
Dim WOLookup As Long
Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("qryPalletTag")

SQLstmt = "SELECT dbo_PACKING_SLIPS.Order_No,
dbo_PACKING_SLIPS.SlipCount, dbo_PACKING_SLIPS.ShipTo_Name FROM
dbo_PACKING_SLIPS"
SQLstmt = SQLstmt & " WHERE dbo_PACKING_SLIPS.Order_No=" & glngWOLookup

[Forms]![40a_sfrmCreateATag].Form.RecordSource = SQLstmt
[Forms]![40a_sfrmCreateATag].Form.Requery

qdf.SQL = SQLstmt
Set qdf = Nothing
SQLstmt = ""

exit_FormLoad:
Exit Sub
err_FormLoad:
On Error Resume Next
GoTo exit_FormLoad

End Sub

Thanks!
Gary