SEARCH BOX HELP NEEDED

[jerseygirl54]

I have created a Training Roster db in Access 2007. This roster is where we
can enter class titles, dates and who attended specific classes in addition
to a column for Pass/Fail for Range. On the class subform (Officer list) you
can double click on the officer's name and it will open a form on the officer
that lists all classes he has attended.

I would like to have a button on the switchboard that allows someone to
search for a specific officer by last name. Once found you would be able to
open up his his form that lists classes he has attended (same form as
indicated in first paragraph).

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[bhicks11 via AccessMonster.com]

Here's the gist of what I think you are looking for:

Make a form with a combobox - set the data source to Query/Table and in the
record source set up your query for the officer. In the OnExit event of that
combobox, open whatever form you want that shows the officers info and filter
it on the value of the combobox. So filter it something like: =forms!
selectform.combobox. That's the rough sketch but get into it and come back
with questions.

Bonnie

http://www.dataplus-svc.com

 

[jerseygirl54]

Yes, this seems to be what I am seeking, but I really don't understand your
directions. I created a form with the combo box, set the source to
Table/Query for the Officer (Student table). I don't understand the rest of
the directions. I want to enter the beginning characters of the name select
the name then upon "Enter" open that Student's (Officer's) individual form.
I don't know what is the "filter", where to put place it etc. Does it belong
on the form with the combo box ? Is it a macro? Is it an Expression?

Sorry for all the questions, but I'm lost. I've been playing around with
this and not getting anywhere.

Thanks!

 

[bhicks11 via AccessMonster.com]

That's a good start JerseyGirl. By the way, I have a friend we call a
JerseyGirl - not everyone gets the joke.

Did you click on the ... in the Record Source property and set up your Query?
This will define what the user sees and selects. After you have done this,
make sure the combobox shows what you expect. NOTE: if the table has a
unique ID field or autonum type field, make it the first column in the query,
the officer name the second column and then go to the Format tab of
properties and set the number of columns that you have set up and set the
widths with the first column 0 so you don't see it. So if you have 2 columns,
set the width to 0, 1.

Get this done and come back.

Bonnie
http://www.dataplus-svc.com

Yes, this seems to be what I am seeking, but I really don't understand your
directions. I created a form with the combo box, set the source to
Table/Query for the Officer (Student table). I don't understand the rest of
the directions. I want to enter the beginning characters of the name select
the name then upon "Enter" open that Student's (Officer's) individual form.
I don't know what is the "filter", where to put place it etc. Does it belong
on the form with the combo box ? Is it a macro? Is it an Expression?

Sorry for all the questions, but I'm lost. I've been playing around with
this and not getting anywhere.

Thanks!

Here's the gist of what I think you are looking for:

[quoted text clipped - 22 lines]

 

[jerseygirl54]

Got it to work, but no matter what name is choosen, clicking always results
in opening up the "Student" form to the first record.

That's a good start JerseyGirl. By the way, I have a friend we call a
JerseyGirl - not everyone gets the joke.

Did you click on the ... in the Record Source property and set up your Query?
This will define what the user sees and selects. After you have done this,
make sure the combobox shows what you expect. NOTE: if the table has a
unique ID field or autonum type field, make it the first column in the query,
the officer name the second column and then go to the Format tab of
properties and set the number of columns that you have set up and set the
widths with the first column 0 so you don't see it. So if you have 2 columns,
set the width to 0, 1.

Get this done and come back.

Bonnie
http://www.dataplus-svc.com

Yes, this seems to be what I am seeking, but I really don't understand your
directions. I created a form with the combo box, set the source to
Table/Query for the Officer (Student table). I don't understand the rest of
the directions. I want to enter the beginning characters of the name select
the name then upon "Enter" open that Student's (Officer's) individual form.
I don't know what is the "filter", where to put place it etc. Does it belong
on the form with the combo box ? Is it a macro? Is it an Expression?

Sorry for all the questions, but I'm lost. I've been playing around with
this and not getting anywhere.

Thanks!

Here's the gist of what I think you are looking for:

[quoted text clipped - 22 lines]

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[bhicks11 via AccessMonster.com]

Okay, your making progress. What did you put in the filter line of the data
tab (properties) of the form? What is your data source? We may have to
adjust that a little. Maybe we should put:

tableorquery!field = form!firstform.combobox

Bonnie
http://www.dataplus-svc.com

Got it to work, but no matter what name is choosen, clicking always results
in opening up the "Student" form to the first record.

That's a good start JerseyGirl. By the way, I have a friend we call a
JerseyGirl - not everyone gets the joke.

[quoted text clipped - 31 lines]

 

[jerseygirl54]

