Set Record Source using a string in VBA (1 Viewer)

jlb4350 · Feb 20, 2019

Set SourceObject using a string in VBA

Good day all. I have an issue that you might be able to help me with. I've searched the forum and have tried several suggestions that I thought were similar to my issue, but they did not work...leaving me to believe that my issue is either unique, of I've simply done something wrong from the start.

I have a form that has a ComboBox (cboFieldTableList), a SubForm (fsubFieldDataEditor), and a button (btnProcess). The ComboBox is displaying a simple text list from a table, and there is VB code that matches the text in the combobox with a corresponding query. What I'd like to happen is for the subform to display the correct query information when the button is clicked based on what is selected in the combobox. Below is a copy of my code. I get the compile error on this line:

Code:

Me!fsubFieldDataEditor.Form.SourceObject = strForm

Any suggestions how I can get this to work? Thank you in advance for your time and expertise!

Code:

Private Sub Form_Load()
    ' Wipes the subform clean upon form load
    Me.fsubFieldDataEditor.SourceObject = ""
End Sub

Private Sub btnProcess_Click()
Dim strForm As String

If Me.cboFieldTableList = "Change Type" Then
  strForm = "qryFIELD_ChangeType"
ElseIf Me.cboFieldTableList = "Description" Then
  strForm = "qryFIELD_Description"
  Else: MsgBox "Please make a selection from the list."
  Exit Sub
End If

Me!fsubFieldDataEditor.Form.SourceObject = strForm
Me!fsubFieldDataEditor.Requery

End Sub

isladogs · Feb 20, 2019

Your post was moderated which is why it didn't appear initially
I've deleted the duplicate post you created

theDBguy · Feb 20, 2019

Hi. Was there any error message like method not found or syntax error?

Micron · Feb 21, 2019

My guess is that you can't assign a string where an object is expected. Sure would have helped to know more about the message and/or highlighted portion. Usually, you'd create an object variable, not a string, and SET the variable by passing the name of the object. If you use a string variable, then you have to assign it to the property that can accept a string, which would be .Name but in this case, I don't think that would work as you'd be attempting to alter/set its name, not specify what the object should be.

JHB · Feb 21, 2019

Do you want to change the data in a form or do you want to change the form showing in the fsubFieldDataEditor container?
If data then it is RecordSource instead of SourceObject!

Micron · Feb 21, 2019

JHB said:
Do you want to change the data in a form or do you want to change the form showing in the fsubFieldDataEditor container?
If data then it is RecordSource instead of SourceObject!

Good point! I think we've assumed it was the sourceobject because that's in the code. But it now looks like the code is trying to assign a query to the sourceobject.

June7 · Feb 21, 2019

Subform container control can have a table, query, form, report as SourceObject. So programmatically setting the SourceObject to a string which is the name of an object should work. I just tested this and it works for table and form but not query.

However, since the thread title references RecordSource, either code is referencing wrong property or the title is mistyped.

jlb4350 · Feb 21, 2019

Re: Set SourceObject using a string in VBA

Hi. Was there any error message like method not found or syntax error?

Here is the error I get:
Run-time error 2467: The expression you entered refers to an object that is closed or doesn't exist.

Regarding what i'd like to achieve, I would like for the subform to display the complete contents of a query (or table) when I click the button so that users can edit the table without leaving the form or having another tab open up.

I don't write code often, so I'm still learning which references to put in. I assumed it was SourceObject because I thought that whatever is in the SourceObject field would be what is reflected in the subform...my apologies if I'm incorrect. These chats really help me learn though, so thank you all for your contribution.

I've also tried this code (to call a table rather than a query) and i get the same error:

Code:

Private Sub btnProcess_Click()
Dim strForm As String

If Me.cboFieldTableList = "Change Type" Then
  strForm = "tblFIELD_ChangeType"
ElseIf Me.cboFieldTableList = "Description" Then
  strForm = "tblFIELD_Description"
  Else: MsgBox "Please make a selection from the list."
  Exit Sub
End If

Me!fsubFieldDataEditor.Form.SourceObject = strForm

End Sub

I'm trying to use a string because I don't want the ComboBox to display the actual names of the tables. I was hoping that when the button was clicked it would work like this (for example):

** Since "Change Type" is selected in cboFieldTableList, strForm is "tblFIELD_ChangeType". Therefore, the SourceObject for fsubFieldDataEditor should be "tblFIELD_ChangeType".** If I'm doing this wrong, please let me know...

I changed the title btw to hopefully better reflect the contents of the post.

JHB · Feb 21, 2019

Could you post your database, with some sample data, zip it + the name of the form.

jlb4350 · Feb 21, 2019

JHB said:
Could you post your database, with some sample data, zip it + the name of the form.

sure. this is just a sample, but if you open the form frmFieldDataEditor and choose one of the options from the dropdown and click the button, you'll see the error.

I also removed the code that clears the subform because that seems to be working fine

JHB · Feb 21, 2019

Here is the database back.
I've added a new "form" which update automatic when the combobox change, the other is corrected to what you wrote you would like to have.

jlb4350 · Feb 21, 2019

JHB said:
Here is the database back.
I've added a new "form" which update automatic when the combobox change, the other is corrected to what you wrote you would like to have.

Brilliant! That did the trick!! I really appreciate your time, this works perfectly now!

I see my mistake. I needed to make

Code:

strForm = "tblFIELD_Description"

to

Code:

strForm = "table.tblFIELD_Description"

Thank you again and thanks to everyone else who replied!

June7 · Feb 21, 2019

Interesting, worked for me without the table prefix.

jlb4350 · Feb 21, 2019

June7 said:
Interesting, worked for me without the table prefix.

Yea, it started working when I put those prefixes in. I'm actually kind of shocked that I was as close as I was. I'll need the afterupdate code elsewhere in the database, so thanks for offering the two solutions.

JHB · Feb 22, 2019

jlb4350 said:
..
I see my mistake. I needed to make

Code:

strForm = "tblFIELD_Description"

to

Code:

strForm = "table.tblFIELD_Description"

..

That was one thing but you also need to change:
From:

Code:

Me!fsubFieldDataEditor.[B][COLOR=Red]Form.[/COLOR][/B]SourceObject = strForm

To:

Code:

Me.fsubFieldDataEditor.SourceObject = strForm

June7 · Feb 22, 2019

Agree about removing the .Form. I just noticed it there in the original code. Don't see how that can work even with the Table prefix.

Set Record Source using a string in VBA (1 Viewer)

jlb4350

Registered User.

isladogs

MVP / VIP

theDBguy

I’m here to help

Micron

AWF VIP

JHB

Have been here a while

Micron

AWF VIP

June7

AWF VIP

jlb4350

Registered User.

JHB

Have been here a while

jlb4350

Registered User.

Attachments

JHB

Have been here a while

Attachments

jlb4350

Registered User.

June7

AWF VIP

jlb4350

Registered User.

JHB

Have been here a while

June7

AWF VIP

Similar threads

Users who are viewing this thread