How to choose a form record based on selection from a subform?

[Andrew Meador]

I have a main form (in Access 2007) with a few fields for the user
to fill in. Below the data entry fields is a subform that lists all
the records from the same query the main form is based on. This is to
make it easy for the users to see the current record set as well as a
quick way for them to select records for editing. I want to make it so
that if the user clicks on a subform record, the main form will go to
that record for editing.

So far I put a hidden textbox on the main form and set it's Control
Source to "=[Subform]![RecID]. The textbox is updated with the RecID
selected. The problem I'm having now is that I can't figure out how to
use this value to update the current record the main form is showing
to match that RecID. I'm particularly hung up on the events for the
text box. I can't get any of the following events to fire: Before
Update, After Update, On Dirty, or On Change. I have code that should
respond to all of those events that just pops up a message box to see
if the code is executing, but none of them do. So, the textbox is
being updated, but I can't get an event to fire so as to do anything
with the value.

Anyone have an explanation as to why these events aren't firing?

Anyone have a different approach to this?

Thanks!

 

[Jeff Boyce]

Andrew

Don't know if this (different) approach would be suitable for your situation
....

I use a combobox in the header of my form to have the user select a record
to view/edit. In the AfterUpdate event of that (unbound) combobox, I use:
Me.Requery
to get the form to re-check its underlying query.

That underlying query points to the form's combobox to find the ID of the
record to return.

So when I first open the form, the combobox is empty, so the query returns
the record with (no) ID ... that is, no record!

Then, after selecting a record in the combobox and pressing <Tab> or
<Enter>, the form reruns the query, which FINDS an ID in the combobox this
time, and displays that record.

Is that something like what you're looking for?

(I guess I'm a little puzzled by what you've described. Are you saying your
subform displays all the records? How have you related your subform to your
main form... if at all?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Andrew Meador]

I have fooled around with this using listbox and combobox, which
might solve my event firing issues, but the asthetics and
functionality are bothersome to me. Both options don't show the record
list with alternating background colors which helps make the records
listed easier to track across the width of the form. The listbox shows
the area defined to start with, full of records, but doesn't have an
option to show the column names or to sort of the columns. The combo
box has to be clicked on and "minimizes" once a record is clicked. It
can show the column names, but also cannot sort on the columns.

The subform shows 4 columns of interest to the user to determine if
a new record is required or if they need to edit one. It is very
helpfull for this list to remain visible while they are entering or
editing records as the data that is visible can help them with data
consistency. Three fields are pulled from tables so the values in
those are consistant, but the 4th column is not, yet often needs to be
the same as other records that have a different pattern of values in
the first three columns.

So I think the path I'm following gives me the best results - if I
can get it to work.

Thanks!

Andrew

Don't know if this (different) approach would be suitable for your situation
...

I use a combobox in the header of my form to have the user select a record
to view/edit.  In the AfterUpdate event of that (unbound) combobox, I use:
    Me.Requery
to get the form to re-check its underlying query.

That underlying query points to the form's combobox to find the ID of the
record to return.

So when I first open the form, the combobox is empty, so the query returns
the record with (no) ID ... that is, no record!

Then, after selecting a record in the combobox and pressing <Tab> or
<Enter>, the form reruns the query, which FINDS an ID in the combobox this
time, and displays that record.

Is that something like what you're looking for?

