error 3048 cannot open tables

[access101]

I have an unbound form with 50+ combobox that activate the function below but I keep getting the 3048 error because i keep opening and closing the recordset. i thought if i made the recordset public it would stop the error but im still having the same result. iS THERE AN ALTERNATIVE? I want the recordset open until i close the form

Public Rst As DAO.Recordset

Function CTLNUM(ByRef A, B, C, T, S, Y, Z, X)
S = S
e = e
A = A
B = B
C = C
T = T
Y = Y

If X("Employee" & T) = "D1" _
Or X("Employee" & T) = "I1" _
Or X("Employee" & T) = "A1" _
Or X("Employee" & T) = "P1" _
Or X("Employee" & T) = "O1" _
Or X("Employee" & T) = "S1" _
Or IsNull(X("S" & T)) _
Or IsNull(X("E" & T)) Then Exit Function

X("Box" & T).Left = C * 1440
If X("E" & T) = #10:00:00 AM# Then
X("Box" & T).Width = A * 1440
e = #10:00:00 AM#
X("Box" & T).Left = C * 1440

ElseIf X("E" & T) = #10:30:00 AM# Then
X("Box" & T).Width = (A + 0.37) * 1440
e = #10:30:00 AM#
X("Box" & T).Left = C * 1440

ElseIf X("E" & T) = #11:00:00 AM# Then
X("Box" & T).Width = (A + 0.78) * 1440
e = #11:00:00 AM#

ElseIf X("E" & T) = #11:30:00 AM# Then
X("Box" & T).Width = (A + 1.15) * 1440
e = #11:30:00 AM#

ElseIf X("E" & T) = #1:00:00 PM# Then
X("Box" & T).Width = B * 1440
e = #1:00:00 PM#

ElseIf X("E" & T) = #1:15:00 PM# Then
X("Box" & T).Width = (B + 0.17) * 1440
e = #1:15:00 PM#

ElseIf X("E" & T) = #1:30:00 PM# Then
X("Box" & T).Width = (B + 0.36) * 1440
e = #1:30:00 PM#

ElseIf X("E" & T) = #1:45:00 PM# Then
X("Box" & T).Width = (B + 0.55) * 1440
e = #1:45:00 PM#

ElseIf X("E" & T) = #2:00:00 PM# Then
X("Box" & T).Width = (B + 0.75) * 1440
e = "2 p"

ElseIf X("E" & T) = #2:15:00 PM# Then
X("Box" & T).Width = (B + 0.94) * 1440
e = #2:15:00 PM#

ElseIf X("E" & T) = #2:30:00 PM# Then
X("Box" & T).Width = (B + 1.13) * 1440
e = #2:30:00 PM#

ElseIf X("E" & T) = #2:45:00 PM# Then
X("Box" & T).Width = (B + 1.32) * 1440
e = #2:45:00 PM#

ElseIf X("E" & T) = #3:00:00 PM# Then
X("Box" & T).Width = (B + 1.53) * 1440
e = #3:00:00 PM#

ElseIf X("E" & T) = #3:15:00 PM# Then
X("Box" & T).Width = (B + 1.72) * 1440
e = #3:15:00 PM#

ElseIf X("E" & T) = #3:30:00 PM# Then
X("Box" & T).Width = (B + 1.91) * 1440
e = #3:30:00 PM#

ElseIf X("E" & T) = #3:45:00 PM# Then
X("Box" & T).Width = (B + 2.1) * 1440
e = #3:45:00 PM#

ElseIf X("E" & T) = #4:00:00 PM# Then
X("Box" & T).Width = (B + 2.3) * 1440
e = #4:00:00 PM#
End If



Set Rst = CurrentDb.OpenRecordset("Sched")

Do While True
If Rst.RecordCount > 0 Then
If Rst!ID = T And Rst!Day = (Y) Then Exit Do
Rst.MoveNext
End If
If Rst.EOF Then Exit Do
Loop

If Not Rst.EOF Then
Rst.Edit
Rst("ID").Value = T
Rst("Employee").Value = Form_Main(Y).Form("Employee" & T).Value
Rst("Start").Value = S
Rst("End").Value = e

If X("Box" & T).BackColor = 16051931 Then
Rst("Type").Value = Z & "O"
ElseIf X("Box" & T).BackColor = 13040377 Then
Rst("Type").Value = Z & "D"
ElseIf X("Box" & T).BackColor = 14610923 Then
Rst("Type").Value = Z & "P"
ElseIf X("Box" & T).BackColor = vbWhite Then
Rst("Type").Value = Z & "I"
End If

Rst.Update

Else

Rst.AddNew
Rst("ID").Value = T
Rst("Employee").Value = Form_Main(Y).Form("Employee" & T).Value
Rst("Start").Value = S
Rst("End").Value = e

If X("Box" & T).BackColor = 16051931 Then
Rst("Type").Value = Z & "O"
ElseIf X("Box" & T).BackColor = 13040377 Then
Rst("Type").Value = Z & "D"
ElseIf X("Box" & T).BackColor = 14610923 Then
Rst("Type").Value = Z & "P"
ElseIf X("Box" & T).BackColor = vbWhite Then
Rst("Type").Value = Z & "I"
End If

Rst("Day").Value = (Y)
Rst.Update

End If
rst.close
set rst=nothing

'DoCmd.OpenQuery "Clear_Sched_Crosstab"
'DoCmd.OpenQuery "Move_Sched_Crosstab"

Form_Main.ShiftHours.Form.Recalc
Form_Main.Schedule.Form.Requery
Form_Main.Summary.Form.Requery
Form_Main.WeeklySales.Form.Recalc

End Function

 

[access101]

Can any one please help?

 

[Andrzejek]

You have really confusing logic here.

First: do you have [tt]Option Explicit[/tt] on the top of your code? If not, you should.

Your Function:[tt]
Function CTLNUM(ByRef A, B, C, T, S, Y, Z, X)[/tt]
All parameters are Variants. Is that what you want?

What is this assigning values to itself?[tt]
S = S
e = e
A = A
B = B
C = C
T = T
Y = Y[/tt]

This logic:

Do While True
  If Rst.RecordCount > 0 Then
    If Rst!ID = T And Rst!Day = (Y) Then Exit Do
    Rst.MoveNext
  End If
  If Rst.EOF Then Exit Do
Loop

Do While (what is) True ?

And shouldn’t that be:
If Rst!ID = [red]"[/red]T[red]"[/red] And Rst!Day = [red]"[/red](Y)[red]"[/red] Then Exit Do

All those If – ElseIf – ElseIf – ElseIf… are begging for [tt]Select Case[/tt] statement.

Etc, etc, etc. (Just my opinion... )

Anyways, what line of code gives you the error 3048 cannot open tables?


Have fun.

---- Andy

 

[access101]

i know it looks confusing but it works great until i get to the end. the database is a scheduling db i have one form with 7 tabs each tab has a subform in it that all feed my Sched table. The error shows up and highlights Set Rst = CurrentDb.OpenRecordset("Sched") so im thinking its staying in memory somewhere. I would like to keep it open if i could instead of closing everytime i access the function. the loop needs work or do you have a select statement that would work better?

 

[Andrzejek]

I would try:

If Rst.State = 1 Then Rst.Close
Set Rst = Nothing
Set Rst = CurrentDb.OpenRecordset("Sched")

Have fun.

---- Andy