Method or data member not found

[Abeladoni]

Greetings all!

I've got a problem I'm hoping someone can help out with.

I've got a series of tables that are archived data by year. For example: MyData_2006, MyData_2007 etc.

Now they all use the same exact form to view and edit the data. So what I'm trying to do is create a drop down box that will change the record source of that form based upon what the user chose. So the user will choose the year from the drop down, push the button and the Form will open up with the record source changed to that year.

The problem I'm running into is when it gets to the recordsource line of code I get the error message: Method or data member not found.

When I searched for this on Google it indicates that I was missing the "Microsoft DAO 3.5 (or 3.6) Object Library". However, I'm NOT missing it. It's in my list of available references and is checked. I've got 3.5 & 3.6 available (I've tried them both, but am using 3.6 by default).

So if anyone has any further ideas, I would greatly appreciate it!

-Dale

Below is my code:
Private Sub View_Season_Click()

Dim dbs As Database
Set dbs = CurrentDb
strSeason_Value = Me!Combo4.Column(1)
dbs!Forms!Cost_Sheet.RecordSource = strSeason_Value
DoCmd.OpenForm "Cost_Sheet"

End Sub

 

[Norie]

I don't see why this would be caused by a missing reference.

Have you checked all the names of the forms/controls?

 

[Abeladoni]

Well, if I'm looking at it right the only things I could misspell would be the form name and the table name that I'm changing the recordsource to?

The form name is Cost_Sheet and the table name is MyData_2007 or MyData_2008.

I've triple checked the spelling everywhere, and they all look good.

 

[giacomo]

It may help to go to the strSeason_Value = Me!Combo4.Column(1) line in your code and press F9. This will place a red dot to the left of it, this is called a breakpoint. When you try to run your code it will halt when it reaches this line. Then you can check the value of Me!Combo4.Column(1) by hovering over it and ensure that it is the value your expecting.

hth,
Giacomo

 

[VicRauch]

What jumped out at me was that it you are trying to reference the form as if it were already open, but I don't think it is (based on reading your request carefully.)

So, you code is like this:
dbs!Forms!Cost_Sheet.RecordSource = strSeason_Value
DoCmd.OpenForm "Cost_Sheet"

dbs!Forms!????? only works for forms that are open.

Reverse those two lines and it should work.
Better yet (in my opinion) would be to drop the dbs!Forms!Cost_... and place that line in the Load event of the Cost_Sheet form. Have that line refer back to the form that opens the Cost_Sheet form to get the value that is in column 1 of your combo box.

HTH,

 

[Andrew Fergus]

You don't need to use VBA to do this. If the form is based on a query, then the year can be filtered within the query using the criteria :

=Forms![Cost_Sheet].Combo4 or Like Forms![Cost_Sheet].Combo4 Is Null

Hopefully I have the right form and combo box names.....

Andrew

 

[VicRauch]

Andrew, According to the original spec in the original question, this data HAS BEEN archived into tables and each table holds one year of data. Are you actually suggesting a UNION query that will read ALL the data from ALL the archived tables, just so the year can be filtered by year?

Maybe an explaination would help here.

 

[Abeladoni]

Greetings all,

Thanks for the suggestions so far.

Giacoma: I've confirmed that strSeason_Value is giving me the value I'm looking for. I didn't use a break point, but I used a message box to display the value when I clicked the button.

Vic: I added dbs!Forms ... etc while troubleshooting. And I have tried to open the form first. BUT, I forgot to mention that the error "Method or Data member not found" is actually a compile error. So I click the command button and the error immediately comes up.

The code screen comes up and this part of the code is highlighted:
.RecordSource =

So it's acting like it's not recognizing the RecordSource option!

Any other ideas I could try?

 

[giacomo]

this worked for me...

Dim strSeason_Value As String

strSeason_Value = Me.Combo4.Column(1)
DoCmd.OpenForm "Cost_Sheet"
Forms("Cost_Sheet").RecordSource = strSeason_Value

Notice I flipped the docmd.open above the recordsource = line. You cannot change the rs unless it's open. Plus no need to reference the DAO objects

hth,
Giacomo

 

[Abeladoni]

That did the trick!

Woot, thank you kind sir!