-----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-----
eetarnold@kc.rr.com wrote:[color=blue]
> 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
>[/color]
