Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

MS Access RecordSource Compile Error

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
Kirkeous
Asked:
Kirkeous
  • 14
  • 12
  • 2
1 Solution
 
KirkeousAuthor Commented:
And it highlights the .RecordSource.  Is this a library issue?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What does strNewRecord  actually look like ?  Not what you show ... right ?

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

mx
0
 
KirkeousAuthor Commented:
Yes I do. It is just too long to type! I am on my laptop at work.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
KirkeousAuthor Commented:
I just swapped in a simple SQL statement on one field and it still craps out.
0
 
KirkeousAuthor Commented:
It does not like the .RecordSource
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is that code in an Access Form ?

mx
0
 
KirkeousAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I don't see Me.RecordSource in that code ...?

I see Graph.RecordSource = strNewRecord    ?
0
 
KirkeousAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is that code in the sub form or the main form ?

mx
0
 
KirkeousAuthor Commented:
It is in the sub form.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
On that line of code ... if you type:

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

mx
0
 
KirkeousAuthor Commented:
No I don't. That is why I think it is a reference issue.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
KirkeousAuthor Commented:
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
 
KirkeousAuthor Commented:
Is there a specific reference I should have activated?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
KirkeousAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
KirkeousAuthor Commented:
I did it. It still craps out at .recordsource!!!
0
 
KirkeousAuthor Commented:
Why does it not recognize .recordsource?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
stephenlecomptejrCommented:
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
 
stephenlecomptejrCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Has this question been resolved?  Can we close the question ?

thx.mx
0
 
KirkeousAuthor Commented:
I've requested that this question be deleted for the following reason:

Database was corrupt. Repaired and it worked.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I suggested 'repairing' in two different places

@ http:#a35099836

@ http:#a35100463

What did you do differently that 'repaired' it ?

mx

0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 14
  • 12
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now