Re: Searching for and editing a record in form view

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

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.

Now 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" wrote:

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" <bamoob@xxxxxxxxxxxxxxxx> wrote in message
news:ucK$jx3%23HHA.3900@xxxxxxxxxxxxxxxxxxxxxxx
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