HLP! Form Design

[dee]

Hi,

I'm trying to design a form that will allow me to select student names when
they have won an award. Here are the details:

As many students may win many awards (many to many) I have three tables:

Table 1: StudentInfo
StudentID (PK)
Student First
Student Last
DOB
Gender
School
Grade

Table 2: Awards
AwardID (PK)
Award Name
Organization
Description

Table 3: StudentAwards
StudentID (FK)
AwardID (FK)
StudentAwardsID (PK) (I am using this as opposed to ajoint StudentID and
AwardID PK because one student may get the same award many times, just on
different dates)
Date
Comments

I created a query called Awards that I base a form on that is used to input
new awards. No problem.

I created another query that pulls the award information from the QryAwards
(to sort), plus I pull the StudentLast and StudentFirst fields from
QryStudentInfo (to sort), plus I pull AwardDate and Comments from the
TblStudent Awards table.

I then created a form based on the query described above. So far, so good.
What I want to achieve, though is a combo-box that will allow me to select
the student name to avoid typing and typos, then I will input the date and
comments manually.

I tried a combo box, but can't figure out how to concatenate the last and
first name fields and populate the StudentAwards table properly. I guess
what I really want is to view the concatenated names, but in reality populate
the StudentID FK in the TblStudent Awards table.

Help! Thanks!

 

[Klatuu]

Dee,

I assume you are using a query as the record source for the combo. To get
what you want, you can make the StudentId your bound field in the combo box.
In your query you can concatenate the first and last names this way:

In the Field Row of your query builder, give the column a name and use both
Names like this:
By First Name
StdName: [StudentInfo]![Student First] & " " & [Student Last]
By Last Name
StdName: [StudentInfo]![Student Last] & ", " & [Student First]

Then in your combo box properties, if you only want to show the name and not
the ID, then make the column width for your bound field 0.

One other hint. Don't use spaces in names. Database engines do not
understand spaces as a legitimate character in naming. You work around that
with brackets [], but the best way is to use either under scores _ or
capitalization to make you meaning clear.
Student_First or StudentFirst are both better than Student First for example

 

[dee]

Hi and thanks so much for your assistance.

I'm afraid I need a bit more detail - I'm quite new to this (and blonde!)

I am basing my form on the query I mentioned in my first post.

