delete problem (1 Viewer)

[viperstingray]

HELLO

I have a delete button on my form and I also have a text box that counts the records that I have. One text box shows you "RECORD 1 of 27" which is when viewing all the entries and the other shows you "RECORD 1 of 3 for MICROSOFT" which is more specific.

The problem comes in when you are viewing the records by client and you want to delete one of those records. It can delete all but the last record. When you click the delete button on the last entry for a specific client, you get an error and DeBUG goes to this line '.MoveLast'. This line is used in my counter.

Can anyone think of a way around this???

 

[KeithIT]

can you post the code for the delete function and the counter?

 

[viperstingray]

delete code:
Private Sub command271_Click()
On Error GoTo Err_cmdDelete_Click

DoCmd.SetWarnings False
If MsgBox("Are you sure you want to delete this Audit and its associated measures?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
CLIENT_NAME.SetFocus
End If
Exit_command271_Click:
DoCmd.SetWarnings True
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_command271_Click
End Sub
----------------------------
counter code:
Dim rst1 As DAO.Recordset
Dim lngCount As Long

Set rst1 = Me.RecordsetClone

With rst1
.MoveFirst
.MoveLast <================== the PROBLEM
lngCount = .RecordCount
End With

'Show the result of the record count in the text box (txtRecordNo)
If Me.CurrentRecord = lngCount Then
Command126.Enabled = False
Command129.Enabled = False
Else
Command126.Enabled = True
Command129.Enabled = True
End If
Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount & " record(s) for " & [CLIENT PREFIX]
Me.Text292 = "Record " & Me.CurrentRecord & " of " & lngCount & " records"

 

[viperstingray]

any ideas???

 

[viperstingray]

Am I doing something wrong...

Is my code wrong or is it just a stupid problem. The error that I get when you delete the last record is 'no current record to display' or something of that sort. Is there a way for me to tell the program that when the last record is deleted, go to main menu or pop up a message that would tell the user "no more records to display...return to main menut" (OK ONLY)??

Please help
I present this database at work within one week.

Thanks

 

[RuralGuy]

You need to put in some RecordCount testing:

With rst1
   If .RecordCount > 0 Then
      If Not .BOF Then
         .MoveFirst
      End If
      If Not .EOF Then
         .MoveLast <================== the PROBLEM
      End If
      lngCount = .RecordCount
   Else
      lngCount = 0
   End If
End With

 

[viperstingray]

thanks

that works quite well. There is one more problem that I did realize though, I was hoping you can help me out with it.

WHen you are deleting the last record (ex: 3 of 3) it does not cycle back to record 2 of 2 (or know as 2 of 3 before the deletion). I am trying to code it in such a way that:
if the last record for that client is deleted, load the previous record
if the last record that was deleted was the ONLY record, load the main form.

Any clues. I have been playing with it but have made little progress. I think this kind of code would be put in the delete button...RIGHT?

I appreciate your help

 

[RuralGuy]

I believe you will need to test for only one record and have a different delete routine.

 

[viperstingray]

I am sorry, can you please elaborate

 

[RuralGuy]

I do not have enough information about your tables and form to offer any reasonable solution for that problem. If you want to remove any sensitive data and zip up the mdb and post it then I or someone else could look at it and make suggestions.

 

[viperstingray]

unfortunatley I cannot upload the database online. Sorry about that. I am now trying to do the following:
DELETE BUTTON:
Private Sub command271_Click()
On Error GoTo Err_cmdDelete_Click
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "[client name]=" & "'" & Me![CLIENT NAME] & "'"
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to delete this Audit and its associated measures?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
If Me.CurrentRecord = lngCount Then
stDocName = "Audit Info"
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
End If

CLIENT_NAME.SetFocus
End If
Exit_command271_Click:
DoCmd.SetWarnings True
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_command271_Click
End Sub

I am trying to store the client name and when a record is deleted, It will reload any record for that client. Do you see anything wrong with this code. It is not working but not giving me any error messages.

 

[RuralGuy]

What is the name of the Form on which "command271" command button is located? Is this form bound to a table/query?

 

[viperstingray]

the form name is "Audit Info"
and it is bound to a table named "audit info"

 

[RuralGuy]

Having names with spaces in them will cause you unexpected grief some day. You should use either CamelFontNames or Under_Score_Names.

Issuing the following command from the same form is a real lousy idea and not what you want to do anyway:
DoCmd.OpenForm stDocName, , , stLinkCriteria

Let's try the following code:

Private Sub command271_Click()
On Error GoTo Err_cmdDelete_Click
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "[client name]= '" & Me![CLIENT NAME] & "'"
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to delete this Audit and its " & _
              "associated measures?", _
              vbQuestion + vbYesNo + vbDefaultButton2, _
              "Delete?") = vbYes Then
   If Me.CurrentRecord = lngCount Then
