[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1123
  • Last Modified:

Access updating a subform's recordsource using VBA

This will be almost undoubtedly an easy 500 points for somebody!

I've got this line of code:

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

which keeps giving me this error:
Run-time error '440':
You entered an expression that has an invalid reference to the property Form/Report.

It is being run in a Function.
The form is called frm_booking_summary2
The subform is called frm_input_costs2
The subform's control on the form is called sfm_input_costs

Any ideas how to get round what is surely a stupid error?
0
rick_danger
Asked:
rick_danger
  • 9
  • 5
  • 4
  • +3
1 Solution
 
IrogSintaCommented:
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

Open in new window

0
 
rick_dangerAuthor Commented:
Thanks for the advice. However, the form is open.
0
 
IrogSintaCommented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rick_dangerAuthor Commented:
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.
0
 
IrogSintaCommented:
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?
0
 
Helen FeddemaCommented:
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.
0
 
rick_dangerAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" 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
0
 
rick_dangerAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
rick_dangerAuthor Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 ...
0
 
rick_dangerAuthor Commented:
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.
0
 
IrogSintaCommented:
I think your mind is still on the Paralympics :-)  
You forgot the attachment.
0
 
rick_dangerAuthor Commented:
how embarrassing! now attached...
legends-of-rock---Copy--4-.zip
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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.

?

say what ?
0
 
rick_dangerAuthor Commented:
Yes, but I said later that you need to click on the Type dropdown, then the tickbox
0
 
BitsqueezerCommented:
Hi,

sorry to say that, but your database is full of problems and what you get is the result of a very wild mixture of all that.

First, the reason for the error is very simple: After selecting something in the "type" combobox you set the RecordSource to a new query which produces no output (I guess because the query has references to the same form) and Access seems to cannot resolve that correctly so it simply makes the detail section invisible. And that is the simple reason why you cannot access the subform, because Access has deactivated it (by the way: you don't need a requery after setting the RecordSource because this will automatically requery the source).

If you go on creating forms, tables and queries with this style you will get in troubles very soon.

1. The tables contains Lookup fields. This is the first thing you should remove and then forget this feature now and for all times. If you open your table you are not able to see the real values which are stored in the table but only the lookup values with a combobox. As the user never has to look into tables directly this is a useless feature which creates lot of problems later. If you search for lookup field problems in the Internet you will find a lot of answers about that. Instead of lookup fields you only need to set a relationship between the tables where you also can set referential integrity and then you only need to create comboboxes for lookup fields in your forms on your own (once created, you can use the same combobox with copy/paste on other forms).

2. Make all objects independent from each other. You have for example references for standard values in the unbound start and end date fields to another form, you have references to a specific form and controls in your queries and you have references to specific forms in your module although you use a frm parameter. If you want to have flexible modules, forms and queries delete ALL references to other objects and create clean interfaces between them. This means:
A query should always use the PARAMETERS keyword as first row if you need to set variables and then use only these variables in the query itself. You can set the values by using the QueryDef object in VBA which also has the advantage that you don't need complicate SQL strings or parameter encoding like # with dates and so on. As alternative it is better to use dynamic SQL instead of direct references to external objects. If you would rename your form or a control you will see which problems you will produce.
A form should always use public subs/functions/properties if any external object like another form would need to access anything inside the form. This is what I meant with clear interface: If you would have a public property "StartDate" to set the date externally this property is the only one which needs to know which control to set explicitly or what to do with the value. If you then decide to rename the control you only need to adjust this property code and nothing else in your application.
A module which contains common code for use in multiple places should never know any explicit reference like in your module. You have a sub/function which should get all needed values as separate parameters, the module only need to access these parameter values and no explicit form or control name. The calling form can supply the sub/function with all needed values and so again the form is the only object which has control over the own objects.
A default value should not be stored in the default property of the control if it needs to be supplied from another object. It's best to set it by code for example if the cursor is set to the control and this is a NewRecord (which is the name of the property of a form to test this). The code can access other objects (again with a clean interface like described above).
These are only some examples, if you try to always create independent objects you will not have such problems like you have here.

3. SQL date: In your module you do not format the date values which produces wrong SQL strings. Also you do not test the contents of the fields so your WHERE additions later also produces incorrect SQL strings.

4. Although often recommended (also by Microsoft), forget the "!" syntax in VBA. The only compiler-safe method is to use the dot instead because only with the dot the compiler tests the names of each object after the dot. Using the "!" will stop the compiler at this character and you will get errors only at run-time if you misspelled some name or any property name later in the line. The dot only also doesn't work in cases where VBA cannot find the correct object type like in collections and so on, but in most cases it is the better choice. You only need the "!" syntax in direct references in controls/queries where I said above that this is in general not a good idea which can be avoided in 99% of all cases.

I would recommend not to use x queries for your search fields but only one base query which lists all records and then use filters like MX recommended above or use a dynamic query with additional WHEREs (which is in general slower). Filters have only disadvantages if you have x-thousands of records in your form where filters can crash Access completely where a new query go on working. In ADPs/SQL Server for example a new query "ServerFilter" property) can be a lot faster than using filters, so you must decide from case to case which is the better idea.

Cheers,

Christian
0
 
rick_dangerAuthor Commented:
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.
0
 
datAdrenalineCommented:
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.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 9
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now