Open FormView From subDatasheetview

[byman2030]

Hi all,

I have a subform in datasheet view.

When the user doubleclicks the lastname field in the record (in
datasheetview) I want the parent form to appear (in form view) with that
exact record loaded for editing.

Rigth now, I get the parent form to appear but it has first record in the
underlying table and not the one clicked

Here is the code I have so far.

Private Sub txtLastName_DblClick(Cancel As Integer)
DoCmd.OpenForm "LoanSetUpForm", acNormal, , Me.CurrentRecord =
Me.CurrentRecord
End Sub

Thanks

 

[byman2030]

I was able to get the form to appear in formview with the record that was
double clicked in the subdatasheet view work (see code below) but I had to
make a second call to GotoRecord.

But is there a way to do it with just setting the wherecondtion when calling
OpenForm?

Here is the code that works so far.

Private Sub txtLastName_DblClick(Cancel As Integer)
Dim lngrecordnum As Long
lngrecordnum = Me.CurrentRecord

DoCmd.OpenForm "LoanSetUpForm", acNormal
DoCmd.GoToRecord acDataForm, "LoanSetUpForm", acGoTo, lngrecordnum
End Sub

Thanks

 

[Allen Browne]

You have a main form and a subform both bound to the same table? Not a good
design. This is likely to give you concurrency problems ("Access stopped the
process because you and another user..."), particularly if you have any
memos, hyperlinks, or OLE fields. Even worse, it can foul up the AutoNumber
so you cannot add any more records to the table--the 3rd issue listed in
this article:
http://allenbrowne.com/ser-40.html

If you want to do it anyway, you cannot use OpenForm on the main form to
move to the desired record, since it is already open. You could FindFirst in
its RecordsetClone. This example assumes a numeric primary key field named
ID:

Private Sub Surname_DblClick(Cancel As Integer)
Dim strWhere As String
Dim rs As DAO.Recordset

If Me.Dirty Then
Me.Dirty = False 'Save first.
End If
If Me.NewRecord Then
Beep
Else
strWhere = "ID = " & Me.ID
With Me.Parent
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
End If
Set rs = Nothing
End Sub

 

[byman2030]

Thanks for the reply Allen,

I'm not sure if my "main" form is the same thing you have in mind.

I have a main form that holds two subforms. This "main" form is not linked
or bound to any table. It acts more like a frame.

The specific subform in question, however, is in datasheet view, and its
recordsource is a parent form. The parent's recordsource is a table.

So, I have a child form and a main (if parent means "main") form, both
"bound" (via the recordsource setting) to the same table. One is directly
bound and the other indirectly.

When the user double clicks within the lastname field in the datasheet
subform (child form), I wanted the parent form to pop up in form view so the
user could edit the record in that window.

Does that fit the bad design you had in mind?

In the case of your code sample, I do not as yet have a primary key
establishded which is why I am relying on me.currentrecord.

 

[Allen Browne]

You *really* need a primary key. There are too many cases where
Me.CurrentRecord is unreliable, e.g. where another record has been added, a
record has been deleted, a filter has been applied, the sort order has
changed, ...

I'm not sure I follow the way you are using terminology. In Access,
"subform" refers to a form loaded into a subform control. The subform
control is on a form that we call the "main form". The Main form is the
"parent" of the subform. The main form can be unbound.

If the main form and subform are not bound to the same table, the problem
does not arise.

 

[byman2030]

Thanks for pointing out that me.current record is unreliable. I'll get to
work on that.

Looks like I also ned to work on my Access jargon as well!

Basically, I took a brand new blank form and I dragged two other forms onto
it from the forms panel (each has a different table for its recordsource) .

I was refering to the original forms I draggged and dropped as "parent"
forms and I was referring to their copies (now sitting on the blank form) as
child forms.

When I drag and drop an existing form onto this blank form, does that create
a subform in the proper sense of the term? Or, is there a different set of
steps to properly create a subform?

 

[Allen Browne]

If you open a form in design view, and then select another form in the
Database window and drop it onto the form that is open in design view,
Access adds a subform control to the form you are designing. The
SourceObject property of the subform control indicates which form Access
will load into this subform control.

The "main form" is the one that was open in design view. The subform control
contains an instance of the form nominated in the SourceObject property of
its subform. It is possible to have other instances of that form availalble,
either as a stand alone form, or a subform on another form, or even multiple
instances on the same main form (if you dragged it onto the form multiple
times.)

There are other ways to create a subform as well, such as choosing the
Subform control in the Toolbox. But the way you describe is my preferred
approach anyway, so no problem with that.

So, back to your question. It seems that what you are asking for is to open
the form named as the SourceObject of the subform control, as a stand-alone
form? You can do that, though you can run into the concurrency issues.

The basic idea is:
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
Beep
Else
strWhere = "ID = " & Me.ID
DoCmd.OpenForm "MyForm", WhereCondition:= strWhere
End If