Complex ComboBox Functions for Teachers

[eetarnold]

Hi Gurus,

As I've read in other posts the past couple of days, I've read many
great posts and haven't figured out how I can solve this problem...I'm
trying to make a form very user friendly for a group of teachers--who
are not computer teachers.

I have a main form named "frmProgressReports" based on a record source
"qryStudents," which has StudentID, FullName: [FirstName] & " " &
[LastName], LastName, and FirstName.

In frmProgress is an unbound combobox, "comboStudents," which also has
the row source: SELECT qryStudents.StudentID, qryStudents.FullName,
qryStudents.LastName, qryStudents.FirstName FROM qryStudents ORDER BY
qryStudents.LastName;

ComboStudents has column widths 0";4";0";0"---FullName is what appears
in the combo list.

I'd like comboStudents to have this functionality:

(1)
Include an item in the list of students (FullName) that says something
like 'Click to Add Student.' If the teacher clicks on the
comboStudents and selects that item, a data entry form (frmStudents,
based on the students table) is opened, set to a new record, and focus
set on the FirstName field. The 'Click to Add Student' item could be
the first and default record in the underlying students table--I could
maybe just add it into the FirstName field.

(2)
If a teacher selects a student already entered in the underlying table,
comboStudents refreshes, moves frmProgressReports to the right record,
and sets the focus to a control on a subform in frmProgress called
subProgressReports, in which progress data can be entered for that
student.

(3)
If a teacher tries to type a student's name (first and then last name)
into comboStudents (field showing is the expression FullName), an on
not in list event or some other event catches the first and last names,
opens the frmStudents form in add mode, parses(?) the first and last
names, and puts them into the FirstName and LastName fields so the
teacher doesn't have to retype them in this form.

I know enough to know that the first 2 functions are the crucial ones,
and that I need clarification on what code to use and when. Something
like the 3rd function would be nice, but would require some effort...

I'd greatly appreciate any advice/assistence that could be provided.

Respectfully,

Arnold

[Wayne Gillespie]

On 23 Jan 2006 16:38:10 -0800, "ee*******@kc.rr.com" <ee*******@kc.rr.com> wrote:

Hi Gurus,

As I've read in other posts the past couple of days, I've read many
great posts and haven't figured out how I can solve this problem...I'm
trying to make a form very user friendly for a group of teachers--who
are not computer teachers.

I have a main form named "frmProgressReports" based on a record source
"qryStudents," which has StudentID, FullName: [FirstName] & " " &
[LastName], LastName, and FirstName.

In frmProgress is an unbound combobox, "comboStudents," which also has
the row source: SELECT qryStudents.StudentID, qryStudents.FullName,
qryStudents.LastName, qryStudents.FirstName FROM qryStudents ORDER BY
qryStudents.LastName;

ComboStudents has column widths 0";4";0";0"---FullName is what appears
in the combo list.

I'd like comboStudents to have this functionality:

(1)
Include an item in the list of students (FullName) that says something
like 'Click to Add Student.' If the teacher clicks on the
comboStudents and selects that item, a data entry form (frmStudents,
based on the students table) is opened, set to a new record, and focus
set on the FirstName field. The 'Click to Add Student' item could be
the first and default record in the underlying students table--I could
maybe just add it into the FirstName field.

(2)
If a teacher selects a student already entered in the underlying table,
comboStudents refreshes, moves frmProgressReports to the right record,
and sets the focus to a control on a subform in frmProgress called
subProgressReports, in which progress data can be entered for that
student.

(3)
If a teacher tries to type a student's name (first and then last name)
into comboStudents (field showing is the expression FullName), an on
not in list event or some other event catches the first and last names,
opens the frmStudents form in add mode, parses(?) the first and last
names, and puts them into the FirstName and LastName fields so the
teacher doesn't have to retype them in this form.

I know enough to know that the first 2 functions are the crucial ones,
and that I need clarification on what code to use and when. Something
like the 3rd function would be nice, but would require some effort...

I'd greatly appreciate any advice/assistence that could be provided.

Respectfully,

Arnold

1. Set the row source of comboStudents to -

SELECT -999 As StudentID, "<Add New Student>" As FullName, "aaa" As LastName, "x" As FirstName
From qryStudents
UNION SELECT StudentID, FullName, LastName, FirstName
FROM qryStudents
ORDER BY LastName;

