Solved

MS Access RecordSource Compile Error

Posted on 2011-03-10
29
425 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 250 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

790 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