Search with Forms on Access XP

I am a reasonably experienced programmer (at least I thought I was), however
I am new to Access/VBA and visual basic in general. I am used to working in a
*nix environment, etc etc.
My question is this:

I am designing a database that is for managing students in a class. There
are roughly 450 students in the class. Each has a unique student ID that is
the primary key.
What I am trying to accomplish is this: I have a form that contains text
boxes for the most common information needed for each student. i.e Last Name,
First Name, Birthdate, etc.
I want to be able to type the students id into the text box corresponding to
their ID and have the rest of the textboxes populate with their information.
seems like it should be easy enough, yet it is not (or I am barking up the
wrong tree).

I have tried making the ID box in the form an unbound box and using the
following code in it:

dim frm as Form

Set frm = Forms![Form_I_need]
frm.RecordsetClone.FindFirst " [textbox_value] = " & Me![searchable_fieldname]
frm.Bookmark = frm.RecordsetClone.Bookmark

according to information I got from assorted help resources
this should populate the "form_I_need" info with the info about the student.
In this case I get an error (error 2450 : cant find the form "Form_I_Need")
I know the form is there, yet I cannot get it to open. I have double, triple,
and quadruple checked the spelling, form name, etc.
In debugging, I even tried to write an event procedure that simply opens the
form. Still get error 2450.

So my question, in two parts is:

1.) What is the best way to write the code I am trying to write to use the
textbox to search for students?
2.) Why is it not able to find the form specified in the code and giving me
a 2450 error?

Of course the 2450 error resolution may solve everything if my code is right.

Help.

Google - "QUERY BY FORM" that should bring up more than enough information
for ya'. Its a very common technique and easy to implement.

"Frazz" wrote:

> I am a reasonably experienced programmer (at least I thought I was), however
> I am new to Access/VBA and visual basic in general. I am used to working in a
> *nix environment, etc etc.
> My question is this:
>
> I am designing a database that is for managing students in a class. There
> are roughly 450 students in the class. Each has a unique student ID that is
> the primary key.
> What I am trying to accomplish is this: I have a form that contains text
> boxes for the most common information needed for each student. i.e Last Name,
> First Name, Birthdate, etc.
> I want to be able to type the students id into the text box corresponding to
> their ID and have the rest of the textboxes populate with their information.
> seems like it should be easy enough, yet it is not (or I am barking up the
> wrong tree).
>
> I have tried making the ID box in the form an unbound box and using the
> following code in it:
>
> dim frm as Form
>
> Set frm = Forms![Form_I_need]
> frm.RecordsetClone.FindFirst " [textbox_value] = " & Me![searchable_fieldname]
> frm.Bookmark = frm.RecordsetClone.Bookmark
>
> according to information I got from assorted help resources
> this should populate the "form_I_need" info with the info about the student.
> In this case I get an error (error 2450 : cant find the form "Form_I_Need")
> I know the form is there, yet I cannot get it to open. I have double, triple,
> and quadruple checked the spelling, form name, etc.
> In debugging, I even tried to write an event procedure that simply opens the
> form. Still get error 2450.
>
> So my question, in two parts is:
>
> 1.) What is the best way to write the code I am trying to write to use the
> textbox to search for students?
> 2.) Why is it not able to find the form specified in the code and giving me
> a 2450 error?
>
> Of course the 2450 error resolution may solve everything if my code is right.
>
> Help.

Frazz wrote:

