OpenDatabase/Table as shared

[Puebles]

I am new to VBA, my experience is with VFP. I am running into the problem of moving from one form to another with the same table and getting the error that the table is already open. The table is used to collect statistical data based on choices made on screen1 and displayed on screen2. A part of the code looks like this:

' Code on screen1 button to display screen2
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("tblControl", dbOpenTable)
rs1.Edit

' Code goes here to calculate.

rs1!PercentCancel = Round((rs1!CancelOnTime / rs1!CancelCount), 4)
rs1.Update
rs1.Close
' Screen2 (frmStats) uses tblControl in the Form Property Record Source
DoCmd.OpenForm "frmStats"

Note: I have reviewed many articles and advice, most of which is "How do you make it read only?" "How do you make it exclusive?" I have seen the term "Shared" used in descriptions, but never in the code. As I said, my experience is with VFP. In VFP the command would simply be: "Use tblControl Shared".



Thank you in advance.

[Dal Jeanis]

If you don't need to update the second recordset, then use dbOpenSnapshot, not dbOpenTable. There are a couple of other types of recordsets. You can review the details here - http://office.microsoft.com/en-us/ac...080753713.aspx

[Puebles]

I still can't get it to work. But, I did find one problem with my coding and a work around:

1. I created small snippets of code based on examples that I have seen. Each snippet began by opening the table using the SET rs1..... code. Somtimes there were two tables and the second was opened with SET rs2. Turns out that after 12 small snippets of code were strung together I had tblControl opened last as rs1 and from a prior incident as rs2. Yep, very sloppy. I cleaned up the code and designated unique prefixes for each table. I really expected that this was why I still received the error even though I ran rs1.close prior to openeing screen 2. No such luck, and snapshot didn't work either, so....

2. I created a query to copy the table and switched the controlling table to the new one.

Unfortunately this is only a temporary solution. I will need to figure how to open two or more forms with the same table. This is common given the environment I am used to working in.

[Dal Jeanis]

Is there a technical reason you are basing your form on a recordset rather than a query? Queries don't usually cause these kinds of issues.

[June7]

I open mulitiple forms that pull records from the same table - they are different forms. Are you opening multiple instances of the same form?

Along with Dal, I don't understand why you are using recordset to populate a field that should be available for reference on the form.

Also, you are saving a calculated value - why?

[Puebles]

Habit....I learned programming on dBase3 by tacking other's code and tweeking it. I then moved to VFP. I have had little experience with queries until lately. I will have to do this now as this is a display info/read only form.

Thanks.