[B]'*** Does the above line mean 1 record left???
'*** I would think something more like:
'*** If lngCount = 1 Then[/B]
      DoCmd.RunCommand acCmdSelectRecord
      DoCmd.RunCommand acCmdDeleteRecord

      DoCmd.Requery               ' Get any changes to the table.
      ' Find the Client again.
      Me.RecordsetClone.FindFirst stLinkCriteria
      If Not Me.RecordsetClone.NoMatch Then
         Me.Bookmark = Me.RecordsetClone.Bookmark
      End If
   Else
      DoCmd.RunCommand acCmdSelectRecord
      DoCmd.RunCommand acCmdDeleteRecord
   End If
   Me.CLIENT_NAME.SetFocus
End If

Exit_command271_Click:
   DoCmd.SetWarnings True
   Exit Sub
Err_cmdDelete_Click:
   MsgBox Err.Description
   Resume Exit_command271_Click
End Sub

 

[viperstingray]

thank you for your response and for all of your trouble. IT STILL DOES NOT WANT TO WORK

Its these little things that really get to me. it seems like such a simple problem. If you are deleting the last record, do not show an empty one, show the preious one for that client. if the deleted record is the only record for that client, go to the main menu. I am about to run out of ideas.

Please post any other ideas that you might have. The good news is that it is not returing any error messages

I'm trying to be positive

 

[RuralGuy]

I have no idea what this means:

...if the deleted record is the only record for that client, go to the main menu.

This is not very helpful:

IT STILL DOES NOT WANT TO WORK

Did it not change something? Since I can not see what is happening, the only way we will succeed is if you are as discriptive as possible about what is happening.

 

[viperstingray]

well, I took the code that you gave me and implemented it into the command button.

The button still deletes records. (so that is ok)

Here is an example of the porblem at hand:
I have a client that has 5 records, I use my 'search form' to go to that clients records and look through them. Let us say that I no longer need one of those records for that client (remember the counter is showing 1 out of 5 records; 2 out of 5 records; etc) so I use the delete button. The button will work fine as long as I am not deleting the LAST entry (5 out 5 records) for that client.

The MAIN problem is the following. When you delete any one of the first 4 records, the next record will apprear and the counter will update automatically. The prolem comes in when deleting the FINAL record. When you are at the 5th record and hit the delete button, an EMPTY form apprears. I do not want that, I need the previous form belonging to that client to apprear. If i return to the main menu and then 'search' for that client again, the record conunter will now say "1 out of 4, 2 out of 4, etc." and that delted record will not show

Don't get me wrong, the button will get rid of the record from all the tables where it is refrenceed but

 

[RuralGuy]

I have a client that has 5 records,...

...When you are at the 5th record and hit the delete button, an EMPTY form apprears. I do not want that, I need the previous form belonging to that client to apprear.

These two statements are in conflict. What previous form? You just deleted all of their records.

 

[viperstingray]

Ok, now I see why you were misunderstanding me

I have a client that has 5 facilities, when I hit the delete button, I want one of the other facilities to appear, not a blank form.

In a nutshell, the way the database works is as follows:
There is a main form called "audit info"
this form accepts inputs regarding a specific client, including facility information.

I use the 'search' form to go to a specific clients set of facilities. Assume I have MICROSOFT as a client. MICROSOFT has 5 facilities: California, Buffalo, Seatle, NY, and Miami. I only want to delete the MIAMI facility from the database; so when I hit the delete button I would want the NY facility to appear. What is currently happening is that it would delete the MIAMI facility but then open up a new and empty 'audit info' form.
If I only have the MIAMI facility and I choose to delete it, I would want to go the the main form, not a new blank form.

 

[RuralGuy]

Why would New York appear rather than Seatle or Buffalo? This is *not* just an idle question.