>I am a reasonably experienced programmer (at least I thought I was), however
>I am new to Access/VBA and visual basic in general. I am used to working in a
>*nix environment, etc etc.
>My question is this:
>
>I am designing a database that is for managing students in a class. There
>are roughly 450 students in the class. Each has a unique student ID that is
>the primary key.
>What I am trying to accomplish is this: I have a form that contains text
>boxes for the most common information needed for each student. i.e Last Name,
>First Name, Birthdate, etc.
>I want to be able to type the students id into the text box corresponding to
>their ID and have the rest of the textboxes populate with their information.
>seems like it should be easy enough, yet it is not (or I am barking up the
>wrong tree).
>
>I have tried making the ID box in the form an unbound box and using the
>following code in it:
>
>dim frm as Form
>
>Set frm = Forms![Form_I_need]
>frm.RecordsetClone.FindFirst " [textbox_value] = " & Me![searchable_fieldname]
>frm.Bookmark = frm.RecordsetClone.Bookmark
>
>according to information I got from assorted help resources
>this should populate the "form_I_need" info with the info about the student.
>In this case I get an error (error 2450 : cant find the form "Form_I_Need")
>I know the form is there, yet I cannot get it to open. I have double, triple,
>and quadruple checked the spelling, form name, etc.
>In debugging, I even tried to write an event procedure that simply opens the
>form. Still get error 2450.
>
>So my question, in two parts is:
>
>1.) What is the best way to write the code I am trying to write to use the
>textbox to search for students?
>2.) Why is it not able to find the form specified in the code and giving me
>a 2450 error?