I created a "LastNameSearch" form with an Unbound Combo Box.

There is nothing in "Control Source", but in "Row Source" I have:

SELECT[OfficerRoster].[StudentID],[OfficerRoster].[LastName],[OfficerRoster].[FirstName] FROM OfficerRoster ORDER BY [LastName];

Okay, your making progress. What did you put in the filter line of the data
tab (properties) of the form? What is your data source? We may have to
adjust that a little. Maybe we should put:

tableorquery!field = form!firstform.combobox

Bonnie
http://www.dataplus-svc.com

Got it to work, but no matter what name is choosen, clicking always results
in opening up the "Student" form to the first record.

That's a good start JerseyGirl. By the way, I have a friend we call a
JerseyGirl - not everyone gets the joke.

[quoted text clipped - 31 lines]

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[bhicks11 via AccessMonster.com]

Okay, don't close the LastNameSearch form when you open the main form so that
it can be referred to.


In the filter line of the properties of the form put:

thisTableorQuery!thisIDfield = form!LastNameSearch.combobox

Of course change the names to what you really have. Instead of
thisTableorQuery put the name of your Data Source and for thisIDField put the
name of the field that has the ID. I am assuming you used the ID as your
bound column which would have been the default.

Let me know how it goes or if you don't understand what I mean.

Bonnie
http://www.dataplus-svc.com

I created a "LastNameSearch" form with an Unbound Combo Box.

There is nothing in "Control Source", but in "Row Source" I have:

SELECT[OfficerRoster].[StudentID],[OfficerRoster].[LastName],[OfficerRoster].[FirstName] FROM OfficerRoster ORDER BY [LastName];

Okay, your making progress. What did you put in the filter line of the data
tab (properties) of the form? What is your data source? We may have to

[quoted text clipped - 13 lines]

 

[jerseygirl54]

Does this belong on the "SearchLastName" form or the "Student" form. Also, I
don't see a "Filter" line. Do I place it in "Control Source" or "Row Source".

Sorry for being so dumb wih this. I really do appreciate your patience!

Okay, don't close the LastNameSearch form when you open the main form so that
it can be referred to.


In the filter line of the properties of the form put:

thisTableorQuery!thisIDfield = form!LastNameSearch.combobox

Of course change the names to what you really have. Instead of
thisTableorQuery put the name of your Data Source and for thisIDField put the
name of the field that has the ID. I am assuming you used the ID as your
bound column which would have been the default.

Let me know how it goes or if you don't understand what I mean.

Bonnie
http://www.dataplus-svc.com

I created a "LastNameSearch" form with an Unbound Combo Box.

There is nothing in "Control Source", but in "Row Source" I have:

SELECT[OfficerRoster].[StudentID],[OfficerRoster].[LastName],[OfficerRoster].[FirstName] FROM OfficerRoster ORDER BY [LastName];

Okay, your making progress. What did you put in the filter line of the data
tab (properties) of the form? What is your data source? We may have to

[quoted text clipped - 13 lines]

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[jerseygirl54]

I've been playing with this and found that the "Filter" line is on the
"Students" form, but I still can't get it to work.

Okay, don't close the LastNameSearch form when you open the main form so that
it can be referred to.


In the filter line of the properties of the form put:

thisTableorQuery!thisIDfield = form!LastNameSearch.combobox

Of course change the names to what you really have. Instead of
thisTableorQuery put the name of your Data Source and for thisIDField put the
name of the field that has the ID. I am assuming you used the ID as your
bound column which would have been the default.

Let me know how it goes or if you don't understand what I mean.

Bonnie
http://www.dataplus-svc.com

I created a "LastNameSearch" form with an Unbound Combo Box.

There is nothing in "Control Source", but in "Row Source" I have:

SELECT[OfficerRoster].[StudentID],[OfficerRoster].[LastName],[OfficerRoster].[FirstName] FROM OfficerRoster ORDER BY [LastName];

Okay, your making progress. What did you put in the filter line of the data
tab (properties) of the form? What is your data source? We may have to

[quoted text clipped - 13 lines]

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[bhicks11 via AccessMonster.com]

Lets just review and make sure we are on the same page. You made a form with
a combobox that you select the officer (SearchLastName). You can open
SearchLastName from the switchboard.

SearchLastName opens the Class form (or whatever form has the data source
that you want to see - of course this form must have the ID that you selected
in SearchLastName so you can filter it) and sets you on that particular
officer's record/info.

All forms have a filter property. You right click on the black button at the
upper left hand corner of your form and then select Properties, from there
you select the Data tab and you will see your data source and the next line
will be filter. That's were you put the filter criteria.

Bonnie
http://www.dataplus-svc.com

