This is such a simple problem. I've asked for, and gotten, help with this in
the past, and I can't make this work. Once more, please -

I want to be able to put a member number (which is the primary key of a
table called tblMemberInfo) into a text box on an unbound form and get back,
in some other text boxes, other information (name, e-mail address and date
of birth, say) from the same table. I also want to get, from a table called
tblMembershipInfo, the membership expiration date. I used the QBE pane to
construct the following SQL, and it does return the information I want. It
runs as a parameter query -

SELECT tblMemberInfo.fldMemNum, tblMemberInfo.fldLastName,
tblMemberInfo.fldFirstName, tblMemberInfo.fldEMailAddy,
Max(tblMembershipInfo.fldExpDate) AS MaxOffldExpDate

FROM tblMemberInfo INNER JOIN tblMembershipInfo ON
tblMemberInfo.fldMemNum = tblMembershipInfo.fldMemNum

GROUP BY tblMemberInfo.fldMemNum, tblMemberInfo.fldLastName,
tblMemberInfo.fldFirstName, tblMemberInfo.fldEMailAddy

HAVING
(((tblMemberInfo.fldMemNum)=[Forms]![GuestAdmin]![txtFindThisMember].[text])
);

However, I cannot get the information that is returned from the query to
come back in text boxes - I can't bind them to the query. I've been told
that this is possible, but I'm overlooking something.

I am using an unbound form because if I bind the form to the query, I get
the request for the Member Number in the form of a parameter request. If I
bind the form to the table, I get all the records.

I would like to have the form open, show the blank text boxes and with the
cursor in the text box where the user can enter the member number of the
person whose information is to be returned.

This is the kind of request that databases handle - information about a
particular person, who is identified to the DB with a member number, an
employee number, a whatever number. So this must be possible.

I want to use text boxes for the returned information because I think they
look better on the form. Is that the problem? Will I have to use a combo box
or a list box, even though only one record will be returned for any given
request?

Any and all help, as always, will be much appreciated.


Steve E.

Bind your form to a query that either returns all the records, not a query
that refers to a form control. In the Header of the Form, place a Combo Box
and use the option on the Combo Box wizard to look up a particular record.
You could require that the user type in the member number in a text box and
use similar code, but there would be no check that the member number
actually exists (which you would have with the combo box, and limit to
list), and it wouldn't be nearly so user-friendly.

If you want to modify the code just a bit, you could bind the Form to a
Query that just brings back one (arbitrary) record with a TOP 1 query, and
use the entered value to modify the SQL that is the Form's RecordSource and
replace it. This would be my choice if the table(s) are large, because it
would be more efficient.

Unbound forms are rarely the best approach for handling _data_. They are
fine for switchboards, selection forms, etc., but usually bound forms work
better for handling data.

Larry Linson
Microsoft Access MVP

"Serious_Practitioner" wrote
[color=blue]
> I want to be able to put a member number
> (which is the primary key of a table called
> tblMemberInfo) into a text box on an unbound
> form and get back, in some other text boxes,
> other information (name, e-mail address and date
> of birth, say) from the same table. I also want to
> get, from a table called tblMembershipInfo, the
> membership expiration date.[/color]
[color=blue]
> However, I cannot get the information that
> is returned from the query to come back in
> text boxes - I can't bind them to the query.
> I've been told that this is possible, but I'm
> overlooking something.
>
> I am using an unbound form because if I
> bind the form to the query, I get the request
> for the Member Number in the form of a
> parameter request. If I bind the form to the
> table, I get all the records.
>
> I would like to have the form open, show the blank text boxes and with the
> cursor in the text box where the user can enter the member number of the
> person whose information is to be returned.
>
> This is the kind of request that databases handle - information about a
> particular person, who is identified to the DB with a member number, an
> employee number, a whatever number. So this must be possible.
>
> I want to use text boxes for the returned information because I think they
> look better on the form. Is that the problem? Will I have to use a combo[/color]
box[color=blue]
> or a list box, even though only one record will be returned for any given
> request?
>
> Any and all help, as always, will be much appreciated.
>
>
> Steve E.
>
>
>[/color]