Need help removing a record

[gcrutch]

i have some forms and some reports in an access application. Ok, this is what i have:
i have a form that adds a new file and then i have a form that closes a file. and then i have some reports that users will run that shows "active" files and that shows "closed" files. Once they open a new file it creates a record that has an "active" status. then sometime later they will close that file. Once they close the file it will get a status of "closed". But the problem is if they run a report with all active files by a particular attorney, it will show the active record even though it has been closed and has a "closed" status. what i need to be able to do is, once the file is closed to some how remove that record that had the "active" status so if the user run a report for a particular attorney with all active records it won't show the one that was closed too. i hope all of that make sense. Here is my code to close a file. I was thinking it needs to be done somewhere in this code:

Private Sub cmdCloseFile_Click()
On Error GoTo Err_cmdCloseFile_Click
Dim Result
Dim rst As ADODB.Recordset
Dim adocmd As ADODB.Command
sqlStmt = ""
Set adocmd = New ADODB.Command
Set rst = New ADODB.Recordset
With adocmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "Select * from dbo_tbl_Files where Status = 'closed' and FileNo = " & Me.FileNo
End With
rst.CursorLocation = adUseClient
rst.Open adocmd, , adOpenStatic, adLockReadOnly
If rst.RecordCount > 0 Then
MsgBox "This File has already been closed.", vbCritical, "FileNo Confirmation"
Result = vbNo
DoCmd.Close
DoCmd.OpenForm "Legal Files Main"
Else
Result = MsgBox("Are you sure you want to close File No. " + Str$(Me.FileNo) + "?", vbYesNo, "Add File")
If Result = VbMsgBoxResult.vbNo Then

Me.Room = ""
Me.BoxNo = ""
Me.DateClosed = ""



DoCmd.Close
DoCmd.OpenForm "Legal Files Main"

End If
End If

rst.Close
Set rst = Nothing
Set adocmd = Nothing
If Result = vbNo Then
Exit Sub
End If

Dim rstFile As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String

rstFile.Open "dbo_tbl_Files", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rstFile.AddNew
rstFile!FileNo = Me.FileNo
rstFile!FileName = Me.FileName
rstFile!Dept = Me.Dept
rstFile!Room = Me.Room
rstFile!BoxNo = Me.BoxNo
rstFile!DateClosed = Me.DateClosed
rstFile!AttyID = Me.AttyID
rstFile!Description = Me.Description
rstFile!Status = "Closed"

rstFile.Update
rstFile.Close

MsgBox "File Successfully Closed."

Msg = "Do you want to close another file?"
Response = MsgBox(Msg, vbYesNo)
If Response = vbYes Then
DoCmd.Save
DoCmd.Close
DoCmd.OpenForm "Close File"
Else
DoCmd.Close
DoCmd.OpenForm "Legal Files Main"
End If
Exit_cmdCloseFile_Click:
Exit Sub
Err_cmdCloseFile_Click:
MsgBox Err.Description
Resume Exit_cmdCloseFile_Click

End Sub

 

[jzwp22]

...they run a report with all active files by a particular attorney, it will show the active record even though it has been closed and has a "closed" status

You have to filter the record source of the report to only look for those records that have status = active. So what table or query provides the record source to the report?

 

[gcrutch]

yes, i have a query that does that and say for instance. The user opened a file on 1/22/2008 and it has an "active" status. on 12/4/2008 the user closes the file and now it has a "closed" status. But when the user runs the report for a particular atty it still shows that record that was opened on 1/22/2008 even though it's now closed. I need that record on 1/22/2008 with the "active" status to now be removed. So that it will only show that particular file no. on the "closed" reports.

 

[jzwp22]

Can you provide the SQL text of the record source for the report?

 

[gcrutch]

yes here it is....


SELECT Files.FileNo, Files.DateOpened, (RTrim(Attorney.FName) & ' ' & RTrim(Attorney.LName)) AS Attorney, (RTrim(Secretary.FName) & ' ' & RTrim(Secretary.LName)) AS Secretary, Files.FileName, Files.Dept, Files.Description, Files.Status
FROM (dbo_tbl_Files AS Files INNER JOIN dbo_tbl_Employees AS Secretary ON Files.SectID = Secretary.EmpID) INNER JOIN dbo_tbl_Employees AS Attorney ON Files.AttyID = Attorney.EmpID
WHERE (((Files.Status)="Active") AND ((Attorney.FName)=[Which Attorney]));

 

[jzwp22]

Based on the following section of your code, it looks like you are appending a brand new record where the only thing that is different is the status rather than updating the existing record. Therefore, you will have 2 records for each file one says active and the other closed. Is that what you have in your table? I don't think that was the intent of your code was it?

