473,837 Members | 1,592 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Complex ComboBox Functions for Teachers

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 "frmProgressRep orts" based on a record source
"qryStudent s," which has StudentID, FullName: [FirstName] & " " &
[LastName], LastName, and FirstName.

In frmProgress is an unbound combobox, "comboStudents, " which also has
the row source: SELECT qryStudents.Stu dentID, qryStudents.Ful lName,
qryStudents.Las tName, qryStudents.Fir stName FROM qryStudents ORDER BY
qryStudents.Las tName;

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 frmProgressRepo rts to the right record,
and sets the focus to a control on a subform in frmProgress called
subProgressRepo rts, 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

Jan 24 '06 #1
12 3503
On 23 Jan 2006 16:38:10 -0800, "ee*******@kc.r r.com" <ee*******@kc.r r.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 "frmProgressRep orts" 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.Stu dentID, qryStudents.Ful lName,
qryStudents.La stName, qryStudents.Fir stName FROM qryStudents ORDER BY
qryStudents.La stName;

ComboStudent s 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
comboStudent s 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,
comboStudent s refreshes, moves frmProgressRepo rts to the right record,
and sets the focus to a control on a subform in frmProgress called
subProgressRep orts, 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_A fterUpdate()

If Me.comboStudent s = -999 Then
DoCmd.OpenForm "frmStudent s", , , , acFormAdd
Else
With Me.RecordSetClo ne
.FindFirst "[StudentID]=" & Me.comboStudent s
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

Me.comboStudent s = 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 frmProgressRepo rts 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
Jan 24 '06 #2
-----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_Aft erUpdate()

' Assumes that Column(0) is the Bound column
If Not IsNull(Me!cboSt udents) 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 "frmStudent s", acNormal, _
DataMode:=acFor mAdd, WindowMode:=acD ialog
Else
Me.RecordSource = "SELECT * FROM Students WHERE StudentID=" & _
Me!cboStudents
Me!subProgressR eports!MyContro l.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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQ9WJj4echKq OuFEgEQIbWQCeNC QS+gYmAoRTyxyuw NWxasUYAN4An0oJ
WKCb7N6b6jhc+ox C1YvQnaN4
=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 "frmProgressRep orts" based on a record source
"qryStudent s," which has StudentID, FullName: [FirstName] & " " &
[LastName], LastName, and FirstName.

In frmProgress is an unbound combobox, "comboStudents, " which also has
the row source: SELECT qryStudents.Stu dentID, qryStudents.Ful lName,
qryStudents.Las tName, qryStudents.Fir stName FROM qryStudents ORDER BY
qryStudents.Las tName;

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 frmProgressRepo rts to the right record,
and sets the focus to a control on a subform in frmProgress called
subProgressRepo rts, 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

Jan 24 '06 #3
"ee*******@kc.r r.com" <ee*******@kc.r r.com> wrote in
news:11******** **************@ g49g2000cwa.goo glegroups.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 "frmProgressRep orts" based on a
record source "qryStudent s," which has StudentID, FullName:
[FirstName] & " " & [LastName], LastName, and FirstName.

In frmProgress is an unbound combobox, "comboStudents, " which
also has the row source: SELECT qryStudents.Stu dentID,
qryStudents.Ful lName, qryStudents.Las tName,
qryStudents.Fir stName FROM qryStudents ORDER BY
qryStudents.Las tName;

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
frmProgressRepo rts to the right record, and sets the focus to
a control on a subform in frmProgress called
subProgressRepo rts, 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.
Jan 24 '06 #4
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

Jan 24 '06 #5
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

Jan 24 '06 #6
On 24 Jan 2006 04:28:19 -0800, "Arnold" <ee*******@kc.r r.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
comboStudent s. 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
Jan 24 '06 #7
Thanks Wayne,

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

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

Is this part of the problem?

Jan 24 '06 #8
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_D blClick(Cancel As Integer)

DoCmd.OpenForm "frmStudent s", acNormal, "", "", , acNormal
DoCmd.GoToContr ol "StudentID"
DoCmd.FindRecor d Me!StudentID, acEntire, False, , False, , True
If Me!StudentID = 1 Then
DoCmd.GoToRecor d , , acNewRec
End If
DoCmd.GoToContr ol "FirstName"

Exit_ComboStude nts_Click:
Exit Sub

