Display all columns on a form that are shown in a drop-down box

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

Guest

I am making a database to track employee schedules. I am stuck on a form and
cannot get a very simply action to take place. I based the form on a query.
One of my fields is a look-up column of the employee's first name,last name,
and store
#. When I use the look-up table on the form, I am able to see all 3 fields,
however, when I select an employee from the list, only their first name is
displayed on the form. I want to be able to see all 3 fields, as they are
shown in the look-up column (first name, last name, store #).

I am 99% sure I need a combo box to do this, but have been trying to make
one and it is not working. I am putting a combo box on the form and trying
to go about it that way, but it is not working, always getting an error in
syntax or such. I think I just need step-by-step instruction on how to make
this combo box correctly. I have tried looking at other similar posts and
replicating those based on my needs, but it just isn't working.

Any help would be greatly appreciated. Thanks!
 
Since your form is based on a query, you should be able to locate the
appropriate data when you set up your combo box. I would make a field
in your query for the employee name:
FullName: LastName & ", " & FirstName.
Make a new combo box and in the wizard select "Find a record on my form
based on the record I selected in my combo box." The Fields you are
going to want are EmployeeID, FullName. Your bound column should be 1,
number of columns is 2, and column widths should be 0;1. Now when you
type in your employee name, last name first, your combo box will
pattern match the name. When you find the one you want, the other
fields should populate automatically, namely the store number. You
don't really need the separate name fields for this.

Hope that helps!
 
Hi Jeff L,

Thanks for your quick response. I am about halfway where I want to be now,
as I am having trouble seeing a list of all employees. When I was designing
the query, I entered a 3 fake employees into the query's datasheet view.

Now, I took your advice and have the following field in the query the form
is based off of:

FIELD: FirstNameLastNameHomeStore: [FirstName] & " " & [LastName] & " " &
[HomeStore]
I left TABLE blank and SHOW is checked. I think my problem is that I do not
have a table specified, but I want to be sure before I fiddle around with it
more.

Also, FirstNameLastNameHomeStore is my variable name in design view of a
lookup column in a "scheduling" table, which most of the query is based off
of. Also, per user preference, I have decided to put first name first, fyi.

Thanks so much, I'm sure it's a simple fix, but I can't figure it out.

Kate
 
Leaving the table blank is fine. The only time you would need to use
the table name is if your query were using tables that had field names
that were the same, in which case you would put
FirstNameLastNameHomeStore: TableName.[FirstName] & " " &
TableName.[LastName] & " " & TableName.[HomeStore]

Also, FirstNameLastNameHomeStore is my variable name in design view of
a
lookup column in a "scheduling" table, which most of the query is based
off
of. Also, per user preference, I have decided to put first name first,
fyi.

I didn't really follow what you were saying here.
Let me know if you are still having issues.

Hi Jeff L,

Thanks for your quick response. I am about halfway where I want to be now,
as I am having trouble seeing a list of all employees. When I was designing
the query, I entered a 3 fake employees into the query's datasheet view.

Now, I took your advice and have the following field in the query the form
is based off of:

FIELD: FirstNameLastNameHomeStore: [FirstName] & " " & [LastName] & " " &
[HomeStore]
I left TABLE blank and SHOW is checked. I think my problem is that I do not
have a table specified, but I want to be sure before I fiddle around with it
more.

Also, FirstNameLastNameHomeStore is my variable name in design view of a
lookup column in a "scheduling" table, which most of the query is based off
of. Also, per user preference, I have decided to put first name first, fyi.

Thanks so much, I'm sure it's a simple fix, but I can't figure it out.

Kate



Jeff L said:
Since your form is based on a query, you should be able to locate the
appropriate data when you set up your combo box. I would make a field
in your query for the employee name:
FullName: LastName & ", " & FirstName.
Make a new combo box and in the wizard select "Find a record on my form
based on the record I selected in my combo box." The Fields you are
going to want are EmployeeID, FullName. Your bound column should be 1,
number of columns is 2, and column widths should be 0;1. Now when you
type in your employee name, last name first, your combo box will
pattern match the name. When you find the one you want, the other
fields should populate automatically, namely the store number. You
don't really need the separate name fields for this.

Hope that helps!
Click to expand...
Click to expand...
 
Unfortunately, I think I have fiddled around too much and actually made
things worse - the datasheet view of my query is a mess, and when I try to
enter data in the form (based on the messy query), I hear only error noises
(but no error messages).

I think I may have figured out why I am having so much trouble - I was not
following rules of database normalization. The database is based on 4 main
tables, two of which are the most important - "StoreDetectives" and
"Schedules". The "StoreDetectives" table stores personal information
(FirstName, LastName, HomeStore, Address, Phone, etc.). The "Scheduling"
query also holds fields to be used in a query to calculate shifts
(WeekEndingDate,SundayStart,SundayEnd,etc.) BUT, I also put
FirstNameLastNameHomeStore in "Scheduling" as a Look-Up column, using the
Look-up Wizard and basing the list off of the FirstName, LastName, and
HomeStore columns of the "StoreDetectives" table.

Please correct me if I'm wrong, but I think I need to overhaul this to
ELIMINATE the name fields from the scheduling query. The way I see it, the
Store Detectives names and scheduling information should come together by way
of a query in which I would pull FirstName, LastName, and HomeStore from the
"StoreDetectives" table, and the scheduling info from "Schedules", along with
shift calculations and such. I would then base my form off of this, and
hopefuly make a functioning combo box along the lines of what you had
explained before.

I think this was the root of my problems, I sure hope so. I have been
working on this database for quite some time now, and working mostly by trial
and error. Unfortunately there has been a lot of trial and error involved in
this process, and it has taken me a lot longer than it should have. I also
only have 2 more days to work on this, as I leave this position then and need
to present the dept. with a functioning database.

Thanks for your help thus far. Please let me know if I'm on the right track
and should overhaul the tables, then I'll go from there with your
suggestions.

Kate

Jeff L said:
Leaving the table blank is fine. The only time you would need to use
the table name is if your query were using tables that had field names
that were the same, in which case you would put
FirstNameLastNameHomeStore: TableName.[FirstName] & " " &
TableName.[LastName] & " " & TableName.[HomeStore]

Also, FirstNameLastNameHomeStore is my variable name in design view of
a
lookup column in a "scheduling" table, which most of the query is based
off
of. Also, per user preference, I have decided to put first name first,
fyi.

I didn't really follow what you were saying here.
Let me know if you are still having issues.

Hi Jeff L,

Thanks for your quick response. I am about halfway where I want to be now,
as I am having trouble seeing a list of all employees. When I was designing
the query, I entered a 3 fake employees into the query's datasheet view.

Now, I took your advice and have the following field in the query the form
is based off of:

FIELD: FirstNameLastNameHomeStore: [FirstName] & " " & [LastName] & " " &
[HomeStore]
I left TABLE blank and SHOW is checked. I think my problem is that I do not
have a table specified, but I want to be sure before I fiddle around with it
more.

Also, FirstNameLastNameHomeStore is my variable name in design view of a
lookup column in a "scheduling" table, which most of the query is based off
of. Also, per user preference, I have decided to put first name first, fyi.

Thanks so much, I'm sure it's a simple fix, but I can't figure it out.

Kate



Jeff L said:
Since your form is based on a query, you should be able to locate the
appropriate data when you set up your combo box. I would make a field
in your query for the employee name:
FullName: LastName & ", " & FirstName.
Make a new combo box and in the wizard select "Find a record on my form
based on the record I selected in my combo box." The Fields you are
going to want are EmployeeID, FullName. Your bound column should be 1,
number of columns is 2, and column widths should be 0;1. Now when you
type in your employee name, last name first, your combo box will
pattern match the name. When you find the one you want, the other
fields should populate automatically, namely the store number. You
don't really need the separate name fields for this.

Hope that helps!

KateCee wrote:
I am making a database to track employee schedules. I am stuck on a form and
cannot get a very simply action to take place. I based the form on a query.
One of my fields is a look-up column of the employee's first name,last name,
and store
#. When I use the look-up table on the form, I am able to see all 3 fields,
however, when I select an employee from the list, only their first name is
displayed on the form. I want to be able to see all 3 fields, as they are
shown in the look-up column (first name, last name, store #).

I am 99% sure I need a combo box to do this, but have been trying to make
one and it is not working. I am putting a combo box on the form and trying
to go about it that way, but it is not working, always getting an error in
syntax or such. I think I just need step-by-step instruction on how to make
this combo box correctly. I have tried looking at other similar posts and
replicating those based on my needs, but it just isn't working.

Any help would be greatly appreciated. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Yes you are certainly on the right track. Your scheduling table should
be related to your StoreDectives table via some sort of unique Id
field, probably your primary key field in StoreDectives. To see a
store dective and their schedule, you need to make a query that joins
the StoreDectives to Schedules on the store dective ID field. You can
use this query in your form.

Hope that helps!

Unfortunately, I think I have fiddled around too much and actually made
things worse - the datasheet view of my query is a mess, and when I try to
enter data in the form (based on the messy query), I hear only error noises
(but no error messages).

I think I may have figured out why I am having so much trouble - I was not
following rules of database normalization. The database is based on 4 main
tables, two of which are the most important - "StoreDetectives" and
"Schedules". The "StoreDetectives" table stores personal information
(FirstName, LastName, HomeStore, Address, Phone, etc.). The "Scheduling"
query also holds fields to be used in a query to calculate shifts
(WeekEndingDate,SundayStart,SundayEnd,etc.) BUT, I also put
FirstNameLastNameHomeStore in "Scheduling" as a Look-Up column, using the
Look-up Wizard and basing the list off of the FirstName, LastName, and
HomeStore columns of the "StoreDetectives" table.

Please correct me if I'm wrong, but I think I need to overhaul this to
ELIMINATE the name fields from the scheduling query. The way I see it, the
Store Detectives names and scheduling information should come together by way
of a query in which I would pull FirstName, LastName, and HomeStore from the
"StoreDetectives" table, and the scheduling info from "Schedules", along with
shift calculations and such. I would then base my form off of this, and
hopefuly make a functioning combo box along the lines of what you had
explained before.

I think this was the root of my problems, I sure hope so. I have been
working on this database for quite some time now, and working mostly by trial
and error. Unfortunately there has been a lot of trial and error involved in
this process, and it has taken me a lot longer than it should have. I also
only have 2 more days to work on this, as I leave this position then and need
to present the dept. with a functioning database.

Thanks for your help thus far. Please let me know if I'm on the right track
and should overhaul the tables, then I'll go from there with your
suggestions.

Kate

Jeff L said:
Leaving the table blank is fine. The only time you would need to use
the table name is if your query were using tables that had field names
that were the same, in which case you would put
FirstNameLastNameHomeStore: TableName.[FirstName] & " " &
TableName.[LastName] & " " & TableName.[HomeStore]

Also, FirstNameLastNameHomeStore is my variable name in design view of
a
lookup column in a "scheduling" table, which most of the query is based
off
of. Also, per user preference, I have decided to put first name first,
fyi.

I didn't really follow what you were saying here.
Let me know if you are still having issues.

Hi Jeff L,

Thanks for your quick response. I am about halfway where I want to be now,
as I am having trouble seeing a list of all employees. When I was designing
the query, I entered a 3 fake employees into the query's datasheet view.

Now, I took your advice and have the following field in the query the form
is based off of:

FIELD: FirstNameLastNameHomeStore: [FirstName] & " " & [LastName] & " " &
[HomeStore]
I left TABLE blank and SHOW is checked. I think my problem is that I do not
have a table specified, but I want to be sure before I fiddle around with it
more.

Also, FirstNameLastNameHomeStore is my variable name in design view of a
lookup column in a "scheduling" table, which most of the query is based off
of. Also, per user preference, I have decided to put first name first, fyi.

Thanks so much, I'm sure it's a simple fix, but I can't figure it out.

Kate



:

Since your form is based on a query, you should be able to locate the
appropriate data when you set up your combo box. I would make a field
in your query for the employee name:
FullName: LastName & ", " & FirstName.
Make a new combo box and in the wizard select "Find a record on my form
based on the record I selected in my combo box." The Fields you are
going to want are EmployeeID, FullName. Your bound column should be 1,
number of columns is 2, and column widths should be 0;1. Now when you
type in your employee name, last name first, your combo box will
pattern match the name. When you find the one you want, the other
fields should populate automatically, namely the store number. You
don't really need the separate name fields for this.

Hope that helps!

KateCee wrote:
I am making a database to track employee schedules. I am stuck on a form and
cannot get a very simply action to take place. I based the form on a query.
One of my fields is a look-up column of the employee's first name,last name,
and store
#. When I use the look-up table on the form, I am able to see all 3 fields,
however, when I select an employee from the list, only their first name is
displayed on the form. I want to be able to see all 3 fields, as they are
shown in the look-up column (first name, last name, store #).

I am 99% sure I need a combo box to do this, but have been trying to make
one and it is not working. I am putting a combo box on the form and trying
to go about it that way, but it is not working, always getting an error in
syntax or such. I think I just need step-by-step instruction on how to make
this combo box correctly. I have tried looking at other similar posts and
replicating those based on my needs, but it just isn't working.

Any help would be greatly appreciated. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff,

thanks for your input - I have now started a fresh database with more
normalized tables. I'm trying to do this one as error-proof as possible,
since I have such a short timeline to complete it in. I have posted a few
questions recently about how to properly format my form and how to fix
negative DateDiff calculations. If you don't mind taking a look at them, I'd
really like to get your input on those questions.

Thanks again,

Kate

Jeff L said:
Yes you are certainly on the right track. Your scheduling table should
be related to your StoreDectives table via some sort of unique Id
field, probably your primary key field in StoreDectives. To see a
store dective and their schedule, you need to make a query that joins
the StoreDectives to Schedules on the store dective ID field. You can
use this query in your form.

Hope that helps!

Unfortunately, I think I have fiddled around too much and actually made
things worse - the datasheet view of my query is a mess, and when I try to
enter data in the form (based on the messy query), I hear only error noises
(but no error messages).

I think I may have figured out why I am having so much trouble - I was not
following rules of database normalization. The database is based on 4 main
tables, two of which are the most important - "StoreDetectives" and
"Schedules". The "StoreDetectives" table stores personal information
(FirstName, LastName, HomeStore, Address, Phone, etc.). The "Scheduling"
query also holds fields to be used in a query to calculate shifts
(WeekEndingDate,SundayStart,SundayEnd,etc.) BUT, I also put
FirstNameLastNameHomeStore in "Scheduling" as a Look-Up column, using the
Look-up Wizard and basing the list off of the FirstName, LastName, and
HomeStore columns of the "StoreDetectives" table.

Please correct me if I'm wrong, but I think I need to overhaul this to
ELIMINATE the name fields from the scheduling query. The way I see it, the
Store Detectives names and scheduling information should come together by way
of a query in which I would pull FirstName, LastName, and HomeStore from the
"StoreDetectives" table, and the scheduling info from "Schedules", along with
shift calculations and such. I would then base my form off of this, and
hopefuly make a functioning combo box along the lines of what you had
explained before.

I think this was the root of my problems, I sure hope so. I have been
working on this database for quite some time now, and working mostly by trial
and error. Unfortunately there has been a lot of trial and error involved in
this process, and it has taken me a lot longer than it should have. I also
only have 2 more days to work on this, as I leave this position then and need
to present the dept. with a functioning database.

Thanks for your help thus far. Please let me know if I'm on the right track
and should overhaul the tables, then I'll go from there with your
suggestions.

Kate

Jeff L said:
Leaving the table blank is fine. The only time you would need to use
the table name is if your query were using tables that had field names
that were the same, in which case you would put
FirstNameLastNameHomeStore: TableName.[FirstName] & " " &
TableName.[LastName] & " " & TableName.[HomeStore]

Also, FirstNameLastNameHomeStore is my variable name in design view of
a
lookup column in a "scheduling" table, which most of the query is based
off
of. Also, per user preference, I have decided to put first name first,
fyi.

I didn't really follow what you were saying here.
Let me know if you are still having issues.


KateCee wrote:
Hi Jeff L,

Thanks for your quick response. I am about halfway where I want to be now,
as I am having trouble seeing a list of all employees. When I was designing
the query, I entered a 3 fake employees into the query's datasheet view.

Now, I took your advice and have the following field in the query the form
is based off of:

FIELD: FirstNameLastNameHomeStore: [FirstName] & " " & [LastName] & " " &
[HomeStore]
I left TABLE blank and SHOW is checked. I think my problem is that I do not
have a table specified, but I want to be sure before I fiddle around with it
more.

Also, FirstNameLastNameHomeStore is my variable name in design view of a
lookup column in a "scheduling" table, which most of the query is based off
of. Also, per user preference, I have decided to put first name first, fyi.

Thanks so much, I'm sure it's a simple fix, but I can't figure it out.

Kate



:

Since your form is based on a query, you should be able to locate the
appropriate data when you set up your combo box. I would make a field
in your query for the employee name:
FullName: LastName & ", " & FirstName.
Make a new combo box and in the wizard select "Find a record on my form
based on the record I selected in my combo box." The Fields you are
going to want are EmployeeID, FullName. Your bound column should be 1,
number of columns is 2, and column widths should be 0;1. Now when you
type in your employee name, last name first, your combo box will
pattern match the name. When you find the one you want, the other
fields should populate automatically, namely the store number. You
don't really need the separate name fields for this.

Hope that helps!

KateCee wrote:
I am making a database to track employee schedules. I am stuck on a form and
cannot get a very simply action to take place. I based the form on a query.
One of my fields is a look-up column of the employee's first name,last name,
and store
#. When I use the look-up table on the form, I am able to see all 3 fields,
however, when I select an employee from the list, only their first name is
displayed on the form. I want to be able to see all 3 fields, as they are
shown in the look-up column (first name, last name, store #).

I am 99% sure I need a combo box to do this, but have been trying to make
one and it is not working. I am putting a combo box on the form and trying
to go about it that way, but it is not working, always getting an error in
syntax or such. I think I just need step-by-step instruction on how to make
this combo box correctly. I have tried looking at other similar posts and
replicating those based on my needs, but it just isn't working.

Any help would be greatly appreciated. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top