Add/Edit Records Via Combo Box

[Alan]

I have at table of Students with the following fields:

StuID (key)
FirstName
LastName
Address
Telephone

I'm designing a form and I have an unbound combo box control tied to a
query that provides a pick list of existing StuIDs sorted by LastName,
FirstName. What I want to be able to do is have the user pick an
existing Student from the combo box which causes the record to change
to the selected student with their FirstName, LastName, Address and
Telephone field values available for edited. Alternatively, if the
user types in a new StuID, the field values should be blank allowing
the user to create a new record for that StuID.

I've had limited experience with forms so please be specific when
discussing any event procedures that may be needed to accomplish my
goal. Thanks for any assistance.

Alan

 

[Steve Schapel]

Alan,

Here's some sample code for one (of a number of possible) approach, to
go on the After Update event of the unbound combobox:

Me.StuID.SetFocus
DoCmd.FindRecord Me.NameOfYourUnboundCombobox
Me.NameOfYourUnboundCombobox = Null

This assumes you have a StuID control in the bound data area of the form.

I would suggest you keep the unbound combobox exclusively for the
purpose of selecting records for existing students. It's certainly
possible to do so, but I don't like the idea of piggy-backing the
process of entering the StuID for a new record in here. Instead, I
would suggest a little command button, maybe alongside the combobox,
which the user can click to go to a new record, and then enter all the
data in there, including the StuID for the new record. The code on the
Click event of the button would be simply:
DoCmd.GoToRecord, , acNewRec

 

[Alan]

Thanks for the quick reply, Steve. That was most helpful.

While I appreciate your suggestion to keep the unbound combo box
exclusively for selecting records to edit, I really want the user to
be able to add new records with it as well. The reason is that my
StuID field is not an autono field but is instead comprised of the
first 3 letters of the LastName, the first 3 letters of the FirstName
and a numeric digit "tie-breaker." Therefore, I want the user to be
able to scan the existing records' StuIDs before inputting a new StuID
that avoids duplication. Can you offer any suggestions? Do I need to
use the LimitToList and NotInList controls to do this? Any specific
suggestions would be greatly appreciated.

Alan

 

[Steve Schapel]

Alan,

I don't quite understand. Why can't the user "scan" the existing
records in the combobox, and then if not found they can click the button
to open the blank form? If you like you can also do a double check when
the new record is being entered, for example in the Before Update event
of the StuID control:
If DCount("*","YourTable","StuID = '" & Me.StuID & "'") > 0 Then
MsgBox "There is already a " & Me.StuID & " in the database."
Cancel = True
End If
Though I assume StuID is the Primary Key field of the table, so
ultimately you wouldn't be able to duplicate anyway.

 

[Alan]

You're right of course, Steve. Sometimes I need procedural guidance. I
will put an add new student command button next to the combo box as
you originally suggested.

One last question: as the user edits fields in the records he/she has
pulled up via the combo box, will those changes "stick" as they move
from field to field or do I need to include some sort of command
button to save them?

Thanks again for all your help.

Alan

 

[Steve Schapel]

Alan,

The changes are not saved as you move from field to field, unless you
use macro or code to force this to happen, which there is probably no
need to do so. Access automatically saves updated data to the table
when you close the form, or when you move to another record.

 

[Alan]

Alan,

I ended up putting a couple of command buttons after the last field on
the form, one to allow the user to save and add another and one to
save and close. Everything is working perfectly. Thanks for all your
help, Steve.

Alan

 

[zhu]

Alan,

Here's some sample code for one (of a number of possible) approach, to
go on the After Update event of the unbound combobox:

Me.StuID.SetFocus
DoCmd.FindRecord Me.NameOfYourUnboundCombobox
Me.NameOfYourUnboundCombobox = Null

This assumes you have a StuID control in the bound data area of the form.

I would suggest you keep the unbound combobox exclusively for the
purpose of selecting records for existing students. It's certainly
possible to do so, but I don't like the idea of piggy-backing the
process of entering the StuID for a new record in here. Instead, I
would suggest a little command button, maybe alongside the combobox,
which the user can click to go to a new record, and then enter all the
data in there, including the StuID for the new record. The code on the
Click event of the button would be simply:
DoCmd.GoToRecord, , acNewRec