Bound/Unbound... Subform... Write Conflict... Conceptual Help (1 Viewer)

[grinnZ]

[RESOLVED] Bound/Unbound... Subform... Write Conflict... Conceptual Help

I'm unsure whether this belongs in this Forms thread or in Theory & Practice. Based on the nature of the discussion I chose here.

I write to start a discussion for conceptual help. After returning to Access after many years I am finding that I am relearning most of what I knew. The first form that I most recently designed I ended up writing twice. I chose a standard bound form from the builder that had a lower subform attached. It was only in the final testing phase that I encountered a write conflict which sent me back to the drawing board and I successfully redesigned using a completely unbound form. Yet for that solution and to avoid the write conflict I did forego the lower datasheet view. Therein lies my discussion.

For my next form I would like to again employ a data entry form but this time I would also like to include the use of a subform to view multiple records. My question is how. Do I design an unbound form on the top and a locked and bound subform? Can this subform appear to be paging through records even though it is locked? And what about that dreaded write conflict error when the unbound form programmically updates the record that appears chosen in the lower subform? Am I thinking in the right direction or is there an easier or more elegant solution that will avoid the write conflict using bound forms?

I'm usually good at muddling through and figuring this stuff out. Yet before I proceed I'm sure that receiving some insight as to a direction to follow would indeed be helpful so that writing the same project multiple times can be alleviated. Thank you.

 

[jzwp22]

Yet for that solution and to avoid the write conflict I did forego the lower datasheet view. Therein lies my discussion.

What was the nature of the write conflict?

Was the conflict due to a flaw in the table structure that prevented the addition of a new record?

Were the tables and join between them properly set up before creating the form/subform?

 

[grinnZ]

No, the record addition was successful yet the cursor left in the subform of the changed record balked after the program updated the record. It's not like I physically put the cursor there. The subform was bound to the top bound form record and moved with the navigation.

Tables/joins... everything looked fine and worked as expected up until after the table record was update with the program.

 

[jzwp22]

When you say that the record was successfully added did you mean the record in the main form or the subform? Do you have any code running in any of the subform events?

 

[grinnZ]

There was no code in the subform. That said, the subform was simply a complete listing of the recordset of which the top of the form showed only the current record within that set.

Successful addition... I could physically see the record with changes when i open the physical table or query. The rst.update worked.

 

[jzwp22]

Typically, the main form is used for the one side of a one-to-many relationship while the subform is used to show the related records on the many side of the relationship. Having the same records in both the main and subforms could cause the issue you describe.

 

[grinnZ]

And indeed did cause the conflict. Interestingly enough that is how the wizard setup the form yet I will say it did not know that I was going to update the records using a recordset.

And so this discussion end and returns us to our original question of a single record in the main along with a locked subform to see the complete recordset. Possible without hitting the write conflict wall?

By the way, I would be lax not to thank you for your input. It did indeed shed a bit of light on subforms but still my question remains.

 

[jzwp22]

Obviously the record shown in the main form is somehow related to records shown in the subform. Could you provide a little more detail on the table structure with which you are working?

I am thinking that one of the forms has to be unbound.

 

[grinnZ]

This is the query on which the form was bound...

SELECT tblAuctions.DateListed, tblAuctions.DateClosed, tblAuctions.Qty, tblAuctions.Bid_Price, tblAuctions.BO_Price, tblAuctions.Duration, tblAuctions.Deposit, tblAuctions.AH_Cut, tblAuctions.Status, tblAuctions.Notes, tblItems.Item, tblItems.Level, tblItems.Type, tblItems.Composition, tblItems.Dep48, tblItems.Dep24, tblItems.Dep12, tblItems.ItemID, tblAuctions.AucID
FROM tblItems INNER JOIN tblAuctions ON tblItems.ItemID = tblAuctions.ItemIDLookup
WHERE (((Left([tblauctions].[status],1))="L"));

 

[dfenton]

In general, it's not a good idea to have the same table open for editing in two forms simultaneously.

While it's kind of unusual to have a main form and subform based on the same table, it's not at all unheard of. However, if that's how you are doing things, I would recommend that only one of the forms be editable. In general, I don't make continuous/datasheet forms editable (except in obvious cases like an invoice detail).

One easy to way to structure this is to use a listbox or continuous/datasheet (non-editable) for displaying the list and then have a single subform whose LinkMaster property is the expression that will return the PK of the record you want to display. If, for instance, you're listing people and PersonID is the PK, your listbox would have PersonID as its bound field, and then your subform would have [lstPersonList] as LinkMaster and PersonID as LinkChild. If the list were a form, it would be subPersonList.Form!PersonID for the LinkMaster.

This structure insures that when you select a record in the list, the subform is populated automatically with the detail for that selected record, and it's in that detail form that you can do the editing.

Now, if you want to do this without a second subform, you could make the AfterUpdate event of the list navigate (or filter) the main form, but I prefer doing it the other way.

 

[grinnZ]

David, thank you for your reply. But before I read, reread, and think about what you have said let me be clear. The datasheet subform is not where I want to edit. I could care less whether it is bound, unbound, live, or otherwise. It's simply nice to see the complete set of records of which the record presented in the main form above is one. I would much rather the editing happen on the main form. It's simply easier to control the total flow in that single record form. That is why I asked about a bound/unbound structure. Moreover, it doesn't matter to me whether the main form is bound or not. The bottom datasheet would only be used as a point of reference but not for specific use. I can build all of that into the top part of the form. I suppose I need to research subforms just a bit further but your input and insight has certainly added to my knowledge. I appreciate the time that you have taken to explain to me.

 

[SteveH2508]

I feel that you might be making a mountain out of a molehill here. You sound as if you want to use the sub-form as a navigation tool for the main form. Why not use a listbox or a combobox instead? Select the record you want and code takes you to that record. (3rd option in the combo box wizard)
A listbox could show you the fields you want without all the palarver of write conflicts etc.

(probably not worth 2 pence but...)

 

[grinnZ]

OK, I'm going to close this thread and thank all who have offered their thoughts and comments. With what has been said and the new knowledge that I gained by watching a YouTube video or two I now have a much clearer understanding of subforms and at least conceptually I know how I can proceed.

Thank you again.