Re: Searching for and editing a record in form view

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 *** 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 ***, 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" <Monique@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B43859D7-06E6-4187-9953-07F5C16A1926@xxxxxxxxxxxxxxxx
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" wrote:


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


"Monique" wrote:

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


.

Quantcast