The error message implies that the dorm is not open. If
form Form_I_need is not open, then it is not a member of the
Forms collection and it can not possibly have a recordset
clone. If the form were already open, your FindFirst should
be more like:
.FindFirst "StudentID = " & Me!txtSearchID
or, if the student ID field is a Text field:
.FindFirst "StudentID = """ & Me!txtSearchID & """"

OTOH, if you want to open the form filtered to the specific
student, then use different code like:
DoCmd.OpenForm "Form_I_need", _
WhereCondition:= "StudentID = " & Me!txtSearchID

--
Marsh
MVP [MS Access]

Thanks
going to try that now

"Marshall Barton" wrote:

> Frazz wrote:
>
> >I am a reasonably experienced programmer (at least I thought I was), however
> >I am new to Access/VBA and visual basic in general. I am used to working in a
> >*nix environment, etc etc.
> >My question is this:
> >
> >I am designing a database that is for managing students in a class. There
> >are roughly 450 students in the class. Each has a unique student ID that is
> >the primary key.
> >What I am trying to accomplish is this: I have a form that contains text
> >boxes for the most common information needed for each student. i.e Last Name,
> >First Name, Birthdate, etc.
> >I want to be able to type the students id into the text box corresponding to
> >their ID and have the rest of the textboxes populate with their information.
> >seems like it should be easy enough, yet it is not (or I am barking up the
> >wrong tree).
> >
> >I have tried making the ID box in the form an unbound box and using the
> >following code in it:
> >
> >dim frm as Form
> >
> >Set frm = Forms![Form_I_need]
> >frm.RecordsetClone.FindFirst " [textbox_value] = " & Me![searchable_fieldname]
> >frm.Bookmark = frm.RecordsetClone.Bookmark
> >
> >according to information I got from assorted help resources
> >this should populate the "form_I_need" info with the info about the student.
> >In this case I get an error (error 2450 : cant find the form "Form_I_Need")
> >I know the form is there, yet I cannot get it to open. I have double, triple,
> >and quadruple checked the spelling, form name, etc.
> >In debugging, I even tried to write an event procedure that simply opens the
> >form. Still get error 2450.
> >
> >So my question, in two parts is:
> >
> >1.) What is the best way to write the code I am trying to write to use the
> >textbox to search for students?
> >2.) Why is it not able to find the form specified in the code and giving me
> >a 2450 error?
>
>
> The error message implies that the dorm is not open. If
> form Form_I_need is not open, then it is not a member of the
> Forms collection and it can not possibly have a recordset
> clone. If the form were already open, your FindFirst should
> be more like:
> .FindFirst "StudentID = " & Me!txtSearchID
> or, if the student ID field is a Text field:
> .FindFirst "StudentID = """ & Me!txtSearchID & """"
>
> OTOH, if you want to open the form filtered to the specific
> student, then use different code like:
> DoCmd.OpenForm "Form_I_need", _
> WhereCondition:= "StudentID = " & Me!txtSearchID
>
> --
> Marsh
> MVP [MS Access]
>

I did away with a separate form for search and am just trying to use an
unbound textbox, where I would type the ID into, right on the main form. So,
the form has four or five bound textboxes that contain info about the
students and one unbound that I would type the ID into, that would in turn
populate the other textboxes with the students info.
This is the code I tried.
I did not work. Nor did I get an error.


Private Sub ADC_AfterUpdate()
Dim frm As Form
'I am not sure that I need to even set the form as a variable since I am in
the form and it is open.
Set frm = Forms![Form_I’m_in]

'not sure what to use here if I do not set a variable.

frm.RecordsetClone.FindFirst "StudentID = " & Me!txt_studentid
frm.Bookmark = frm.RecordsetClone.Bookmark

Like I said, initially I was trying to hit a button that would bring up
another small form that I could type the student id in.
However, just using the one form seems easier.

Hopefully I am giving you the information you need to assist me.


"Marshall Barton" wrote:

> Frazz wrote:
>
> >I am a reasonably experienced programmer (at least I thought I was), however
> >I am new to Access/VBA and visual basic in general. I am used to working in a
> >*nix environment, etc etc.
> >My question is this:
> >
> >I am designing a database that is for managing students in a class. There
> >are roughly 450 students in the class. Each has a unique student ID that is
> >the primary key.
> >What I am trying to accomplish is this: I have a form that contains text
> >boxes for the most common information needed for each student. i.e Last Name,
> >First Name, Birthdate, etc.
> >I want to be able to type the students id into the text box corresponding to
> >their ID and have the rest of the textboxes populate with their information.
> >seems like it should be easy enough, yet it is not (or I am barking up the
> >wrong tree).
> >
> >I have tried making the ID box in the form an unbound box and using the
> >following code in it:
> >
> >dim frm as Form
> >
> >Set frm = Forms![Form_I_need]
> >frm.RecordsetClone.FindFirst " [textbox_value] = " & Me![searchable_fieldname]
> >frm.Bookmark = frm.RecordsetClone.Bookmark
> >
> >according to information I got from assorted help resources
> >this should populate the "form_I_need" info with the info about the student.
> >In this case I get an error (error 2450 : cant find the form "Form_I_Need")
> >I know the form is there, yet I cannot get it to open. I have double, triple,
> >and quadruple checked the spelling, form name, etc.
> >In debugging, I even tried to write an event procedure that simply opens the
> >form. Still get error 2450.
> >
> >So my question, in two parts is:
> >
> >1.) What is the best way to write the code I am trying to write to use the
> >textbox to search for students?
> >2.) Why is it not able to find the form specified in the code and giving me
> >a 2450 error?
>
>
> The error message implies that the dorm is not open. If
> form Form_I_need is not open, then it is not a member of the
> Forms collection and it can not possibly have a recordset
> clone. If the form were already open, your FindFirst should
> be more like:
> .FindFirst "StudentID = " & Me!txtSearchID
> or, if the student ID field is a Text field:
> .FindFirst "StudentID = """ & Me!txtSearchID & """"
>
> OTOH, if you want to open the form filtered to the specific
> student, then use different code like:
> DoCmd.OpenForm "Form_I_need", _
> WhereCondition:= "StudentID = " & Me!txtSearchID
>
> --
> Marsh
> MVP [MS Access]
>

here's another approach; similar to your textbox attempt.....

delete that textbox and in your form add an unbound combobox......when you
do that it will launch the combobox wizard and one choice will be for it to
"find a record based on this selection" (or some verbage along these
lines...)

I think it will do what you want it to do. Plus one doesn't have to use the
dropdown/cursor approach...one can just type the values into the combo box
and it will pull up the record
--
NTC


"Frazz" wrote:

> I did away with a separate form for search and am just trying to use an
> unbound textbox, where I would type the ID into, right on the main form. So,
> the form has four or five bound textboxes that contain info about the
> students and one unbound that I would type the ID into, that would in turn
> populate the other textboxes with the students info.
> This is the code I tried.
> I did not work. Nor did I get an error.
>
>
> Private Sub ADC_AfterUpdate()
> Dim frm As Form
> 'I am not sure that I need to even set the form as a variable since I am in
> the form and it is open.
> Set frm = Forms![Form_I’m_in]
>
> 'not sure what to use here if I do not set a variable.
>
> frm.RecordsetClone.FindFirst "StudentID = " & Me!txt_studentid
> frm.Bookmark = frm.RecordsetClone.Bookmark
>
> Like I said, initially I was trying to hit a button that would bring up
> another small form that I could type the student id in.
> However, just using the one form seems easier.
>
> Hopefully I am giving you the information you need to assist me.
>
>
> "Marshall Barton" wrote:
>
> > Frazz wrote:
> >
> > >I am a reasonably experienced programmer (at least I thought I was), however
> > >I am new to Access/VBA and visual basic in general. I am used to working in a
> > >*nix environment, etc etc.
> > >My question is this:
> > >
> > >I am designing a database that is for managing students in a class. There
> > >are roughly 450 students in the class. Each has a unique student ID that is
> > >the primary key.
> > >What I am trying to accomplish is this: I have a form that contains text
> > >boxes for the most common information needed for each student. i.e Last Name,
> > >First Name, Birthdate, etc.
> > >I want to be able to type the students id into the text box corresponding to
> > >their ID and have the rest of the textboxes populate with their information.
> > >seems like it should be easy enough, yet it is not (or I am barking up the
> > >wrong tree).
> > >
> > >I have tried making the ID box in the form an unbound box and using the
> > >following code in it:
> > >
> > >dim frm as Form
> > >
> > >Set frm = Forms![Form_I_need]
> > >frm.RecordsetClone.FindFirst " [textbox_value] = " & Me![searchable_fieldname]
> > >frm.Bookmark = frm.RecordsetClone.Bookmark
> > >
> > >according to information I got from assorted help resources
> > >this should populate the "form_I_need" info with the info about the student.
> > >In this case I get an error (error 2450 : cant find the form "Form_I_Need")
> > >I know the form is there, yet I cannot get it to open. I have double, triple,
> > >and quadruple checked the spelling, form name, etc.
> > >In debugging, I even tried to write an event procedure that simply opens the
> > >form. Still get error 2450.
> > >
> > >So my question, in two parts is:
> > >
> > >1.) What is the best way to write the code I am trying to write to use the
> > >textbox to search for students?
> > >2.) Why is it not able to find the form specified in the code and giving me
> > >a 2450 error?
> >
> >
> > The error message implies that the dorm is not open. If
> > form Form_I_need is not open, then it is not a member of the
> > Forms collection and it can not possibly have a recordset
> > clone. If the form were already open, your FindFirst should
> > be more like:
> > .FindFirst "StudentID = " & Me!txtSearchID
> > or, if the student ID field is a Text field:
> > .FindFirst "StudentID = """ & Me!txtSearchID & """"
> >
> > OTOH, if you want to open the form filtered to the specific
> > student, then use different code like:
> > DoCmd.OpenForm "Form_I_need", _
> > WhereCondition:= "StudentID = " & Me!txtSearchID
> >
> > --
> > Marsh
> > MVP [MS Access]
> >

Frazz wrote:

>I did away with a separate form for search and am just trying to use an
>unbound textbox, where I would type the ID into, right on the main form. So,
>the form has four or five bound textboxes that contain info about the
>students and one unbound that I would type the ID into, that would in turn
>populate the other textboxes with the students info.
>This is the code I tried.
>I did not work. Nor did I get an error.
>
>
>Private Sub ADC_AfterUpdate()
>Dim frm As Form
> 'I am not sure that I need to even set the form as a variable since I am in
>the form and it is open.
>Set frm = Forms![Form_I’m_in]
>
>'not sure what to use here if I do not set a variable.
>
>frm.RecordsetClone.FindFirst "StudentID = " & Me!txt_studentid
>frm.Bookmark = frm.RecordsetClone.Bookmark
>
>Like I said, initially I was trying to hit a button that would bring up
>another small form that I could type the student id in.
>However, just using the one form seems easier.


I think an unbound text or combo box in the form's header
section is a good idea. The form object for code in the
form is Me

I would use code like:

With Me.RecordsetClone
.FindFirst "StudentID = " & Me!txt_studentid
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

In A2003, the find a record wizard generates bad code. If
you used that, you should modify it to use the NoMatch
property instead of the EOF property.

--
Marsh
MVP [MS Access]