Double click record in subform to go to new record in Main (and subform)

[Jpipher]

I have a main form with a tab control on it that contains 10 pages.
One of the pages contains a subform that performs a union query for all
records related to the current record in the main form and displays
them in Datasheet view.

What I am trying to do is create a Double-Click event on the subform
that will update the main form to the selected record on the subform.

I have tried a number of round-about methods, none of which are
working.
I know that whatever I do will go on the double-click event of the
subform. I can't figure out how to get the IDNum from the subform and
go to that record in the main form.

Any assistance would be appreciated.

 

[Jpipher]

I may have found a solution BUT I don't know what to reference in it...


This was posted last year and basically describes what I want to do
except I don't want to select from a combo box, but rather the form's
record selectors. I am not sure what to include in the "rst.FindFirst"
line though.

The control that identifies the record is named "IDNum" so I assume
that would take place of "TopicID".
What does "Me.cboCrossrefs" reference, the parent form or the subform
or the underlying table?

Regardless of which it is, how do I reference something that has spaces
in the name in VBA, such as Product Tracking Main Form? (I didn't
create it and name it that way but am now reposnsible for it and it
links to quite a bit so I can't change it now without a significant
amount of hassle that I don't have time for. )

 

[Jpipher]

Thank you. I am going to need more help with that coding but here are
the answer to your questions first.

Is there just one record displayed on the main form? It runs a query that pulls up all the records in the underlying table and displays them in single form view. This data is spread out on the various tabs in the tab control of the main form. We have a Find Record button that runs the Find command from the edit menu to go to specific records but also allows my supervisor to click through, which is a function I would like to keep. Only one subform exists on the form, and is the one we are talking about.

Is this one record defined by the value of IDNum as primary key? Yes

Is your main form bound or unbound? Bound

If bound what is the underlying recordset? See first answer.

Have you defined explicitly or implicitly any Parent-Child relationship
between your main form and your sub form? No Parent-Child Linking fields. The subform is based on a union query that looks at Parent.IDNum and selects all records in the underlying table where IDNum=Parent.IDNum and where DerivedFrom=Parent.IDNum

Thanks again.

 

[Jpipher]

Can anyone else contribute since Rod has gone home for what appears to
be the weekend?

 

[Guest]

Can anyone else contribute since Rod has gone home for what appears to
be the weekend?

 

[Guest]

Hi,

Yes it's the weekend but that does not mean to say I'm gagged.

Your answers are all as I expected.

The solution I suggested is still my first choice; others would no doubt
suggest different ways such as addressing the main form directly from the sub
form. Each solution has its benefits and disadvantages. Anyway let's press
on with the user defined event solution and I'll explain a little as I go
along.

The first thing to note is that form modules you see in the VBA coding
window are in fact class objects. Access constructs the names of these
classes by adding the prefix 'Form_' to your form name. Why is this
important? Well classes give you the opportunity of defining events; an
object instantiated from one class can raise an event while an object (or
more than one) instantiated from another class can 'listen' for that event.
You can even get an object to 'listen' for the standard Access events in
another object if you so wish (which is a variant on the solution I'm
describing here). The restriction with user events is that you cannot
establish duplex communication between objects: if A listens for events in B
then you cannot also set up B to listen for events in A: you are restricted
to one-way event handling.

Enough theory for the moment. Setting up the communication is simplicity
itself. Open up the code module for the form that is used a s a sub form. In
the declaration section at the top enter the following statement, probably
best placed following the Option statement(s).

Public Event NewID(lngIDNum as long)

I assume your IDNum is long integer; if not adjust the phrase inside the
parentheses accordingly. This statement defines an event named 'NewID.' Now
you need to select an Access event when this user event will be raised. If
you have enabled controls on your form then the form's double click event
does not fire, so choose a control such as a name/title field. (Alternatively
you could put a command button in the form's footer to do the same job but
let's assume you choose a name field.) In the DblClick event for your chosen
control enter the statement

RaiseEvent NewID(Me.IDNum)

You could enter this statement in the DblClick event for other controls if
you wish. Anyway your now finished with this form. Open up the code module
for the main form and in the declaration section at the top following the
Option statements insert the following statement.

Private WithEvents mfrmMyList as Form_MySubFormName

Change mfrmMyList to any name that seems natural to you but change
Form_MySubFormName to exactly match the Access class name of the form you use
as the sub form. This name will be listed in the left-hand pane of the VBA
window and anyway VBA will give you an autocomplete list to choose from.

The final step on establishing the communication is to set mfrmMyList to
refer to your sub form - up to now you have only define its class. For the
main form select either the Open or Load events. I personally use the Load
event but it's immaterial. Inside that event place the following statement.

Set mfrmMyList = Me.MySubFormControl.Form

Change MySubFormControl to be the name of the control on the main form that
contains your sub form. A common misconception among people new to Access is
that when they place a sub form control on a form that the control is the sub
form. It is not; it is simply a container for the sub form. Hence the .Form
suffix in the statement above.

Now you're done; four statements to establish communication: two in the sub
form and two in the main form. Now you need to code some action to take place
when the main form 'hears' the sub form event. If when you are in the VBA
window for the main form you pull down the left-hand list at the top of the
code pane you should find an entry for mfrmMyList or whatever you called it.
The right-hand combo box will now display NewID. Select this and VBA will
create the top and bottom of a procedure for you. Inside this procedure you
need to code something similar to the following.

Me.Filter = "IDNum = " & lngIDNum
Me.FilterOn

If the synchronisation of the main form does not happen immediately then
include DoEvents as a third statement but I do not think you will need it.

This has been long and tedious. If I've pedantically described things you
already know then I apologise. Anyway let me know how you get on. An
advantage of using events over direct main form addressing is that you don't
have to test whether the main form is loaded; if no one's listening then
nothing happens. Moreover you can redeploy your sub from in another main form
and have that main form take different action.

Cheers,

Rod - Yes I'm enjoying my weekend and looking forward to a Burns Night at
the embassy this evening.

 

[Jpipher]

Thanks so much Rod.

I didn't want to impede on your weekend but this will help me a great
deal. And I love the theory because I can't learn what I am doing if I
don't know why. I've taught myself so much of this that it is nice to
get a bit more understanding of why I do certain things.
Hope Burns Night was a success

Jessica

 

[Jpipher]

Ron,

Is there a reason my buttons now will not work on the main form
associated with this process? They worked before i made the changes.

Thanks, Jessica

 

[Jpipher]

Rod -
A couple of problems.

In step one -- the PublicEvent step
When I enter what you wrote it keeps giving me a compile error message
:

Expected: list separator or ).

Also, my buttons on the main form have stopped working. This may or may
not be related. I originally had them on the tab control and then moved
them off the control so that they are available with all tabs.

In step 3 -- Private WithEvents
mfrmMyList -- should I be naming that the same as the main form name
or something else entirely?

In step 4 -- Set mfrmMyList=
Me.MySubFormControl.Form -- is .Form supposed to be literal or
..TheNameOfMySubform?
The SubFormControl I was looking at using was IDNum, but it exists
in both forms. Is that going to be a problem?

And finally,
When I pull down the left-hand list, there is no entry for
mfrmMyList.

How much of this will be resolved when I fix whatever is wrong in step
1?

Thanks. Jessica

Oh wait there is more... I cannot use the navigation buttons at the
bottom of the main form to go to a specific record. It always asks me
for a parameter value for the IDNum and then it will only move up 1
record, regardless of what I enter into the parameter box. And my
subform no longer shows the results of the query.

Thanks again.

 

[Guest]

Hi Jessica,

Sounds as though you're in a little bit of a mess but don't panic. I can
think of no reason why the buttons stopped working and no reason why you
cannot navigate the main form correctly... but more of that in a moment.

The error message you are getting when entering the Event statement is
usually generated by VBA when it partially 'understands' the statement but
the syntax is wrong. A lot of the time it is bitching that the statement is
incorrectly terminated - but not in this case. Here is the official syntax.

[Public] Event procedurename [(arglist)]

Public as you see is an optional keyword. I always include it to remind
myself that the event is accessible throughout the project and not just
inside this module. Make sure there is a space between Public and Event, I
notice you omitted this in your post. Procedurename is any name that makes
sense and describes the event/condition that occurs but should be unique
within your project. The statement should be placed in the declarations
section at the top of the module and you will get an error message - but a
different message - if you try to put it inside a procedure. If you cannot
solve this then you may have to send me the code.

You can substitute any name you like for mfrmMyList, it does not have to
relate to your event name or the name of any existing form. In my standards
(somewhat close to Reddick VBS Naming Conventions the prefix indicates that
this is a form object and the leading m indicates that this is a module level
name (hence the keyword Private). Note here that WithEvents is all one word.

The .Form in the Me.mysubformcontrol.Form is literal. It is a property of
the sub form control. In fact if the VBA auto prompt functionality is working
you will see it in the list after typing the period (full stop?).

Not surprised you don't see mfrmMyList in the left-hand combo box; you won't
until the Public Event statement is correct and accepted by VBA.

"The SubFormControl I was looking at using was IDNum, but it exists
in both forms. Is that going to be a problem? "

I suspect you may be addressing the wrong control. First, however, no
problem: control names can be reused from form to form; it just means that
when referring to a control where there may be an alternative you should use
explicit addressing. Suppose you have a procedure coded in the module for a
form named frmA containing a control named ID. ID, Me.ID and Forms("frmA").ID
all refer to the same control. The first term is implicit; VBA assumes you
mean the local control on the form in which the code runs; the last two are
explicit - Me being the VBA 'shorthand' for the object within which the code
is running, in this case a form. Also note that the first two are relative;
the code can be copied into another module and will now refer to that module
rather than the original module. Hence the popularity of Me: it is both
explicit and relative!

Now why do I expect that you are addressing the wrong control. Thinking
about your main form it makes sense that IDNum is a control on that form,
after all you tell me it is the primary key of the recordset being displayed,
but it does not make sense that this is the control that contains your sub
form. IDNum is probably a text control bound to a recordset attribute also
named IDNum. This habit of using the same name for the control and attribute
and the fact that VBA objects have default properties leads to a lot of
confusion when starting out with VBA and Access. What do I mean? Well I bet
you think that when you write IDNum in your code you are referring to the
attribute itself. Wrong, you're referring to the control and the control has
a default property of .Value. So IDNum is interpreted by VBA as
Me.IDNum.Value. As a corollary, you can't have two controls of the same name
on a form but you can have controls of different names bound to the same
attribute, a device you might use if you wanted to repeat the same
information of different pages of your tab control.

But I digress. I find it improbable that you designed a control called IDNum
for your sub form when a control of that name was probably created for the
primary key if you used the Access wizard to design the form in the first
place. If the wizard was used to create the sub form control then Access
gives the control the same name as the sub form (Arrrrrggh!). Whatever its
name, this is the control you want when setting the object reference in the
Load or Open events of the main form.

If you have been using the name IDNum then it might explain why VBA thinks
this is a parameter/argument and why the navigation does not work correctly.

Nearly there. There is a question on a previous post. If an Access name has
imbedded spaces then enclose it in brackets, e.g. [Product Tracking Main Form]

I somehow leapfrogged your second post so let me sign off here, study that
post and then get back to you.

Regards,

Rod

 

[Guest]

Hi again Jessica,

It didn't take a lot of study. Did I say before that there are many ways to
do what you want? if I didn't then I should have done. What you have
discovered here is a method of synchronising a recordset with a supplied
value using bookmarks while directly addressing that form from another form.
Me.Parent is a reference to the parent or main form. I don't know offhand
what the value of this property would be if the sub form was run alone. I
don't think it would be Nothing (that you can test for) but that condition
that VBA reports as error 2452.

The code is basically OK and your assumptions correct. One thing that should
be made clear is that the use of FindFirst indicates the recordset is a DAO
recordset and not a ADO recordset when the method Find or Seek would be used.
Also there is no trap in case the value of IDNum is not found in the main
form's recordset. My version:

Dim rstClone as DAO.Recordset
Dim frmMain as Access.Form
Set frmMain = Me.Parent 'Should trap error 2452 here and exit
Set rstClone = frmMain.RecordsetClone 'Note RecordsetClone is all one word
rstClone.MoveFirst 'Position at first record ready for search
rstClone.FindFirst "IDNum = " & Me.IDnum 'Here Me is the subform
If rstClone.NoMatch then
Exit Sub 'or take whatever other action necessary
Else
frmMain.Bookmark = rstClone.Bookmark
End If

Regards,

Rod

 

[Guest]

Silly me! If you get as far as getting my solution to work then the Me.Filter
and Me.FilterOn statement will 'lock' the main form to that particular record
until the user selects filter off (or whatever) from the Access menu. I
suspect this is not really what you want. Shout when you get this far and
I'll give you the bookmark solution but for the main form.

Rod

 

[Jpipher]

Rod.

Thanks for all your assistance. I haven't been able to look at your
responses all week so I went back to review the original steps that
you gave me. I was able to work on it again this morning. I found a
bunch of code in the module for the subform that wasn't supposed to be
there (a couple of other efforts at making this work and the coding
for the buttons that are on the main form - God knows how/why I put it
there) so when I removed that and redid the original statement you
gave me "Public Event NewID(lngIDNum as long)" as Public Event
NewID(lngIDNum), it worked. There still are a couple of bugs, but we
are definitely getting there. Thank you so much.

I am at the Me.Filter and Me.FilterOn statements and yes, I do want to
fix that so that it is more of a GoToRecord Where Record=selected
IDNum from the subform, rather than a filter. But if I have to leave
it that way, what do I need to do after the Me.FilterOn statement to
make the focus jump back to the Main Form Tab Page 1.

The buttons on the main form still aren't working, but we'll get
there. I think it might have something to do with the fact that they
are directly on the main form while the records that I am trying to
search are in a tab control, maybe?

Thanks again. I am very excited about getting this working.

Jessica

PS. What does the "Ing" argument after the Public Event mean?

 

[Jpipher]

Let me clarify one thing. I realize the records are not actually in
the the tab control, but that is where they are viewed. I get the
basic premise of how the form works looking at the records in the
source table or query (which has pulled the data from the tables) Just
so you don't worry about clarifying how that works. I probably don't
use the correct wording when describing my problems because I am self-
taught and have no programming background otherwise, but I have been
working in access for 10 years and get the concept of it. Each year I
learn a liitle more about VBA and SQL to do something I haven't done
before. And this year I have decided to venture out with a little more
creativity... Anyway, I am loving the theory that you are providing
about the coding so if you want to keep right on going with that, you
will have no complaints from me. I just didn't want you to think you
have to waste time on some of the more basic concepts of how the
program functions.

Thanks!!! Jessica

 

[Guest]

Hi Jessica,

Have you noticed that we've slipped to page 27 already!

So you've been using Access for 10 years. If any of my pedantic
explanations are belittling then I apologise; it is always dificult to know
at what level to pitch a response and I always lean toward over-explanation.
Could I recommend that you obtain an Access reference - not a 'Teach
Yourself' or a 'Beginners' text but something that includes VBA as well as
the standard Access objects. I think you're ready for it. As for using the
correct terms there are so many conventions out there that anarchy and
confusion reign: in certain contexts 'array' and 'table' are synonymous, in
others 'table' and 'file'; when referring to tables 'fields', 'columns, and
'attributes' are used interchangeably. I could go on.

Public Event NewID(lngIDNum as long) should cause no error. If you remove
the 'as long' phrase Access will treat lngIdNum as a variant. (I assume
lngIDNum is not defined anywhere else in your project.) What's it for? The MS
help for user events states that you cannot use an event to pass values
whereas the mechanism gives you just that functionality: you can pass as many
values/parameters/arguments as you wish and these are defined in the Publlic
Event statement within parentheses. So in this case there is one argument
named lngIDNum. You set it's value when you use the RaiseEvent statement and
that value becomes available in any event-handler that you code in the
'listening' object. Specifically I suggested it to pass the primary key of
the line being double-clicked. The parent form could, of course, look back at
the sub form and obtain the value directly but I chose a parameter because it
seemed 'cleaner.' (There are many ways to skin a cat - perhaps that's why
they have nine lives.)

OK on to your main concern. In your 'listening' form you will have an event
handler as a procedure defined something like Private Sub
xxxxxxx_NewId(lngIDNum) where xxxxxxx is the name you used in the WithEvents
statement in the declaration section. I originally suggested use of the
Filter property to synchronise your form but then realised that it would
inhibit navigating away from the record(s) matching the filter.

The best way of positioning a recordset at a particular record is to use a
find method on a clone of the recordset. Why use a clone, after all you could
do it on the recordset itself? Well the search goes on 'behind the scenes'
and Access does not have to continually update the form display and the
chance of the user getting a migrane is reduced. So in this event handler
code something like the following (this is the DAO solution).

Dim rstClone as DAO.Recordset
Set rstClone = Me.RecordsetClone 'Note RecordsetClone is all one word
rstClone.MoveFirst 'Position at first record ready for search
rstClone.FindFirst "IDNum = " & lngIDNum 'Here we use the event value
If rstClone.NoMatch then
Exit Sub 'or take whatever other action necessary
Else
Me.Bookmark = rstClone.Bookmark 'Now position form at desired record
End If

Avoid solutions using GoToRecord - it's somewhat 'clunky.' VBA developers
brought forward many of the Access menu and toolbar actions into VBA as
methods. Most reside in the DoCmd object. These methods simulate the Access
commands. By that very definition they have to be less efficient and
possibly more restrictive than the 'native' VBA methods.

Regards,

Rod


Dim rstClone as DAO.Recordset
Dim frmMain as Access.Form
Set frmMain = Me.Parent 'Should trap error 2452 here and exit
Set rstClone = frmMain.RecordsetClone 'Note RecordsetClone is all one word
rstClone.MoveFirst 'Position at first record ready for search
rstClone.FindFirst "IDNum = " & Me.IDnum 'Here Me is the subform
If rstClone.NoMatch then
Exit Sub 'or take whatever other action necessary
Else
frmMain.Bookmark = rstClone.Bookmark
End If