Cannot find referenced Form error

[Middlemarch]

From Googling I'm told Forms![MyForm].Recordsource = strSQL is how to set a forms recordsource. But it doesn't work for me!




I've tried each of the following without success.

Forms![frmShow].RecordSource = strSQL
Forms!["frmShow"].RecordSource = strSQL
Forms!["frmShow"].Form.RecordSource = strSQL

If I don't include .Form. there is no .Recordsource dropdown. Does that matter ?What am I doing wrong ? frmShow does exist.

[June7]

Is the form open?

The first should work. The other two are not correct syntax because of quote marks.

[Micron]

"doesn't work" means what? Without knowing anything about the db, here's 2 stabs
- your form is a subform
- sql variable has no value; i.e it's "" aka zero length string

I would also have listed "form is not open" but that's been covered.

[Middlemarch]

The Form is not open and is not a SubForm. The sql variable is correct/complete.
"doesn't work" means Access is giving the error message used as the Subject.
The full wording is Run-time error '2450':Microsoft Access cannot find the referenced form 'frmShow'.
Does the Form have to be Open for Access to know it Exists? Surely not ?
I'll try it in a new db with nothing but the one Form

[June7]

The form must be open in some mode to change its properties.

[Middlemarch]

@June. OK, but how can you open it if Access can't find it.
I'll attach the rogue db that can't see what it has. And (sigh) fully expecting it will work for you.

[June7]

Did you try opening form manually from Navigation Pane? What happens? Should see an error message because form is bound to ShowUnique which doesn't exist as a table nor a query. This is why the VBA fails. Textboxes are bound to fields that don't exist.

Why do you want to programmatically change form RecordSource?

[Middlemarch]

@June7, I had removed the ShowUnique. Access indicated it couldn't find the Form, not that there was something wrong with it. At one stage I got the same error when trying to Open it.
I want to change the recordsource to test a theory. At the moment QueryDefs are used to make a query abd I want to see if that is really needed, i.e. can the Form use just sql.

[June7]

Then how do you expect me test for the behavior you experience?

I created a table ShowUnique and form opens just fine.

Still haven't explained why you want to do this. Why do you use QueryDefs to make a query? What are you really trying to accomplish?

[Middlemarch]

I didn't know it would error with 'can't find Form" because the recordsource didn't exist. To me, that did not seem a correct error as the Form did exist. I was wanting to establish a new Record source so the current one wasn't relevant.
But this is interesting, your question Why do you use QueryDefs to make a query puzzles me and I wonder why would not ? So here's my code and perhaps you can show how you would do it ?
frmDisco's recordsource is qryDisco. This is built at runtime, from the data active at the time. The sql will appear gibberish but that shouldn't matter

Dim myquery As QueryDef  
qryName = "qryDisco"
    sql = "Select A, B, HandleThis(C) as iC, D from " & MyCompany & " Where " & ActiveBtn & " Like " & Chr$(34) & Dat & Chr$(34) & ";"
    Set myquery = db.QueryDefs(qryName)
    myquery.sql = sql$


  DoCmd.OpenForm "frmDisco", acFormDS

[Welshgasman]

No Option Explicit I see?

[June7]

Certainly can change a form's RecordSource without modifying a query object. I've only had one occasion where I needed to use QueryDefs to modify query object and had nothing to do with setting a form's RecordSource. But as already noted, the form must be open in some mode.

I suspect that because code dynamically sets the source table, your data structure may not be optimized.


Learned something new. In posted db, procedure has Dim sql$ but then uses sql. I added Option Explicit line and VBA accepts these variables. However, when I reverse the variables, VBA does not like it.

Agree, really should have Option Explicit in every module. Review https://excelchamps.com/vba/option-e...0new%20module.

[Micron]

The $ in Dim Sql$ replaces "As String". Personally I don't use/like that syntax. AFAIK, if you use Dim Sql$ you must use Sql$ = something, not Sql =
OP apparently got away with that because of not having Option Explicit. In that case Sql$ would exist but Sql would be what is assigned a value where Sql = is used.
Late to the party so I guess I won't upload db sample. As far as I'm concerned, the error is not about whether or not the form exists in the forms collection - it is about whether or not vba can "see" it, which it cannot if it's not open either in design or form view.

[June7]

As I stated, I added Option Explicit and the sql reference is accepted.

[Micron]

Correct. I should have 'listened' to myself
Dim sql$ is the same as Dim sql As String