Access updating a subform's recordsource using VBA

I'm thinking that your form is closed.  Be sure that frm_booking_summary2 is open when this function is run.
Also you don't need brackets around RecordSource since it's a property.
You could dispense with the other brackets as well.  Brackets would be required if you have something like spaces or symbols in the names of your objects.

Forms!frm_booking_summary2!sfm_input_costs.Form.RecordSource = strSQL

Select allOpen in new window

Thanks for the advice. However, the form is open.

Is this function on a separate module or in the form itself?  Is it being run on the form's OnOpen event?  If so, the subform would not have loaded yet.  Use the form's OnLoad event instead.

It's a separate module, which is run every time a user updates a tickbox on the form. It's in a separate module because it is run by changing the values of several tickboxes on that form.

Perhaps the problem lies in your strSQL string.  Why do you need to update the recordsource when a checkbox is checked anyway?  Can you upload a bare-bones version of your database (remove any privacy info) so that we can see what's going on?

Try recreating the line referencing the subform in the Expression Builder, to make sure there are no typos.  Also, what is the SQL string?  That may be the problem.

IrogSinta - I need to change the recordsource each time because the SQL string will be different, so I need to change the recordsource to reflect that. I have changed the strSQL string to be "select * from tbl_booking" and it still produces the error.

Helen - that is how I got the expression in the first place, hence the plethora of square brackets.

" I need to change the recordsource each time because the SQL string will be different,"
Consider Filtering the subform instead of resetting the RecordSource each time.

mx

mx - yes, good idea. I could try that, but I really want to know why my solution doesn't work. But I will do as you say as a good alternative, thanks.

"It's a separate module, which is run every time a user updates a tickbox on the form."
In a regular vba module that is not a Form module?
And tickbox is on the Form that has this subform ?

Actually, Are you *sure* the Source Object (which would be frm_input_costs2) set on the Subform Control (sfm_input_costs) ?

Everything "looks" OK ... so, something else is going on ...

mx

Overall, manipulating the Filter property is more efficient that setting the RecordSource, because each time you set the RS, Access has to rebind all of the controls, and other overhead.

mx

I tried that, but of course come back to the original problem, but this time when trying to set the Filter property of the subform

I've attached the database - in order to see the problem, open the one form on the Switchboard and chose an Accommodation Type, then tick the tickbox next to it
legends-of-rock---Copy--2-.zip

I'm confused. When I click the check box, a query opens.

Private Sub chk_accommodation_AfterUpdate()
If Me.chk_accommodation = -1 Then
    Me.chk_accommodation_type = 0
    Me.chk_nights = 0
    'Me.chk_accommodation = 0
    DoCmd.OpenQuery "qry_list_bookings_accommodation"
End If
End Sub

And this form:

frm_input_costs2

is missing ...

I wasn't concentrating - I was watching the Paralympics closing ceremony! Sorry!

In cutting down the size of the database to send to you, I accidentally deleted the subform. The attached version now has it.

So now, when the form opens, go to the "Type" dropdown and then tick the tickbox and the fault will appear.

I think your mind is still on the Paralympics :-)  
You forgot the attachment.

how embarrassing! now attached...
legends-of-rock---Copy--4-.zip

OK ... I'm sure I'm missing something here, but ... when I open the Form from the SWB, select an Accommodation Type and click the check box, a query opens.

?

Yes, but I said later that you need to click on the Type dropdown, then the tickbox

Christian
Point 1. The SQL does produce output, although maybe the cut-down version of the database doesn't contain any relevant data. But just by running it you can see that it would produce data, so I do not accept that this is the problem.
Point 2 may be valid, although I have never had any problems in the past.
Point 3 is correct, but as this is still in development, I have not bothered to correct the date formats yet.
Point 4 - thank you, I had not realised that.

So, after all that, I am no nearer a solution.

Short on time, and have not followed the whole dicussion, but back to the original question. The issue is likely raised from the square brackets around the RecordSource property.

This ...

[Forms]![frm_booking_summary2]![sfm_input_costs].[Form].[RecordSource] = strSQL

Should be one of the following ...

Forms![frm_booking_summary2]![sfm_input_costs].Form.RecordSource = strSQL
Forms!frm_booking_summary2!sfm_input_costs.Form.RecordSource = strSQL
Forms("frm_booking_summary2").Controls("sfm_input_costs").Form.RecordSource = strSQL

Or ... if the code is behind the main form ..

Me.sfm_input_costs.Form.RecordSource = strSQL

I am sure there are other variations.  I personally use the 3rd one I listed if the code is not behind the main form, then use the Me.<etc> if the code is behind the main form. I choose those methods because they are easy to read and interpret.