By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,896 Members | 2,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,896 IT Pros & Developers. It's quick & easy.

Displaying first record of subform in parent form.

P: n/a
I have a form with a child form. In the child form there is a list of
names that can grow quite large. On the parent form I want to display
the first name from the child form. I set up a test box that is
populated with the code

=subfrm_media_review_sec_party.Form!first_name & " " &
subfrm_media_review_sec_party.Form!last_name

It works except that when I flip through the names it populates the
parent form with the name of what ever record in the subform I have
clicked on. Of course this is how this code works but how can I modify
it so that so that the text box only displays the first record in the
subform?

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Why are you doing this? Can you think of any well designed program that use
this layout/navigation?

Why not use a continuous form to show a scrolling list of your records, and
a command button to open a single record form to show complete details of
the selected record?

That said, one solution to what you seek is to use a totals query to find
the first/last/max/min record in a set, as the record source of your form.
-Ed

<td*******@yahoo.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
I have a form with a child form. In the child form there is a list of
names that can grow quite large. On the parent form I want to display
the first name from the child form. I set up a test box that is
populated with the code

=subfrm_media_review_sec_party.Form!first_name & " " &
subfrm_media_review_sec_party.Form!last_name

It works except that when I flip through the names it populates the
parent form with the name of what ever record in the subform I have
clicked on. Of course this is how this code works but how can I modify
it so that so that the text box only displays the first record in the
subform?

Nov 13 '05 #2

P: n/a
The reason I am doing this is that I have 4 tabs/pages at the bottom of
the page and the tab/page that lists peoples names is on the second
tab.. The customer that I am designing the database for expects that
even though these peoples names are being stored in a stubform in 90%
of the cases there will only be one record in this subform. Therefore
they would like to display the name of the first record on the main
form.

I know that I can build a sql query that displays the first record
however I cant figure out how to get the results of the query on the
main form since it is not the source for the main form.

Nov 13 '05 #3

P: n/a
OK, I still don't get the big picture, but leave the main form's record
source property blank. In the OnOpen event of the form, put code to set the
form's record source based on the conditions you anticipate.

pseudocode
If Me!subfrmControlName.recordcount=1 then
Me!recordsource = "myTable"
Else
Me!recordsource = "somethingelse"
End if

What will you do when there are no records in the subform? A blank main
form will be pretty confusing.
-Ed

<td*******@yahoo.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
The reason I am doing this is that I have 4 tabs/pages at the bottom of
the page and the tab/page that lists peoples names is on the second
tab.. The customer that I am designing the database for expects that
even though these peoples names are being stored in a stubform in 90%
of the cases there will only be one record in this subform. Therefore
they would like to display the name of the first record on the main
form.

I know that I can build a sql query that displays the first record
however I cant figure out how to get the results of the query on the
main form since it is not the source for the main form.

Nov 13 '05 #4

P: n/a
I guess I didnt explain this very well. I have a main form with lots
of fields. Lets say the form is for a bank and contains the account
holders information such as their name and address. In bottom of this
subform I have 2 pages/tabs that display subforms. The tab that
displays by default is the account sub form and it lists all the
accounts the person has at the bank. The second tab that is not
normally displayed contains information about additional authorized
users(like the accoutns holders wifes name). Since in day to day
business the list of accounts is more important we display the list of
account subform by default(there is not enough room to put both the
subforms on the main form without tabs). However, what I want to do in
a single text box on our main form is to display the name of the first
records in the subform that contains additional authroized users.

I thought I had it by adding the source to the textbox in the main form
of
=subfrm_media_review_sec_party.Form!first_name & " " &
subfrm_media_review_sec_party.Form!last_name

My code by default displays the first authroized user, however if there
are more then one authorized users and someone tabs through the subform
of authorized useres the text box in the main form changes since

=subfrm_media_review_sec_party.Form!first_name & " " &
subfrm_media_review_sec_party.Form!last_name

shows the value of the current record in the subform. So what happens
is as I flip through the rows of the datasheet in teh subform the text
box changes. I just want to display the name from the first record of
the subform no matter what record the user in the subform is viewing.

Nov 13 '05 #5

P: n/a
You don't say how records are sorted in your "addtional users" subform, so
not sure how you are determining what is the first record. Use a domain
function as the control source of your text box to display the value of one
or more fields in that subform record.

DFirst("[AdditionalAcctHolderName]","tblAccAcctNames]","AcctID=Forms!myMainForm!AcctID")

You'll be looking up the first record of related account holders - ones that
match the account ID that shows on the main form. This value will remain
until you move to a new record on the main form.
-Ed

================================================== =====
From Access Help:
DFirst, DLast Functions
See Also Specifics
You can use the DFirst and DLast functions to return a random record from a
particular field in a table or query when you simply need any value from
that field. Use the DFirst and DLast functions in a macro, module, query
expression, or calculated control on a form or report.

DFirst(expr, domain, [criteria])

DLast(expr, domain, [criteria])

The DFirst and DLast functions have the following arguments.

Argument Description
expr An expression that identifies the field from which you want to
find the first or last value. It can be either a string expression
identifying a field in a table or query, or an expression that performs a
calculation on data in that field. In expr, you can include the name of a
field in a table, a control on a form, a constant, or a function. If expr
includes a function, it can be either built-in or user-defined, but not
another domain aggregate or SQL aggregate function.
domain A string expression identifying the set of records that
constitutes the domain.
criteria An optional string expression used to restrict the range of
data on which the DFirst or DLast function is performed. For example,
criteria is often equivalent to the WHERE clause in an SQL expression,
without the word WHERE. If criteria is omitted, the DFirst and DLast
functions evaluate expr against the entire domain. Any field that is
included in criteria must also be a field in domain; otherwise, the DFirst
and DLast functions return a Null.
Remarks
Note If you want to return the first or last record in a set of records (a
domain), you should create a query sorted as either ascending or descending
and set the TopValues property to 1. For more information, see the TopValues
property topic. From Visual Basic, you can also create an ADO Recordset
object and use the MoveFirst or MoveLast method to return the first or last
record in a set of records.

<td*******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I guess I didnt explain this very well. I have a main form with lots
of fields. Lets say the form is for a bank and contains the account
holders information such as their name and address. In bottom of this
subform I have 2 pages/tabs that display subforms. The tab that
displays by default is the account sub form and it lists all the
accounts the person has at the bank. The second tab that is not
normally displayed contains information about additional authorized
users(like the accoutns holders wifes name). Since in day to day
business the list of accounts is more important we display the list of
account subform by default(there is not enough room to put both the
subforms on the main form without tabs). However, what I want to do in
a single text box on our main form is to display the name of the first
records in the subform that contains additional authroized users.

I thought I had it by adding the source to the textbox in the main form
of
=subfrm_media_review_sec_party.Form!first_name & " " &
subfrm_media_review_sec_party.Form!last_name

My code by default displays the first authroized user, however if there
are more then one authorized users and someone tabs through the subform
of authorized useres the text box in the main form changes since

=subfrm_media_review_sec_party.Form!first_name & " " &
subfrm_media_review_sec_party.Form!last_name

shows the value of the current record in the subform. So what happens
is as I flip through the rows of the datasheet in teh subform the text
box changes. I just want to display the name from the first record of
the subform no matter what record the user in the subform is viewing.

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.