469,336 Members | 5,385 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

How to release the record lock between forms?

sueb
379 256MB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Edit_18_1_Click()
  2. On Error GoTo Err_Edit_18_1_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "18-1"
  8.  
  9.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  10.  
  11.     stLinkCriteria = "[Accounts_Index]=" & Me![Index]
  12.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  13.  
  14. Exit_Edit_18_1_Click:
  15.     Exit Sub
  16.  
  17. Err_Edit_18_1_Click:
  18.     MsgBox Err.Description
  19.     Resume Exit_Edit_18_1_Click
  20.  
  21. End Sub
  22.  
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.
Mar 25 '11 #1

✓ answered by 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.

18 5634
TheSmileyCoder
2,321 Expert Mod 2GB
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?
Mar 25 '11 #2
sueb
379 256MB
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.
Mar 25 '11 #3
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Mar 25 '11 #4
sueb
379 256MB
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.
Mar 25 '11 #5
sueb
379 256MB
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.
Mar 26 '11 #6
sueb
379 256MB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Edit_18_1_Click()
  2. On Error GoTo Err_Edit_18_1_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "18-1"
  8.  
  9.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  10.  
  11.     stLinkCriteria = "[Accounts].[Index]=" & Me![Index]
  12.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  13.  
  14. Exit_Edit_18_1_Click:
  15.     Exit Sub
  16.  
  17. Err_Edit_18_1_Click:
  18.     MsgBox Err.Description
  19.     Resume Exit_Edit_18_1_Click
  20.  
  21. End Sub
  22.  

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):

Expand|Select|Wrap|Line Numbers
  1. SELECT Accounts.Discharge           , Patients.ChartNum
  2.      , [Family Name] & ", " & [Given Name] AS Name
  3.      , Patients.[Patient Index]     , Patients.[Family Name]
  4.      , Patients.[Given Name]        , Patients.[Other Name]
  5.      , Patients.[Date of Birth]     , [UR Demographics].*
  6.      , Accounts.Index               , Accounts.[IUR Patient Index]
  7.      , Accounts.Account             , Accounts.Admit
  8.      , Accounts.Payer               , Accounts.[Ready for Review]
  9.      , Accounts.[Pull Date]         , Accounts.[Days Approved]
  10.      , Accounts.[Days Denied]       , Accounts.[TARs Deferred]
  11.      , Accounts.Note                , Accounts.[Emergency Admit]
  12.      , Accounts.[Admit ICD9s]       , Accounts.[Admit Diag]
  13.      , Accounts.[Current Diag]      , Accounts.[Diag ICD9s]
  14.      , Accounts.[Auth Rep]          , Accounts.[Current Condition]
  15.      , Accounts.[Planned Procedures], Accounts.[UR Nurse]
  16.      , Accounts.[UR Date Signed]    , Accounts.[Responsible Physician]
  17.      , Accounts.[Dr Date Signed]
  18. FROM Accounts 
  19.         RIGHT JOIN ([UR Demographics] 
  20.         RIGHT JOIN Patients 
  21.         ON [UR Demographics].[IUR Patient Index] = Patients.[Patient Index]) 
  22.         ON Accounts.[IUR Patient Index] = Patients.[Patient Index]
  23. ORDER BY Accounts.Discharge DESC 
  24.        , Patients.ChartNum;

Any help through this tangle will be much appreciated!
Mar 26 '11 #7
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Mar 26 '11 #8
sueb
379 256MB
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?
Mar 28 '11 #9
TheSmileyCoder
2,321 Expert Mod 2GB
When you opened the query directly, did you first ensure that you didn't have the parent form (or any other form) open?
Mar 28 '11 #10
sueb
379 256MB
Right. The query was the only thing open.
Mar 28 '11 #11
TheSmileyCoder
2,321 Expert Mod 2GB
I have asked in our experts forum if anyone else has insights into this, cause I am out of ideas.
Mar 29 '11 #12
patjones
931 Expert 512MB
Hi,

You are working with bound forms, correct?

Pat
Mar 29 '11 #13
patjones
931 Expert 512MB
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
Mar 29 '11 #14
sueb
379 256MB
I surely do appreciate both of your efforts to help me with this!
Mar 29 '11 #15
sueb
379 256MB
@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?
Mar 31 '11 #16
TheSmileyCoder
2,321 Expert Mod 2GB
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?
Mar 31 '11 #17
patjones
931 Expert 512MB
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
Mar 31 '11 #18
sueb
379 256MB
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.)
Apr 2 '11 #19

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by John P. Speno | last post: by
reply views Thread by Andrew Cutforth | last post: by
reply views Thread by Andrew Cutforth | last post: by
3 posts views Thread by cFleury | last post: by
8 posts views Thread by Komandur Kannan | last post: by
reply views Thread by Matt | last post: by
5 posts views Thread by payffl | last post: by
1 post views Thread by ABrown | last post: by
2 posts views Thread by austris | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.