Two forms with same record source prevent writing conflict

[bruceblack]

Dear friends,

I'll keep it short and sweet.

I have two forms, with the same record source. Say form A and form B.

Form-A is the main form. But Form-B goes into more detail.
Both forms are showing completely different fields on it. But once i entered the details in Form-B, close it, and want to close Form-A, i get a writing conflict.

So, i kind of understand why i think? And i don't have any record locks enabled. But since i am not changing the same fields, i am confused on how to solve the issue. I tried just saving the records (vba) before i open or close them, but then i get the error that i can not save the record right now.

Is there simple fix? I just want to edit some details in another form with the same record source if possible. Thanks in advance!

 

[isladogs]

Close Form A before editing in Form B ...or just use one form hiding the additional detail when not needed

 

[bruceblack]

Close Form A before editing in Form B ...or just use one form hiding the additional detail when not needed

Who else than isladogs

Thank you. I did that actually. The problem was that Form-A is a continuous form. So when i am scrolling, and i close it, i have to scroll back to where i left off each time. Which gets annoying fast.

I understand that its just not possible other than that? I think i used it like that in the past somehow. But i could be wrong.

The second option is just too much. I would have alot of empty space on my form. The details forms is quite elaborate.

 

[Gasman]

Try removing the record source.?
Easy enough to store the id, to go back to the record you were on.

 

[KitaYama]

I understand that its just not possible other than that?

Before closing FormB, requery or refresh FromA.
To maintain scrollbar position and cursor position I use this :

Maintain Record Position After Update « Access... entials

access-diva.com

 

[MajP]

The most common way to do this properly is
1 )Form A stores the ID of the current record in a variable
2) form A calls pop up form FormB using the DOCMD.Openform
using the WHERE argument of the Docmd to open the correct record
using the ACDIALOG value of the Docmd.open form WINDOWMODE argument. This stops code execution in FormA until FormB is closed
4)Once form B closes execution returns in Form A
5) Form A requeries
6) Form A moves back to the record whose ID is stored in step 1

Private SomeEventProcedure()
  dim id as long
  ID = Me.somePrimaryKeyField
  docmd.OpenForm FormName := "FrmName", WhereCondition: = "SomePKfield:= " & ID, WindowMode:= ACDIALOG
  'code stops in FormA until FormB closes
 'Resumes here
  me.requery
  me.recordset.findfirst "SomePrimaryKeyField = " & ID
end sub

You must use ACDIALOG if not code execution in Form A continues and the requery happens immediately before anything happens in FormB.

 

[isladogs]

The second option is just too much. I would have a lot of empty space on my form. The details forms is quite elaborate.

You can easily shrink / expand the form as required. For example, see

Subform Shrink & Grow

This example shows how a subform can be shrunk/hidden or made visible/enlarged at the click of a button

www.isladogs.co.uk

 

[CJ_London]

The problem was that Form-A is a continuous form.

have you considered creating something like the split form ? I don't recommend the built split form but 'grow your own' is OK. Simplistically have a continuous form and in the header or footer repeat the controls and layout in a 'single form' style.

Or if Form A is effectively just a list, use a listbox?

Or set the Form A recordset type to snapshot? (haven't tested). But if it works if there are fields that could be modified by Form B you would need to refresh the recordset (no need to requery unless Form B adds or deletes records)

 

[Gasman]

@MarkK produced a few lines of code for what I call an ESF form (Emulated Split Form)
@isladogs enhanced the idea. Search for ESF and my user name if that might be of interest for you.

 

[isladogs]

Emulated Split Form

The emulated split form was created as a project to overcome the difficulties inherent with the built-in split form supplied with Access

www.isladogs.co.uk

Build Your Own Split form

The Access split form has several important limitations. This article shows how you can create your own simulated split form which doesn't have the restrictions inherent with the built-in Access split form

www.isladogs.co.uk