473,322 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Record source error

15
I have set up a database so that users can run a report by entering the criteria on an unbound parameter form. Sometimes the form works and then other times it does not and we get the following error.

The record source '~sq_cParamForm~sq_cFindMgr' specified on this form or report does not exist.

This error seems to happen if a user starts to run a report and then exits out of it before they complete it. When this happens in order to run the reports I have to go into the database and replace the form with a copy of the form from my backup file.

Is there any way to keep this from happening?

Thank you,
Sep 9 '09 #1

✓ answered by Stewart Ross

Ahh. Access can be such a fickle tool to work with!

When forms or reports start giving error messages stating "the record source xxxxx does not exist" Access has typically encountered some form of internal corruption which has damaged the form or report concerned. It is not necessarily consistent, but when it has happened to me the form concerned worked on and off and I could not trace why it didn't work when it failed. Eventually it failed altogether and had to be recreated from backup copies.

The giveaway is in post 1, which mentions record sources beginning with '~' characters. Access uses these internally to indicate temporary (or system) tables - and my guess is that you are seeing internal intermediate recordsources generated by the JET database engine when some failure or other is occurring.

Anyway, I'd suggest going back to backups (which you mention you are doing anyway) - and failing that recreating the item concerned from scratch.

I'd also suggest that you create a blank database under a different name and import all current known good objects into it, as a belt-and-braces backup for the current DB. If there is one area of corruption there may well be others.

Be aware that one very common cause of Access failures and consequent corruption is interruptions to network connections whilst data is passing back and forth between Access and the network. You mention that this started happening when users close a report before it has fully run, and that suggests to me that Access is taking a long time to complete something - in which case if this is happening across a network you are highly dependent on the quality of the connection concerned.

-Stewart

13 7014
NeoPa
32,556 Expert Mod 16PB
I expect so, but without any knowledge of what is causing it, it's hard to say what.

You need to provide some information that pertains to the problem.
Sep 9 '09 #2
BeaBea
15
I'm not exactly sure what is causing it. The form has an unbound combo box for the Manager Name, an unbound box for the start date, an unbound box for the end date and a command button to run the report. I thought the problem might be that when a user starts to enter the information in the form, they choose a name from the Manager Name dropdown box, enter the start date and then they decide not to run the report and just close out of it. I am not sure if this makes the form hang up because then when you try to go back in and try to run the report as sooon as we click on the drop down box for the Managers Name we get the error message. We click on OK on the error message and close the parameter form, an Enter Parameter Value box comes up (as it would in a normal access report that is not being run from a parameter form.

Hope this helps in understanding the issue I am having, tried to explain as best as I could. I wonder if I put a requery procedure in the after update event of the Manager Name combo box if that would help.

Any assistance would be very much appreciated.
Sep 9 '09 #3
ajalwaysus
266 Expert 100+
Are you running code in VBA when they enter data in the fields, or do you wait for an OnClick event?

I think you need to post the code you are running.

-AJ
Sep 9 '09 #4
NeoPa
32,556 Expert Mod 16PB
That's a far better description. We now have a problem at least we can think about.

What would be very helpful, now you've given us a bit of background to the issue, would be the form's module code posted to review.

The RecordSource of the report may also be helpful, but we may have all we need from the VBA code.
Sep 9 '09 #5
BeaBea
15
This is the code I have for the Manager Name combo box.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command7_Click()
  2. Me.Visible = False
  3.  
  4. End Sub
  5.  
  6. Private Sub FindMgr_AfterUpdate()
  7.  
  8.     Me!FindMgr.Requery
  9.     Me!FindMgr.SetFocus
  10.  
  11. End Sub
This is what I have in the row source of the Manager Name combo box:
Expand|Select|Wrap|Line Numbers
  1. SELECT"All" As  MgrName FROM tblMain UNION SELECT MgrName FROM tblMain ORDER BY MgrName
Thank you,
Sep 10 '09 #6
NeoPa
32,556 Expert Mod 16PB
This looks like a section of the module. If so, I'd like to see all of it really. I can't see anything here that pertains to a report running.
Sep 10 '09 #7
ajalwaysus
266 Expert 100+
Is this EXACTLY how it appears in your row source?
Expand|Select|Wrap|Line Numbers
  1. SELECT"All" As  MgrName FROM tblMain UNION SELECT MgrName FROM tblMain ORDER BY MgrName
  2.  
If so, it needs to be
Expand|Select|Wrap|Line Numbers
  1. SELECT "All" As  MgrName FROM tblMain UNION SELECT MgrName FROM tblMain ORDER BY MgrName
  2.  
And if this is mistyped in this thread, then please make sure you copy and paste all your code going forward. It helps avoid wasting any time on something that may be correct in the DB but is mistyped in the thread.

Thanks,
-AJ
Sep 10 '09 #8
NeoPa
32,556 Expert Mod 16PB
AJ is thinking along the right lines here. I would suggest a small change (other than the layout - which always helps)
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 'All' As  MgrName
  2. FROM tblMain
  3. UNION SELECT MgrName
  4. FROM tblMain
  5. ORDER BY MgrName
PS. Please don't overlook my earlier post where I request you post your whole module.
Sep 10 '09 #9
Stewart Ross
2,545 Expert Mod 2GB
Ahh. Access can be such a fickle tool to work with!