I've been playing with this and found that the "Filter" line is on the
"Students" form, but I still can't get it to work.

Okay, don't close the LastNameSearch form when you open the main form so that
it can be referred to.

[quoted text clipped - 24 lines]

 

[jerseygirl54]

(Answer to Paragraph 1)

Not from the Switchboard. All I did was create an Unbound Combo Box
"SearchLastName" that has a query "Officer Roster Query". I can select a
name and that's it. I've placed an "On Exit" , "OpenForm" which opens the
"Students", but it only opens to the first officer by Serial number
(OfficerID), no matter which officer is chosen in the combo box.

(Answer to Paragraph 2)

Yes, it opens the Students form, but only to the first officer on the roster
sorted (Asending) by OfficerID. In other words, the Officer with the lowest
ID number.

I have found the filter line on the "Students' form, but am unable to find
any filter on the SearchLastName form. I will look again.

Lets just review and make sure we are on the same page. You made a form with
a combobox that you select the officer (SearchLastName). You can open
SearchLastName from the switchboard.

SearchLastName opens the Class form (or whatever form has the data source
that you want to see - of course this form must have the ID that you selected
in SearchLastName so you can filter it) and sets you on that particular
officer's record/info.

All forms have a filter property. You right click on the black button at the
upper left hand corner of your form and then select Properties, from there
you select the Data tab and you will see your data source and the next line
will be filter. That's were you put the filter criteria.

Bonnie
http://www.dataplus-svc.com

I've been playing with this and found that the "Filter" line is on the
"Students" form, but I still can't get it to work.

Okay, don't close the LastNameSearch form when you open the main form so that
it can be referred to.

[quoted text clipped - 24 lines]

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[jerseygirl54]

I think I'm ALMOST there. This is what I have so far:

