MsgBox problem 1

[hu5]

Hi,
I use one command button to control 2 queries, after adding all completed records to archive file, it should delete completed records from the original file.

I want to create a prompt message to alert user that there's no more completed records to move when they press the Move button.

The code looks for "...completed...." in the Status field. If it didn't find the letter "completed", the message should pop up immediately when user press Move button.

The following code only came up after it asks the user "Do you want to append....then delete..., you will append 0 record....will delete 0 record....".

Where should I place this code? and is this code only work for the current record listed on the form or for the entire table? Thank you.



Private Sub MoveToArchive_Click()
On Error GoTo Err_MoveToArchive_Click

Dim stDocName1 As String
Dim stDocName2 As String


stDocName1 = "append_to_archive"
DoCmd.OpenQuery stDocName1, acNormal, acEdit

stDocName2 = "delete_completed_records"
DoCmd.OpenQuery stDocName2, acNormal, acEdit

If Me!Status.Value <> ("*" & "completed" & "*") Then
MsgBox ("No more completed record to move.")
End If

Exit_MoveToArchive_Click:
Exit Sub

Err_MoveToArchive_Click:
MsgBox Err.Description
Resume Exit_MoveToArchive_Click


End Sub

 

[PHV]

Try something like this before the 2 queries:
If DCount("*", "[Table Name]", "Status Like '*completed*'") = 0 Then
MsgBox ("No more completed record to move.")
Exit Sub
End If


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CajunCenturion]

As your code stands, you will always execute the append and delete queries before the status field is checked.

When you say "looking for '...completed...' in the status field, do you mean that the status field = "completed", or that the word completed appears somewhere in the status field? Currently you're checking for the exact string "*Completed*". These asteriks do not behave as they do in an SQL LIKE clause, but rather treated as literal characters.

The code will be triggered when the MoveToArchive button is pressed. Whether it acts on only the current record or the entire table depends on the two query definitions.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein