Open a form from a record in Querry result or Table

Hi,
How do I attach a form to a query (or a table) so when I select a record, I can have a form popped up?
Thanx.
David

David

It sounds like you are asking about a couple different issues.

If you create a form, you can indicate which query or table is the source.

If you "select a record" ... what do you mean? How? From the form above?

"I can have a form popped up" -- what do you mean? What does this form
relate to? Is it the same as the form above?

--
More info, please ...

Jeff Boyce
Access MVP

Hi Jeff,
When I run a selection query. It produces say 10 records, with 100 columns in each record. All 10 records are presented to me in the datasheet format, which is just fine as it allows me to visually pick out one particular record that I want to examine in more detail.
Once I have "visually" selected a record, I want to review and/or edit all the 100 fileds for that record. So I designed a form with say 4 tab controls, and spread the 100 fields over the tab controls.
What I can't figure out is how to bring up my form, and have it populated with values from the record that I have "visually" selected. I want to be able to open the form with as few clicks as possible, ideally just one click.
I thought of adding a column to my query results and put something in it so that when I click on that something, it will select the record (i.e. row) that I clicked on, and open the form. Is this possible ? (In other words, can a command control be embedded in a column field of a database table?)
I am new to access and am trying to do something with it. So any help would be appreciated.
David

"Jeff Boyce" wrote:

David

It sounds like you are asking about a couple different issues.

If you create a form, you can indicate which query or table is the source.

If you "select a record" ... what do you mean? How? From the form above?

"I can have a form popped up" -- what do you mean? What does this form
relate to? Is it the same as the form above?

--
More info, please ...

Jeff Boyce
Access MVP

David

!Red flag!

Access is a relational database ... and it sounds like you have "committed
spreadsheet" 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

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
spreadsheet" 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

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 datasheet.

--
Reggie

----------
"David" wrote in message
...
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
spreadsheet" 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

Reggie,
Thanks for the comment.
Do you happen to know where I may find actual or sample codes to implement my requirement ? Recall I need to code a form so that it opens a query's nth record.

I am buying the set of bookcs published by Sybex on 2002 Acces.
Perhaps I will be able to find similar codes there.
But if you know exactly where to look, please let me know.


"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 datasheet.

--
Reggie

----------
"David" wrote in message
...
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
spreadsheet" 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

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 datasheet.

--
Reggie

----------
"David" wrote in message
...
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
spreadsheet" 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

David assume for our discussion that you have a form and it is set to
datasheet 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" wrote in message
...
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 datasheet.

--
Reggie

----------
"David" wrote in message
...
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
spreadsheet" 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