I'm not clear on what query to base the combo box on in my form when I am
going through the wizard. It would appear that I'm not doing it correctly,
because it doesn't seem to find the StudentID. Should I create a separate
query with JUST this information? (StudentID, StdName (concatenated fields
as you described)

Oh, I also made typos in my first post. None of my fields have spaces in
their names. Thanks for the advice, though. I really appreciate it.

Thanks!

Dee,

I assume you are using a query as the record source for the combo. To get
what you want, you can make the StudentId your bound field in the combo box.
In your query you can concatenate the first and last names this way:

In the Field Row of your query builder, give the column a name and use both
Names like this:
By First Name
StdName: [StudentInfo]![Student First] & " " & [Student Last]
By Last Name
StdName: [StudentInfo]![Student Last] & ", " & [Student First]

Then in your combo box properties, if you only want to show the name and not
the ID, then make the column width for your bound field 0.

One other hint. Don't use spaces in names. Database engines do not
understand spaces as a legitimate character in naming. You work around that
with brackets [], but the best way is to use either under scores _ or
capitalization to make you meaning clear.
Student_First or StudentFirst are both better than Student First for example

Hi,

I'm trying to design a form that will allow me to select student names when
they have won an award. Here are the details:

As many students may win many awards (many to many) I have three tables:

Table 1: StudentInfo
StudentID (PK)
Student First
Student Last
DOB
Gender
School
Grade

Table 2: Awards
AwardID (PK)
Award Name
Organization
Description

Table 3: StudentAwards
StudentID (FK)
AwardID (FK)
StudentAwardsID (PK) (I am using this as opposed to ajoint StudentID and
AwardID PK because one student may get the same award many times, just on
different dates)
Date
Comments

I created a query called Awards that I base a form on that is used to input
new awards. No problem.

I created another query that pulls the award information from the QryAwards
(to sort), plus I pull the StudentLast and StudentFirst fields from
QryStudentInfo (to sort), plus I pull AwardDate and Comments from the
TblStudent Awards table.

I then created a form based on the query described above. So far, so good.
What I want to achieve, though is a combo-box that will allow me to select
the student name to avoid typing and typos, then I will input the date and
comments manually.

I tried a combo box, but can't figure out how to concatenate the last and
first name fields and populate the StudentAwards table properly. I guess
what I really want is to view the concatenated names, but in reality populate
the StudentID FK in the TblStudent Awards table.

Help! Thanks!

 

[Klatuu]

Dee,

Since you are blond, I am typing my response very slowly so you can
understand
(sorry, couldn't resist)

Try creating the query first. Test it to see you are getting the desired
results, then create your combo and the wizard should be able to find the
query.

Hi and thanks so much for your assistance.

I'm afraid I need a bit more detail - I'm quite new to this (and blonde!)

I am basing my form on the query I mentioned in my first post.

I'm not clear on what query to base the combo box on in my form when I am
going through the wizard. It would appear that I'm not doing it correctly,
because it doesn't seem to find the StudentID. Should I create a separate
query with JUST this information? (StudentID, StdName (concatenated fields
as you described)

Oh, I also made typos in my first post. None of my fields have spaces in
their names. Thanks for the advice, though. I really appreciate it.

Thanks!

Dee,

I assume you are using a query as the record source for the combo. To get
what you want, you can make the StudentId your bound field in the combo box.
In your query you can concatenate the first and last names this way:

In the Field Row of your query builder, give the column a name and use both
Names like this:
By First Name
StdName: [StudentInfo]![Student First] & " " & [Student Last]
By Last Name
StdName: [StudentInfo]![Student Last] & ", " & [Student First]

Then in your combo box properties, if you only want to show the name and not
the ID, then make the column width for your bound field 0.

One other hint. Don't use spaces in names. Database engines do not
understand spaces as a legitimate character in naming. You work around that
with brackets [], but the best way is to use either under scores _ or
capitalization to make you meaning clear.
Student_First or StudentFirst are both better than Student First for example

Hi,

I'm trying to design a form that will allow me to select student names when
they have won an award. Here are the details:

As many students may win many awards (many to many) I have three tables:

Table 1: StudentInfo
StudentID (PK)
Student First
Student Last
DOB
Gender
School
Grade

Table 2: Awards
AwardID (PK)
Award Name
Organization
Description

Table 3: StudentAwards
StudentID (FK)
AwardID (FK)
StudentAwardsID (PK) (I am using this as opposed to ajoint StudentID and
AwardID PK because one student may get the same award many times, just on
different dates)
Date
Comments

I created a query called Awards that I base a form on that is used to input
new awards. No problem.

I created another query that pulls the award information from the QryAwards
(to sort), plus I pull the StudentLast and StudentFirst fields from
QryStudentInfo (to sort), plus I pull AwardDate and Comments from the
TblStudent Awards table.

I then created a form based on the query described above. So far, so good.
What I want to achieve, though is a combo-box that will allow me to select
the student name to avoid typing and typos, then I will input the date and
comments manually.

I tried a combo box, but can't figure out how to concatenate the last and
first name fields and populate the StudentAwards table properly. I guess
what I really want is to view the concatenated names, but in reality populate
the StudentID FK in the TblStudent Awards table.

Help! Thanks!

 

[dee]

Thanks so much for typing your response s-l-o-w-l-y... it really helped!

Honestly, I tried what you suggested and it worked like a charm. I was
under the mistaken impression that the entire sub-form had to be based on a
single query, instead of having the combo box refer to a separate query.

I appreciate your help! You saved me hours of trial and error (which I had
already spent!).
--
Thanks!

Dee

Dee,

Since you are blond, I am typing my response very slowly so you can
understand
(sorry, couldn't resist)

Try creating the query first. Test it to see you are getting the desired
results, then create your combo and the wizard should be able to find the
query.

Hi and thanks so much for your assistance.

I'm afraid I need a bit more detail - I'm quite new to this (and blonde!)

I am basing my form on the query I mentioned in my first post.

I'm not clear on what query to base the combo box on in my form when I am
going through the wizard. It would appear that I'm not doing it correctly,
because it doesn't seem to find the StudentID. Should I create a separate
query with JUST this information? (StudentID, StdName (concatenated fields
as you described)

Oh, I also made typos in my first post. None of my fields have spaces in
their names. Thanks for the advice, though. I really appreciate it.

Thanks!

Dee,

I assume you are using a query as the record source for the combo. To get
what you want, you can make the StudentId your bound field in the combo box.
In your query you can concatenate the first and last names this way:

In the Field Row of your query builder, give the column a name and use both
Names like this:
By First Name
StdName: [StudentInfo]![Student First] & " " & [Student Last]
By Last Name
StdName: [StudentInfo]![Student Last] & ", " & [Student First]

Then in your combo box properties, if you only want to show the name and not
the ID, then make the column width for your bound field 0.

One other hint. Don't use spaces in names. Database engines do not
understand spaces as a legitimate character in naming. You work around that
with brackets [], but the best way is to use either under scores _ or
capitalization to make you meaning clear.
Student_First or StudentFirst are both better than Student First for example

:
Hi,

I'm trying to design a form that will allow me to select student names when
they have won an award. Here are the details:

As many students may win many awards (many to many) I have three tables:

Table 1: StudentInfo
StudentID (PK)
Student First
Student Last
DOB
Gender
School
Grade

Table 2: Awards
AwardID (PK)
Award Name
Organization
Description

Table 3: StudentAwards
StudentID (FK)
AwardID (FK)
StudentAwardsID (PK) (I am using this as opposed to ajoint StudentID and
AwardID PK because one student may get the same award many times, just on
different dates)
Date
Comments

I created a query called Awards that I base a form on that is used to input
new awards. No problem.

I created another query that pulls the award information from the QryAwards
(to sort), plus I pull the StudentLast and StudentFirst fields from
QryStudentInfo (to sort), plus I pull AwardDate and Comments from the
TblStudent Awards table.

I then created a form based on the query described above. So far, so good.
What I want to achieve, though is a combo-box that will allow me to select
the student name to avoid typing and typos, then I will input the date and
comments manually.

I tried a combo box, but can't figure out how to concatenate the last and
first name fields and populate the StudentAwards table properly. I guess
what I really want is to view the concatenated names, but in reality populate
the StudentID FK in the TblStudent Awards table.

Help! Thanks!

 

[Klatuu]

Thanks so much for your kind words. I am happy you got your problem solved.

Thanks so much for typing your response s-l-o-w-l-y... it really helped!

Honestly, I tried what you suggested and it worked like a charm. I was
under the mistaken impression that the entire sub-form had to be based on a
single query, instead of having the combo box refer to a separate query.

I appreciate your help! You saved me hours of trial and error (which I had
already spent!).
--
Thanks!

Dee

Dee,

Since you are blond, I am typing my response very slowly so you can
understand
(sorry, couldn't resist)

Try creating the query first. Test it to see you are getting the desired
results, then create your combo and the wizard should be able to find the
query.

Hi and thanks so much for your assistance.

I'm afraid I need a bit more detail - I'm quite new to this (and blonde!)

I am basing my form on the query I mentioned in my first post.

I'm not clear on what query to base the combo box on in my form when I am
going through the wizard. It would appear that I'm not doing it correctly,
because it doesn't seem to find the StudentID. Should I create a separate
query with JUST this information? (StudentID, StdName (concatenated fields
as you described)

Oh, I also made typos in my first post. None of my fields have spaces in
their names. Thanks for the advice, though. I really appreciate it.

Thanks!


:

Dee,

I assume you are using a query as the record source for the combo. To get
what you want, you can make the StudentId your bound field in the combo box.
In your query you can concatenate the first and last names this way:

In the Field Row of your query builder, give the column a name and use both
Names like this:
By First Name
StdName: [StudentInfo]![Student First] & " " & [Student Last]
By Last Name
StdName: [StudentInfo]![Student Last] & ", " & [Student First]

Then in your combo box properties, if you only want to show the name and not
the ID, then make the column width for your bound field 0.

One other hint. Don't use spaces in names. Database engines do not
understand spaces as a legitimate character in naming. You work around that
with brackets [], but the best way is to use either under scores _ or
capitalization to make you meaning clear.
Student_First or StudentFirst are both better than Student First for example

:
Hi,

I'm trying to design a form that will allow me to select student names when
they have won an award. Here are the details:

As many students may win many awards (many to many) I have three tables:

Table 1: StudentInfo
StudentID (PK)
Student First
Student Last
DOB
Gender
School
Grade

Table 2: Awards
AwardID (PK)
Award Name
Organization
Description

Table 3: StudentAwards
StudentID (FK)
AwardID (FK)
StudentAwardsID (PK) (I am using this as opposed to ajoint StudentID and
AwardID PK because one student may get the same award many times, just on
different dates)
Date
Comments

I created a query called Awards that I base a form on that is used to input
new awards. No problem.

I created another query that pulls the award information from the QryAwards
(to sort), plus I pull the StudentLast and StudentFirst fields from
QryStudentInfo (to sort), plus I pull AwardDate and Comments from the
TblStudent Awards table.

I then created a form based on the query described above. So far, so good.
What I want to achieve, though is a combo-box that will allow me to select
the student name to avoid typing and typos, then I will input the date and
comments manually.

I tried a combo box, but can't figure out how to concatenate the last and
first name fields and populate the StudentAwards table properly. I guess
what I really want is to view the concatenated names, but in reality populate
the StudentID FK in the TblStudent Awards table.

Help! Thanks!