Err_ComboStuden ts_Click:
MsgBox Err.Description
Resume Exit_ComboStude nts_Click

Jan 30 '06 #9
On 29 Jan 2006 15:54:08 -0800, "Arnold" <ee*******@kc.r r.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_D blClick(Cancel As Integer)

DoCmd.OpenFo rm "frmStudent s", acNormal, "", "", , acNormal
DoCmd.GoToContr ol "StudentID"
DoCmd.FindRecor d Me!StudentID, acEntire, False, , False, , True
If Me!StudentID = 1 Then
DoCmd.GoToRecor d , , acNewRec
End If
DoCmd.GoToContr ol "FirstName"

Exit_ComboStud ents_Click:
Exit Sub

Err_ComboStude nts_Click:
MsgBox Err.Description
Resume Exit_ComboStude nts_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.GoToRecor d , , acNewRec
DoCmd.GoToContr ol "FirstName"
Else
DoCmd.FindRecor d Me!StudentID, acEntire, False, , False, , True
DoCmd.GoToContr ol "StudentID"
End If
Wayne Gillespie
Gosford NSW Australia
Jan 30 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

22
3821
by: TC | last post by:
I have an Access database application with a lot of custom row functions written in VBA. In other words, a lot of queries contain calculated fields which use functions defined in the modules. I am now thinking about upgrading the database from Access to SQL Server. If I do, how can I implement the custom row functions? Is Visual Basic integrated with SQL Server just as it is with Access? Or does T-SQL in SQL Server offer the...
6
1268
by: Sam | last post by:
Hi, I have a datagrid which has (amongst other stuff) 2 comboboxes columns. So far so good. The trick is that when I select a value in my first column, it must updates the items of the combobox in the second column for the same row. In the first column, comboboxes contain names of tables. In the second column, comboboxes contain names of the fields in the table selected in the first column.
11
6624
by: Martin | last post by:
Hi all! In VB6.0 A combobox had items, which basically were the description and ItemData which could be used to create a link to a record, that is if the recordset had a numeric key. I was hoping that in VB2005 this would have been changed to an alphanumeric itemdata, so that no longer need to create an array "on the side" to store the key values. But I can't even find the itemdata anymore.
4
5549
by: Vish | last post by:
Hi, I need to make the text on a combobox that is disabled to be drawn with a black color. I was not able to find any help on this online. The drawItem seems to apply only for the dropdpwn items. Any ideas or sugesstions would be appreciated. Thank you,
12
2769
by: vj | last post by:
Hi! I have a piece of code (shown below) involving complex numbers. The code is not running and giving error ("Invalid floating point operation" and "SQRT:Domain error"). I would be very thankful if someone can tell me where is the problem. I am aware that my code is far from being efficient and organized, and also there are many extra #include statements not really required for the code. I am a novice programmer, as you can see ! At...
0
1410
by: Tommaso Caldarola | last post by:
2.0 - No Typed DataSet. My custom object implements all the needed interface (IList, IBindingList, ITypedList) in order to manage complex binding. With DataGridView control I have no problem. I have encountered problems when I try using data bound ComboBox, namely if I set combo.DataSource = OrderTypeCollection; (of OrderType class e.g.)
2
2229
by: Richard Hollenbeck | last post by:
I originally wrote my grades program in MS-Access to help community college teachers in the California community colleges keep track of their students' grades and produce reports for the students to know how they are doing in the semester and to submit reports to the school at the end of the semester. I later discovered many flaws my program's design and in my own skills as a programmer. I would like to put this program "out there" so...
1
9568
by: perroe | last post by:
Hi I have a array of complex numbers that are stored in a simple double array. This is done since the array is part of an wrapper for an external C library, and the imaginary part of the first element, and the last element are known to be 0. I've implemented a -operator that returns a ComplexReference object that basically maps a complex<doubleinto the storage used in the array. What I would like to do is using the ComplexReference...
55
3047
by: rhino | last post by:
I need to learn more about making two kinds of menus: A. menus that have hierarchies of three or four levels B. horizontal menus that have so many options that they are wider than the available space Can anyone direct me to articles, examples, or other resources illustrating how to do this? I'd prefer to stay with CSS-only solutions but would consider solutions that use Javascript if that is the only realistic way to do what I want to...
0
9851
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10902
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10583
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9420
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5680
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4481
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4060
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3128
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.