This will add an item to the top of the dropdownlist displaying <Add New Student>

2. Set the AfterUpdate event of comboStudents to -

************************************************** ***************
Sub comboStudents_AfterUpdate()

If Me.comboStudents = -999 Then
DoCmd.OpenForm "frmStudents", , , , acFormAdd
Else
With Me.RecordSetClone
.FindFirst "[StudentID]=" & Me.comboStudents
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

Me.comboStudents = Null

End Sub
************************************************** ***************

If the user selects <Add New Student>, frmStudents will open at a new record ready for them to enter the student
details.
If they select anything else frmProgressReports will navigate to the corresponding record.

3. This can be done by using the NotInList event of the combo to open frmStudents at a new record, similar to 2 above.
The difficulty is automatically parsing the student name into first / last name. Consider -

Billy Bob Thornton (Billy Bob Thornton or Billy Bob Thornton)
Jill St James (Jill St James or Jill St James)
Rip van Winkle (Rip van Winkle or Rip van Winkle)
Wayne Gillespie
Gosford NSW Australia

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For (1) create another query that looks like this:

SELECT StudentID, FullName, LastName, FirstName
FROM qryStudents

UNION ALL

SELECT 0, "<Click to Add Student>", NULL, NULL
FROM qryStudents

ORDER BY LastName

Save it and make this query name the RowSource of the ComboBox.

The "<Click to add student>" will be the top item in the combo list.
When that is selected use the ComboBox's AfterUpdate event to add the
new student info (see below).

For (2) use the AfterUpdate event of the ComboBox to change the form's
RecordSource property and then set the focus to the control in the
subform. E.g.:

Private Sub cboStudents_AfterUpdate()

' Assumes that Column(0) is the Bound column
If Not IsNull(Me!cboStudents) Then
If Me!cboStudents = 0 Then
' Run the add new student routine
' Open the form in dialog mode, which waits
' until the form is closed to resume the VBA code flow.
DoCmd.OpenForm "frmStudents", acNormal, _
DataMode:=acFormAdd, WindowMode:=acDialog
Else
Me.RecordSource = "SELECT * FROM Students WHERE StudentID=" & _
Me!cboStudents
Me!subProgressReports!MyControl.SetFocus
End If
End If

End Sub

If the StudentID is a string the WHERE clause would look like this:

WHERE StudentID = '" & Me!cboStudents & "'"

For (3) you can see how that would work from the above: i.e., grab the
info from the frmStudents & place it in the parent form, etc.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9WJj4echKqOuFEgEQIbWQCeNCQS+gYmAoRTyxyuwNWxas UYAN4An0oJ
WKCb7N6b6jhc+oxC1YvQnaN4
=WK4M
-----END PGP SIGNATURE-----

ee*******@kc.rr.com wrote:

Hi Gurus,

As I've read in other posts the past couple of days, I've read many
great posts and haven't figured out how I can solve this problem...I'm
trying to make a form very user friendly for a group of teachers--who
are not computer teachers.

I have a main form named "frmProgressReports" based on a record source
"qryStudents," which has StudentID, FullName: [FirstName] & " " &
[LastName], LastName, and FirstName.

In frmProgress is an unbound combobox, "comboStudents," which also has
the row source: SELECT qryStudents.StudentID, qryStudents.FullName,
qryStudents.LastName, qryStudents.FirstName FROM qryStudents ORDER BY
qryStudents.LastName;

ComboStudents has column widths 0";4";0";0"---FullName is what appears
in the combo list.

I'd like comboStudents to have this functionality:

(1)
Include an item in the list of students (FullName) that says something
like 'Click to Add Student.' If the teacher clicks on the
comboStudents and selects that item, a data entry form (frmStudents,
based on the students table) is opened, set to a new record, and focus
set on the FirstName field. The 'Click to Add Student' item could be
the first and default record in the underlying students table--I could
maybe just add it into the FirstName field.

(2)
If a teacher selects a student already entered in the underlying table,
comboStudents refreshes, moves frmProgressReports to the right record,
and sets the focus to a control on a subform in frmProgress called
subProgressReports, in which progress data can be entered for that
student.

