Form Save and Visible Issues 1

[misscrf]

I have a form where you can view the custodian names in a given project.
The form header has the project name you are in, and the detail is continuous with each custodian name. Each record has an edit button to open a form, which will have open args to that custodian id. This edit form has the custodian name in the header, and a continuous detail form with any projects that will be affected by the change. The custodian names are in a tblCustodian table. There is a project custodian table that has the project key and the custodian FK for each custodian to link to projects they are used in.

When I go to the edit custodian form, change the custodian, and go back to the manage project custodians 2 things don't work correctly. The change to the custodian name does not change, and the manage project custodian form does not become invisible. I have referential integrity. The tables are sql based, and linked to access with ODBC. I can open the tables, I can change values in query that the edit form is based on.

Here is the info.

tblCustodian

ID
Custodian
CreatedBy
Created
ModifiedBy
Modified

tblProjectCustodians

ID
ProjectKey
FKCustodian
CreatedBy
Created
ModifiedBy
Modified

Code on frmProjectCustodiansMain

Private Sub cmdEditCustodian_Click()
Dim stOpenArgs As String
stOpenArgs = Me.FKCustodian
DoCmd.OpenForm "frmEditCustodian", , , , , , stOpenArgs
Forms!frmMatterCustodiansMain.Visible = False
End Sub

That seems to work. This is the code for the on open of the edit custodian form:

Private Sub Form_Open(Cancel As Integer)
With Me.Form
    .Filter = "[FKCustodian] = " & Me.OpenArgs
    .FilterOn = True
End With
End Sub

That seems to work too. This is the record source for the edit form:

SELECT tblProjectCustodian.ProjectKey, tblProjectCustodian.FKCustodian, tblCustodian.Custodian
FROM tblProjectCustodian RIGHT JOIN tblCustodian ON tblProjectCustodian.FKCustodian = tblCustodian.ID
WHERE (((tblProjectCustodian.ProjectKey) Is Not Null))
ORDER BY tblProjectCustodian.ProjectKey;

This is the code that goes back to the manage project custodian form:

Forms!frmProjectCustodiansMain.Visible = True
DoCmd.Close acForm, "frmEditCustodian", acSaveYes

The edit custodian form closes, but the change I made to the custodian record, doesn't save. The project custodian form also doesn't become visible. nothing happens. no error.

I have another button on the edit custodian form, to go straight back to the project batch setup form. This is where the user started, when they needed to go manage the project custodians, and then edit one of the custodians. This is the code for that button:

Private Sub cmdRetDiscProcessing_Click()
Forms!frmProcessingTracking.Visible = True
DoCmd.Close acForm, "frmEditCustodian", acSaveYes
DoCmd.Close acForm, "frmProjectCustodiansMain", acSaveYes
End Sub

Now this does close the edit form, close the project custodian form and make the project batch set up form visible again. It doesn't save the change to the custodian though. When I go to the query, I get the attached error.

I have checked the link to the tables and it all seems ok. So confused!

Thanks for any help!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I don't see any code that would refresh the original form.

I would open the edit form in acDialog mode and then refresh the main form after closing the edit form.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I tried that, and it doesn't seem to do anything. Going back to project form, doesn't make it visible and doesn't change the data. Going to the batch form works, but doesn't change the data.

I changed the code to this:

Private Sub cmdRetProjectCustodians_Click()
Forms!frmProjectCustodiansMain.Form.Visible = True
Forms!frmProjectCustodiansMain.Form.Refresh
DoCmd.Close acForm, "frmEditCustodian", acSaveYes
End Sub

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I wouldn't set anything invisible. You didn't show us the code that opens frmEditCustodian.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I did put that code it, but it might have gotten lost in the list of stuff. It is on the click of a button:

Code on frmProjectCustodiansMain which opens the edit form:

Private Sub cmdEditCustodian_Click()
Dim stOpenArgs As String
stOpenArgs = Me.FKCustodian
DoCmd.OpenForm "frmEditCustodian", , , , , , stOpenArgs
Forms!frmMatterCustodiansMain.Visible = False
End Sub 

This is the code for the on open of the edit custodian form:
[code]
Private Sub Form_Open(Cancel As Integer)
With Me.Form
    .Filter = "[FKCustodian] = " & Me.OpenArgs
    .FilterOn = True
End With
End Sub

Maybe you are right. Maybe I should just close and open the forms as I switch between them.

Still trying to figure out the odbc issues. It doesn't make sense, considering there are so many other tables connecting through same protocol and they all work.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I figured out the issue. I have a mod trigger on the sql tables. The mod trigger was attempting to update the wrong table name, so attempting to modify the table wasn't working. It was cancelling the event.

Now things are working. Thanks for talking it through with me. You led me to the issue!

Can you tell me why you don't like to make forms invisible and then visible when switching between them? I have had others recommend that for handling the transitions, when you are going back and forth.

Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I guess my preference is determined by if a new form being opened will modify data in an existing form. In this case, I open the new form acDialog so the old form might still be visible but the code execution stops until the new form is closed. I just don't see a reason to make the existing form invisible.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I am not changing data in the original form. I am editing a record in a table in the edit form, and then refreshing the main form, which has a record source including that value, when the edit form closes. You are right. I don't technically need to make the form invisible, but I don't like forms on top of forms. It just looks messy.

misscrf

It is never too late to become what you could have been ~ George Eliot