Error: 'saveRecord' isn't available now

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im getting an error on the save statement below. It actually saves the first
2 statments in the table using the 'QueryAddStatusNextDate'. But it does not
add the 'QueryAddStatusReminderDate'. I don't know if it is because the
QueryAddStatusReminderDate is a update query and the 'QueryAddStatusNextDate'
is an append query.

Do have any suggestions why it is not saving the QueryAddStatusReminderDate
but is it saving the other?

Dim qdfRemindDate As DAO.QueryDef
Dim qdfStudyIdSSN As DAO.QueryDef

If Visit_Num.Value = 1 Then
Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 2 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())

qdfStudyIdSSN.Execute

Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 3 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())

qdfStudyIdSSN.Execute

'Add a new reminder calldate

Set qdfRemindDate =
CurrentDb.QueryDefs("QueryAddStatusReminderDate")
qdfRemindDate.Parameters("newId") = InterviewID
qdfRemindDate.Parameters("newVisit") = 1
qdfRemindDate.Parameters("newCallDate") = DateValue(Now())
qdfRemindDate.Execute

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
qdfStudyIdSSN.Close
qdfRemindDate.Close
 
Im getting an error on the save statement below. It actually saves
the first 2 statments in the table using the 'QueryAddStatusNextDate'.
But it does not add the 'QueryAddStatusReminderDate'. I don't know
if it is because the QueryAddStatusReminderDate is a update query and
the 'QueryAddStatusNextDate' is an append query.


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
Click to expand...


Is the form dirty? IIRC, you can only save a record if there is
anything to save. In any case, DoMenuItem is unrecommended these days:
you get better control doing

DoCmd.Runcommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord

or the even easier

If Me.Dirty = True then Me.Dirty = False

HTH


Tim F
 
I put in the dirty statement below and it is giving me this error:

Object Variable or With Block not set


Dim qdfRemindDate As DAO.QueryDef
Dim qdfStudyIdSSN As DAO.QueryDef

If Me.Dirty = True Then
Me.Dirty = False
End If

If Visit_Num.Value = 1 Then
Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 2 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())

qdfStudyIdSSN.Execute

Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 3 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())

qdfStudyIdSSN.Execute

'Add a new reminder calldate

Set qdfRemindDate =
CurrentDb.QueryDefs("QueryAddStatusReminderDate")
qdfRemindDate.Parameters("newId") = InterviewID
qdfRemindDate.Parameters("newVisit") = 1
qdfRemindDate.Parameters("newCallDate") = DateValue(Now())
qdfRemindDate.Execute

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
qdfStudyIdSSN.Close
qdfRemindDate.Close
 
I put in the dirty statement below and it is giving me this error:

Object Variable or With Block not set
Click to expand...
If Me.Dirty = True Then
Me.Dirty = False
End If
Click to expand...

Where is this code? What is causing it to run. I assumed from your use of

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord

that it is in a form module. If it's not, then you need to reference the
form that you do want to save:

With Forms("HopeThisFormIsOpenOnTheDesktop")
If .Dirty Then .Dirty = False
End With

If it's not running behind a form, how do you know which record is going
to be saved by running the code above? Seems a bit of a crossed-your-
fingers job to me!

All the best


Tim F
 
Ok here is all of my code for the form Followup Termination.

So do I put this inplace of the if statment that you gave me?
Do I still put Me.Dirty? This form is open when the save button is clicked.
With Forms("Followup Termination")
If .Dirty Then .Dirty = False
End With


Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
'make sure the interview has complete baseline
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdfRemindDate As DAO.QueryDef
Dim qdfStudyIdSSN As DAO.QueryDef
Dim rs As DAO.Recordset

If Me.Dirty = True Then
Me.Dirty = False
End If

save = True
Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStudyIdSSN")
qdfStudyIdSSN.Parameters("newId") = TextInterviewId.Value
qdfStudyIdSSN.Parameters("newSSN") = TextSSN.Value
qdfStudyIdSSN.Execute

Set qdfStudyIdSSN = CurrentDb.QueryDefs("Terminated_Query")
qdfStudyIdSSN.Parameters("newId") = TextInterviewId.Value
qdfStudyIdSSN.Parameters("newSSN") = TextSSN.Value
qdfStudyIdSSN.Parameters("newVisit") = Visit_Num.Value
qdfStudyIdSSN.Execute


'/////////////////////////////////////////////////////////////////////////////////////////////
If Visit_Num.Value = 1 Then
Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 2 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())

qdfStudyIdSSN.Execute

Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 3 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())

qdfStudyIdSSN.Execute

'Add a new reminder calldate

Set qdfRemindDate =
CurrentDb.QueryDefs("QueryAddStatusReminderDate")
qdfRemindDate.Parameters("newId") = InterviewID
qdfRemindDate.Parameters("newVisit") = 1
qdfRemindDate.Parameters("newCallDate") = DateValue(Now())
qdfRemindDate.Execute

