Form Combo Box - Lastname, Firstname, MI

[Robert T]

We have approximately 4,500 students in tblStudents, and as you would
imagine, there are numerous students with the same last name.

I have a Find a Record combo box on a form where the user types in the
student's last name, hits the Enter key, and Access displays that student's
info. This works well unless there are 1 or more users with the same last
name. If the user types in the lastname and firstname, it still goes to the
first student with that last name.

The user can use the drop down box to select another student with the same
last name and that works. But it would be nice if the user could simply type
the last and first name into the combo box. There must be a way to properly
handle this. I'm guessing I'll have to chane the query as it's currently
constructed.

The current query has 4 columns:

1. StudentID [hidden]
2. StudentLastName
3. StudentFirstName
4. MI

Thanks,
Robert

 

[Robert T]

Please disregard this question, I figured it out for myself. I changed the
query by combining the lastname and firstname fields into one field with both
names separated by a comma and then a space. I feel lilke an idiot, I should
have tried that before posting my question.

Sorry about that,
Robert T

 

[John W. Vinson]

Please disregard this question, I figured it out for myself. I changed the
query by combining the lastname and firstname fields into one field with both
names separated by a comma and then a space. I feel lilke an idiot, I should
have tried that before posting my question.

Sorry about that,
Robert T

Well... there's actually a better solution yet. Your Table should NOT contain
this combined name!

You can instead base the combo box on a Query using a calculated, concatenated
field. Leave your table's FirstName and LastName fields alone, and create a
query

SELECT StudentID, LastName & ", " & [FirstName] & (" " + MI)

Note that you might well have duplicates even so - Dave Smith and Dave Smith,
the studious scholarly senior and the ne'er-do-well freshman who's about to
drop out respectively. You may need some other distinguishing piece of
information!

 

[RussCRM]

Could you give me some details as to how you set this up on your
form? Like, how do you get it to bring up the record when they hit
enter? This would be very useful to me!!!

 

[John W. Vinson]

Could you give me some details as to how you set this up on your
form? Like, how do you get it to bring up the record when they hit
enter? This would be very useful to me!!!

Assuming your fieldnames and a Primary Key named StudentID, create a query

SELECT StudentID, [LastName] & ", " & [FirstName] & (" " + [MI]) AS
StudentName FROM Students ORDER BY LastName, FirstName, MI;

Use this query as the rowsource of an unbound (nothing in its Control Source)
combo box named cboFindStudent on the form. Set the combo's ColumnCount to 2,
ColumnWidths to (say) 0"; 1.5" to conceal the id while displaying the name.
Make the Bound Column 1 so that the value of the combo is the ID.

In the combo's AfterUpdate event put code like

Private Sub cboFindStudent_AfterUpdate()
Dim rs As DAO.Recordset ' define a recordset object
Set rs = Me.RecordsetClone ' set it to the form's recordsource
rs.FindFirst "[StudentID] = " & Me!cboFindStudent
If rs.NoMatch Then ' shouldn't happen but protect yourself!
Msgbox "This student was not found! Must be a ghost.", vbOKOnly
Else
Me.Bookmark = rs.Bookmark ' jump to that student's record
End If
Set rs = Nothing ' clean up after yourself
End Sub

 

[Robert T]

Hello John:

You provided some excellent suggestions in your next message, but I'm
curious, what made you think our table contained a combined name? The table
has separate fields for StudentID, Student_LastName, Student_FirstName, and
Student_MI. Of course there are other fields, but they are irrelevant for the
Find Combo box on the form.

The calculated, concatenated field is in the query I created. In fact, the
combo field was always based on a query, however, the original query used the
separate fields listed above.

Now I'm going to read your other suggestions which sound very interesting.

Thanks,

Robert

 

[Robert T]

Russ:

1. Place your form in Design Mode.
2. On the ToolBox, drag something called a ComboBox to your form and draw a
rectangle with your mouse.
3. A Wizard should pop.
4. Select the third choice which is find a record on my form based on the
value I select in the Combo Box.

I think that explains the process. If it doesn't there are numerous examples
of this process on the web.

Good Luck,
Robert

 

[Robert T]

Hello John:

Wow! It must have been dumb luck, but that's essentially what I did
yesterday. The cboFindStudent_AfterUpdate() script is a little different, but
not by much. I guess I'm not as lacking in skills as I originally thought.

Thanks so much for the valuable input,
Robert T.

Could you give me some details as to how you set this up on your
form? Like, how do you get it to bring up the record when they hit
enter? This would be very useful to me!!!

Assuming your fieldnames and a Primary Key named StudentID, create a query

SELECT StudentID, [LastName] & ", " & [FirstName] & (" " + [MI]) AS
StudentName FROM Students ORDER BY LastName, FirstName, MI;

