Saving a Record Prior to Running Action Query

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

I need some help from the group. I have a form, which has an option
group with three options. When I select the first option, it firsts
checks the Remarks text field for Null. If it is null, then I have a
message box telling the user he must first enter remarks. On selecting
OK on the message box, the code sets the value of the option group to
null and sets focus to the Remarks text box. After the Remarks field
has been updated, the user then selects the first option again, the code
then runs two action queries, an append query that archives the data to
another table and then a delete query, which deletes the record from the
original table. Both of the queries work fine. What is not happening
is the record is not updating the Remarks field or the Option Group
selection in the original table before performing the append and delete
actions. I have tried to use SendKeys to save the record before the
queries run, but that action is not saving the record. I would
appreciate any ideas on how to save the remarks and the option group
selection to the original table before the action queries run. The code
is executed on the AfterUpdate event of the option group and I have
included the code below. Thanks in advance.

Private Sub frameData_AfterUpdate()
Dim Iret As Byte
Dim Msg As String
Dim Msg1 As String
Dim Msg2 As String

Msg = "Have you provided remarks to explain the reason for cancellation?"
Msg = Msg + " If not select No and enter an appropriate remark."
Msg = Msg + " If you have, select Yes."
Msg1 = "Selecting this option indicates you are not submitting a Travel
Claim for local travel. Have you made an appropriate remark? If Yes, do
you wish to continue?"
Msg2 = "Do you wish to Transfer this data to the committed Travel Form?"
Select Case frameData

Case 1

If IsNull(Me!Rmks) Then
MsgBox "You must enter a reason for cancellation in the Remarks
field!", vbOKOnly, "Rmks Required"
Me!frameData.Value = ""
Me!Rmks.SetFocus
Else
SendKeys "^S"
DoCmd.OpenQuery "qryArchieveAntTable"
DoCmd.OpenQuery "qryArchieveDeleteRecord"
End If

Case 2

iRetval = MsgBox(Msg1, 36, "Confirm Please")

If iRetval = 6 Then
DoCmd.OpenQuery "qryArchieveAntTable"
DoCmd.OpenQuery "qryArchieveDeleteRecord"
ElseIf iRetval = 7 Then
Me!frameData.Value = ""
Me!Rmks.SetFocus
End If

Case 3
iRetval = MsgBox(Msg2, 36, "Confirm Please")
If iRetval = 6 Then
Dim stDocName As String
stDocName = "qryApp_Proj_Trvl"

DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OpenQuery "qryArchieveDeleteRecord"

ElseIf iRetval = 7 Then
Me!frameData.Value = ""
End If
 
If you are working with a bound form, as it sounds you are, try

Me.Dirty = False

That's about the simplest way to save a record. But, just in case, you may
want to put some logic in to make sure the user has actually entered
something in any required fields, and give them a MsgBox if they have not.

Larry Linson
Microsoft Access MVP
 
Larry,
Thank you for the reponse, however with minimal coding experience, not
sure I completely understand the solution you recommended. The form is
bound to a query like you expected. Not sure where the Me.Dirty = False
is suppose to be entered. I would imagine it should be entered in the
afterupdate event of the OptionGroup. Is there any other code I need to
enter to execute the save function or does the Me!Dirty make that
happen? I do have the message box setup on the option group that checks
to make sure there are remarks each time a selection is made. Should I
have another message box related to this action? Thanks again, I really
appreciate your help.
 
Back
Top