error when trying to view 2 subforms

[tractorvix]

Hi all,

Hope you can help me. I've been asked to produce a form to show items that require actioning today and items that require action tomorrow (they want to be able to see it on a nice form view rather than a report)
what is required is:

due today due tomorrow
item no name start date | item no name start date

I've tried setting this up as two subforms on a mainform, but I get an error on open for subform2 stating that the table containing the data is already opened exclusively by another user (subform1) and cannot be manipulated programmatically.

I only want to be able to view the data, not update it. Can anyone think of a way round this? Is there something clever I can do with recordsets rather than having the source for the subforms set as 2 seperate queries based on the the same table?
The only way I can think round it at the moment is to have a make table query run before the form opens to provide a seperate source for subform2, but this will slow things down.

Thanks

Vicky

 

[NvrSaDi]

Attached code sample populates textboxes on a form without causing a conflict.

Haven't tried it with a subform, but should work.

'Create a query with fields and copy the SQL code to a procedure in a module. This creates a "virtual table" that you can use as a record source for your second form. You can then populate the individual textboxes on the form from the sql string. The "rs!Jan" references the Jan field in the virtual table.

dim strsql as string

strsql = "SELECT tbl2007Budget.ENTID, 1 AS Sort, tbl2007Budget.PROPID, tbl2007Budget.[GL_Acct_Num], tbl2007Budget.Jan, tbl2007Budget.Feb, tbl2007Budget.Mar, tbl2007Budget.Apr, tbl2007Budget.May, tbl2007Budget.Jun, tbl2007Budget.Jul, tbl2007Budget.Aug, tbl2007Budget.Sep, tbl2007Budget.Oct, tbl2007Budget.Nov, tbl2007Budget.[Dec], tbl2007Budget.Tot, tbl2007Budget.[GL_Acct_Name] " _
& "FROM tbl2007Budget " _
& "WHERE (((tbl2007Budget.PROPID)='" & txtPropID & "') AND ((tbl2007Budget.[GL_Acct_Num])=" & txtGLNO & "));"

Set rs = db.OpenRecordset(strsql)

Me.Jan1 = rs!Jan
Me.Feb1 = rs!Feb
Me.Mar1 = rs!Mar
Me.Apr1 = rs!Apr
Me.May1 = rs!May
Me.Jun1 = rs!Jun
Me.Jul1 = rs!Jul
Me.Aug1 = rs!Aug
Me.Sep1 = rs!Sep
Me.Oct1 = rs!Oct
Me.Nov1 = rs!Nov
Me.Dec1 = rs!Dec
Me.Totals1 = rs!Totals

 

[tractorvix]

Thanks for the response, I have tried the following but can an 'object required' error on the
set rs = db.openrecordset(strsql) line
any ideas?

Private Sub Form_Load()
strsql = "SELECT qrysduetodaytomorrow.qryNo, qrysduetodaytomorrow.QryDate, qrysduetodaytomorrow.Owner, qrysduetodaytomorrow.DueDate, qrysduetodaytomorrow.complete " _
& "FROM qrysduetodaytomorrow" _
& "WHERE (((qrysduetodaytomorrow.DueDate)=DateAdd(""w"",1,Date())) AND ((qrysduetodaytomorrow.complete)=0));"

Set rs = db.OpenRecordset(strsql)

Me.QryDate = rs!QryDate
Me.Owner = rs!Owner
Me.QryNo = rs!QryNo

End Sub

Thanks
Vicky

 

[tractorvix]

I managed to manipulate my code for the two subforms to show the correct data when opened individually, but I got the same error when trying to open them both on the same main form.
I've now used a couple of listboxes to show the data instead. Not quite as nice looking, but does the job.

Thanks

Vicky

 

[NvrSaDi]

I left out some code when I copied and pasted

Dim strsql as string

dim db as database
set db = currentdb

dim rs as recordset