Solved

MS Access RecordSource Compile Error

Posted on 2011-03-10
29
423 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 - Access MVP) 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 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

24 Experts available now in Live!

Get 1:1 Help Now