rstFile.Open "dbo_tbl_Files", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rstFile.AddNew
rstFile!FileNo = Me.FileNo
rstFile!FileName = Me.FileName
rstFile!Dept = Me.Dept
rstFile!Room = Me.Room
rstFile!BoxNo = Me.BoxNo
rstFile!DateClosed = Me.DateClosed
rstFile!AttyID = Me.AttyID
rstFile!Description = Me.Description
rstFile!Status = "Closed"

 

[gcrutch]

you are absolutely right, now that i think about it. i guess i want to update the same record.

 

[jzwp22]

So, you'll have to change the section of your code when you want to close an active file. Assuming that the field FileNo in your table is a numeric datatype, the code would go something like this (not tested):

Dim rstFile As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String

Dim mySQL as string

mySQL="SELECT * FROM dbo_tblFiles WHERE FileNo=" & me.FileNo

rstFile.Open mySQL CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rstFile!Status = "Closed"
rstFile.Update
rstFile.Close

MsgBox "File Successfully Closed."

 

[gcrutch]

i have added that code and this is the error i'm getting:

error: Command Text was not set for the command object

here is the code again:

Private Sub cmdCloseFile_Click()
On Error GoTo Err_cmdCloseFile_Click
Dim Result
Dim rst As ADODB.Recordset
Dim adocmd As ADODB.Command

Set adocmd = New ADODB.Command
Set rst = New ADODB.Recordset
With adocmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "Select * from dbo_tbl_Files where Status = 'closed' and FileNo = " & Me.FileNo
End With
rst.CursorLocation = adUseClient
rst.Open adocmd, , adOpenStatic, adLockReadOnly
If rst.RecordCount > 0 Then
MsgBox "This File has already been closed.", vbCritical, "FileNo Confirmation"
Result = vbNo
DoCmd.Close
DoCmd.OpenForm "Legal Files Main"
Else
Result = MsgBox("Are you sure you want to close File No. " + Str$(Me.FileNo) + "?", vbYesNo, "Add File")
If Result = VbMsgBoxResult.vbNo Then

Me.Room = ""
Me.BoxNo = ""
Me.DateClosed = ""


DoCmd.Close
DoCmd.OpenForm "Legal Files Main"

End If
End If

rst.Close
Set rst = Nothing
Set adocmd = Nothing
If Result = vbNo Then
Exit Sub
End If

Dim rstFile As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String
Dim sqlstmt As String
rstFile.Open sqlstmt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rstFile!Status = "Closed"

rstFile.Update
rstFile.Close

MsgBox "File Successfully Closed."

Msg = "Do you want to close another file?"
Response = MsgBox(Msg, vbYesNo)
If Response = vbYes Then
DoCmd.Save
DoCmd.Close
DoCmd.OpenForm "Close File"
Else
DoCmd.Close
DoCmd.OpenForm "Legal Files Main"
End If
Exit_cmdCloseFile_Click:
Exit Sub
Err_cmdCloseFile_Click:
MsgBox Err.Description
Resume Exit_cmdCloseFile_Click




End Sub

 

[gcrutch]

dah...i guess i need to add the SQL stmt...

 

[gcrutch]

ok it worked one time but now i'm getting that Write Conflict error box...don't understand why

 

[jzwp22]

Are you executing this code from a form that is bound to the table that is being updated?

 

[gcrutch]

yes I am but my record source is a query

 

[jzwp22]

Does the query include the dbo_tbl_Files table?

I'm am thinking that you may not need all this code & the recordsets. If you are using either a continuous form or the single record form view you should be able to show or not show the button depending on the current status of the record. If the file is already closed, do not show the close file button, if the file is open then show the button. In the on click event of the button, you would just need to set the control to Closed


me.ControlnameThatIsBoundtotheStatusField="Closed"

 

[gcrutch]

Yes the query does include the Files table..I have left work so I will continue with this tomorrow. I really appreciate all your help today. I may be calling on you again tomorrow, LOL Thanks Again!

 

[gcrutch]

I'm just using a single form. But I think i need all of the code because i need it to tell the user if they've already closed that file or not. I am a total loss....

 

[jzwp22]

If the form is bound to the table (or a query that includes the table) you should be able to just update the form control that holds the status value without going through all of the code.

Would it be possible to post your database? You can make a copy of it and purge all of the data and post that if you have sensitive info.

 

[gcrutch]

yes, but i'm not sure how to post the database

 

[jzwp22]

In the tools above where you type your reply there is a paper clip. Just click on it and it will open a window that will allow you to browse and select your database file. Once you have the file selected, click the Upload button and then close the window and then submit your reply as usual.

 

[gcrutch]

let me know if you can't open it.