Re: Open a form from a record in Querry result or Table

From: Reggie (NoSpamreggie_at_NoSpamsmittysinet.com)
Date: 07/23/04 

Date: Fri, 23 Jul 2004 15:29:25 -0700

David assume for our discussion that you have a form and it is set to
data*** view and this form is based on the select query you mentioned
above. Also, the form with all the tabs on it is also based on this same
query. If I understand you correctly you want to doubleclick a record on
the first form so that it opens your tab form and only display the one
record that you double-clicked on the first form. If this is correct then
open the first form in design view select view/properties from the main
menu. Click the Event tab and scroll down to the On Dbl Click event and
click the ellipsis(...) to open the code window. Paste this appropriate
code shown below. Replace the names with the names of the controls/forms in
your app.

'Use this if the search field is a number field
Private Sub Form_DblClick(Cancel As Integer)
    Dim intSearch As Long
    intSearch = Me.MyIDField
    DoCmd.OpenForm "MyTabForm", acNormal, , "[MyIDField] = " & intSearch
End Sub

'Use this if the search field is text field
Private Sub Form_DblClick(Cancel As Integer)
    Dim strSearch As String
    strSearch = Me.MyTextField
    DoCmd.OpenForm "MyTabForm", acNormal, , "[MyTextField] = '" & strSearch
& "'"
End Sub

'Use this if the serach is a date field
Private Sub Form_DblClick(Cancel As Integer)
    Dim dteSearch As Date
    dteSearch = Me.MyDateField
    DoCmd.OpenForm "MyTabForm", acNormal, , "[MyDateField] = #" & dteSearch
& "#"
End Sub

Now when you double-click a record(you must doubleclick the record selector
at the left of the record) on your first form it will open the tab form to
the record you just doubleclicked. Hope this helps. 

-- 
Reggie
----------
"David" <David@discussions.microsoft.com> wrote in message
news:913F3E93-5232-43E7-8CEA-311B5C2811DB@microsoft.com...
> Hi Reggie,
> Thanks for the clarificcation
> Do you know where I may find the actual code or code samples to implement
my requirement?  Recall my requirement is to be able to open an arbitrary
RECORD in a QUERY from say, a command button on a FORM.
> I am buying 2 books on ACCSS 2002 by Sybex.
> Perhaps I can find what I need there.
> But if you know where the codes are pl let  me know.
> David
>
>
>
> "Reggie" wrote:
>
> > David, queries do not have events associated with them.  Design a form
and
> > base the recordsource of the form to the query.  The form has all the
events
> > you mention.  If you want it to be displayed to look like the query
result
> > set the view of the form to data***.
> >
> > -- 
> > Reggie
> >
> > ----------
> > "David" <David@discussions.microsoft.com> wrote in message
> > news:5E330944-FF42-431C-BF4D-A9CFA2170BAF@microsoft.com...
> > > Hi Jeff,
> > > I am sorry the 100 column seems to have side-tracked my question.
> > > Actually it doesn't matter if it is 10 or 100 columns, for my purpose.
> > > I can repose my question is a different way:
> > > I open a form.
> > > It triggers a querry which produces so many rows of data on the
screen.
> > > When I click on (or mouse over) a row in the query result, I want my
form
> > to recognize that "event".
> > > The questions is:  how could the click on the query result be captured
as
> > an event by the form?
> > > I look into the "properties" associated with a select query and do not
> > find "events" associated with the the life of a select query.
> > > I suppose some coding may be needed.
> > > Any hints on how to get it done?
> > > David
> > >
> > > "Jeff Boyce" wrote:
> > >
> > > > David
> > > >
> > > > !Red flag!
> > > >
> > > > Access is a relational database ... and it sounds like you have
> > "committed
> > > > spread***" on it!  A look through the tablesdbdesign newsgroup
will
> > reveal
> > > > that it is VERY uncommon to have more than 30 or so fields in a well
> > > > normalized database design.  ?100 fields?!
> > > >
> > > > We could discuss "how to's" for a long time, but unless you revisit
the
> > > > topic of normalization and consider revising your table structure,
we
> > will
> > > > be trying to make Access do things it is neither intended nor
designed
> > to do
> > > > well.  Sorta like trying to drive nails with a chainsaw <g> ...
> > > >
> > > > You can do it, and even succeed, but it's a lot harder than using
the
> > > > correct tool, and the risk of "hurting yourself" is a lot higher.
> > > >
> > > > Good luck!
> > > >
> > > > Jeff Boyce
> > > > <Access MVP>
> > > >
> > > >
> >
> >
> >