How to release the record lock between forms?

[sueb]

I have a form, Patients, that has two subforms:
- Demographics (one-to-one relationship with Patients)
- Accounts (many accounts for a single Patients)

Accounts allows modification of some of the account's fields. It has a button that opens a separate form, which offers a different set of fields for modification (used for reporting to Medi-Cal on their printed form "18-1", hence the secondary form's name, as you'll see in the following code).

The problem is the record lock is remaining set. Here's the current code:

Private Sub Edit_18_1_Click()
On Error GoTo Err_Edit_18_1_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "18-1"
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
    stLinkCriteria = "[Accounts_Index]=" & Me![Index]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Edit_18_1_Click:
    Exit Sub
 
Err_Edit_18_1_Click:
    MsgBox Err.Description
    Resume Exit_Edit_18_1_Click
 
End Sub
 

The form 18-1 opens correctly (with some locked controls displayed correctly, so I know it's getting the right record and all). I have a similar situation in another database, and similar code works just fine there, so I'm puzzled about what I'm missing here.

[TheSmileyCoder]

Hi Sue

What the recordsource for both forms?

In what way do you determine that the recordlock is set? Are you simply unable to edit the record or are you getting an error message?

[sueb]

I'm not at my station today, but I'm sure that I use the same query for both forms (this is a new database and I've created only 2-3 queries).

And, yes, I determine this by the fact that I can't modify any of the unlocked controls on the 18-1 form.

[TheSmileyCoder]

Well there are several possibilities here.

One is that you have actively set page or table lvl locking on your query (which I dont have much experience with)
Another is that you have dirtied your main form, before opening the sub form.
Another is that you have created a recordset that is not updatable.

[sueb]

I checked the locking level, and it's Edited Record, which I think should be correct.

I have seen the OnDirty property, but have no idea what it is, so how do I determine that?

The recordset is updatable in the other forms, so I think it's probably updatable.

[sueb]

Oh, and I should explain that each form/subform pulls from a separate table, with the secondary form, 18-1, pulling from the same table as Accounts.

[sueb]

I apologize for the length of this post: I'm trying to hit a balance between necessary and unnecessary information.

So I've been reading about non-updatable queries, and experimenting with creating new forms based on my query, and I think it must be that my query is not updatable. However, I'm having a difficult time understanding all the conditions that make a query non-updatable. The pages I've referenced are:

http://msdn.microsoft.com/en-us/library/aa198446.aspx

and

http://msdn.microsoft.com/en-us/library/aa199585.aspx

I think my query qualifies as updatable, but is there any way for someone to help me check that out? maybe even by asking me questions that I could research the answer to? My forms' Recordset Types are all set to Dynaset, Record Locks are Edited Record, and they all Allow Filters/Edits/Deletions/Additions. The two subforms both link to the parent form using Patient Index, and the button that opens the secondary form (18-1) uses the subform's own Index as the link. Here's that button's code again:

Private Sub Edit_18_1_Click()
On Error GoTo Err_Edit_18_1_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "18-1"
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
    stLinkCriteria = "[Accounts].[Index]=" & Me![Index]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Edit_18_1_Click:
    Exit Sub
 
Err_Edit_18_1_Click:
    MsgBox Err.Description
    Resume Exit_Edit_18_1_Click
 
End Sub
 

As I think I mentioned earlier, the secondary form opens correctly, with the correct data displayed; it just doesn't allow entry into any of the (unlocked) fields.

Here's the query's SQL (which I realize is only a small part of whether it's updatable or not, but I'm not sure what properties to post given the scope of the conditions):

SELECT Accounts.Discharge           , Patients.ChartNum
     , [Family Name] & ", " & [Given Name] AS Name
     , Patients.[Patient Index]     , Patients.[Family Name]
     , Patients.[Given Name]        , Patients.[Other Name]
     , Patients.[Date of Birth]     , [UR Demographics].*
     , Accounts.Index               , Accounts.[IUR Patient Index]
     , Accounts.Account             , Accounts.Admit
     , Accounts.Payer               , Accounts.[Ready for Review]
     , Accounts.[Pull Date]         , Accounts.[Days Approved]
     , Accounts.[Days Denied]       , Accounts.[TARs Deferred]
     , Accounts.Note                , Accounts.[Emergency Admit]
     , Accounts.[Admit ICD9s]       , Accounts.[Admit Diag]
     , Accounts.[Current Diag]      , Accounts.[Diag ICD9s]
     , Accounts.[Auth Rep]          , Accounts.[Current Condition]
     , Accounts.[Planned Procedures], Accounts.[UR Nurse]
     , Accounts.[UR Date Signed]    , Accounts.[Responsible Physician]
     , Accounts.[Dr Date Signed]
FROM Accounts 
        RIGHT JOIN ([UR Demographics] 
        RIGHT JOIN Patients 
        ON [UR Demographics].[IUR Patient Index] = Patients.[Patient Index]) 
        ON Accounts.[IUR Patient Index] = Patients.[Patient Index]
ORDER BY Accounts.Discharge DESC 
       , Patients.ChartNum;

Any help through this tangle will be much appreciated!

[TheSmileyCoder]

I don't spot anything in the query that should make it not-update-able.

The easiest way to check if the query is updateable is to open your form in design view, look at the forms properties, then the Recordsource of the form. Click the three ... on the right to open the query builder, and view the recordset. If you can update the records from here, it means the recordset is updateable and the problem is likely in your form.


About Dirty:
Whenever you start editing a record the record becomes Dirty (basicly meaning it has uncommitted changes). If you have record-selectors turned on (the small grey vertical line on the left) you will see it changing from a small triangle to a writing icon. The same line can also show you if a record is locked by someone else.

[sueb]

So now I'm pretty confused. I did as you suggested (i.e., view the query's recordset directly--from the query itself), the records were not updatable. But when I open the parent form, the same fields are updatable through the controls on the subforms.

How can that be?

[TheSmileyCoder]

When you opened the query directly, did you first ensure that you didn't have the parent form (or any other form) open?

[sueb]

Right. The query was the only thing open.

[TheSmileyCoder]

I have asked in our experts forum if anyone else has insights into this, cause I am out of ideas.

[patjones]

Hi,

You are working with bound forms, correct?

Pat

[patjones]

I would add that I agree with Smiley about the SQL you posted. Everything should be updateable except for the concatenation of [Given Name] and [Family Name]. Also, the [Patient Index] field will not be updateable on the "one" side of the relationship unless you have cascade update enabled on the join between the respective tables.

My reason for asking whether you are working with bound forms was that if the Data Entry property is set to "Yes", the form only opens to allow entry of new records into blank fields. But you already indicated that the form is displaying existing records properly, so my concern is unfounded.

I'll have to think this one over some more...

Pat

[sueb]

I surely do appreciate both of your efforts to help me with this!

[sueb]

@zepphead80,I checked, and the Data Entry property is set to No, so I think that part's okay. I can't understand how it is that I cannot update fields in the query itself, but I can update controls for those same fields in the subforms, and then I cannot update those controls when the secondary form is opened from the subform.

What can be going on here?

[TheSmileyCoder]

If you have the record selector turned on, what icon does it have in both forms?

Can you copy the relevant forms to a fresh db and upload it here?

[patjones]

Smiley beat me to it...I was just going to suggest the same thing. If you can post a stripped-down version of the db here with a small sample of data (you can put in bogus data if it's confidential), that would probably help us a lot. Thanks.

Pat

[sueb]

I came to the conclusion that the problem had to lie in the relationships among the three tables, so I completely re-worked the database so that instead of Patients having a one-to-one with UR Demographics, and a one-to-many with Accounts...

Patients

1-1 UR Demographics

1-m Accounts

...now Patients has a one-to-one with UR Demographics, and UR Demographics has a one-to-many with Accounts:

Patients

1-1 UR Demographics

1-m Accounts

This a more correct representation anyway, since the Patients is a table in a separate database, which has nothing to do with the Accounts in this database. I was thinking of the data in UR Demographics as a mere extension of information about each patient, which it is, but an Accounts dependency on that other database makes no sense. UR Demographics should parent Accounts.

I just completed the bulk of this, and most things are working well now. There are one or two little places where I think I just haven't made the correct adjustments in queries, etc. If I run into roadblocks in the cleanup, I'll ask them in a separate post.

Thanks so very much for all the advice and help--I think your questions and comments helped me see more clearly!

(I picked TheSmileyCoder's response as best because it sort of got me started thinking out of the little box I'd been circling in.)