messege box

[javier_83]

Hi i have this code on a bottom that move me a record to another table and then delete it from the table it was!!

and works perfect

the code is like this

Private Sub Comando0_Click()
On Error GoTo Err_Comando91_Click
Dim stDocName As String
stDocName = "historial_mover"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "historial_eliminacion"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.Close

Exit_Comando91_Click:
Exit Sub

Err_Comando91_Click:
MsgBox Err.Description
Resume Exit_Comando91_Click
End Sub

the querys that move and delete have some conditions to make the change, so if the record doesnt have all conditions it doesn execute the query

my question is how can i mkae a messege box that can tell me that the querys did they work, and other who can tell me that it didint??


thx for all your help!!

 

[FoFa]

You could create a count query or use dcount with the same criteria and see if it returns zero or not zero and use that, as one way.

 

[javier_83]

how can i create a count query??

 

[javier_83]

this is my query

INSERT INTO historial ( id_persona, id_modelo, serial, Assettag, [Fecha de Entrega], [Fecha de Vencimiento], Comentarios, Status )
SELECT registroasset.id_persona, registroasset.id_modelo, registroasset.serial, registroasset.Assettag, registroasset.[Fecha de Entrega], registroasset.[Fecha de Vencimiento], registroasset.Comentarios, registroasset.Status
FROM registroasset
WHERE (((registroasset.id_persona)=[Forms]![Registro_Assets]![id_persona]) AND ((registroasset.[Fecha de Vencimiento]) Is Not Null) AND ((registroasset.Status)=2)) OR (((registroasset.Status)=3));:

 

[javier_83]

please, someone help me jejeje

i ask because on the query design doesnt let me choose the count option

 

[dkinley]

I am not sure what you should do but I think what FoFa was suggesting that before you run your query, you run a different query which does a count.

For instance, you run a query that counts all of your records. Let's say the total is 100. Then you run your delete query. Now you run the count query again and the total is 90.

Now your message box comes back and tells you it deleted (100 - 90) 10 records.

-dK

 

[javier_83]

thanks again dk, let me try to make that count function because i never use it

 

[javier_83]

the problem with the query is that doesnt have the count option!!

because its a Append Query, how can i do it??

 

[dkinley]

A different query.

Like I said, I am not sure what you should do to do this check. I'm just going off Fofa's suggestion - better than anything I could come up with which was nada.

Just to get you going in this method I would simplify and do something like this in the code of the form you run this query from ...

Dim lBeforeCount As Long
Dim lAfterCount As Long
 
     lBeforeCount = DCount("[TableID]", "TableName")
     'Do your delete query
     lAfterCount = DCount("[TableID]", "TableName")
 
MsgBox "You deleted " & (lBeforeCount - lAfterCount) & " records."

I am not in love with this because it might bog down depending on how many records you have. There may be a simpler and much more efficient way, but I've never did anything like this because I don't let users do any sort of batches.

-dK

 

[javier_83]

oh ok, thanks!!

my problem is that i move some records, with this query

INSERT INTO historial ( id_persona, id_modelo, serial, Assettag, [Fecha de Entrega], [Fecha de Vencimiento], Comentarios, Status )
SELECT registroasset.id_persona, registroasset.id_modelo, registroasset.serial, registroasset.Assettag, registroasset.[Fecha de Entrega], registroasset.[Fecha de Vencimiento], registroasset.Comentarios, registroasset.Status
FROM registroasset
WHERE (((registroasset.id_persona)=[Forms]![Registro_Assets]![id_persona]) AND ((registroasset.[Fecha de Vencimiento]) Is Not Null) AND ((registroasset.Status)=2)) OR (((registroasset.Status)=3));:

i use this botton to ejecute the query

Private Sub Comando0_Click()
On Error GoTo Err_Comando91_Click
Dim stDocName As String
stDocName = "historial_mover"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.Close

Exit_Comando91_Click:
Exit Sub

Err_Comando91_Click:
MsgBox Err.Description
Resume Exit_Comando91_Click
End Sub

so i want to see how to add a messege box, that if the query is ejecuted appear mee a msg box that tell me that the movement was done

 

[dkinley]

I think I have it edited for your database.

Private Sub Comando091_Click()
On Error GoTo Err_Comando91_Click
 
     Dim lHistorialBeforeCount As Long
     Dim lHistorialAfterCount As Long
     Dim stDocName As String
 
     stDocName = "historial_mover"
     lHistorialBeforeCount = Nz(DCount("[id_persona]", "historial"),0)
     DoCmd.OpenQuery stDocName, acNormal, acEdit
     lHistorialAfterCount = Nz(DCount("[id_persona]", "historial"),0)
 
     If (lHistorialAfterCount - lHistorialBeforeCount) = 0 Then
          MsgBox "No records were moved."
     ElseIf (lHistorialAfterCount - lHistorialBeforeCount) > 0 Then
          MsgBox "You moved" & (lHistorialAfterCount - lHistorialBeforeCount) & " records."
     Else
          "System error."
     End If
 
     DoCmd.Close
 
Exit_Comando91_Click:
     Exit Sub
 
Err_Comando91_Click:
     MsgBox Err.Description
     Resume Exit_Comando91_Click
 
End Sub

-dK

 

[javier_83]

I think I have it edited for your database.

Private Sub Comando091_Click()
On Error GoTo Err_Comando91_Click
 
     Dim lHistorialBeforeCount As Long
     Dim lHistorialAfterCount As Long
     Dim stDocName As String
 
     stDocName = "historial_mover"
     lHistorialBeforeCount = Nz(DCount("[id_persona]", "historial"),0)
     DoCmd.OpenQuery stDocName, acNormal, acEdit
     lHistorialAfterCount = Nz(DCount("[id_persona]", "historial"),0)
 
     If (lHistorialAfterCount - lHistorialBeforeCount) = 0 Then
          MsgBox "No records were moved."
     ElseIf (lHistorialAfterCount - lHistorialBeforeCount) > 0 Then
          MsgBox "You moved" & (lHistorialAfterCount - lHistorialBeforeCount) & " records."
     Else
          "System error."
     End If
 
     DoCmd.Close
 
Exit_Comando91_Click:
     Exit Sub
 
Err_Comando91_Click:
     MsgBox Err.Description
     Resume Exit_Comando91_Click
 
End Sub

-dK

you are a ACcess GOD!! THNKLS A LOT MEN!! YOU TEACH ME A LOT IN 2 DAYS!!

THANKS!!

 

[dkinley]

heh! Not hardly. Just banged away at the keyboard a little more than you have.

-dK

 

[javier_83]

jee well it mutch better!! THANKS A LOT!!