Query/Lookup Problem

[Guest]

Hi

I run Win 2k with Access 2K.

I have a table called "Add Names" with has 3 colimns in it.

Column 1 has a "First Name" in it (ie; John)
Column 2 has a "Last Name" in it (ie; Smith)
Column 3 has a "Crew" in it (ie; A Crew)

Each name entered into the table is allocated one of the 4 crews that are
available.
(ie; John Smith A Crew)

I have constructed a query that combines the First Name and the Last Name.
(ie; John, Smith).

This allows me to have a combo box in a form that displays the First and
Last Names together. (ie; John, Smith)

I would like to have another field on my form that when I select a persons
name from the combo box the allocated crew that that person is on
automatically displays in that field.

I am not sure if this can be done via a query and how that query would be
attached to the field. I am a very new user to Access so the more detailed
the explanation the better it would help me.

Thank you in advance

John

 

[Ed Warren]

1. add the Crew column to your query
2. now you have a query with two columns (name: John, Smith) (crew: A)
3. Open your form in design view, select the combobox and set the number of
columns to 2 and column widths to 1:0 (the second column is not visible,
zero length)
4. Add a new textbox: txtCrew
5. Set the data value to : =ComboBoxName.column(1) (calculated value equal
to the second column (zero index) of the combobox you are using to show the
name)

Save and enjoy

Ed Warren

 

[Guest]

Thanks for your response ED.

I tried what you said but all I get in the text box is #NAME?

Any Idea what it is I am doing wrong?


Thanks

 

[Ed Warren]

Post the first few rows of the query you are using for the lookup in the
combobox. The name of your combobox, and the name of your textbox

Ed Warren

 

[Guest]

The query I am using is called "Combine Names"

In the pane above where you create the query I have the Add Names Table

Then in the entry fields I have in the 1st colum of the query:

Field = Name: [Last Name] & "," & [First Name] - (this works fine)

In the 2nd column of the query i have:

Field = Crew
Table = Add Names

Both colums in the query have the check boxes ticked (in design view)

When I switch from the design view it displays what I think is correct.

Column 1 = Name (this shows the Last & First names combined - John, Smith)
Column 2 - Crew (this shows the crew - A Crew)

When I add the combo box I pick the query called "Combine Names" for the
source of the data and then select "Name" Doing this allows the combo box to
display the Last and First names together in the combo box.

Lets say the name of the combobox is Combo33.

Now here is where I get stuck. I add a text box (lets say "text33"). Its
from here I start to have problems. I assumed that as the query is created
(Combine Names) has the crews identified in the 2nd column that this text box
would in some way "connect" to the "crews" in the "Combine Names" query?

Sorry for being such a pain with this Ed but I am finding access much more
difficult that Excel to use.

 

[Ed Warren]

When I add the combo box I pick the query called "Combine Names" for the

source of the data and then select "Name" Doing this allows the combo box
to
display the Last and First names together in the combo box

A combobox has two sources of data:
Data tab (in design view)
the Control Source: (where the data is stored)
and the Row Source (the lookup values)
In the case at hand I don't know what the control source should be set to

ComboBox 33

We want
Row Source Type to be set to: Table\query
Row Source : Combine Names
Bound Column: 1

On the Format Tab

Column Count = 2
Column Heads = No
Column Widths = 1";0"

For textBox33 we want to set the Control Source = [ComboBox33].column(1)
this will go to the Combine Names query and for the name selected in the
combobox display the crew.

What is missing in all of this is the basic data table you have tied to the
form with these boxes.

Normally one sees a table something like

PersonID, field1, field2, field3, field4

then uses the combobox to fill in the PersonID

getting the List from a table like your Names Table using a query like your
Combine Names query, with the addition of a PersonID field

So you would have : PersonID, Name, Crew returned
your combobox would have a Control Source of PersonID
a Row Source of Combine Names (three columns)
Widths of 0;1;0 (hide the first column, show the names column, hide the crew
column)
a Width of 0;1;1 would show the second and third column in the combobox.
then you would show the crew in the textbox using a control source of:
ComboBox33.Column(2)

Yes Access ain't Excel. It is used for data handling. Excel is used for
calculations on rows. You may be able to drive nails with a cresent wrench,
but that don't make it a hammer!

Ed Warren.

The query I am using is called "Combine Names"

In the pane above where you create the query I have the Add Names Table

Then in the entry fields I have in the 1st colum of the query:

Field = Name: [Last Name] & "," & [First Name] - (this works fine)

In the 2nd column of the query i have:

Field = Crew
Table = Add Names

Both colums in the query have the check boxes ticked (in design view)

When I switch from the design view it displays what I think is correct.

Column 1 = Name (this shows the Last & First names combined - John, Smith)
Column 2 - Crew (this shows the crew - A Crew)

When I add the combo box I pick the query called "Combine Names" for the
source of the data and then select "Name" Doing this allows the combo box
to
display the Last and First names together in the combo box.

Lets say the name of the combobox is Combo33.

Now here is where I get stuck. I add a text box (lets say "text33"). Its
from here I start to have problems. I assumed that as the query is created
(Combine Names) has the crews identified in the 2nd column that this text
box
would in some way "connect" to the "crews" in the "Combine Names" query?

Sorry for being such a pain with this Ed but I am finding access much more
difficult that Excel to use.

Post the first few rows of the query you are using for the lookup in the
combobox. The name of your combobox, and the name of your textbox

Ed Warren