 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2008Add/Edit Records Via Combo Box
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 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  SignatureSteve Schapel, Microsoft Access MVP > I have at table of Students with the following fields: > [quoted text clipped - 18 lines] > > 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 > Alan, > [quoted text clipped - 42 lines] > > > Alan 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. SignatureSteve Schapel, Microsoft Access MVP > Thanks for the quick reply, Steve. That was most helpful. > [quoted text clipped - 8 lines] > use the LimitToList and NotInList controls to do this? Any specific > suggestions would be greatly appreciated. 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 > Alan, > [quoted text clipped - 25 lines] > > use the LimitToList and NotInList controls to do this? Any specific > > suggestions would be greatly appreciated. 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. SignatureSteve Schapel, Microsoft Access MVP > 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? > 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 > 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 [quoted text clipped - 8 lines] > > from field to field or do I need to include some sort of command > > button to save them? > Alan, > [quoted text clipped - 39 lines] > > > > Alan??,,,,,, |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.