BruceM said:If I understand you correctly, you want to select a student name on the
switchboard, then open the Student form (frmStudent) to that student's
record. I think the switchboard wizard option you used will go to a
record on the current form only. That is, if you placed such a combo box
into the header of frmStudent you could use the combo box to take you to
that student's record.
In your case you need to go to that student's record on a form that isn't
open yet. I take it that StudentID is a number field containing the
student ID number or some other unique number such as autonumber. For the
Row Source of the combo box on the switchboard, you can create a query
(qryStudent) based on the student table. The query will have StudentID in
the first column, and something like this in the second column:
FullName: [LastName] & ", " & [FirstName] & " " & [MI]
Sort by this field (in query design view, select Ascending in the Sort
row). This esample assumes you have the bracketed fields in the Student
table. Adjust to suit your needs (for instance, you may need ClassYear or
some other field to guard against duplicate names) and to reflect your
field names.
I will call the combo box cboSelStudent.
The combo box is unbound. Open its property sheet by clicking the combo
box to select it, then clicking View > Properties. Click the Data tab,
and select qryStudent as the Row Source. Set the Bound Column to 1 (which
is StudentID). Now click the Format tab. Set the column count to 2, and
the column widths to something like 0";1.5".
Still at the Property sheet, click the Event tab. Click After Update,
click the three dots to the right, select Code Builder, and click OK. The
VBA editor should open, with the cursor blinking between:
Private Sub cboSelStudent_AfterUpdate() and End Sub. Add something like
the following to open the frmStudent to show just the selected student's
record:
DoCmd.OpenForm "frmStudent", , , "StudentID = " & Me.cboSelStudent
This part of the code is the Where condition: "StudentID = " &
Me.cboSelStudent
What you are telling Access to do is to open frmStudent to the record
where StudentID matches the combo box selection.
To open the form to the selected student, but with all records available,
use OpenArgs. In the AfterUpdate event for cboSelStudent:
Dim strFilter As String
strFilter = "StudentID = " & Me.cboSelStudent
DoCmd.OpenForm "frmStudent", , , , , , strFilter
Note that you need to use all of the commas. You should be prompted for
OpenArgs as you type (it will be highlighted in the box that appears as
you type the code). You could use some shorthand:
DoCmd.OpenForm "frmStudent", OpenArgs:=strFilter
There's quite a bit of stuff here for a beginner, so read through it a few
times if need be. Note carefully the names I have used. Use names of
your choosing, of course; substitute them for the names I used in the
instructions.
Monique said:OK, I'm trying to get my head around this, I'm pretty new to MS Access,
it's
my first DB I've created so forgive me if I sound vague.
What I'm thinking you want me to do is go to my switchboard and insert a
combo box, when I did this I did it like this:
Clicked on the combo box icon on the switchboard in design mode, then
choose
from the wizard "I want the combo box to look up the values in a query or
table", then I clicked for it to look in my main table called Students,
not
to look for a query. Then I choose StudentID field from the list and
clicked
through to finish.
When I go to my switchboard I see it's added a combo box that shows all
the
student id's.
Now I'm not sure what to do from here, I went into design view to create
a
query, I added all the fields from the main table but I don't really know
what to do from here on, don't know how I'm going to eventually get it to
take me to my form for that student and edit that form.
Monique
scubadiver said:In the properties of the combo box, go to "other". The first in the list is
the name. Choose something appropriate (like "studentID"). If the switchboard
form is called "switchboard" type the following in the student ID criteria
row:
[forms]![switchboard]![StudentID]
In the "on click" event of the button on the switchboad type
docmd.openform "form name"
replacing "form name" with whatever your form is (or you can type this code
in the "after update" event of the combo).
let me know
Monique said:OK, I'm trying to get my head around this, I'm pretty new to MS Access, it's
my first DB I've created so forgive me if I sound vague.
What I'm thinking you want me to do is go to my switchboard and insert a
combo box, when I did this I did it like this:
Clicked on the combo box icon on the switchboard in design mode, then choose
from the wizard "I want the combo box to look up the values in a query or
table", then I clicked for it to look in my main table called Students, not
to look for a query. Then I choose StudentID field from the list and clicked
through to finish.
When I go to my switchboard I see it's added a combo box that shows all the
student id's.
Now I'm not sure what to do from here, I went into design view to create a
query, I added all the fields from the main table but I don't really know
what to do from here on, don't know how I'm going to eventually get it to
take me to my form for that student and edit that form.
Monique
BruceM said:I should have mentioned that the syntax is different if StudentID is a text
field:
"StudentID = " " " & Me.cboSelStudent & " " " "
The spaces between the quotes are for clarity. You may be able to use:
"StudentID = ' " & Me.cboSelStudent & " ' "
But I think that can cause problems if the field contains an apostrophe.
BruceM said:If I understand you correctly, you want to select a student name on the
switchboard, then open the Student form (frmStudent) to that student's
record. I think the switchboard wizard option you used will go to a
record on the current form only. That is, if you placed such a combo box
into the header of frmStudent you could use the combo box to take you to
that student's record.
In your case you need to go to that student's record on a form that isn't
open yet. I take it that StudentID is a number field containing the
student ID number or some other unique number such as autonumber. For the
Row Source of the combo box on the switchboard, you can create a query
(qryStudent) based on the student table. The query will have StudentID in
the first column, and something like this in the second column:
FullName: [LastName] & ", " & [FirstName] & " " & [MI]
Sort by this field (in query design view, select Ascending in the Sort
row). This esample assumes you have the bracketed fields in the Student
table. Adjust to suit your needs (for instance, you may need ClassYear or
some other field to guard against duplicate names) and to reflect your
field names.
I will call the combo box cboSelStudent.
The combo box is unbound. Open its property sheet by clicking the combo
box to select it, then clicking View > Properties. Click the Data tab,
and select qryStudent as the Row Source. Set the Bound Column to 1 (which
is StudentID). Now click the Format tab. Set the column count to 2, and
the column widths to something like 0";1.5".
Still at the Property sheet, click the Event tab. Click After Update,
click the three dots to the right, select Code Builder, and click OK. The
VBA editor should open, with the cursor blinking between:
Private Sub cboSelStudent_AfterUpdate() and End Sub. Add something like
the following to open the frmStudent to show just the selected student's
record:
DoCmd.OpenForm "frmStudent", , , "StudentID = " & Me.cboSelStudent
This part of the code is the Where condition: "StudentID = " &
Me.cboSelStudent
What you are telling Access to do is to open frmStudent to the record
where StudentID matches the combo box selection.
To open the form to the selected student, but with all records available,
use OpenArgs. In the AfterUpdate event for cboSelStudent:
Dim strFilter As String
strFilter = "StudentID = " & Me.cboSelStudent
DoCmd.OpenForm "frmStudent", , , , , , strFilter
Note that you need to use all of the commas. You should be prompted for
OpenArgs as you type (it will be highlighted in the box that appears as
you type the code). You could use some shorthand:
DoCmd.OpenForm "frmStudent", OpenArgs:=strFilter
There's quite a bit of stuff here for a beginner, so read through it a few
times if need be. Note carefully the names I have used. Use names of
your choosing, of course; substitute them for the names I used in the
instructions.
Monique said:OK, I'm trying to get my head around this, I'm pretty new to MS Access,
it's
my first DB I've created so forgive me if I sound vague.
What I'm thinking you want me to do is go to my switchboard and insert a
combo box, when I did this I did it like this:
Clicked on the combo box icon on the switchboard in design mode, then
choose
from the wizard "I want the combo box to look up the values in a query or
table", then I clicked for it to look in my main table called Students,
not
to look for a query. Then I choose StudentID field from the list and
clicked
through to finish.
When I go to my switchboard I see it's added a combo box that shows all
the
student id's.
Now I'm not sure what to do from here, I went into design view to create
a
query, I added all the fields from the main table but I don't really know
what to do from here on, don't know how I'm going to eventually get it to
take me to my form for that student and edit that form.
Monique
:
What I would do is base the form on a select query and attach a criteria
to
the student name in the query so the criteria references a combo box in
the
switchboard that lists all the students. When you click on the button in
the
switchboard it opens the form for the required student.
--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video
:
I have a form setup and what I would like to do is from my switchboard
that
I've created look for a student in the database and open that in form
view so
that we can edit the record if needed.
Monique
Monique said:Thanks Bruce, it's nice to talk to someone that explains it exactly how it
is
to be done, I would have had no clue how to do this otherwise.
OK, I've gone through and done exactly what you said, my combo box is
called
StudentID and so is the field that I need to search on. The field is a
text
field as it's the just the students initials that I'm looking for, we
don't
have their names in the database just their initials.
I've created a query but I was a bit unsure of this, the query I've put
every field in my table in the query and put the sort ascending on the
StudentID record only? This query I called qryStudent
So I've done what you said, went to the switchboard, highlighted the combo
box called StudentID, gone through and added the qryStudent to the row
source. I've set the bound column to 1, changed the count to 2, and the
column width as instructed.
Those are two separate instructions for two different circumstances. INow I've gone to the event tab and clicked on the after update, clicked
code
builder and it took me to VB like you said it would, I put the following
in
and am not sure if this is right:
Private Sub StudentID_AfterUpdate()
DoCmd.OpenForm "frmStudents", , , "StudentID = " & Me.StudentID
Dim strFilter As String
strFilter = "StudentID = ' " & Me.StudentID & " ' "
DoCmd.OpenForm "fromStudents", , , , , , strFilter
End Sub
I wasn't sure whether to put the filter in just directly after the other
bit. This all comes back with no errors but when I go to the switchboard
section and click on the combo box there is no student id's in the list,
no
error message at all but nothing for me to choose from?
I'm leaving this job today and really need to finish this off if possible.
Monique
BruceM said:I should have mentioned that the syntax is different if StudentID is a
text
field:
"StudentID = " " " & Me.cboSelStudent & " " " "
The spaces between the quotes are for clarity. You may be able to use:
"StudentID = ' " & Me.cboSelStudent & " ' "
But I think that can cause problems if the field contains an apostrophe.
BruceM said:If I understand you correctly, you want to select a student name on the
switchboard, then open the Student form (frmStudent) to that student's
record. I think the switchboard wizard option you used will go to a
record on the current form only. That is, if you placed such a combo
box
into the header of frmStudent you could use the combo box to take you
to
that student's record.
In your case you need to go to that student's record on a form that
isn't
open yet. I take it that StudentID is a number field containing the
student ID number or some other unique number such as autonumber. For
the
Row Source of the combo box on the switchboard, you can create a query
(qryStudent) based on the student table. The query will have StudentID
in
the first column, and something like this in the second column:
FullName: [LastName] & ", " & [FirstName] & " " & [MI]
Sort by this field (in query design view, select Ascending in the Sort
row). This esample assumes you have the bracketed fields in the Student
table. Adjust to suit your needs (for instance, you may need ClassYear
or
some other field to guard against duplicate names) and to reflect your
field names.
I will call the combo box cboSelStudent.
The combo box is unbound. Open its property sheet by clicking the
combo
box to select it, then clicking View > Properties. Click the Data tab,
and select qryStudent as the Row Source. Set the Bound Column to 1
(which
is StudentID). Now click the Format tab. Set the column count to 2,
and
the column widths to something like 0";1.5".
Still at the Property sheet, click the Event tab. Click After Update,
click the three dots to the right, select Code Builder, and click OK.
The
VBA editor should open, with the cursor blinking between:
Private Sub cboSelStudent_AfterUpdate() and End Sub. Add something
like
the following to open the frmStudent to show just the selected
student's
record:
DoCmd.OpenForm "frmStudent", , , "StudentID = " & Me.cboSelStudent
This part of the code is the Where condition: "StudentID = " &
Me.cboSelStudent
What you are telling Access to do is to open frmStudent to the record
where StudentID matches the combo box selection.
To open the form to the selected student, but with all records
available,
use OpenArgs. In the AfterUpdate event for cboSelStudent:
Dim strFilter As String
strFilter = "StudentID = " & Me.cboSelStudent
DoCmd.OpenForm "frmStudent", , , , , , strFilter
Note that you need to use all of the commas. You should be prompted
for
OpenArgs as you type (it will be highlighted in the box that appears as
you type the code). You could use some shorthand:
DoCmd.OpenForm "frmStudent", OpenArgs:=strFilter
There's quite a bit of stuff here for a beginner, so read through it a
few
times if need be. Note carefully the names I have used. Use names of
your choosing, of course; substitute them for the names I used in the
instructions.
OK, I'm trying to get my head around this, I'm pretty new to MS
Access,
it's
my first DB I've created so forgive me if I sound vague.
What I'm thinking you want me to do is go to my switchboard and insert
a
combo box, when I did this I did it like this:
Clicked on the combo box icon on the switchboard in design mode, then
choose
from the wizard "I want the combo box to look up the values in a query
or
table", then I clicked for it to look in my main table called
Students,
not
to look for a query. Then I choose StudentID field from the list and
clicked
through to finish.
When I go to my switchboard I see it's added a combo box that shows
all
the
student id's.
Now I'm not sure what to do from here, I went into design view to
create
a
query, I added all the fields from the main table but I don't really
know
what to do from here on, don't know how I'm going to eventually get it
to
take me to my form for that student and edit that form.
Monique
:
What I would do is base the form on a select query and attach a
criteria
to
the student name in the query so the criteria references a combo box
in
the
switchboard that lists all the students. When you click on the button
in
the
switchboard it opens the form for the required student.
--
"Loose Change 2nd Edition" has been seen by almost 7 million people
on
Google video
:
I have a form setup and what I would like to do is from my
switchboard
that
I've created look for a student in the database and open that in
form
view so
that we can edit the record if needed.
Monique
BruceM said:Replies inline.
Monique said:Thanks Bruce, it's nice to talk to someone that explains it exactly how it
is
to be done, I would have had no clue how to do this otherwise.
OK, I've gone through and done exactly what you said, my combo box is
called
StudentID and so is the field that I need to search on. The field is a
text
field as it's the just the students initials that I'm looking for, we
don't
have their names in the database just their initials.
I can't even imagine how that would work out using initials only, since
there is a very good chance of two people with the same initials. Anyhow,
this is the combo box on the switchboard? Remember, it is unbound (i.e. it
does not have a field as its row source).I've created a query but I was a bit unsure of this, the query I've put
every field in my table in the query and put the sort ascending on the
StudentID record only? This query I called qryStudent
The extra fields are not needed, but other than maybe a little bit of
performance drag (probably not noticeable) they do no harm. However, all
you need in the query is what you want to see in the list, and maybe a
hidden field to identify the record. Typically StudentID would be a number
field, different from the StudentID for any other student. If you do not
have a unique identification for each student record you will almost surely
have problems at some point. A typical query would contain something like
the following:
StudentID FullName
01 Jones, Johnny
02 Martin, Mary
03 Jefferson, Tom
FullName is a concatenation (combining) of fields and punctuation, as I
outlined in my previous post. Concatenation is not required, but it can be
helpful.
So I've done what you said, went to the switchboard, highlighted the combo
box called StudentID, gone through and added the qryStudent to the row
source. I've set the bound column to 1, changed the count to 2, and the
column width as instructed.
That would work if your query was as I have sketched. The user would see
Jones, Johnny (the second column), but when you refer to the combo box in
code Access will look at the bound column (01, in the case of Johnny). The
first column is hidden from the user (width is 0"). The second column has a
width, so the user sees it. What is in the first and second columns of your
qryStudent?
Those are two separate instructions for two different circumstances. INow I've gone to the event tab and clicked on the after update, clicked
code
builder and it took me to VB like you said it would, I put the following
in
and am not sure if this is right:
Private Sub StudentID_AfterUpdate()
DoCmd.OpenForm "frmStudents", , , "StudentID = " & Me.StudentID
Dim strFilter As String
strFilter = "StudentID = ' " & Me.StudentID & " ' "
DoCmd.OpenForm "fromStudents", , , , , , strFilter
End Sub
assume that "fromStudents" is a typo in the second DoCmd expression, since
it would have generated an error in your code. I am afraid I complicated
the matter by using strFilter in one case and not in the other. Dim
strFilter as String means that you are identifying the word strFilter as a
text string. In the next line you are giving strFilter a value. It is a
convenience that does not affect the code, but can make it easier to read
and write.
Dim strFilter as String
strFilter = "StudentID = ' " & Me.StudentID & " ' "
DoCmd.OpenForm "fromStudents", , , , , , strFilter
is the same as:
DoCmd.OpenForm "frmStudents", , , , , , "StudentID = ' " & Me.StudentID & "
' "
In the first case strFilter is given a value and used in the expression. In
the second case the value itself is used in the expression.
An important point here is that, as mentioned, these are two separate
instructions. This one opens the form to a specific record. No other
records are available. Watch out for word wrapping. This should all be on
one line.
DoCmd.OpenForm "frmStudents", , , "StudentID = ' " & Me.StudentID & " ' "
Let's say you have selected Jones, Johnny in the combo box. The bound
column (the hidden one) contains the value 01. This expression is saying to
open frmStudents with just the record that matches the bound column. If
frmStudents is based on tblStudents (or a query based on tblStudents), it
has a StudentID field available. The expression is saying to open
frmStudents to the record identified as 01 (Johnny's record).
The following opens the form with all records available, but a particular
record displayed:
DoCmd.OpenForm "frmStudents", , , , , , "StudentID = ' " & Me.StudentID & "
' "
Use one or the other, depending on your needs, but not both.
I wasn't sure whether to put the filter in just directly after the other
bit. This all comes back with no errors but when I go to the switchboard
section and click on the combo box there is no student id's in the list,
no
error message at all but nothing for me to choose from?
The first thing is to be sure the combo box is showing what it needs to
show. After that, apply the code. Again, what are the first two columns of
qryStudents? Provide some sample data (disguising any actual names).I'm leaving this job today and really need to finish this off if possible.
Monique
BruceM said:I should have mentioned that the syntax is different if StudentID is a
text
field:
"StudentID = " " " & Me.cboSelStudent & " " " "
The spaces between the quotes are for clarity. You may be able to use:
"StudentID = ' " & Me.cboSelStudent & " ' "
But I think that can cause problems if the field contains an apostrophe.
If I understand you correctly, you want to select a student name on the
switchboard, then open the Student form (frmStudent) to that student's
record. I think the switchboard wizard option you used will go to a
record on the current form only. That is, if you placed such a combo
box
into the header of frmStudent you could use the combo box to take you
to
that student's record.
In your case you need to go to that student's record on a form that
isn't
open yet. I take it that StudentID is a number field containing the
student ID number or some other unique number such as autonumber. For
the
Row Source of the combo box on the switchboard, you can create a query
(qryStudent) based on the student table. The query will have StudentID
in
the first column, and something like this in the second column:
FullName: [LastName] & ", " & [FirstName] & " " & [MI]
Sort by this field (in query design view, select Ascending in the Sort
row). This esample assumes you have the bracketed fields in the Student
table. Adjust to suit your needs (for instance, you may need ClassYear
or
some other field to guard against duplicate names) and to reflect your
field names.
I will call the combo box cboSelStudent.
The combo box is unbound. Open its property sheet by clicking the
combo
box to select it, then clicking View > Properties. Click the Data tab,
and select qryStudent as the Row Source. Set the Bound Column to 1
(which
is StudentID). Now click the Format tab. Set the column count to 2,
and
the column widths to something like 0";1.5".
Still at the Property sheet, click the Event tab. Click After Update,
click the three dots to the right, select Code Builder, and click OK.
The
VBA editor should open, with the cursor blinking between:
Private Sub cboSelStudent_AfterUpdate() and End Sub. Add something
like
the following to open the frmStudent to show just the selected
student's
record:
DoCmd.OpenForm "frmStudent", , , "StudentID = " & Me.cboSelStudent
This part of the code is the Where condition: "StudentID = " &
Me.cboSelStudent
What you are telling Access to do is to open frmStudent to the record
where StudentID matches the combo box selection.
To open the form to the selected student, but with all records
available,
use OpenArgs. In the AfterUpdate event for cboSelStudent:
Dim strFilter As String
strFilter = "StudentID = " & Me.cboSelStudent
DoCmd.OpenForm "frmStudent", , , , , , strFilter
Note that you need to use all of the commas. You should be prompted
for
OpenArgs as you type (it will be highlighted in the box that appears as
you type the code). You could use some shorthand:
DoCmd.OpenForm "frmStudent", OpenArgs:=strFilter
There's quite a bit of stuff here for a beginner, so read through it a
few
times if need be. Note carefully the names I have used. Use names of
your choosing, of course; substitute them for the names I used in the
instructions.
OK, I'm trying to get my head around this, I'm pretty new to MS
Access,
it's
my first DB I've created so forgive me if I sound vague.
What I'm thinking you want me to do is go to my switchboard and insert
a
combo box, when I did this I did it like this:
Clicked on the combo box icon on the switchboard in design mode, then
choose
from the wizard "I want the combo box to look up the values in a query
or
table", then I clicked for it to look in my main table called
Students,
not
to look for a query. Then I choose StudentID field from the list and
clicked
through to finish.
When I go to my switchboard I see it's added a combo box that shows
all
the
student id's.
Now I'm not sure what to do from here, I went into design view to
create
a
query, I added all the fields from the main table but I don't really
know
what to do from here on, don't know how I'm going to eventually get it
to
take me to my form for that student and edit that form.
Monique
:
What I would do is base the form on a select query and attach a
criteria
to
the student name in the query so the criteria references a combo box
in
the
switchboard that lists all the students. When you click on the button
in
the
switchboard it opens the form for the required student.
--
"Loose Change 2nd Edition" has been seen by almost 7 million people
on
Google video
:
I have a form setup and what I would like to do is from my
switchboard
that
I've created look for a student in the database and open that in
form
view so
that we can edit the record if needed.
Monique
Monique said:I just typed out this massive message and it went so I have to do it again.
I'm only here on Mondays.
OK, I'll have to be brief for this as I have to go soon.
This is how my table is structured:
* One table only called Students
* I have a field called UniqueID, this is an autonumber generated to each
students information and autofilled
* Then I have the field StudentID, this is where I input initials only, we
cannot put whole names in for the students, it's a statistical database
mostly. I use different initials so they are not duplicated for example if
it's Johnny Jones they would be JJ and then if there is a Janet James she
would be JJA, I know it's a bit dodgy but it's the only way I could figure to
do it and it seems to work OK.
I have setup the query which is called qryStudents1, it has the StudentID in
the first column and the UniqueID in the second, I sort by ascending order on
StudentID.
I've then setup an unbound combo box on that query, it shows me just a list
of the student id's which is what I want but I cannot click on them and pick
them, it doesn't do anything so I'm assuming this is my after update event.
When I go to that and go to code builder it takes me to this section, this is
what I've entered:
Private Sub cboSelStudent_AfterUpdate()
DoCmd.OpenForm "fromStudents", , , , , , "StudentID = ' " & Me.StudentID & "
' "
End Sub
I'm not sure if that is right, I'm assuming from your last message that I
can use one or the other of those options that you gave me. I figure this is
telling me to open the students form depending on what initials I choose from
the combo box? BTW the name of the form that I want to get to is also called
students.
I'm only here on Mondays so I will reply next week.
Kind regards
Monique
BruceM said:Replies inline.
Monique said:Thanks Bruce, it's nice to talk to someone that explains it exactly how it
is
to be done, I would have had no clue how to do this otherwise.
OK, I've gone through and done exactly what you said, my combo box is
called
StudentID and so is the field that I need to search on. The field is a
text
field as it's the just the students initials that I'm looking for, we
don't
have their names in the database just their initials.
I can't even imagine how that would work out using initials only, since
there is a very good chance of two people with the same initials. Anyhow,
this is the combo box on the switchboard? Remember, it is unbound (i.e. it
does not have a field as its row source).I've created a query but I was a bit unsure of this, the query I've put
every field in my table in the query and put the sort ascending on the
StudentID record only? This query I called qryStudent
The extra fields are not needed, but other than maybe a little bit of
performance drag (probably not noticeable) they do no harm. However, all
you need in the query is what you want to see in the list, and maybe a
hidden field to identify the record. Typically StudentID would be a number
field, different from the StudentID for any other student. If you do not
have a unique identification for each student record you will almost surely
have problems at some point. A typical query would contain something like
the following:
StudentID FullName
01 Jones, Johnny
02 Martin, Mary
03 Jefferson, Tom
FullName is a concatenation (combining) of fields and punctuation, as I
outlined in my previous post. Concatenation is not required, but it can be
helpful.
So I've done what you said, went to the switchboard, highlighted the combo
box called StudentID, gone through and added the qryStudent to the row
source. I've set the bound column to 1, changed the count to 2, and the
column width as instructed.
That would work if your query was as I have sketched. The user would see
Jones, Johnny (the second column), but when you refer to the combo box in
code Access will look at the bound column (01, in the case of Johnny). The
first column is hidden from the user (width is 0"). The second column has a
width, so the user sees it. What is in the first and second columns of your
qryStudent?
Those are two separate instructions for two different circumstances. INow I've gone to the event tab and clicked on the after update, clicked
code
builder and it took me to VB like you said it would, I put the following
in
and am not sure if this is right:
Private Sub StudentID_AfterUpdate()
DoCmd.OpenForm "frmStudents", , , "StudentID = " & Me.StudentID
Dim strFilter As String
strFilter = "StudentID = ' " & Me.StudentID & " ' "
DoCmd.OpenForm "fromStudents", , , , , , strFilter
End Sub
assume that "fromStudents" is a typo in the second DoCmd expression, since
it would have generated an error in your code. I am afraid I complicated
the matter by using strFilter in one case and not in the other. Dim
strFilter as String means that you are identifying the word strFilter as a
text string. In the next line you are giving strFilter a value. It is a
convenience that does not affect the code, but can make it easier to read
and write.
Dim strFilter as String
strFilter = "StudentID = ' " & Me.StudentID & " ' "
DoCmd.OpenForm "fromStudents", , , , , , strFilter
is the same as:
DoCmd.OpenForm "frmStudents", , , , , , "StudentID = ' " & Me.StudentID & "
' "
In the first case strFilter is given a value and used in the expression. In
the second case the value itself is used in the expression.
An important point here is that, as mentioned, these are two separate
instructions. This one opens the form to a specific record. No other
records are available. Watch out for word wrapping. This should all be on
one line.
DoCmd.OpenForm "frmStudents", , , "StudentID = ' " & Me.StudentID & " ' "
Let's say you have selected Jones, Johnny in the combo box. The bound
column (the hidden one) contains the value 01. This expression is saying to
open frmStudents with just the record that matches the bound column. If
frmStudents is based on tblStudents (or a query based on tblStudents), it
has a StudentID field available. The expression is saying to open
frmStudents to the record identified as 01 (Johnny's record).
The following opens the form with all records available, but a particular
record displayed:
DoCmd.OpenForm "frmStudents", , , , , , "StudentID = ' " & Me.StudentID & "
' "
Use one or the other, depending on your needs, but not both.
I wasn't sure whether to put the filter in just directly after the other
bit. This all comes back with no errors but when I go to the switchboard
section and click on the combo box there is no student id's in the list,
no
error message at all but nothing for me to choose from?
The first thing is to be sure the combo box is showing what it needs to
show. After that, apply the code. Again, what are the first two columns of
qryStudents? Provide some sample data (disguising any actual names).I'm leaving this job today and really need to finish this off if possible.
Monique
:
I should have mentioned that the syntax is different if StudentID is a
text
field:
"StudentID = " " " & Me.cboSelStudent & " " " "
The spaces between the quotes are for clarity. You may be able to use:
"StudentID = ' " & Me.cboSelStudent & " ' "
But I think that can cause problems if the field contains an apostrophe.
If I understand you correctly, you want to select a student name on the
switchboard, then open the Student form (frmStudent) to that student's
record. I think the switchboard wizard option you used will go to a
record on the current form only. That is, if you placed such a combo
box
into the header of frmStudent you could use the combo box to take you
to
that student's record.
In your case you need to go to that student's record on a form that
isn't
open yet. I take it that StudentID is a number field containing the
student ID number or some other unique number such as autonumber. For
the
Row Source of the combo box on the switchboard, you can create a query
(qryStudent) based on the student table. The query will have StudentID
in
the first column, and something like this in the second column:
FullName: [LastName] & ", " & [FirstName] & " " & [MI]
Sort by this field (in query design view, select Ascending in the Sort
row). This esample assumes you have the bracketed fields in the Student
table. Adjust to suit your needs (for instance, you may need ClassYear
or
some other field to guard against duplicate names) and to reflect your
field names.
I will call the combo box cboSelStudent.
The combo box is unbound. Open its property sheet by clicking the
combo
box to select it, then clicking View > Properties. Click the Data tab,
and select qryStudent as the Row Source. Set the Bound Column to 1
(which
is StudentID). Now click the Format tab. Set the column count to 2,
and
the column widths to something like 0";1.5".
Still at the Property sheet, click the Event tab. Click After Update,
click the three dots to the right, select Code Builder, and click OK.
The
VBA editor should open, with the cursor blinking between:
Private Sub cboSelStudent_AfterUpdate() and End Sub. Add something
like
the following to open the frmStudent to show just the selected
student's
record:
DoCmd.OpenForm "frmStudent", , , "StudentID = " & Me.cboSelStudent
This part of the code is the Where condition: "StudentID = " &
Me.cboSelStudent
What you are telling Access to do is to open frmStudent to the record
where StudentID matches the combo box selection.
To open the form to the selected student, but with all records
available,
use OpenArgs. In the AfterUpdate event for cboSelStudent:
Dim strFilter As String
strFilter = "StudentID = " & Me.cboSelStudent
DoCmd.OpenForm "frmStudent", , , , , , strFilter
Note that you need to use all of the commas. You should be prompted
for
OpenArgs as you type (it will be highlighted in the box that appears as
you type the code). You could use some shorthand:
DoCmd.OpenForm "frmStudent", OpenArgs:=strFilter
There's quite a bit of stuff here for a beginner, so read through it a
few
times if need be. Note carefully the names I have used. Use names of
your choosing, of course; substitute them for the names I used in the
instructions.
OK, I'm trying to get my head around this, I'm pretty new to MS
Access,
it's
my first DB I've created so forgive me if I sound vague.
What I'm thinking you want me to do is go to my switchboard and insert
a
combo box, when I did this I did it like this:
Clicked on the combo box icon on the switchboard in design mode, then
choose
from the wizard "I want the combo box to look up the values in a query
or
table", then I clicked for it to look in my main table called
Students,
not
to look for a query. Then I choose StudentID field from the list and
clicked
through to finish.
When I go to my switchboard I see it's added a combo box that shows
all
the
student id's.
Now I'm not sure what to do from here, I went into design view to
create
a
query, I added all the fields from the main table but I don't really
know
what to do from here on, don't know how I'm going to eventually get it
to
take me to my form for that student and edit that form.
Monique
:
What I would do is base the form on a select query and attach a
criteria
to
the student name in the query so the criteria references a combo box
in
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.