Save Current Record - not working

[lcook1974]

Good Afternoon Everyone,
So i've been working on this Death record review form (unbound) so that many people can access it based on their hospital at the same time.

I'm having a problem with saving the values in the contols until I can update the batch at the end. I keep receiving an error message.
"Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

Could anyone help me out?

Below is the code I am using for the form.

Option Compare Database
'this is the table I pull information from our SQL server from
Dim rs As ADODB.Recordset
'this is the actual table to write the information too
Dim rsDRR As ADODB.Recordset
Dim db As Database
Dim strConnection As String
Dim cnDRRTable As ADODB.Connection
 
 
Private Sub Form_Load()
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                "Data Source = " & CurrentProject.path & "\MasterDBv2_DATA.mdb;"
 
Set db = CurrentDb
Set rs = New ADODB.Recordset
Set rsDRR = New ADODB.Recordset
Set cnDRRTable = New ADODB.Connection
cnDRRTable.Open strConnection
 
With rsDRR
     .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .ActiveConnection = Nothing
    'Open the recordset for the form
    .Open "Select * From tblDeathRecordReview ", CurrentProject.Connection
End With
With rs
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .ActiveConnection = Nothing
    'Open the recordset for the form
    .Open "Select * From dbo_DeathRecordReview WHERE Shortname = 'CMC' ORDER By CaseFileID DESC", CurrentProject.Connection
End With
Me.txtCaseFileID = rs!CaseFileID
Me.txtPatientName = rs!Donor
Me.txtAge = rs!Age
Me.txtReferOrganization = rs!Shortname
Me.txtUnit = rs!Unit
Me.txtOnVent = rs!ReferOnVent
Me.txtReferralClass = rs!Referral_Classification
Me.txtPotDonorType = rs!PotentialDonorType
Me.txtDeathDate = rs!DeathDate
Me.txtReferralDate = rs!ReferralDate
Me.txtTriggersMet = rs!ClinicalTriggersMet
Me.txtCauseOfDeath = rs!CauseOfDeath
Me.txtMOD = rs!MOD
Me.txtOPOCauseOfDeath = rs!OPOCauseOfDeath
Me.txtCircumOfDeath = rs!COD
Me.txtOrganOutcome = rs!OrganOutcome
Me.txtTissueOutcome = rs!TissueOutcome
Me.txtEyeOutcome = rs!EyeOutcome
Me.txtTimelyOrgan = rs!OrganTimely
Me.txtTimelyTissue = rs!TissueTimely
End Sub
Sub PopulateControlsOnForm()
'Use this function to populate controls on the DRR form
'with the current record from the navigation buttons
If Not rs.BOF And Not rs.EOF Then
    Me.txtCaseFileID = rs!CaseFileID
Me.txtPatientName = rs!Donor
Me.txtAge = rs!Age
Me.txtReferOrganization = rs!Shortname
Me.txtUnit = rs!Unit
Me.txtOnVent = rs!ReferOnVent
Me.txtReferralClass = rs!Referral_Classification
Me.txtPotDonorType = rs!PotentialDonorType
Me.txtDeathDate = rs!DeathDate
Me.txtReferralDate = rs!ReferralDate
Me.txtTriggersMet = rs!ClinicalTriggersMet
Me.txtCauseOfDeath = rs!CauseOfDeath
Me.txtMOD = rs!MOD
Me.txtOPOCauseOfDeath = rs!OPOCauseOfDeath
Me.txtCircumOfDeath = rs!COD
Me.txtOrganOutcome = rs!OrganOutcome
Me.txtTissueOutcome = rs!TissueOutcome
Me.txtEyeOutcome = rs!EyeOutcome
Me.txtTimelyOrgan = rs!OrganTimely
Me.txtTimelyTissue = rs!TissueTimely
ElseIf rs.BOF Then
    rs.MoveNext
ElseIf rs.EOF Then
    rs.MovePrevious
End If
End Sub
 
Private Sub cmdPrevRecord_Click()
On Error GoTo Err_cmdPrevRecord_Click
Call SaveCurrentRecord
If Not rs.EOF Then
    rs.MovePrevious
    Call PopulateControlsOnForm
End If
 
Exit_cmdPrevRecord_Click:
    Exit Sub
Err_cmdPrevRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrevRecord_Click
 
End Sub
Private Sub cmdNextRecord1_Click()
On Error GoTo Err_cmdNextRecord1_Click
Call SaveCurrentRecord
If Not rs.EOF Then
    rs.MoveNext
    Call PopulateControlsOnForm
    Call ClearControlsOnForm
End If
 
Exit_cmdNextRecord1_Click:
    Exit Sub
Err_cmdNextRecord1_Click:
    MsgBox Err.Description
    Resume Exit_cmdNextRecord1_Click
 
End Sub
Private Sub cmdLastRecord_Click()
On Error GoTo Err_cmdLastRecord_Click
Call SaveCurrentRecord
  If Not rs.EOF Then
    rs.MoveLast
    Call PopulateControlsOnForm
End If
 
Exit_cmdLastRecord_Click:
    Exit Sub
Err_cmdLastRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdLastRecord_Click
 