LastName Search (form) - Record Source - OfficerRoster Query (This Officer
Roster Query is linked to Officer Roster by Officer ID.

On Exit of LastName Search (form)
OpenForm-
Form Name - Students
View - Form
Filter Name -
Where Condition: [LastName]=[Forms]![LastName_Search]![LastName]


On Students Form I have:

Filter: [LastName]=[Forms]![LastName_Search]![LasName]

When I open the LastName Search Form, I can type a name, but when I close
the box (OnExit) I have a popup

"Enter Parameter Value" where I have to re-enter the last name. I do get
the correct Officer form though! (Hurray for THAT!)

I want to be able to type into the original "LastName Search" box without
having to reenter the name into Enter Parameter Value.

Lets just review and make sure we are on the same page. You made a form with
a combobox that you select the officer (SearchLastName). You can open
SearchLastName from the switchboard.

SearchLastName opens the Class form (or whatever form has the data source
that you want to see - of course this form must have the ID that you selected
in SearchLastName so you can filter it) and sets you on that particular
officer's record/info.

All forms have a filter property. You right click on the black button at the
upper left hand corner of your form and then select Properties, from there
you select the Data tab and you will see your data source and the next line
will be filter. That's were you put the filter criteria.

Bonnie
http://www.dataplus-svc.com

I've been playing with this and found that the "Filter" line is on the
"Students" form, but I still can't get it to work.

Okay, don't close the LastNameSearch form when you open the main form so that
it can be referred to.

[quoted text clipped - 24 lines]

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[bhicks11 via AccessMonster.com]

Your making progress. What do you have in the row source of your combobox?
I think that's why you are having a popup.

Bonnie
http://www.dataplus-svc.com

(Answer to Paragraph 1)

Not from the Switchboard. All I did was create an Unbound Combo Box
"SearchLastName" that has a query "Officer Roster Query". I can select a
name and that's it. I've placed an "On Exit" , "OpenForm" which opens the
"Students", but it only opens to the first officer by Serial number
(OfficerID), no matter which officer is chosen in the combo box.

(Answer to Paragraph 2)

Yes, it opens the Students form, but only to the first officer on the roster
sorted (Asending) by OfficerID. In other words, the Officer with the lowest
ID number.

I have found the filter line on the "Students' form, but am unable to find
any filter on the SearchLastName form. I will look again.

Lets just review and make sure we are on the same page. You made a form with
a combobox that you select the officer (SearchLastName). You can open
SearchLastName from the switchboard.

SearchLastName opens the Class form (or whatever form has the data source
that you want to see - of course this form must have the ID that you selected

[quoted text clipped - 17 lines]

 

[jerseygirl54]

Row Source is "OfficerRoster Query

Your making progress. What do you have in the row source of your combobox?
I think that's why you are having a popup.

Bonnie
http://www.dataplus-svc.com

(Answer to Paragraph 1)

Not from the Switchboard. All I did was create an Unbound Combo Box
"SearchLastName" that has a query "Officer Roster Query". I can select a
name and that's it. I've placed an "On Exit" , "OpenForm" which opens the
"Students", but it only opens to the first officer by Serial number
(OfficerID), no matter which officer is chosen in the combo box.

(Answer to Paragraph 2)

Yes, it opens the Students form, but only to the first officer on the roster
sorted (Asending) by OfficerID. In other words, the Officer with the lowest
ID number.

I have found the filter line on the "Students' form, but am unable to find
any filter on the SearchLastName form. I will look again.

Lets just review and make sure we are on the same page. You made a form with
a combobox that you select the officer (SearchLastName). You can open
SearchLastName from the switchboard.

SearchLastName opens the Class form (or whatever form has the data source
that you want to see - of course this form must have the ID that you selected

[quoted text clipped - 17 lines]

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[bhicks11 via AccessMonster.com]

Make sure it is a combobox, the row type should be Table/Query and for the
row source click on the ... on that line to create the query. When you
create the query and exit it, the query SQL will be placed on the Row Source
line.

Bonnie
http://www.dataplus-svc.com

Row Source is "OfficerRoster Query

Your making progress. What do you have in the row source of your combobox?
I think that's why you are having a popup.

[quoted text clipped - 28 lines]

 

[jerseygirl54]

Yes, I checked and it is a combobox. It is a Table/Query and for the row
source, I clicked on the ... and have Officer Roster as the table choice with
three choices picked: OfficerID, LastName, FirstName. I closed the query and
what shows on the Row Source line is "OfficerRoster Query" (nothing else)

Make sure it is a combobox, the row type should be Table/Query and for the
row source click on the ... on that line to create the query. When you
create the query and exit it, the query SQL will be placed on the Row Source
line.

Bonnie
http://www.dataplus-svc.com

Row Source is "OfficerRoster Query

Your making progress. What do you have in the row source of your combobox?
I think that's why you are having a popup.

[quoted text clipped - 28 lines]

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[bhicks11 via AccessMonster.com]

Okay, check what is in the criteria of that query. I'm trying to figure out
what you are having a parameter popup box.

Bonnie
http://www.dataplus-svc.com

Yes, I checked and it is a combobox. It is a Table/Query and for the row
source, I clicked on the ... and have Officer Roster as the table choice with
three choices picked: OfficerID, LastName, FirstName. I closed the query and
what shows on the Row Source line is "OfficerRoster Query" (nothing else)

Make sure it is a combobox, the row type should be Table/Query and for the
row source click on the ... on that line to create the query. When you

[quoted text clipped - 11 lines]

 

[jerseygirl54]

There isn't anything in any of the three columns :

OfficerID LastName FirstName
OfficerRoster OfficerRoster OfficerRoster
Assending

Okay, check what is in the criteria of that query. I'm trying to figure out
what you are having a parameter popup box.

Bonnie
http://www.dataplus-svc.com

Yes, I checked and it is a combobox. It is a Table/Query and for the row
source, I clicked on the ... and have Officer Roster as the table choice with
three choices picked: OfficerID, LastName, FirstName. I closed the query and
what shows on the Row Source line is "OfficerRoster Query" (nothing else)

Make sure it is a combobox, the row type should be Table/Query and for the
row source click on the ... on that line to create the query. When you

[quoted text clipped - 11 lines]

I am VERY new and need VERY simple instructions.
Any help you can offer is GREATLY appreciated!

 

[bhicks11 via AccessMonster.com]

I see the problem. You're bound column in the combobox is the OfficerID so
that has to be what you are filtering. Change your filter on to:

[LastName]=[Forms]![LastName_Search]![OfficerID]

Make sure OfficerID is included in the data source (query or table) of that
form.

Bonnie
http://www.dataplus-svc.com

I think I'm ALMOST there. This is what I have so far:

LastName Search (form) - Record Source - OfficerRoster Query (This Officer
Roster Query is linked to Officer Roster by Officer ID.

On Exit of LastName Search (form)
OpenForm-
Form Name - Students
View - Form
Filter Name -
Where Condition: [LastName]=[Forms]![LastName_Search]![LastName]

On Students Form I have:

Filter: [LastName]=[Forms]![LastName_Search]![LasName]

When I open the LastName Search Form, I can type a name, but when I close
the box (OnExit) I have a popup

"Enter Parameter Value" where I have to re-enter the last name. I do get
the correct Officer form though! (Hurray for THAT!)

I want to be able to type into the original "LastName Search" box without
having to reenter the name into Enter Parameter Value.

Lets just review and make sure we are on the same page. You made a form with
a combobox that you select the officer (SearchLastName). You can open

[quoted text clipped - 21 lines]