P
PJS
Hi,
We're in our first week of running an SQL Server database
that was made from an upsized Access (XP) back end. We
are not, however, using an Access ADP front end. There
are other changes needed before we convert to an ADP. I'm
having a lot of problems with subforms on many of our
existing forms and am not sure why. It seems that the new
system is not happy when I issue a Requery command for a
subform, as in: subSomeSubForm.Requery . It either takes
a long time or hangs permanently. Is SQL Server not happy
with this kind of operation?
Here's a specific one. We have a switchboard for our
shipping department that has an MS Calendar control on
it. Users click a date to see what deliveries are due for
the selected date. On open, the date defaults to the
current date. The OnOpen event of the form populates the
subform recordset. This code, which works great w/ an
Access back end, hangs at the point indicated since
switching to SQL Server back end. (Note: There are other
functions on the form that provide users with different
options for filling this subform, therefore, I didn't bind
the subform to a specific query, but used code intstead.)
Private Sub Form_Open(Cancel As Integer)
'Set Calendar control to today's date and populate subform.
On Error GoTo Err_FormOpen
Dim strSQL As String
Dim datDate As Date
datDate = DateValue(Now())
With Me
!ocxCalendar.SetFocus
!ocxCalendar.Value = datDate
!txtSelDate = datDate
strSQL = "SELECT * FROM qryPOIDueByVend WHERE
due_date = #" & datDate & "#"
!subDueToday.SourceObject = "sfrmPOIDueByProj"
!subDueToday.Form.RecordSource = strSQL
'*** CRASH OCCURS ON LINE ABOVE ***
End With
Exit_FormOpen:
Exit Sub
Err_FormOpen:
MsgBox Err.description & " -- " & Err.Number
Resume Exit_FormOpen
End Sub
There's no error message, it just hangs and Access has to
be closed thru Task Manager. The query in the SQL string
is a standard Access query, not a SQL pass-thru, view, or
stored procedure. I tried wrapping the date value in
single quotes instead of #, but NG. Also, I've tried to
include any reference to a SQL Server or RDO library in
the References settings in VBA.
Thanks for reading this saga. Any help would be great. I
have 2 companies on opposite coasts that are waiting for
me to fix things. The horror.
Regards,
PJS
We're in our first week of running an SQL Server database
that was made from an upsized Access (XP) back end. We
are not, however, using an Access ADP front end. There
are other changes needed before we convert to an ADP. I'm
having a lot of problems with subforms on many of our
existing forms and am not sure why. It seems that the new
system is not happy when I issue a Requery command for a
subform, as in: subSomeSubForm.Requery . It either takes
a long time or hangs permanently. Is SQL Server not happy
with this kind of operation?
Here's a specific one. We have a switchboard for our
shipping department that has an MS Calendar control on
it. Users click a date to see what deliveries are due for
the selected date. On open, the date defaults to the
current date. The OnOpen event of the form populates the
subform recordset. This code, which works great w/ an
Access back end, hangs at the point indicated since
switching to SQL Server back end. (Note: There are other
functions on the form that provide users with different
options for filling this subform, therefore, I didn't bind
the subform to a specific query, but used code intstead.)
Private Sub Form_Open(Cancel As Integer)
'Set Calendar control to today's date and populate subform.
On Error GoTo Err_FormOpen
Dim strSQL As String
Dim datDate As Date
datDate = DateValue(Now())
With Me
!ocxCalendar.SetFocus
!ocxCalendar.Value = datDate
!txtSelDate = datDate
strSQL = "SELECT * FROM qryPOIDueByVend WHERE
due_date = #" & datDate & "#"
!subDueToday.SourceObject = "sfrmPOIDueByProj"
!subDueToday.Form.RecordSource = strSQL
'*** CRASH OCCURS ON LINE ABOVE ***
End With
Exit_FormOpen:
Exit Sub
Err_FormOpen:
MsgBox Err.description & " -- " & Err.Number
Resume Exit_FormOpen
End Sub
There's no error message, it just hangs and Access has to
be closed thru Task Manager. The query in the SQL string
is a standard Access query, not a SQL pass-thru, view, or
stored procedure. I tried wrapping the date value in
single quotes instead of #, but NG. Also, I've tried to
include any reference to a SQL Server or RDO library in
the References settings in VBA.
Thanks for reading this saga. Any help would be great. I
have 2 companies on opposite coasts that are waiting for
me to fix things. The horror.
Regards,
PJS