how to jump from form to related record in table

  • Thread starter Thread starter Jesse
  • Start date Start date
J

Jesse

I am trying to deal with the limits on updating fields in queries.
So, in a form that shows data re orders waiting to be shipped, I'd
like to be able to doubleclick on the order_ID number field (let's say
order # 02975) and have that cause a related form or table to open at
record number 02975. Then, I can directly edit the data that needs
editing.

Using Access 2000 "Macro builder" and the On Dbl Click event, I have
been able to get a desired form to open and have the focus, but I
cannot get the related record to be selected. (I'd prefer selected to
filtered.) I am hoping this can be done via the Macro Builder, but if
actual macro coding is required, so be it. If you know a
straightforward way to do this, I'd appreciate some advice.

(Note: This is for a very small business (mine), so while sending
someone to a table to edit it may not be Best Practices, for me it
would work fine.)

Thanks -- Jesse
 
I am trying to deal with the limits on updating fields in queries.
So, in a form that shows data re orders waiting to be shipped, I'd
like to be able to doubleclick on the order_ID number field (let's say
order # 02975) and have that cause a related form or table to open at
record number 02975. Then, I can directly edit the data that needs
editing.

Using Access 2000 "Macro builder" and the On Dbl Click event, I have
been able to get a desired form to open and have the focus, but I
cannot get the related record to be selected. (I'd prefer selected to
filtered.) I am hoping this can be done via the Macro Builder, but if
actual macro coding is required, so be it. If you know a
straightforward way to do this, I'd appreciate some advice.

(Note: This is for a very small business (mine), so while sending
someone to a table to edit it may not be Best Practices, for me it
would work fine.)

Thanks -- Jesse
Click to expand...

allowing users to edit tables directly is a BAD practice. Also, if
you modify tables directly, you cannot take advantage of all the handy
things forms allow you to do... like show related records. If you
create a form based on your table/query, you can create a button that
will do exactly what you describe... the wizard will do most of it for
you.
 
allowing users to edit tables directly is a BAD practice. Also, if
you modify tables directly, you cannot take advantage of all the handy
things forms allow you to do... like show related records. If you
create a form based on your table/query, you can create a button that
will do exactly what you describe... the wizard will do most of it for
you.
Click to expand...

Thanks for the button suggestion. I can get part of that to work, but
I cannot figure out how to use one of the Access 2000 event builders
to get it to goto the _related_ record in the target table/query/form.
The best I can do is to have it open the find dialog in the target.

So, for example: I have 1000 records in my main table and am looking
at order_num 0237 in sumry_form . When I click on the custom button,
I'd like the process to open details_form -and- automatically
find/move to order_num 0237. Can this be done via one of the
builders? If not, any other suggested solution? Thanks -- Jesse
 
Thanks for the button suggestion. I can get part of that to work, but
I cannot figure out how to use one of the Access 2000 event builders
to get it to goto the _related_ record in the target table/query/form.
The best I can do is to have it open the find dialog in the target.

So, for example: I have 1000 records in my main table and am looking
at order_num 0237 in sumry_form . When I click on the custom button,
I'd like the process to open details_form -and- automatically
find/move to order_num 0237. Can this be done via one of the
builders? If not, any other suggested solution? Thanks -- Jesse
Click to expand...

I think so. Haven't used A2000 in a long time.

You can pass a filter in the DoCmd.OpenForm command. You would just
provide a valid WHERE clause, without the "WHERE" keyword.

This is the code the Access2002 Wizard builds...

' Yes, naming a button Command# is BAD!

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

Dim stDocName As String
Dim stLinkCriteria As String

'---Change "sfrmHasNeed" to the name of your related form. (NOT a
table...)
stDocName = "sfrmHasNeed"

'---Criteria: show the fields on each side with matching values, so
the filter will work...
stLinkCriteria = "[OrphanID]=" & Me![OrphanID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub


Hope that helps.
Pieter
 
On Wed, 15 Aug 2007 19:26:00 -0700, "(e-mail address removed)"

[clip]
I think so. Haven't used A2000 in a long time.

You can pass a filter in the DoCmd.OpenForm command. You would just
provide a valid WHERE clause, without the "WHERE" keyword.

This is the code the Access2002 Wizard builds...

' Yes, naming a button Command# is BAD!

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

Dim stDocName As String
Dim stLinkCriteria As String

'---Change "sfrmHasNeed" to the name of your related form. (NOT a
table...)
stDocName = "sfrmHasNeed"

'---Criteria: show the fields on each side with matching values, so
the filter will work...
stLinkCriteria = "[OrphanID]=" & Me![OrphanID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub


Hope that helps.
Pieter
Click to expand...

Pieter - This is an interesting challenge for me, even simply
implementing your suggestion. Thank you very much for the help and
inspiration. - Jesse
 
Back
Top