Help with popup msg box

lookforsmt

Registered User.
Local time
Today, 21:39
Joined
Dec 26, 2011
Messages
672
HI! all

I have button on form which when on-Click will trigger series of querydef and then execute the query. Between the querydef there is a popup msg box which will provide the count of transactions.

Code:
Private Sub cmd_Update_envbrcd_Click()
On Err Goto Park

	Dim qdef As DAO.QueryDef
	Dim rstenvbrcd As Recordset
	Set qdef = CurrentDb.QueryDefs("14 Update envbrcdTochqbrcd")
	qdef.Execute
	MsgBox qdef.RecordsAffected & " " & " Envelope Barcode updated to tbl_ICCS
    Master"
	Set qdef = Nothing

    Park:
	If Err.Number > 0 Then
		MsgBox Err.Number & " " & Err.Description
	End If

	Const cstrPrompt As String = _
		"Are you sure you want to Delete this record? Yes/No"
	If MsgBox(cstrPrompt, vbQuestionn + vbYesNo) = vbYes Then

	Set qdef = CurrentDB.QueryDefs("14 Delete tblTempenvelopeBarcode")
	qdef.Execute

	MsgBox qdef.RecordsAffected & " " & " Records Deleted from tbl_Temp envbrcd"
	Set qdef = Nothing

	If Me.Dirty Then
	Me.Dirty = False ' save the record
	End If
	End If
    End Sub

Above code is working fine but I need the popup msg box (Yes or No) to also display the count of the records deleted in the 2nd msgbox instead of showing in the 3rd msg box.

I have tried all the possible ways but unable to do this, can anyone help me in this
Thanks
 
This looks to me like 2 completely separate and unrelated functions somehow trying to be run at the same time?

Normally updating records would be one process and deleting them another one?

It looks like you have copied and pasted a lot of bits of code into one routine. The error handler is strange - that normally is the last part of any procedure, as you don't want any code to run after it has been called.
 
Thanks Minty, I am not familiar about the vba code and what comes first.
But I wanted to run vba code in the below sequence on the onClick event:

1) Run the update query.
2) Popup msg box will display the total count of records updated
3) Run the delete query
4) Popup msg box to be displayed with total count of records deleted and in the same box yes/no question, so the user can either accept or reject the event.

Can this be possible.

Kindly note I was using the update & delete query on the same onclick button which was working fine but after I placed the msgbox code it is displaying the msg before the event. I need this at the last so the user can decide whether to continue or cancel the event.

hope I was clear
 
I seem to recall in my searches that MS stated that you would run a Select query of the same criteria to identify those records that would be affected by the delete query?

I cannot find the link now though?
 
You would need to query the number of records that would be deleted by your query so you could tell users before hand.

The simplest way would be to use a DCount using the same criteria that you use for the deletion.
 
Yes - And to get that information you will have to do some extra work.
What is the sql for your delete query? (Go to the query and select SQL view).
 
below is the delete query sql

Code:
DELETE temp_envbrcd.*
FROM temp_envbrcd;
 
Right - to get the count of affected records you simply need to use
Code:
dim lCount as Long
lCount = DCount("*",temp_envbrcd)

And put lCount in your msgbox
 
Thanks Minty

I tried putting the code but it still not working. can I attach my db if you look at the code. pls
 
Just post up the code as you have it now, and highlight which line isn't working and the error message. "Not Working" doesn't help us.
 
hi! Monty

below code which I tired to put your code in but it gives me error

Code:
Private Sub cmd_Updateenvbrcd_Click()
  On Err GoTo Park
  
    Dim qdef As DAO.QueryDef
    Dim lCount as Long
    Dim rstCapture_due_date_c As Recordset
    Set qdef = CurrentDb.QueryDefs("qry_update_chq_env")
    qdef.Execute
    MsgBox qdef.RecordsAffected & " " & "env_brcd records updated to  tbl_Master"
    Set qdef = Nothing

Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If

Const cstrPrompt As String = _
        "Are you sure you want to Delete this record? Yes/No"
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
    lCount = DCount("*",temp_envbrcd)
    
    Set qdef = CurrentDb.QueryDefs("qry_delete_chq_env")
    qdef.Execute
    MsgBox qdef.RecordsAffected & " " & "env_brcd records deleted"
    Set qdef = Nothing

    If Me.Dirty Then
    Me.Dirty = False ' save the record
    End If
    End If
End Sub

will you be able to help me pls
 
Here is a tided up version - you weren't doing anything with the count you had.
Code:
Private Sub cmd_Updateenvbrcd_Click()


    Dim qdef            As DAO.QueryDef
    Dim lCount          As Long
    Dim rstCapture_due_date_c As Recordset
    Dim sMsg            As String

    On Err GoTo Park
    
    Set qdef = CurrentDb.QueryDefs("qry_update_chq_env")
    qdef.Execute
    MsgBox qdef.RecordsAffected & " " & "env_brcd records updated to tbl_Master"
    Set qdef = Nothing

    lCount = DCount("*", "temp_envbrcd")      [COLOR="green"] 'Get the number of records that would be deleted[/COLOR]
    sMsg = "Are you sure you want to Delete " & lCount & " records? Yes/No"    [COLOR="Green"]' Put them in the message[/COLOR]

    If MsgBox(sMsg, vbQuestion + vbYesNo) = vbYes Then
        Set qdef = CurrentDb.QueryDefs("qry_delete_chq_env")
        qdef.Execute
        MsgBox qdef.RecordsAffected & " " & "env_brcd records deleted"
        Set qdef = Nothing

        If Me.Dirty Then
            Me.Dirty = False                  [COLOR="green"] ' save the record[/COLOR]
        End If
    End If
    Exit Sub

Park:                                          [COLOR="Green"]' error handler only called if there is an error and no further code is run.[/COLOR]
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If
End Sub

I would recommend you read up and understand what the bits of code do you are gathering from the web.

There are a load of good tutorials available on youtube and links in peoples signatures here that will help you.
 
Thank Minty, yes this I was looking for.
You are right, I need to learn and understand these codes. But without initial training I have tried to do copying from the net and wherever help is required I refer to the forum.
Its because of you guys that I am thankful to for helping us.
 

Users who are viewing this thread

Back
Top Bottom