(I guess I'm a little puzzled by what you've described.  Are you sayingyour
subform displays all the records?  How have you related your subform toyour
main form... if at all?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

  I have a main form (in Access 2007) with a few fields for the user
to fill in. Below the data entry fields is a subform that lists all
the records from the same query the main form is based on. This is to
make it easy for the users to see the current record set as well as a
quick way for them to select records for editing. I want to make it so
that if the user clicks on a subform record, the main form will go to
that record for editing.

  So far I put a hidden textbox on the main form and set it's Control
Source to "=[Subform]![RecID]. The textbox is updated with the RecID
selected. The problem I'm having now is that I can't figure out how to
use this value to update the current record the main form is showing
to match that RecID. I'm particularly hung up on the events for the
text box. I can't get any of the following events to fire: Before
Update, After Update, On Dirty, or On Change. I have code that should
respond to all of those events that just pops up a message box to see
if the code is executing, but none of them do. So, the textbox is
being updated, but I can't get an event to fire so as to do anything
with the value.

  Anyone have an explanation as to why these events aren't firing?

  Anyone have a different approach to this?

  Thanks!- Hide quoted text -

- Show quoted text -

 

[Andrew Meador]

Oops. To answer your other questions. The subquery is not related to
the main form other than the fact that they are using the same query/
table to pull their data and store the data. The subform shows all the
records from the stored query. And, upon entering a new record in the
main form it is immediately requeried in the subform to show it. But
there is not other 'link' bwtween these forms.

But what about the non-firing of the text box events? Or, is there
some link I can make from the main table to the text box that is
receiveing the RecID value from the subform? Some code I can use to
get the main form to jump to that record in some other way?

Thanks!

Andrew

Don't know if this (different) approach would be suitable for your situation
...

I use a combobox in the header of my form to have the user select a record
to view/edit.  In the AfterUpdate event of that (unbound) combobox, I use:
    Me.Requery
to get the form to re-check its underlying query.

That underlying query points to the form's combobox to find the ID of the
record to return.

So when I first open the form, the combobox is empty, so the query returns
the record with (no) ID ... that is, no record!

Then, after selecting a record in the combobox and pressing <Tab> or
<Enter>, the form reruns the query, which FINDS an ID in the combobox this
time, and displays that record.

Is that something like what you're looking for?

(I guess I'm a little puzzled by what you've described.  Are you sayingyour
subform displays all the records?  How have you related your subform toyour
main form... if at all?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

  I have a main form (in Access 2007) with a few fields for the user
to fill in. Below the data entry fields is a subform that lists all
the records from the same query the main form is based on. This is to
make it easy for the users to see the current record set as well as a
quick way for them to select records for editing. I want to make it so
that if the user clicks on a subform record, the main form will go to
that record for editing.

  So far I put a hidden textbox on the main form and set it's Control
Source to "=[Subform]![RecID]. The textbox is updated with the RecID
selected. The problem I'm having now is that I can't figure out how to
use this value to update the current record the main form is showing
to match that RecID. I'm particularly hung up on the events for the
text box. I can't get any of the following events to fire: Before
Update, After Update, On Dirty, or On Change. I have code that should
respond to all of those events that just pops up a message box to see
if the code is executing, but none of them do. So, the textbox is
being updated, but I can't get an event to fire so as to do anything
with the value.

  Anyone have an explanation as to why these events aren't firing?

  Anyone have a different approach to this?

  Thanks!- Hide quoted text -

- Show quoted text -

 

[Jeff Boyce]

Andrew

Hopefully another reader has taken your approach and worked it out.

I don't have any experience with the technique you're trying.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Oops. To answer your other questions. The subquery is not related to
the main form other than the fact that they are using the same query/
table to pull their data and store the data. The subform shows all the
records from the stored query. And, upon entering a new record in the
main form it is immediately requeried in the subform to show it. But
there is not other 'link' bwtween these forms.

But what about the non-firing of the text box events? Or, is there
some link I can make from the main table to the text box that is
receiveing the RecID value from the subform? Some code I can use to
get the main form to jump to that record in some other way?

Thanks!

Andrew

Don't know if this (different) approach would be suitable for your
situation
...

I use a combobox in the header of my form to have the user select a record
to view/edit. In the AfterUpdate event of that (unbound) combobox, I use:
Me.Requery
to get the form to re-check its underlying query.

That underlying query points to the form's combobox to find the ID of the
record to return.

So when I first open the form, the combobox is empty, so the query returns
the record with (no) ID ... that is, no record!

Then, after selecting a record in the combobox and pressing <Tab> or
<Enter>, the form reruns the query, which FINDS an ID in the combobox this
time, and displays that record.

Is that something like what you're looking for?

(I guess I'm a little puzzled by what you've described. Are you saying
your
subform displays all the records? How have you related your subform to
your
main form... if at all?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a main form (in Access 2007) with a few fields for the user
to fill in. Below the data entry fields is a subform that lists all
the records from the same query the main form is based on. This is to
make it easy for the users to see the current record set as well as a
quick way for them to select records for editing. I want to make it so
that if the user clicks on a subform record, the main form will go to
that record for editing.

So far I put a hidden textbox on the main form and set it's Control
Source to "=[Subform]![RecID]. The textbox is updated with the RecID
selected. The problem I'm having now is that I can't figure out how to
use this value to update the current record the main form is showing
to match that RecID. I'm particularly hung up on the events for the
text box. I can't get any of the following events to fire: Before
Update, After Update, On Dirty, or On Change. I have code that should
respond to all of those events that just pops up a message box to see
if the code is executing, but none of them do. So, the textbox is
being updated, but I can't get an event to fire so as to do anything
with the value.

Anyone have an explanation as to why these events aren't firing?

Anyone have a different approach to this?

Thanks!- Hide quoted text -

- Show quoted text -

 

[Jeff Boyce]

Andrew

I'll suggest that you re-post your question. That way, more folks are
likely to see it and jump in.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Oops. To answer your other questions. The subquery is not related to
the main form other than the fact that they are using the same query/
table to pull their data and store the data. The subform shows all the
records from the stored query. And, upon entering a new record in the
main form it is immediately requeried in the subform to show it. But
there is not other 'link' bwtween these forms.

But what about the non-firing of the text box events? Or, is there
some link I can make from the main table to the text box that is
receiveing the RecID value from the subform? Some code I can use to
get the main form to jump to that record in some other way?

Thanks!

Andrew

Don't know if this (different) approach would be suitable for your
situation
...

I use a combobox in the header of my form to have the user select a record
to view/edit. In the AfterUpdate event of that (unbound) combobox, I use:
Me.Requery
to get the form to re-check its underlying query.

That underlying query points to the form's combobox to find the ID of the
record to return.

So when I first open the form, the combobox is empty, so the query returns
the record with (no) ID ... that is, no record!

Then, after selecting a record in the combobox and pressing <Tab> or
<Enter>, the form reruns the query, which FINDS an ID in the combobox this
time, and displays that record.

Is that something like what you're looking for?

(I guess I'm a little puzzled by what you've described. Are you saying
your
subform displays all the records? How have you related your subform to
your
main form... if at all?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a main form (in Access 2007) with a few fields for the user
to fill in. Below the data entry fields is a subform that lists all
the records from the same query the main form is based on. This is to
make it easy for the users to see the current record set as well as a
quick way for them to select records for editing. I want to make it so
that if the user clicks on a subform record, the main form will go to
that record for editing.

So far I put a hidden textbox on the main form and set it's Control
Source to "=[Subform]![RecID]. The textbox is updated with the RecID
selected. The problem I'm having now is that I can't figure out how to
use this value to update the current record the main form is showing
to match that RecID. I'm particularly hung up on the events for the
text box. I can't get any of the following events to fire: Before
Update, After Update, On Dirty, or On Change. I have code that should
respond to all of those events that just pops up a message box to see
if the code is executing, but none of them do. So, the textbox is
being updated, but I can't get an event to fire so as to do anything
with the value.

Anyone have an explanation as to why these events aren't firing?

Anyone have a different approach to this?

Thanks!- Hide quoted text -

- Show quoted text -

 

[blake7]

Hi try the code below, this works for me, when I double click fields in my
datasheet it opens the form "mainauditdata" for viewing individual records,
this VBA code is copied to all the fields in the datasheet in the "double
click" option in visual basic, so is does not matter which field in the
datasheet you double click on. so just replace my "mainauditdata" with your
form name and the stLinkCriteria with the name of your primary key field(mine
is ID) hope this helps.
Regards
Tony


Private Sub Area_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "mainauditdata"
stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Maximize
End Sub

Andrew

I'll suggest that you re-post your question. That way, more folks are
likely to see it and jump in.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Oops. To answer your other questions. The subquery is not related to
the main form other than the fact that they are using the same query/
table to pull their data and store the data. The subform shows all the
records from the stored query. And, upon entering a new record in the
main form it is immediately requeried in the subform to show it. But
there is not other 'link' bwtween these forms.

But what about the non-firing of the text box events? Or, is there
some link I can make from the main table to the text box that is
receiveing the RecID value from the subform? Some code I can use to
get the main form to jump to that record in some other way?

Thanks!

Andrew

Don't know if this (different) approach would be suitable for your
situation
...

I use a combobox in the header of my form to have the user select a record
to view/edit. In the AfterUpdate event of that (unbound) combobox, I use:
Me.Requery
to get the form to re-check its underlying query.

That underlying query points to the form's combobox to find the ID of the
record to return.

So when I first open the form, the combobox is empty, so the query returns
the record with (no) ID ... that is, no record!

Then, after selecting a record in the combobox and pressing <Tab> or
<Enter>, the form reruns the query, which FINDS an ID in the combobox this
time, and displays that record.

Is that something like what you're looking for?

(I guess I'm a little puzzled by what you've described. Are you saying
your
subform displays all the records? How have you related your subform to
your
main form... if at all?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a main form (in Access 2007) with a few fields for the user
to fill in. Below the data entry fields is a subform that lists all
the records from the same query the main form is based on. This is to
make it easy for the users to see the current record set as well as a
quick way for them to select records for editing. I want to make it so
that if the user clicks on a subform record, the main form will go to
that record for editing.

So far I put a hidden textbox on the main form and set it's Control
Source to "=[Subform]![RecID]. The textbox is updated with the RecID
selected. The problem I'm having now is that I can't figure out how to
use this value to update the current record the main form is showing
to match that RecID. I'm particularly hung up on the events for the
text box. I can't get any of the following events to fire: Before
Update, After Update, On Dirty, or On Change. I have code that should
respond to all of those events that just pops up a message box to see
if the code is executing, but none of them do. So, the textbox is
being updated, but I can't get an event to fire so as to do anything
with the value.

Anyone have an explanation as to why these events aren't firing?

Anyone have a different approach to this?

Thanks!- Hide quoted text -

- Show quoted text -

.