Record source error

[BeaBea]

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,

[NeoPa]

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.

[BeaBea]

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.

[ajalwaysus]

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

[NeoPa]

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.

[BeaBea]

This is the code I have for the Manager Name combo box.

Private Sub Command7_Click()
Me.Visible = False

End Sub
    
Private Sub FindMgr_AfterUpdate()
    
    Me!FindMgr.Requery
    Me!FindMgr.SetFocus
    
End Sub

This is what I have in the row source of the Manager Name combo box:

SELECT"All" As  MgrName FROM tblMain UNION SELECT MgrName FROM tblMain ORDER BY MgrName

Thank you,

[NeoPa]

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.

[ajalwaysus]

Is this EXACTLY how it appears in your row source?

SELECT"All" As  MgrName FROM tblMain UNION SELECT MgrName FROM tblMain ORDER BY MgrName

If so, it needs to be

SELECT "All" As  MgrName FROM tblMain UNION SELECT MgrName FROM tblMain ORDER BY MgrName

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

[NeoPa]

AJ is thinking along the right lines here. I would suggest a small change (other than the layout - which always helps)

SELECT DISTINCT 'All' As  MgrName
FROM tblMain
UNION SELECT MgrName
FROM tblMain
ORDER BY MgrName

PS. Please don't overlook my earlier post where I request you post your whole module.

[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

[BeaBea]

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.

[NeoPa]

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).

[BeaBea]

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:

Option Compare Database

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"

End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub

I pressed Control R and double clicked on the ParamForm Module and this is the following code:

Option Compare Database

Private Sub Command7_Click()
Me.Visible = False

End Sub

Private Sub FindMgr_AfterUpdate()
    
    Me!FindMgr.Requery
    Me!FindMgr.SetFocus
    
End Sub

Private Sub Form_Open(Cancel As Integer)

    DoCmd.OpenForm "ParamForm", , , , , acDialog

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.

[NeoPa]

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.