Set qdfRemindDate =
CurrentDb.QueryDefs("QueryAddStatusReminderDate")
qdfRemindDate.Parameters("newId") = InterviewID
qdfRemindDate.Parameters("newVisit") = 2
qdfRemindDate.Parameters("newCallDate") = DateValue(Now())
qdfRemindDate.Execute

End If

If Visit_Num.Value = 2 Then
Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 3 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())


'Add a new reminder calldate
Set qdfStudyIdSSN =
CurrentDb.QueryDefs("QueryAddStatusReminderDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 2
qdfStudyIdSSN.Parameters("newCallDate") = DateValue(Now())

qdfStudyIdSSN.Execute

'/////////////////////////////////////////////////////////////////////////////////////////////
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
qdfStudyIdSSN.Close
qdfRemindDate.Close


Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub
 
Ok here is all of my code for the form Followup Termination.


Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
'make sure the interview has complete baseline ...
If Me.Dirty = True Then
Me.Dirty = False
End If
Click to expand...

OK: I understood that you were saying that the Object Variable Not Set
error was being called on the

If Me.Dirty = True

line, but if this is code running on the form, it cannot be so. Which
line is actually causing the error?

A couple of things strike me: using the function CurrentDB() as a
variable is fraught and does not always work as planned in all versions
of Access. Fragments like this sometimes work and sometimes don't:-

Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStudyIdSSN")
qdfStudyIdSSN.Parameters("newId") = TextInterviewId.Value

as the DB goes out of scope immediately and that can bazook the qdf
handle too. I would suggest this to be safe:-

Set db = CurrentDB()
Set qdfStudyIdSSN = db.QueryDefs("QueryAddStudyIdSSN")
qdfStudyIdSSN.Parameters("newId") = TextInterviewId.Value

Secondly, you need to check and double check the spelling. Getting a
parameter name wrong will produce a non-existent object error, because
pdf.Paramters("WrongName") does not indeed exist.

AHAHAHAHAHA -- I've just spotted the line that is really killing you.

On Error GoTo Err_cmdSave_Click

You have to get rid of this in order to have any chance of finding where
errors are actually happening. Error trapping is all very well at
runtime, but only once you have got rid of all the programming errors!
This looks like some crappy piece of MSA Wizardry (you see; all those
people at Redmond _are_ out to get you!) -- a proper error routine would
be reporting which qdf object it could not find. Even once you have fixed
this code, you should still fix this error trap to make it at least a
little bit intelligent.

Sorry: longer answer than I intended!
All the best


Tim F
 
Ok I got it to save now thanks for the help with that. I got rid of the error
statement and moved the me.dirty statments and that seemed to work.

I am having another problem on of my update queries isn't updating a field
in a table. Right now the field is blank but needs have a date put in it.
The problem is in '**' below.

Also here is my query:
PARAMETERS newId Long, newVisit Short, newCallDate DateTime;
INSERT INTO TableInterviewStatus ( interviewId, visit, reminderCallDate )
SELECT [newId] AS Expr1, [newVisit] AS Expr2, [newCallDate] AS Expr3;

I don't mean to be so bother some but you have been a big help. :)


Private Sub cmdSave_Click()
'On Error GoTo Err_cmdSave_Click
'make sure the interview has complete baseline
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdfRemindDate As DAO.QueryDef
Dim qdfStudyIdSSN As DAO.QueryDef
Dim rs As DAO.Recordset

save = True
Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStudyIdSSN")
qdfStudyIdSSN.Parameters("newId") = TextInterviewId.Value
qdfStudyIdSSN.Parameters("newSSN") = TextSSN.Value
qdfStudyIdSSN.Execute

Set qdfStudyIdSSN = CurrentDb.QueryDefs("Terminated_Query")
qdfStudyIdSSN.Parameters("newId") = TextInterviewId.Value
qdfStudyIdSSN.Parameters("newSSN") = TextSSN.Value
qdfStudyIdSSN.Parameters("newVisit") = Visit_Num.Value
qdfStudyIdSSN.Execute


'/////////////////////////////////////////////////////////////////////////////////////////////
If Visit_Num.Value = 1 Then
Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 2 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())
qdfStudyIdSSN.Execute

Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 3 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())
qdfStudyIdSSN.Execute

'Add a new reminder calldate

'*********************************************************
Set qdfRemindDate =
CurrentDb.QueryDefs("QueryAddStatusReminderDate")
qdfRemindDate.Parameters("newId") = InterviewID
qdfRemindDate.Parameters("newVisit") = 1
qdfRemindDate.Parameters("newCallDate") = DateValue(Now())
qdfRemindDate.Execute
'**********************************************************

