Solved Cannot Change RecordSource of SubForm (1 Viewer)

[Cotswold]

I have the strangest error that I cannot find solution to.
I have a Form with a SubForm. The subform is the one with the data linked to the table with a Query.
In the code below it fails at Line 5 with the error 2580; "The recordsource "Q20CensusName_FiltName" specified does not exist on this form or report"

01    If Me!fFiltOnOff = False Then 
02      If Len(Trim(Me!fFiltName)) > 3 Then
03           Me!fFiltName = UCase(Me!fFiltName)
04          'Forms!F20CensusMain!F20CensusSub.Form.RecordSource = "Q20CensusName_FiltName"
05           Me.F20CensusSub.Form.RecordSource = "Q20CensusName_FiltName"
06           Me!fFiltOnOff = True
07      Else
08           mOk = MsgBox("Your filter name must be four or more characters long!", 0, "FILTER SETTING ERROR")
09      End If
10  Else
11      Me!fFiltOnOff = False
12     Forms!F20CensusMain!F20CensusSub.Form.RecordSource = "Q20CensusName"
13  End If

The Query stated does exist. In fact, if I change the QueryName to the one that is used when the form opens in line 5, it will also fail with 2580.

The actual cause of the error I cannot see, but it must be simple.

 

[theDBguy]

Just as a test, does it also fail if you do something like?

...subform.Form.RecordSource=...subform.Form.RecordSource

 

[Cotswold]

Hello DBguy, I can't quite understand. Are you saying set a RecordSource to that of another Form/SubForm that is open? Which is possibly not a likely scenario

 

[theDBguy]

Hello DBguy, I can't quite understand. Are you saying set a RecordSource to that of another Form/SubForm that is open? Which is possibly not a likely scenario

No. I am saying set the record source to itself. Just trying to see if it's really a problem of the query you're selecting or something else.

 

[June7]

I cannot replicate issue. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[sonic8]

error 2580; "The recordsource "Q20CensusName_FiltName" specified does not exist on this form or report"

Can you open the query on it's own? The error may also indicate that a table or query that is used within your query does not exist.

 

[Cotswold]

No. I am saying set the record source to itself. Just trying to see if it's really a problem of the query you're selecting or somethin

I have already tried that by changing the one to open to the Query set in the form design, but the same error. I'm getting Err 2580 but it cannot be the case. It is really weird and illogical.

 

[Cotswold]

Can you open the query on it's own? The error may also indicate that a table or query that is used within your query does not exist.

Yes sonic8 I've done that also. I've spent hours trying everything I can think of............in fact typing this I've just had an idea & will get back to the forum.

 

[oleronesoftwares]

Check and verify name of query is same as what is been reference, look out for each character, any special character etc on both the name of query in navigation window and name been referenced

 

[Cotswold]

Check and verify name of query is same as what is been reference, look out for each character, any special character etc on both the name of query in navigation window and name been referenced

Thanks for that oleronesoftwares, one of the first things I did. Also renamed them, deleted and recreated, all to no avail.

The main form has two subforms. When you move about on Sub1 then it refreshes Sub2. I wondered if that was creating the error but it isn't as I have removed Sub2 and all references and no change.

If in Design I set the subform RecordSource to its Query everything opens Ok. If I attempt to switch to another Query it falls over with Err2580. If I change the RescordSource to the underlying Table instead of a Query it works. But when I attempt to switch off the Filter by switching from the Table back to the Query used to open the form, I get Err2580. I'm going to create the forms again from scratch and see how that goes. I've switched RecordSources in so many programs before without any problem but just punching clouds on this one.

 

[sonic8]

Just some possibly stupid questions:
Your query is literally named: "Q20CensusName_FiltName"?
You do NOT intend to set the the Recordsource to something like "Q20CensusName_NameSuffixForAFilterQueryYouCreated"?

 

[Cotswold]

Just some possibly stupid questions:
Your query is literally named: "Q20CensusName_FiltName"?
You do NOT intend to set the the Recordsource to something like "Q20CensusName_NameSuffixForAFilterQueryYouCreated"?

No sonic8, but I was thinking of calling it "UnlessIGetThisToWorkPDQIWillThrowTheDamnThingOutOftheWindow"

I use a specific naming system since the 1980s and it has served me well in Access since 1999. Usually names of around eight characters are enough. My system has the advantage of ensuring that different but associated Forms, Queries, Reports etc always show together. Functions also follow the same notation, so it is always obvious where they are and what they refer to. Tables also follow a specific naming system that is common over all of my database systems. If I have over 400 objects I don't want to be scrolling all over the place to locate the ones I want. It was convenient to use the ones I did here, usually I just use _0, _1, _2...etc. as the suffix. My software is self documenting and older systems easy to update.

What gets me is that I only did exactly the same thing a fortnight ago on another system and it is fine. So I can't blame Access19!

 

[The_Doc_Man]

I think your proposed "...OutTheWindow" name would fail because of a limit on object name size.

Looking at the original post, in lines 5 & 12, you are diddling with the subform's .RecordSource, but with different conditions leading to different query names for the two cases. Can you force the code, via your form-based options, to take the path leading through line 12 to verify that you can reach the .RecordSource that way? (The object of the experiment is to verify that the .RecordSource is responding to being changed.)

But I'm also curious about your naming. Were you trying to select one of a set of sub-form queries with related names that varied based on this filter name? I'm seeing what looks like someone who was planning to do a partial substitution but forgot to implement it. Sonic8 appears to have seen the same thing I saw.

I have already tried that by changing the one to open to the Query set in the form design, but the same error. I'm getting Err 2580 but it cannot be the case. It is really weird and illogical.

If setting the query name in form design mode is allowed using the dropdown method of selecting the source, then I agree it is extremely odd. But if you put the source name in place by simply typing it in, then I don't think we know enough here to say it is impossible. (Not from OUR end, anyway.)

 

[CJ_London]

perhaps the subform has a touch of corruption? Have you tried decompiling? Or recreating the form from scratch? Or using Application.SaveAsText and Application.LoadFromText - a quick way to 'reset' a form. It's not documented but more about it here if you aren't aware of it

Restore Database Objects (LoadFromText)

hi all, today it was my turn to have a corrupted db :banghead:. Luckily I found this thread:- Export All Database Objects Into Text Files But I missed a way to restore the objects again in a new empty database. So I created a table to store the saved objects. step 1 => define the table. I...

www.access-programmers.co.uk

 

[Cotswold]

But I'm also curious about your naming. Were you trying to select one of a set of sub-form queries with related names that varied based on this filter name? I'm seeing what looks like someone who was planning to do a partial substitution but forgot to implement it. Sonic8 appears to have seen the same thing I saw.

I cannot see a problem with my naming system, so a bit puzzled there. There was no partial substitution planned or forgotten.

Well after all these years I never knew that the size of an Object's name was important! I've had it in the back of my mind that it was 64. However, I rarely go much larger than 12 and never use any spaces. I have reduced the Query names to Q20Census_0 and Q20Census_1 and everything is now working as it should. However, I have also created a new Form and the two SubForms which are also working. It does appear that the name length of a Query may have an effect in creating the Err2580 but I have been unable to prove it conclusively. On a test on the new form set I increased a Query name to 30 and it still worked. Maybe there was some corruption in the original forms, and/or Queries? As I say it is now working but never had this issue before. Many thanks to all.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.