(3)
If a teacher tries to type a student's name (first and then last name)
into comboStudents (field showing is the expression FullName), an on
not in list event or some other event catches the first and last names,
opens the frmStudents form in add mode, parses(?) the first and last
names, and puts them into the FirstName and LastName fields so the
teacher doesn't have to retype them in this form.

I know enough to know that the first 2 functions are the crucial ones,
and that I need clarification on what code to use and when. Something
like the 3rd function would be nice, but would require some effort...

I'd greatly appreciate any advice/assistence that could be provided.

Respectfully,

Arnold

[Bob Quintal]

"ee*******@kc.rr.com" <ee*******@kc.rr.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

Hi Gurus,

As I've read in other posts the past couple of days, I've read
many great posts and haven't figured out how I can solve this
problem...I'm trying to make a form very user friendly for a
group of teachers--who are not computer teachers.

I have a main form named "frmProgressReports" based on a
record source "qryStudents," which has StudentID, FullName:
[FirstName] & " " & [LastName], LastName, and FirstName.

In frmProgress is an unbound combobox, "comboStudents," which
also has the row source: SELECT qryStudents.StudentID,
qryStudents.FullName, qryStudents.LastName,
qryStudents.FirstName FROM qryStudents ORDER BY
qryStudents.LastName;

ComboStudents has column widths 0";4";0";0"---FullName is what
appears in the combo list.

I'd like comboStudents to have this functionality:

(1)
Include an item in the list of students (FullName) that says
something like 'Click to Add Student.' If the teacher clicks
on the comboStudents and selects that item, a data entry form
(frmStudents, based on the students table) is opened, set to a
new record, and focus set on the FirstName field. The 'Click
to Add Student' item could be the first and default record in
the underlying students table--I could maybe just add it into
the FirstName field.

http://www.mvps.org/access/forms/frm0043.htm
(2)
If a teacher selects a student already entered in the
underlying table, comboStudents refreshes, moves
frmProgressReports to the right record, and sets the focus to
a control on a subform in frmProgress called
subProgressReports, in which progress data can be entered for
that student.
http://www.mvps.org/access/forms/frm0005.htm

(3)
If a teacher tries to type a student's name (first and then
last name) into comboStudents (field showing is the expression
FullName), an on not in list event or some other event catches
the first and last names, opens the frmStudents form in add
mode, parses(?) the first and last names, and puts them into
the FirstName and LastName fields so the teacher doesn't have
to retype them in this form.

http://www.mvps.org/access/forms/frm0015.htm
I know enough to know that the first 2 functions are the
crucial ones, and that I need clarification on what code to
use and when. Something like the 3rd function would be nice,
but would require some effort...

I'd greatly appreciate any advice/assistence that could be
provided.

Respectfully,

Arnold

http://www.mvps.org/access/ is a very useful site! Thank you Dev
Ashish, Arvin Meyer and Felix Kasza.

--
Bob Quintal

PA is y I've altered my email address.

[eetarnold]

Thanks Wayne and MGFoster for the code, and Bob for the links (which I
had tried to make sense of previously).

I really apreciate your help, and will study the different code you
gave. This is kind of why I'm confused. I realize that with
programming, all things are pretty much possible, and different code
can do the same thing.

I am wondering which of the code above more popular, efficient, stable,
etc. Is there a rhyme or reason to use one approach over the other?

Also, good point about the "Rip van Winkle" possibility. Maybe I
should add yet another field...

Thanks again,
Arnold

[Arnold]

One last question--multiple entries in combobox.

In comboStudents, why would there be some name duplication occuring, as
well as overwriting of existing list items with those names being
selected? That seems to be happening--in the table named Students,
multiple names have been created, apparently just by selecting them in
comboStudents. Is there a way to protect against this?

Thanks again,
Arnold

[Wayne Gillespie]

On 24 Jan 2006 04:28:19 -0800, "Arnold" <ee*******@kc.rr.com> wrote:

One last question--multiple entries in combobox.

In comboStudents, why would there be some name duplication occuring, as
well as overwriting of existing list items with those names being
selected? That seems to be happening--in the table named Students,
multiple names have been created, apparently just by selecting them in
comboStudents. Is there a way to protect against this?