Set qdfRemindDate =
CurrentDb.QueryDefs("QueryAddStatusReminderDate")
qdfRemindDate.Parameters("newId") = InterviewID
qdfRemindDate.Parameters("newVisit") = 2
qdfRemindDate.Parameters("newCallDate") = DateValue(Now())
qdfRemindDate.Execute

End If


If Visit_Num.Value = 2 Then
Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStatusNextDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 3 'schedule next interview
qdfStudyIdSSN.Parameters("newInterviewDate") = DateValue(Now())
qdfStudyIdSSN.Execute

'Add a new reminder calldate
Set qdfStudyIdSSN =
CurrentDb.QueryDefs("QueryAddStatusReminderDate")
qdfStudyIdSSN.Parameters("newId") = InterviewID
qdfStudyIdSSN.Parameters("newVisit") = 2
qdfStudyIdSSN.Parameters("newCallDate") = DateValue(Now())
qdfStudyIdSSN.Execute

' Set qdfDone = CurrentDb.QueryDefs("QueryAddDoneSession")
' qdfDone.Parameters("newId") = InterviewID
' qdfDone.Parameters("newVisit") = visit
' qdfDone.Execute

End If

If Visit_Num.Value = 3 Then

' Set qdfDone = CurrentDb.QueryDefs("QueryAddDoneSession")
' qdfDone.Parameters("newId") = InterviewID
' qdfDone.Parameters("newVisit") = visit
' qdfDone.Execute

End If


'/////////////////////////////////////////////////////////////////////////////////////////////

If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
qdfStudyIdSSN.Close
qdfRemindDate.Close
End Sub
 
I got it working thanks for you help

Tim Ferguson said:
OK: I understood that you were saying that the Object Variable Not Set
error was being called on the

If Me.Dirty = True

line, but if this is code running on the form, it cannot be so. Which
line is actually causing the error?

A couple of things strike me: using the function CurrentDB() as a
variable is fraught and does not always work as planned in all versions
of Access. Fragments like this sometimes work and sometimes don't:-

Set qdfStudyIdSSN = CurrentDb.QueryDefs("QueryAddStudyIdSSN")
qdfStudyIdSSN.Parameters("newId") = TextInterviewId.Value

as the DB goes out of scope immediately and that can bazook the qdf
handle too. I would suggest this to be safe:-

Set db = CurrentDB()
Set qdfStudyIdSSN = db.QueryDefs("QueryAddStudyIdSSN")
qdfStudyIdSSN.Parameters("newId") = TextInterviewId.Value

Secondly, you need to check and double check the spelling. Getting a
parameter name wrong will produce a non-existent object error, because
pdf.Paramters("WrongName") does not indeed exist.

AHAHAHAHAHA -- I've just spotted the line that is really killing you.

On Error GoTo Err_cmdSave_Click

You have to get rid of this in order to have any chance of finding where
errors are actually happening. Error trapping is all very well at
runtime, but only once you have got rid of all the programming errors!
This looks like some crappy piece of MSA Wizardry (you see; all those
people at Redmond _are_ out to get you!) -- a proper error routine would
be reporting which qdf object it could not find. Even once you have fixed
this code, you should still fix this error trap to make it at least a
little bit intelligent.

Sorry: longer answer than I intended!
All the best


Tim F
Click to expand...
 
Ok I got it to save now thanks for the help with that. I got rid of
the error statement and moved the me.dirty statments and that seemed
to work.
Click to expand...

Okay; well done. Which line was producing the Object Error?
I am having another problem on of my update queries isn't updating a
field in a table. Right now the field is blank but needs have a date
put in it. The problem is in '**' below.

PARAMETERS
newId Long,
newVisit Short,
newCallDate DateTime;
INSERT INTO TableInterviewStatus (
interviewId,
visit,
reminderCallDate )
SELECT
[newId] AS Expr1,
[newVisit] AS Expr2,
[newCallDate] AS Expr3;
Click to expand...

I cannot see much wrong with this: it's a bit prolix but not illegal.
Set qdfRemindDate = _
CurrentDb.QueryDefs("QueryAddStatusReminderDate")
qdfRemindDate.Parameters("newId") = InterviewID
qdfRemindDate.Parameters("newVisit") = 1
qdfRemindDate.Parameters("newCallDate") = DateValue(Now())
qdfRemindDate.Execute
Click to expand...

Ditto. One thing would be to use the dbFailOnError parameter for the
..execute method -- at the moment you are preventing Jet from returning
any error information.

A general note -- don't be afraid of errors; they are your friend.
Especially whilst developing, don't use any suppression because you need
to see what is going on. Later on, when you expose your work to real
users, you need to be able to intercept and hide them, but you still need
to know where they are coming from!
I don't mean to be so bother some but you have been a big help. :)
Click to expand...

No problem :-)

HTH


Tim F
 
Back
Top