stuck again!

L

Lee Taylor-Vaughan

I am getting this error with the following code "91 Object Variable or With
Block Variable not set".

The problem began when I added a txt box(txtNumberDays) to a form
(frmDispoMain) so the user can select the number of days on the form. When
the "Send Fax" cmd is clicked i want the following code to execute (SendFax)
This works fine without txtNumberDays.

I thought, and tried to use a RecordSetClone for the open form
(frmDipsoMain) which sounded great, but i when i tried to do it I got lost
and figured that i would try to put it in to a SQL statement. (i am lost)

Please post some pointers for me.
Lee



Here is the code.
***************************************
Function SendFax() 'send fax to each ED, MICU Patients-dispo manager
On Error GoTo Error_SendFax

Dim dbsMICU As DAO.Database
Dim rstDisposNeeded As DAO.Recordset
Dim strSQl As String

strSQl = "PARAMETERS [forms]![frmdispomain].[txtnumberdays] Long;SELECT
[Receiving Hospital].RecHospID" & vbCrLf
strSQl = strSQl & " , [Receiving Hospital].ERFaxNumber AS FAX" &
vbCrLf
strSQl = strSQl & " FROM [Receiving Hospital] " & vbCrLf
strSQl = strSQl & " RIGHT JOIN (TblDispatch " & vbCrLf
strSQl = strSQl & " LEFT JOIN TblPatients " & vbCrLf
strSQl = strSQl & " ON TblDispatch.DispatchID =
TblPatients.DispatchID) " & vbCrLf
strSQl = strSQl & " ON [Receiving Hospital].ReceivingHospital =
TblPatients.ReceivingHospital" & vbCrLf
strSQl = strSQl & " WHERE
(((DateDiff(""d"",CDate(Format([dispatchdate],""Short Date"") & "" "" &
Format([DispatchAvailable],""Short
Time"")),Now()))<=[forms]![frmdispomain].[txtnumberdays]) " & vbCrLf
strSQl = strSQl & " AND
((TblPatients.ReceivingHospital)<>""None"") " & vbCrLf
strSQl = strSQl & " AND ((TblPatients.Support)<>""Refused Medical
Treatment"") " & vbCrLf
strSQl = strSQl & " AND ((IIf([support]=""release to
bls"",""N/A"",[ALSreleasestatus])) Is Null)) " & vbCrLf
strSQl = strSQl & " OR
(((DateDiff(""d"",CDate(Format([dispatchdate],""Short Date"") & "" "" &
Format([DispatchAvailable],""Short
Time"")),Now()))<=[forms]![frmdispomain].[txtnumberdays]) " & vbCrLf
strSQl = strSQl & " AND
((TblPatients.ReceivingHospital)<>""None"") " & vbCrLf
strSQl = strSQl & " AND ((TblPatients.Support)<>""Refused Medical
Treatment"") " & vbCrLf
strSQl = strSQl & " AND ((IIf([Support]=""Advanced Life
Support"",""N/A"",[BLSReleaseStatus])) Is Null))" & vbCrLf
strSQl = strSQl & " GROUP BY [Receiving Hospital].RecHospID" & vbCrLf
strSQl = strSQl & " , [Receiving Hospital].ERFaxNumber" & vbCrLf
strSQl = strSQl & " HAVING ((([Receiving Hospital].ERFaxNumber) Is
Not Null)) " & vbCrLf
strSQl = strSQl & " OR ((([Receiving Hospital].ERFaxNumber) Is
Not Null));"

Set dbsMICU = CurrentDb()
Set rstDisposNeeded = dbsMICU.OpenRecordset(strSQl, dbOpenDynaset)
'Set rstDisposNeeded = dbsMICU.OpenRecordset("qryDispoManagerFax",
dbOpenDynaset) '(works fine without txtNumberDays on frmDispoMain)

With rstDisposNeeded
If MsgBox("Do you want to send Dispo Faxes to ED's?", _
vbYesNo + vbQuestion, "Fax Dispos") = 6 Then

