Refresh Subform with Save Buttom

[puppy39]

I have a main form called Companies and two subforms in a Tabcontrol

TAB1 - SubformActive, RecordSource: qryActive, Criteria: Status = "Active"
TAB2 - SubfromInactive RecordSource: qryInactive, Criteria: Status = "Inactive"

When I change the combo box from active to inactive on the forms then click on the saved button I get the following message:

"The data was added to the database but the data won't be displayed in the form becuase it doesn't satisfy the criteria in the underlying record source"

I click OK and click my save button again and then everything is refreshed correctly.

How do I get rid of the message popping up and having to click on the save button twice. Any suggestions

 

[kzutter]

My guess is that qryActive is only looking at "active" records and when you change the status to inactive, the query no longer has that record in it recordset, so it cannot show it to you.

I would have to see the code behind the 'saved' button and possible the queries before I (or anyone else, probably) can recommend a solution.

 

[puppy39]

Thank you for the reply!

The code in the Save Button is

Private Sub bttnSave_Click()
On Error GoTo Err_bttnSave_Click

DoCmd.RunCommand acCmdSaveRecord

Me.aCons_frm_Consultant_Companies_Subform.Locked = True
Me.Cons_frm_Main_Sub_Contacts_Active.Locked = True
Me.Cons_frm_Main_Sub_Contacts_Inactive.Locked = True
Me.Cons_frm_Main_Sub_Contacts_Active.Requery
Me.Cons_frm_Main_Sub_Contacts_Inactive.Requery
Me.PrimaryCompany.Locked = True
Me.cboFilterPrimary.Requery
Me.cboFilterPrimary.SetFocus
Me.cboFilterPrimary.Locked = False
Me.MsgLabel1.Caption = "Saved Record"
Me.bttnCancel.Visible = False

Exit_bttnSave_Click:
Exit Sub
Err_bttnSave_Click:
MsgBox Err.Description
Resume Exit_bttnSave_Click
End Sub

The query code is SELECT ConsultantContactID, ConsultantCompanyID, Status, InviteStatus, Salutation, FirstName, LastName, Title, Phone, Ext, Cell, Email
FROM dbo.Cons_tbl_Consultant_Contacts
WHERE (Status = N'Active')

 

[kzutter]

I do not understand why you are locking forms and controls.
I know you are trying to "control those pesky users", but I think you are shooting your self in the foot.

If you are trying to prevent other networked users from simultaneously changing data, then this type of 'locking' is useless. It does not lock the database, only the controls of the current user.

You might avoid the message by turning warnings off while you save the record. This may or may not work in this case, you will just have to try it.
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings True

I should also point out that there are many documented reasons for NOT using DoCmd.RunCommand acCmdSaveRecord
The accepted method is:
If Me.Dirty then Me.Dirty = False
You can google the topic if you want to learn why.

It sounds like there is a fundamental structural difference in what you want to accomplish and how you are going about it.

 

[puppy39]

How do I control the pesky user that have absolutely no idea on how access works if I don't add buttons to the forms such as EDIT RECORD AND SAVE RECORD? When I did not have these button the first record was always being changed and other records were getting edited because of user's not paying attention.

I tried DoCmd.SetWarnings but it did not work. Thanks for the help.

Any other suggestions....

 

[kzutter]

Pesky user training is my preferred method, and I understand that is not always feasible.

I had a situation where most users were frequently looking at data and rarely editing it. Occasionally one would inadvertently change data without realizing it.

I had two choices:
1. Detect when a changed record was about to be written to the DB (the form's Before Update event) and ask the user if that was intended (Do you want to save changes to this record?) and cancel if they said no.

2. Method one can be a pain to the user actually trying to make edits, so I came up with this.
a)I set the form properties: AllowEdits, AllowAdditions, and AllowDeletions to NO.
b) I added a label called lblMode with a green background and the caption "Read Only" to the form header
c) I added a button called btnMode with the caption "Change Mode" next to the label
d) I added this code behind the btnMode's click event:

Private Sub btnMode_Click()

Me.AllowEdits = Not Me.AllowEdits
Me.AllowAdditions = Not Me.AllowAdditions
Me.AllowDeletions = Not Me.AllowDeletions

If Me.AllowEdits Then
    Me.lblMode.Caption = "Editing Enabled"
    Me.lblMode.BackColor = vbRed
Else
    Me.lblMode.Caption = "Read Only"
    Me.lblMode.BackColor = vbGreen
End If
End Sub

Then the lookers could look all they wanted to. Editors could edit, add and delete. Everyone was happy.

YMMV

 

[puppy39]

Thank you for your suggestion which is definetely much easier. I will give it try. Thanks!