?
Solved

MS Access RecordSource Compile Error

Posted on 2011-03-10
29
Medium Priority
?
431 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 12
  • 2
29 Comments
 

Author Comment

by:Kirkeous
ID: 35098996
And it highlights the .RecordSource.  Is this a library issue?
0
 
LVL 75
ID: 35099014
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
ID: 35099077
Yes I do. It is just too long to type! I am on my laptop at work.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 75
ID: 35099110
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
ID: 35099134
I just swapped in a simple SQL statement on one field and it still craps out.
0
 

Author Comment

by:Kirkeous
ID: 35099143
It does not like the .RecordSource
0
 
LVL 75
ID: 35099191
Is that code in an Access Form ?

mx
0
 

Author Comment

by:Kirkeous
ID: 35099334
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
ID: 35099384
I don't see Me.RecordSource in that code ...?

I see Graph.RecordSource = strNewRecord    ?
0
 

Author Comment

by:Kirkeous
ID: 35099401
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
ID: 35099434
Is that code in the sub form or the main form ?

mx
0
 

Author Comment

by:Kirkeous
ID: 35099445
It is in the sub form.
0
 
LVL 75
ID: 35099481
On that line of code ... if you type:

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

mx
0
 

Author Comment

by:Kirkeous
ID: 35099491
No I don't. That is why I think it is a reference issue.
0
 
LVL 75
ID: 35099517
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
ID: 35099627
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
ID: 35099632
Is there a specific reference I should have activated?
0
 
LVL 75
ID: 35099697
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
ID: 35099745
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 - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 35099836
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
ID: 35100204
I did it. It still craps out at .recordsource!!!
0
 

Author Comment

by:Kirkeous
ID: 35100207
Why does it not recognize .recordsource?
0
 
LVL 75
ID: 35100463
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
ID: 35201151
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
ID: 35204825
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
ID: 35871366
Has this question been resolved?  Can we close the question ?

thx.mx
0
 

Author Comment

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

Database was corrupt. Repaired and it worked.
0
 
LVL 75
ID: 36191184
I suggested 'repairing' in two different places

@ http:#a35099836

@ http:#a35100463

What did you do differently that 'repaired' it ?

mx

0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

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