Do Until .EOF
strHospWhere = "[RecHospID] = " & ![RecHospID]

If IsNumeric(![Fax]) Or Len(![Fax]) > 0 Then

DoCmd.SendObject acSendReport, "rptDispoFax",
acFormatSNP _
, "[fax: " & ![Fax] & "]", , , , , False
Else
'do nothing (fax number not valid)
End If

.MoveNext
Loop

End If
rstDisposNeeded.Close
End With
Exit_SendFax:
Exit Function

Error_SendFax:
Select Case Err.Number
Case 2293 'no clicked in outlook security

MsgBox "Previous Operation was Cancelled by the User",
vbInformation, "Aborted"
''change from msgbox to update in status window (msgbox freezes
system)

Exit Function
Case 3061
Resume Next
Case Else

MsgBox Err.Number & Err.Description
Resume Exit_SendFax
End Select

End Function
 
T

tina

-----Original Message-----
I am getting this error with the following code "91 Object Variable or With
Block Variable not set".

The problem began when I added a txt box(txtNumberDays) to a form
(frmDispoMain) so the user can select the number of days on the form. When
the "Send Fax" cmd is clicked i want the following code to execute (SendFax)
This works fine without txtNumberDays.

I thought, and tried to use a RecordSetClone for the open form
(frmDipsoMain) which sounded great, but i when i tried to do it I got lost
and figured that i would try to put it in to a SQL statement. (i am lost)

Please post some pointers for me.
Lee



Here is the code.
***************************************
Function SendFax() 'send fax to each ED, MICU Patients- dispo manager
On Error GoTo Error_SendFax

Dim dbsMICU As DAO.Database
Dim rstDisposNeeded As DAO.Recordset
Dim strSQl As String

strSQl = "PARAMETERS [forms]![frmdispomain]. [txtnumberdays] Long;SELECT
[Receiving Hospital].RecHospID" & vbCrLf
strSQl = strSQl & " , [Receiving
Click to expand...
Hospital].ERFaxNumber AS FAX" &
vbCrLf
strSQl = strSQl & " FROM [Receiving Hospital] " & vbCrLf
strSQl = strSQl & " RIGHT JOIN (TblDispatch " & vbCrLf
strSQl = strSQl & " LEFT JOIN TblPatients " & vbCrLf
strSQl = strSQl & " ON TblDispatch.DispatchID =
TblPatients.DispatchID) " & vbCrLf
strSQl = strSQl & " ON [Receiving Hospital].ReceivingHospital =
TblPatients.ReceivingHospital" & vbCrLf
strSQl = strSQl & " WHERE
(((DateDiff(""d"",CDate(Format([dispatchdate],""Short Date"") & "" "" &
Format([DispatchAvailable],""Short
Time"")),Now()))<=[forms]![frmdispomain]. [txtnumberdays]) " & vbCrLf
strSQl = strSQl & " AND
((TblPatients.ReceivingHospital)<>""None"") " & vbCrLf
strSQl = strSQl & " AND ((TblPatients.Support)
Click to expand...
Treatment"") " & vbCrLf
strSQl = strSQl & " AND ((IIf([support] =""release to
bls"",""N/A"",[ALSreleasestatus])) Is Null)) " & vbCrLf
strSQl = strSQl & " OR
(((DateDiff(""d"",CDate(Format([dispatchdate],""Short Date"") & "" "" &
Format([DispatchAvailable],""Short
Time"")),Now()))<=[forms]![frmdispomain]. [txtnumberdays]) " & vbCrLf
strSQl = strSQl & " AND
((TblPatients.ReceivingHospital)<>""None"") " & vbCrLf
strSQl = strSQl & " AND ((TblPatients.Support)
Click to expand...
Treatment"") " & vbCrLf
strSQl = strSQl & " AND ((IIf([Support] =""Advanced Life
Support"",""N/A"",[BLSReleaseStatus])) Is Null))" & vbCrLf
strSQl = strSQl & " GROUP BY [Receiving Hospital].RecHospID" & vbCrLf
strSQl = strSQl & " , [Receiving
Click to expand...
Hospital].ERFaxNumber" & vbCrLf
strSQl = strSQl & " HAVING ((([Receiving Hospital].ERFaxNumber) Is
Not Null)) " & vbCrLf
strSQl = strSQl & " OR ((([Receiving Hospital].ERFaxNumber) Is
Not Null));"