End Sub
 
Private Sub cmdFirstRecord_Click()
On Error GoTo Err_cmdFirstRecord_Click
    Call SaveCurrentRecord
 
   If Not rs.EOF Then
    rs.MoveFirst
    Call PopulateControlsOnForm
End If
 
Exit_cmdFirstRecord_Click:
    Exit Sub
Err_cmdFirstRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdFirstRecord_Click
 
End Sub
 
 
'Here is where I am having the issue...
'All of the DRR* fields are the field names in the table.
'Thought this was suppose to hole the information until the connection 
'was re-established?
 
Sub SaveCurrentRecord()
If Not rs.BOF And Not rs.EOF Then
    rsDRR!DRRCaseFileID = Me.txtCaseFileID
    rsDRR!DRRCompletedBy = Me.txtDRRCompletedBy
    rsDRR!DRRDateCompleted = Me.dtDRRDateCompleted
    rsDRR!DRRNeedFollowup = Me.ckDRRNeedFollowup
    rsDRR!DRRComments = Me.mDRRComments
    rsDRR!DRRMissed_EnteredTC = Me.ckMissedTC
    rsDRR!DRRMissed_Comments = Me.txtMissedTCComment
    rsDRR!DRRComplete = Me.ckDRRCompleted
End If
End Sub
 
 
Sub ClearControlsOnForm()
'Clear the input values
    Me.txtCaseFileID = ""
    Me.txtDRRCompletedBy = ""
    Me.dtDRRDateCompleted = ""
    Me.ckDRRNeedFollowup = ""
    Me.mDRRComments = ""
    Me.ckMissedTC = ""
    Me.txtMissedTCComment = ""
    Me.ckDRRCompleted = ""
End Sub
 
 
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
Call SaveCurrentRecord
rsDRR.UpdateBatch
 
Exit_cmdSave_Click:
    Exit Sub
Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
 
End Sub

 

[boblarson]

Okay, first of all -

1. Is this code that you have here in a database that is DIFFERENT from the one called out in the connection string?
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = " & CurrentProject.path & "\MasterDBv2_DATA.mdb;"

or is this connection to the same database the code is in?

If it is to the same database, you do NOT use that type of connection you just set the connection to CurrentProject.Connection

2. If the recordset comes back as EOF AND BOF that means that there are no records in the recordset.

 

[lcook1974]

Actually it is the same database tables. Some tables are linked to SQL server (pull and review data), others I created to collect the data needed.

What I want to do is create an unbound form that every one can use at the same time, They'll need to select their hospitals and a date range by month. Right now if it's all in the one database they get the error message "database locked..."

I have another DB hidden where the tables will be. Since 90% of the data is pulled from the SQL server (Electronic medical records) and the other 10% is just for them to review it and say yes we reviewed this case.

If the DB (data tables) in the same folder, how should I reference it? I wanted to update the table with VBA (since I'm still learning it) but if SQL insert statement is just as good then I'll do that.

This application will be housed on a 'shared server' for office. so I feel it's almost as if it's 'on one machine' because I can't really put the Front End of the DB on anyone's PC. They are assigned laptops but use the PC's at the hospital instead. (so they don't have to bring them if they dont' need to)

 

[boblarson]

If they are linked tables you just need to use

CurrentProject.Connection

for your connection. If you don't you will get a Too Many Connections error (or something like that).

 

[lcook1974]

Hi Bob,
It's still not playing nice...

I am uploading a demo db so that you can see what it's doing.

Open the DeathRecordReview DB
Open the only form in there.
I put a couple of labels explaining what I want it to do.

Is there a better to go about this?

Larry

 

[boblarson]

I don't know why you are trying to use an unbound form and not linking to the tables. I currently run several databases where we have it bound to SQL server AND to one or two (and in a couple of cases three) Access backend databases. Multiple users can update records in the same table. They just can't modify the SAME record at the same time (as would your situation be as well regardless).

But, you have a problem where users will be using the same frontend and that can be a problem. Your users, since they can't have the frontend should have their own folder with a copy of the frontend there. Don't share the frontend file - period.

 

[lcook1974]

Okay, I'll check that out and see what I can do with it.

I thought that if you have 'Bound' form to a table, that when the form is open, it locks the table so no one else can open it.

 

[boblarson]

I thought that if you have 'Bound' form to a table, that when the form is open, it locks the table so no one else can open it.

Not if you have the Default Record Locking set to NO LOCKS and then the OPEN DATABASES USING RECORD LEVEL LOCKING checked.

That way it uses optimistic locking - it will only lock the record at the time the actual update occurs and then release again.

 

[lcook1974]

Both are correct...

now on to the task at hand...

I'll let you know how it's working.

Thanks Bob!
Larry

 

[lcook1974]

Bob...you just made a 10000 watt light bulb come on!! THANK YOU!!!

The whole point for me to do unbound forms was to be able to get everyone on at the same time....After reading my and your threads again and again (in case i missed something)...I have put front ends on serveral PC's and in folders on the shared drive (as you suggested) and it's working great. Once I get these new additions to the DB, everyone gets some 'new' training.

Thanks again!!

larry