When forms or reports start giving error messages stating "the record source xxxxx does not exist" Access has typically encountered some form of internal corruption which has damaged the form or report concerned. It is not necessarily consistent, but when it has happened to me the form concerned worked on and off and I could not trace why it didn't work when it failed. Eventually it failed altogether and had to be recreated from backup copies.

The giveaway is in post 1, which mentions record sources beginning with '~' characters. Access uses these internally to indicate temporary (or system) tables - and my guess is that you are seeing internal intermediate recordsources generated by the JET database engine when some failure or other is occurring.

Anyway, I'd suggest going back to backups (which you mention you are doing anyway) - and failing that recreating the item concerned from scratch.

I'd also suggest that you create a blank database under a different name and import all current known good objects into it, as a belt-and-braces backup for the current DB. If there is one area of corruption there may well be others.

Be aware that one very common cause of Access failures and consequent corruption is interruptions to network connections whilst data is passing back and forth between Access and the network. You mention that this started happening when users close a report before it has fully run, and that suggests to me that Access is taking a long time to complete something - in which case if this is happening across a network you are highly dependent on the quality of the connection concerned.

-Stewart
Sep 11 '09 #10
BeaBea
15
Sorry it's taken me so long to get back to this post. Been away from it for a few days.

I am not sure what you mean by post the whole module. Do you mean the module under the objects list, if so I do not have one and that could potentially be the problem. Or do you mean all of the code under VB, if so I have posted all of the code I have.

If i need to have something in the module please let me know.

I am changing the Row Source as suggested by NeoPa and see how that works.

Thank you all for your help and suggestions.
Sep 15 '09 #11
NeoPa
32,556 Expert Mod 16PB
To post a whole module :
From Access press Alt-F11 to open the IDE or VBA debugging window.
Press Ctrl-R to open the Project Explorer pane.
Select the module required (in this case I expect you'll need to navigate via Microsoft Office Access Class Objects from your project).
Double-Click on this module to open the code in the Code pane.
Press Ctrl-A to select all text (VBA code) in the module.

You should now be able to post this data from the clipboard into a Bytes post.
Don't forget the [ CODE ] tags ;)

PS. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your profile options (Look near the bottom of the page).
Sep 15 '09 #12
BeaBea
15
NeoPa,

Thank you.

I pressed Alt F11 to open the IDE or VBA debugging window and this is the only code that is in that window:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Report_Close()
  4. DoCmd.Close acForm, "ParamForm"
  5.  
  6. End Sub
  7.  
  8. Private Sub Report_Open(Cancel As Integer)
  9. DoCmd.OpenForm "ParamForm", , , , , acDialog
  10.  
  11. End Sub
I pressed Control R and double clicked on the ParamForm Module and this is the following code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Command7_Click()
  4. Me.Visible = False
  5.  
  6. End Sub
  7.  
  8. Private Sub FindMgr_AfterUpdate()
  9.  
  10.     Me!FindMgr.Requery
  11.     Me!FindMgr.SetFocus
  12.  
  13. End Sub
  14.  
  15. Private Sub Form_Open(Cancel As Integer)
  16.  
  17.     DoCmd.OpenForm "ParamForm", , , , , acDialog
  18.  
  19. End Sub
I did not find any code tags. It appears I may have left some code out of the database that really needs to be there.

Again, thank you for your help.
Sep 15 '09 #13
NeoPa
32,556 Expert Mod 16PB
It looks like Stewart may have the right of it here Bea.

I was hoping to see some code that may have given a clue to what's happening. Unfortunately it seems very straightforward and I see nothing which may go towards explaining your issue.
Sep 16 '09 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Mike Conklin | last post by:
This one really has me going. Probably something silly. I'm using dcount for a report to determine the number of different types of tests proctored in a semester. My report is based on a...
3
by: bill_hounslow | last post by:
I'm trying to transfer data from one Sql Server into a table on another, using a simple INSERT query in an Access database with links to tables on both servers (the reasons for this are complicated...
8
by: Zlatko Matiæ | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the...
4
by: authorking | last post by:
I use the following code to insert a data record in to a datatable of an access database.But every time I execute the command, there will rise an exception and the insert operation can't be...
1
by: authorking | last post by:
I create the databse with the follwing code. I really don't know why the insert operation can't be completed. databse creation code: ADOX.CatalogClass cat = new ADOX.CatalogClass();//define a Jet...
10
by: Stephen Plotnick | last post by:
I've started some code but do not know how to get the anwser. Here is the code: I need the first four characters of data in a field (TextName) to get a name from a DB and put the name back into...
5
by: krwill | last post by:
I'm trying to automate a combo box to add a record to the source table if it's "Not In List". I've tried many different examples and none have worked. Combo Box Name = Combo24 Source Table...
1
by: Peter Herath | last post by:
I have created a report using sample codes taken by the forum. one problem is that the report displays the field/column names in the table in columnar format(field names display in rows.) but i want...
3
by: smugcool | last post by:
Hi, I have created a form in Visual basic 6.0 for adding/updating the change request made by various users. Well i am able to update the record through my code in access database. Can anyone...
7
by: Neil | last post by:
Was working in A2003 and noticed that the Save Record item on the Records menu was not available when the record was not dirty. In A2000, Save Record was always available. (This is a problem for me...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.