Combo box vs. concat?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - Similar question to "Access Rookie's" but I'm not using a subform.
I have table ROOM linked to table KEYS and table LOCATIONS. USERS table is
linked to ROOM. I created a query that concatenates ROOM + BLDG + KEY.
Created a field in form USERS and bound it to the query but it gives me an
error "#Name?". What step am I missing? My goal is to enter a room # in
USERS form and have bldg and key populate; I can do it with combo box but
then only see one field. Thanks!
 
Kpercy,

Do you mean that you have a control on the form that is bound to the
concatenated field in the query? And if you open the query datasheet
itself, the data in the concatenated field is correct? Have you used an
Alias name for the concatenated field in the query that is not the same
as the name of an existing field? Has the name of the concatenated
field been correctly entered as the Control Source of the textbox on the
form? Is the name of the textbox on the form the same as the name of
the concatenated field?
 
Hi Steve & thank you!

Do you mean that you have a control on the form that is bound to the
concatenated field in the query? YES

And if you open the query datasheet itself, the data in the concatenated
field is correct? YES

Have you used an Alias name for the concatenated field in the query that is
not the same as the name of an existing field? ? NOT sure what Alias name
is. In query the concatenated field is call LOCN which is not used
elsewhere.

Has the name of the concatenated field been correctly entered as the Control
Source of the textbox on the
form? YES VIA WIZARD

Is the name of the textbox on the form the same as the name of
 
Kpercy,

Apart from you stating "text box not named" (a textbox must have name),
I am sorry to say that I can't think of an explanation at the moment.
Can you please post back with the SQL of the query that your form is
based on (go to the design view of the query, select SQL from the View
menu, and then copy/paste into your newsgroup post), and maybe that will
give someone a clue?
 
The SQL is:
SELECT [rooms]![building]+" "+[rooms]![room#]+" "+[rooms]![key] AS locn
FROM rooms;

The text box on the form has a label (automatically created) of Text97 and
that's what it's called in the text box properties as well. Is that what you
mean?

Thanks -- hope something in this is an obvious error.

Katie
 
Katie,

Do you mean that this query is the Record Source of the Form. So the
query only has one field, called locn, and therefore the form only has
one data control, i.e. the textbox. Am I right? And in this textbox is
where you are seeing the #Name?, am I right? Can you look in the design
view of the form, and see what is entered in the Control Source property
of the textbox? I assume it should be locn.

Regarding the name, if you look at the properties of the textbox, you
will see that it has a Name property. As far as I know, it is not
possible for this to be left blank.

This is probably not directly related to the problem, but the
concatenation operator is & not + so the query is not correct. The SQL
should read...
SELECT [rooms].[building] & " " & [rooms]![room#] & " " &
[rooms]![key] AS locn
FROM rooms;
Also, by the way, it is not a good idea to use a # as part of the name
of a field.
 
Steve,

The record source of the form is a table USERS; most data controls on the
form have that table as their record source. The record source of the text
box is the query [location]![locn]. Can a form have multiple control sources
without being a subform? Maybe that's the issue; if so, what's the correct
way to do this so that I can see the Building, the Room and the Key on the
form? If I create a lookup field in the Users table I'm back to a combo box
displaying one value at a time.

The name of the text box is Text96, the name access gave it by default that
I haven't bothered to change at this stage.

I changed the + to &, no effect.

Thanks, Katie

Steve Schapel said:
Katie,

Do you mean that this query is the Record Source of the Form. So the
query only has one field, called locn, and therefore the form only has
one data control, i.e. the textbox. Am I right? And in this textbox is
where you are seeing the #Name?, am I right? Can you look in the design
view of the form, and see what is entered in the Control Source property
of the textbox? I assume it should be locn.

Regarding the name, if you look at the properties of the textbox, you
will see that it has a Name property. As far as I know, it is not
possible for this to be left blank.

This is probably not directly related to the problem, but the
concatenation operator is & not + so the query is not correct. The SQL
should read...
SELECT [rooms].[building] & " " & [rooms]![room#] & " " &
[rooms]![key] AS locn
FROM rooms;
Also, by the way, it is not a good idea to use a # as part of the name
of a field.

--
Steve Schapel, Microsoft Access MVP

The SQL is:
SELECT [rooms]![building]+" "+[rooms]![room#]+" "+[rooms]![key] AS locn
FROM rooms;

The text box on the form has a label (automatically created) of Text97 and
that's what it's called in the text box properties as well. Is that what you
mean?

Thanks -- hope something in this is an obvious error.

Katie
Click to expand...
Click to expand...
 
Katie,

No, you can't have more than one table or query as the record source of
a form. Not only that, but it is invalid syntax to try and refer to a
field in a query like [location]![locn] this doesn't mean anything to
Access, and even if it did, it wouldn't know what to do with it, as
there is no way to indicate which record in the query you want it to use.

Anyway, I think I am beginning to see what you are trying to achieve
here. And looking back to your earlier posts I see that the table USERS
must contain a Room field, is this correct? And this is the same data
as in the Room# field in the Rooms table, right? So, it seems to me
that you need to make a query that includes both the USERS table and the
Rooms table, joined on the Room field, and then you can make your
concatenated field in this query, and make this query the Record Source
of the form instead of the table, and then you can make the control
source of Text96 to be directly bound to the concatenated field in the
query. Make sense?
 
Hi Steve, I see what you're saying, I'll give that a try & see what I come up
with. Thanks for your time & help. Katie

Steve Schapel said:
Katie,

No, you can't have more than one table or query as the record source of
a form. Not only that, but it is invalid syntax to try and refer to a
field in a query like [location]![locn] this doesn't mean anything to
Access, and even if it did, it wouldn't know what to do with it, as
there is no way to indicate which record in the query you want it to use.

Anyway, I think I am beginning to see what you are trying to achieve
here. And looking back to your earlier posts I see that the table USERS
must contain a Room field, is this correct? And this is the same data
as in the Room# field in the Rooms table, right? So, it seems to me
that you need to make a query that includes both the USERS table and the
Rooms table, joined on the Room field, and then you can make your
concatenated field in this query, and make this query the Record Source
of the form instead of the table, and then you can make the control
source of Text96 to be directly bound to the concatenated field in the
query. Make sense?

--
Steve Schapel, Microsoft Access MVP

Steve,

The record source of the form is a table USERS; most data controls on the
form have that table as their record source. The record source of the text
box is the query [location]![locn]. Can a form have multiple control sources
without being a subform? Maybe that's the issue; if so, what's the correct
way to do this so that I can see the Building, the Room and the Key on the
form? If I create a lookup field in the Users table I'm back to a combo box
displaying one value at a time.

The name of the text box is Text96, the name access gave it by default that
I haven't bothered to change at this stage.

I changed the + to &, no effect.

Thanks, Katie
Click to expand...
Click to expand...
 
Back
Top