Use this query as the rowsource of an unbound (nothing in its Control Source)
combo box named cboFindStudent on the form. Set the combo's ColumnCount to 2,
ColumnWidths to (say) 0"; 1.5" to conceal the id while displaying the name.
Make the Bound Column 1 so that the value of the combo is the ID.

In the combo's AfterUpdate event put code like

Private Sub cboFindStudent_AfterUpdate()
Dim rs As DAO.Recordset ' define a recordset object
Set rs = Me.RecordsetClone ' set it to the form's recordsource
rs.FindFirst "[StudentID] = " & Me!cboFindStudent
If rs.NoMatch Then ' shouldn't happen but protect yourself!
Msgbox "This student was not found! Must be a ghost.", vbOKOnly
Else
Me.Bookmark = rs.Bookmark ' jump to that student's record
End If
Set rs = Nothing ' clean up after yourself
End Sub

 

[John W. Vinson]

You provided some excellent suggestions in your next message, but I'm
curious, what made you think our table contained a combined name?

I misread your second post, is all.

 

[David W. Fenton]

Assuming your fieldnames and a Primary Key named StudentID, create
a query

SELECT StudentID, [LastName] & ", " & [FirstName] & (" " + [MI])
AS StudentName FROM Students ORDER BY LastName, FirstName, MI;

In case there are blank first or last names:

Mid("12" + [LastName]) & (", " + [FirstName]) & (" " + [MI]), 3)

 

[RussCRM]

Thank you all for your help! It seems to be working well except that
I keep getting a random error message from time to time when I attempt
to go to a new name, new record or close the form.

The error messages are:

1) Error Number - 2147352567
"Update or CancelUpdate without AddNew or Edit"

2) Runtime Error: '3020'
"Update or CancelUpdate without AddNew or Edit"

This one gives me the "End" or "Debug" option, which takes me to
the Me.Bookmark = rs.Bookmark code.

Any ideas?

 

[RussCRM]

Thank you all for your help! It seems to be working well except that
I keep getting a random error message from time to time when I attempt
to go to a new name, new record or close the form.

The error messages are:

1) Error Number - 2147352567
"Update or CancelUpdate without AddNew or Edit"

2) Runtime Error: '3020'
"Update or CancelUpdate without AddNew or Edit"

This one gives me the "End" or "Debug" option, which takes me to
the Me.Bookmark = rs.Bookmark code.

Any ideas?

 

[John W. Vinson]

Thank you all for your help! It seems to be working well except that
I keep getting a random error message from time to time when I attempt
to go to a new name, new record or close the form.

The error messages are:

1) Error Number - 2147352567
"Update or CancelUpdate without AddNew or Edit"

2) Runtime Error: '3020'
"Update or CancelUpdate without AddNew or Edit"

This one gives me the "End" or "Debug" option, which takes me to
the Me.Bookmark = rs.Bookmark code.

Any ideas?

Please post your code.

 

[RussCRM]

Private Sub cboFindGuest_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![cboFindGuest], 0))
If rs.NoMatch Then ' shouldn't happen but protect yourself!
MsgBox "This guest was not found!", vbOKOnly
Else
Me.Bookmark = rs.Bookmark ' jump to that student's record
End Sub


Private Sub cboFindGuest_GotFocus()
cboFindGuest = ""
End Sub

Private Sub cboFindGuest_LostFocus()
cboFindGuest = ""
End Sub

 

[John W. Vinson]

Private Sub cboFindGuest_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![cboFindGuest], 0))
If rs.NoMatch Then ' shouldn't happen but protect yourself!
MsgBox "This guest was not found!", vbOKOnly
Else
Me.Bookmark = rs.Bookmark ' jump to that student's record
End Sub


Private Sub cboFindGuest_GotFocus()
cboFindGuest = ""
End Sub

Private Sub cboFindGuest_LostFocus()
cboFindGuest = ""
End Sub

Sounds like cboFindGuest may have something in its Control Source. It needs to
be unbound - nothing at all in its control source property.

I'd also replace both cboFindGuest = "" with cboFindGuest = Null.

 

[David W. Fenton]

m:

Private Sub cboFindGuest_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![cboFindGuest], 0))
If rs.NoMatch Then ' shouldn't happen but protect
yourself!
MsgBox "This guest was not found!", vbOKOnly
Else
Me.Bookmark = rs.Bookmark ' jump to that student's record
End Sub

A couple of things:

1. When using bookmark navigation, it's important to save changes
before moving to the new record.

2. it is completely senseless (in my opinion) to initialize a
recordset variable for an object that already exists when you run
your code.

So, taking these two things into account, I'd write the code like
this:

With Me.RecordsetClone
.FindFirst "[ID] = " & Str(Nz(Me![cboFindGuest], 0))
If Not .NoMatch
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
End If
End With

Also, you shouldn't use a clone of the form's recordset, but the
pre-existing .RecordsetClone of the form. For details on the
difference, see:

http://trigeminal.com/usenet/usenet022.asp?1033