Set dbsMICU = CurrentDb()
Set rstDisposNeeded = dbsMICU.OpenRecordset(strSQl, dbOpenDynaset)
'Set rstDisposNeeded = dbsMICU.OpenRecordset ("qryDispoManagerFax",
dbOpenDynaset) '(works fine without txtNumberDays on frmDispoMain)

With rstDisposNeeded
If MsgBox("Do you want to send Dispo Faxes to ED's?", _
vbYesNo + vbQuestion, "Fax Dispos") = 6 Then

Do Until .EOF
strHospWhere = "[RecHospID] = " & ! [RecHospID]

If IsNumeric(![Fax]) Or Len(![Fax]) > 0 Then

DoCmd.SendObject acSendReport, "rptDispoFax",
acFormatSNP _
, "[fax: " & ![Fax] & "]", , , , , False
Else
'do nothing (fax number not valid)
End If

.MoveNext
Loop

End If
rstDisposNeeded.Close
End With
Exit_SendFax:
Exit Function

Error_SendFax:
Select Case Err.Number
Case 2293 'no clicked in outlook security

MsgBox "Previous Operation was Cancelled by the User",
vbInformation, "Aborted"
''change from msgbox to update in status window (msgbox freezes
system)

Exit Function
Case 3061
Resume Next
Case Else

MsgBox Err.Number & Err.Description
Resume Exit_SendFax
End Select

End Function


.
did you try concatenating your control reference to the
Click to expand...
SQL statement, rather than including it literally:

Now()))<=" & [forms]![frmdispomain].[txtnumberdays] & ")
etc, etc, etc..."

instead of

Now()))<=[forms]![frmdispomain].[txtnumberdays]) "
 
L

Lee Taylor-Vaughan

Thanks. That fixed it.

One more question, how, in code would I snatch the recordsource of an open
form and use that in the same code instead of the sql code?

Thanks

Lee



tina said:
-----Original Message-----
I am getting this error with the following code "91 Object Variable or With
Block Variable not set".

The problem began when I added a txt box(txtNumberDays) to a form
(frmDispoMain) so the user can select the number of days on the form. When
the "Send Fax" cmd is clicked i want the following code to execute (SendFax)
This works fine without txtNumberDays.

I thought, and tried to use a RecordSetClone for the open form
(frmDipsoMain) which sounded great, but i when i tried to do it I got lost
and figured that i would try to put it in to a SQL statement. (i am lost)

Please post some pointers for me.
Lee



Here is the code.
***************************************
Function SendFax() 'send fax to each ED, MICU Patients- dispo manager
On Error GoTo Error_SendFax

Dim dbsMICU As DAO.Database
Dim rstDisposNeeded As DAO.Recordset
Dim strSQl As String