Thanks again,
Arnold

It sounds like you have comboStudents bound to the StudentID field in the table.
If comboStudents is to be used as a search field it must be UNBOUND. (ControlSource empty)

You can use the combo as a search field OR a data field, but not both.

Wayne Gillespie
Gosford NSW Australia

[ea3666]

Thanks Wayne,

However, comboStudents is unbound--there's nothing in the control
source. Yet it still does this.

However, the subform in this form, subProgressReports, is bound to
StudentID.

Is this part of the problem?

[Arnold]

I ended up using Wayne's approach above--Thanks again. I'll also
mention that the combobox, ComboStudents, also had a double-click event
that should allow the user to double-click a name to open the
frmStudents to the name's record for editing (in case a teacher spelled
it wrong).

However, with the above "<Add New Student>" code, an error now occurs
when double-clicking an name in the list: Run-time error '2137: You
can't use Find or Replace now. Do you know why this happens?

Private Sub ComboStudents_DblClick(Cancel As Integer)

DoCmd.OpenForm "frmStudents", acNormal, "", "", , acNormal
DoCmd.GoToControl "StudentID"
DoCmd.FindRecord Me!StudentID, acEntire, False, , False, , True
If Me!StudentID = 1 Then
DoCmd.GoToRecord , , acNewRec
End If
DoCmd.GoToControl "FirstName"

Exit_ComboStudents_Click:
Exit Sub

Err_ComboStudents_Click:
MsgBox Err.Description
Resume Exit_ComboStudents_Click

[Wayne Gillespie]

On 29 Jan 2006 15:54:08 -0800, "Arnold" <ee*******@kc.rr.com> wrote:

I ended up using Wayne's approach above--Thanks again. I'll also
mention that the combobox, ComboStudents, also had a double-click event
that should allow the user to double-click a name to open the
frmStudents to the name's record for editing (in case a teacher spelled
it wrong).

However, with the above "<Add New Student>" code, an error now occurs
when double-clicking an name in the list: Run-time error '2137: You
can't use Find or Replace now. Do you know why this happens?

Private Sub ComboStudents_DblClick(Cancel As Integer)

DoCmd.OpenForm "frmStudents", acNormal, "", "", , acNormal
DoCmd.GoToControl "StudentID"
DoCmd.FindRecord Me!StudentID, acEntire, False, , False, , True
If Me!StudentID = 1 Then
DoCmd.GoToRecord , , acNewRec
End If
DoCmd.GoToControl "FirstName"

Exit_ComboStudents_Click:
Exit Sub

Err_ComboStudents_Click:
MsgBox Err.Description
Resume Exit_ComboStudents_Click

If you double click on the <Add New Student> you won't be able to use the FindRecord because it uses a false StudentID
to differentiate it from a "true" record. Split the 2 functions with an IF statement.

If Me!StudentID = 1 Then
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "FirstName"
Else
DoCmd.FindRecord Me!StudentID, acEntire, False, , False, , True
DoCmd.GoToControl "StudentID"
End If
Wayne Gillespie
Gosford NSW Australia

[Arnold]

Wayne--thanks for helping out again this weekend.

I included the IF statement, but the form still opens with the 2137
error. The code highlights this line:

DoCmd.FindRecord Me!StudentID, acEntire, False, , False, , True

[Wayne Gillespie]

On 29 Jan 2006 19:28:21 -0800, "Arnold" <ee*******@kc.rr.com> wrote:

Wayne--thanks for helping out again this weekend.

I included the IF statement, but the form still opens with the 2137
error. The code highlights this line:

DoCmd.FindRecord Me!StudentID, acEntire, False, , False, , True

Let's try replacing the FindRecord with searching frmStudents' recordsetclone for a matching record, and setting the
form's bookmarks if the record is found.

**untested**

Dim frm As Form

If Me!StudentID = 1 Then
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "FirstName"
Else
Set frm = Forms("frmStudents")
With frm.RecordsetClone
.FindFirst "[StudentID]=" & Me.StudentID
If Not .NoMatch Then
frm.Bookmark = .Bookmark
End If
End With
End If

Set frm = Nothing
Wayne Gillespie
Gosford NSW Australia

[Arnold]

That sovled it. Thanks A LOT Wayne.