Solved

MS Access RecordSource Compile Error

Posted on 2011-03-10
29
422 Views
Last Modified: 2012-05-11
Dear Guru -

I have a problem with the recordsource property in my code.

Dim strNewReord As String
strNewRecord = "My SQL State is Here"

Me.RecordSource = strNewRecord

When I run the code I get a complie error that states

Method or data member not found


Any help would be great!
0
Comment
Question by:Kirkeous
  • 14
  • 12
  • 2
29 Comments
 

Author Comment

by:Kirkeous
Comment Utility
And it highlights the .RecordSource.  Is this a library issue?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
What does strNewRecord  actually look like ?  Not what you show ... right ?

You have to have a valid SQL Statement for the Recordsource ...

mx
0
 

Author Comment

by:Kirkeous
Comment Utility
Yes I do. It is just too long to type! I am on my laptop at work.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Well ... there must be a typo of a Field name or something ... in the SQL string ... to get that error.  Kind of hard to troubleshoot w/o more info ....

mx
0
 

Author Comment

by:Kirkeous
Comment Utility
I just swapped in a simple SQL statement on one field and it still craps out.
0
 

Author Comment

by:Kirkeous
Comment Utility
It does not like the .RecordSource
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Is that code in an Access Form ?

mx
0
 

Author Comment

by:Kirkeous
Comment Utility
It is in a subform on the form. I am firing it off from a command button.

Here is the actual code:

Private Sub Command44_Click()

On Error GoTo Err_Command44_Click

 

 

'This will make the project name appear on the main form and be used as the filter

Forms!frm_Project_Dash_Tabs!txtProjectName = Forms!frm_Project_Dash_Tabs!sfrm_Projects_Dash_1.Form![Project Name]

 

 

 

Dim k As Control

Dim strNewRecord As String

Dim Graph As SubForm

 

Set Graph = Forms!frm_Project_Dash_Tabs.Form!sfrm_Expense_Resource_Graph_Projects

 

strNewRecord = "SELECT A01_FINAL_BUDGETS.[Programme Category]FROM A01_FINAL_BUDGETS GROUP BY A01_FINAL_BUDGETS.[Programme Category]"

 

Graph.RecordSource = strNewRecord

 

Forms("frm_Project_Dash_Tabs").Refresh

 

Set k = Forms!frm_Project_Dash_Tabs.Form!CumCostGrph

 

 

'Activate the Sub Program in the graph

 

DoCmd.OpenQuery "QRY_DELETE_CumCost_Grph_Proj"

 

DoCmd.OpenQuery "QRY_CumCost_Grph_Jan_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Feb_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Mar_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Apr_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_May_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Jun_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Jul_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Aug_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Sep_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Oct_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Nov_Proj_Drl"

DoCmd.OpenQuery "QRY_CumCost_Grph_Dec_Proj_Drl"

 

 

'DoCmd.OpenQuery "QRY_DELETE_HEADCOUNT_GRPH_PROJ"

'DoCmd.OpenQuery "QRY_CumCost_Grph_Jan_FTE_Proj"

'DoCmd.OpenQuery "QRY_CumCost_Grph_Feb_FTE_Proj"

'DoCmd.OpenQuery "QRY_CumCost_Grph_Mar_FTE_proj"

 

 

 

k.Requery

 

 

Forms!frm_Project_Dash_Tabs!txtHeader = "Project: " & " " & Forms!frm_Project_Dash_Tabs!txtProjectName

 

 

 

 

 

 

 

 

Exit_Command44_Click:

    Exit Sub

 

Err_Command44_Click:

    MsgBox err.Description

    Resume Exit_Command44_Click

   

End Sub
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I don't see Me.RecordSource in that code ...?

I see Graph.RecordSource = strNewRecord    ?
0
 

Author Comment

by:Kirkeous
Comment Utility
Oh. Sorry I just changed it. I am testing here myself. It does not work either way! I am in a state of confusion.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Is that code in the sub form or the main form ?

mx
0
 

Author Comment

by:Kirkeous
Comment Utility
It is in the sub form.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
On that line of code ... if you type:

Me Dot (.) ... do you see RecordSource  in the Intellisense ?

mx
0
 

Author Comment

by:Kirkeous
Comment Utility
No I don't. That is why I think it is a reference issue.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
OK ... yep.   VBA editor ...Tools>>References ... look for any listed as

**Missing <SomeReferenceName>


OR ... you have a compile error.

Menu>>Debug>>Compile

Do you get any errors ?

mx
0
 

Author Comment

by:Kirkeous
Comment Utility
I do not see a missing reference. I have seen those before but not now. I compiled the code and it only breaks there.

This is such a mystery!
0
 

Author Comment

by:Kirkeous
Comment Utility
Is there a specific reference I should have activated?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Can you upload the db (sanitized) ... and tell how to exactly reproduce the issue ?

I assume you have DAO 3.6 Object Library ... is that there ?


Capture1.gif
0
 

Author Comment

by:Kirkeous
Comment Utility
I was just fiddling with the DAO 3.6 Object Library. It has SQL Server backend. I doubt I could upload it here it is too big now.

It is there now what? I am at a loss. Could my db be corrupt?
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
Comment Utility
Corrupt ... always a possibility ... so, try this Decompile procedure:

 
****
0) **Backup your MDB BEFORE running this procedure**
****
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
Close the mdb after the Compact & Repair.
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
0
 

Author Comment

by:Kirkeous
Comment Utility
I did it. It still craps out at .recordsource!!!
0
 

Author Comment

by:Kirkeous
Comment Utility
Why does it not recognize .recordsource?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

How to upload:
http://www.experts-exchange.com/Community_Support/General/A_2790-How-do-I-attach-a-file-at-Experts-Exchange.html

mx
0
 
LVL 1

Expert Comment

by:stephenlecomptejr
Comment Utility
Hey Kirkeous:

I know this shouldn't matter but instead of using:....

Set Graph = Forms!frm_Project_Dash_Tabs.Form!sfrm_Expense_Resource_Graph_Projects

use this instead:

Set Graph = Forms!frm_Project_Dash_Tabs.Form.sfrm_Expense_Resource_Graph_Projects

replace the final ! exclamation shown with a period...

================================

If that also doesn't work - please try to remove the following statements either by deletion or 'remarking it out.

'Dim Graph as SubForm
'Set Graph = Forms!frm_Project_Dash_Tabs.Form!sfrm_Expense_Resource_Graph_Projects

And instead of

Graph.RecordSource = strNewRecord...

do:

Forms!frm_Project_Dash_Tabs.Form.sfrm_Expense_Resource_Graph_Projects.RecordSource = strNewRecord

and that may work instead!
0
 
LVL 1

Expert Comment

by:stephenlecomptejr
Comment Utility
Sometimes I forget that the subform can be named differently as a control name.

Please note this image whereby the subform is shown as sfrmItemListbyRoom_1280 but the control name is really EquipList.

Thus in this example you would use the Forms!MainForm.Form.EquipList.Recordsource = "" instead of Forms!frmMainForm.Form.sfrmItemListbyRoom_1280.Recordsource = ""


Capture.PNG
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Has this question been resolved?  Can we close the question ?

thx.mx
0
 

Author Comment

by:Kirkeous
Comment Utility
I've requested that this question be deleted for the following reason:

Database was corrupt. Repaired and it worked.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I suggested 'repairing' in two different places

@ http:#a35099836

@ http:#a35100463

What did you do differently that 'repaired' it ?

mx

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now