Dynamic Sub Form Record Source

[Guest]

I'm having a senior moment, and don't seem to be able to figure this out.

I want to open a form that has a subform and dynamically set the record
source of the subform. I don't seem to be able to pull this off for some
reason... the chunk of code I'm using is as follows:

Forms!frmMain.Form!frmSub.RecordSource = strSQL

(strSQL contains the SQL query for the record source)

Any thoughts?

- Jeff C.

 

[Douglas J. Steele]

What happens when you use that code?

 

[Guest]

I get an error back of:

Run-tim error '-2146500594 (800f000e)':
Method 'Item' of object 'Forms' failed

- Jeff

 

[Douglas J. Steele]

You don't need to refer to the Form property of the main form:

Forms!frmMain!frmSub.RecordSource = strSQL

(Note: this assumes that the name of the subform control on frmMain is named
frmSub. Depending on how you added frmSub as a subform on frmMain, the name
of the subform control can be different than the name of the form being used
as a subform. It's the name of the subform control on the parent form that
you need to use)

 

[Guest]

Still getting the same message. Let me put some more info. here in case
there is something else going on...

The Sub form control is named "frmSub", so that should be okay.

frmMain is opened from another form. I have it set up so that when you
double click on a value in a listbox on the first form it opens up frmMain
and passes that listbox value in the 'where' part of the frmSub's record
source SQL query.

So this is more of the code:

DoCmd.OpenForm "frmMain"
strSQL = "SELECT * FROM tblData Where ID = " & me.List1 & ";"
Forms!frmMain!frmSub.RecordSource = strSQL

- Jeff C.

 

[Douglas J. Steele]

Sorry, my fault. While you didn't need to refer to the Form property for the
main form, you do need to refer to it for the subform:

Forms!frmMain!frmSub.Form.RecordSource = strSQL

 

[Guest]

This didn't work either (at first).

I tried just punching in code to change something on frmMain just to see if
I could figure out where the error was. That didn't work either, same error.
I copy and pasted frmMain as frmMain2 so I could try and mess around with it
without messing up my first form, and without any other changes (besides the
copy/paste, and obviously changing the code to look at frmMain2) everything
started working fine.

Thanks for all the help Doug!

- Jeff

 

[jvdb]

i had the sameproblem with writing some data to a textbox, and indeed copying
the form and renaming it to the original one's name resolved the problem

nice bug i think