strSQl = "PARAMETERS [forms]![frmdispomain]. [txtnumberdays] Long;SELECT
[Receiving Hospital].RecHospID" & vbCrLf
strSQl = strSQl & " , [Receiving
Click to expand...
Hospital].ERFaxNumber AS FAX" &
vbCrLf
strSQl = strSQl & " FROM [Receiving Hospital] " & vbCrLf
strSQl = strSQl & " RIGHT JOIN (TblDispatch " & vbCrLf
strSQl = strSQl & " LEFT JOIN TblPatients " & vbCrLf
strSQl = strSQl & " ON TblDispatch.DispatchID =
TblPatients.DispatchID) " & vbCrLf
strSQl = strSQl & " ON [Receiving Hospital].ReceivingHospital =
TblPatients.ReceivingHospital" & vbCrLf
strSQl = strSQl & " WHERE
(((DateDiff(""d"",CDate(Format([dispatchdate],""Short Date"") & "" "" &
Format([DispatchAvailable],""Short
Time"")),Now()))<=[forms]![frmdispomain]. [txtnumberdays]) " & vbCrLf
strSQl = strSQl & " AND
((TblPatients.ReceivingHospital)<>""None"") " & vbCrLf
strSQl = strSQl & " AND ((TblPatients.Support)
Click to expand...
Treatment"") " & vbCrLf
strSQl = strSQl & " AND ((IIf([support] =""release to
bls"",""N/A"",[ALSreleasestatus])) Is Null)) " & vbCrLf
strSQl = strSQl & " OR
(((DateDiff(""d"",CDate(Format([dispatchdate],""Short Date"") & "" "" &
Format([DispatchAvailable],""Short
Time"")),Now()))<=[forms]![frmdispomain]. [txtnumberdays]) " & vbCrLf
strSQl = strSQl & " AND
((TblPatients.ReceivingHospital)<>""None"") " & vbCrLf
strSQl = strSQl & " AND ((TblPatients.Support)
Click to expand...
Treatment"") " & vbCrLf
strSQl = strSQl & " AND ((IIf([Support] =""Advanced Life
Support"",""N/A"",[BLSReleaseStatus])) Is Null))" & vbCrLf
strSQl = strSQl & " GROUP BY [Receiving Hospital].RecHospID" & vbCrLf
strSQl = strSQl & " , [Receiving
Click to expand...
Hospital].ERFaxNumber" & vbCrLf
strSQl = strSQl & " HAVING ((([Receiving Hospital].ERFaxNumber) Is
Not Null)) " & vbCrLf
strSQl = strSQl & " OR ((([Receiving Hospital].ERFaxNumber) Is
Not Null));"

Set dbsMICU = CurrentDb()
Set rstDisposNeeded = dbsMICU.OpenRecordset(strSQl, dbOpenDynaset)
'Set rstDisposNeeded = dbsMICU.OpenRecordset ("qryDispoManagerFax",
dbOpenDynaset) '(works fine without txtNumberDays on frmDispoMain)

With rstDisposNeeded
If MsgBox("Do you want to send Dispo Faxes to ED's?", _
vbYesNo + vbQuestion, "Fax Dispos") = 6 Then

Do Until .EOF
strHospWhere = "[RecHospID] = " & ! [RecHospID]

If IsNumeric(![Fax]) Or Len(![Fax]) > 0 Then

DoCmd.SendObject acSendReport, "rptDispoFax",
acFormatSNP _
, "[fax: " & ![Fax] & "]", , , , , False
Else
'do nothing (fax number not valid)
End If

.MoveNext
Loop

End If
rstDisposNeeded.Close
End With
Exit_SendFax:
Exit Function

Error_SendFax:
Select Case Err.Number
Case 2293 'no clicked in outlook security

MsgBox "Previous Operation was Cancelled by the User",
vbInformation, "Aborted"
''change from msgbox to update in status window (msgbox freezes
system)

Exit Function
Case 3061
Resume Next
Case Else

MsgBox Err.Number & Err.Description
Resume Exit_SendFax
End Select

End Function


.
did you try concatenating your control reference to the
Click to expand...
SQL statement, rather than including it literally:

Now()))<=" & [forms]![frmdispomain].[txtnumberdays] & ")
etc, etc, etc..."

instead of

Now()))<=[forms]![frmdispomain].[txtnumberdays]) "
Click to expand...
 
T

tina

-----Original Message-----
Thanks. That fixed it.

One more question, how, in code would I snatch the recordsource of an open
form and use that in the same code instead of the sql code?

Thanks

Lee


well, your sql nearly had me running for the hills (mine
Click to expand...
is usually about 4 lines long), but here's a suggestion:

have you tried using a sql statement that runs off the
form's underlying query, as:

strsql = "SELECT * FROM " & Forms!Form1.RecordSource & "
WHERE WhateverField=" & [forms]![frmdispomain]
[txtnumberdays]
(or however you need to reference that form control)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why wont this code work? (send fax) 8

Top