If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add/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 |
#2
|
|||
|
|||
Add/Edit Records Via Combo Box
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 -- Steve Schapel, Microsoft Access MVP Alan wrote: 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 |
#3
|
|||
|
|||
Add/Edit Records Via Combo Box
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 On May 2, 5:41 am, Steve Schapel wrote: 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 -- Steve Schapel, Microsoft Access MVP Alan wrote: 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 |
#4
|
|||
|
|||
Add/Edit Records Via Combo Box
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. -- Steve Schapel, Microsoft Access MVP Alan wrote: 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. |
#5
|
|||
|
|||
Add/Edit Records Via Combo Box
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 On May 2, 4:30 pm, Steve Schapel wrote: 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. -- Steve Schapel, Microsoft Access MVP Alan wrote: Thanks for the quick reply, Steve. That was most helpful. While I appreciate your suggestion to keep the unboundcombobox 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. |
#6
|
|||
|
|||
Add/Edit Records Via Combo Box
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. -- Steve Schapel, Microsoft Access MVP Alan wrote: 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? |
#7
|
|||
|
|||
Add/Edit Records Via Combo Box
On May 3, 4:03 pm, Steve Schapel wrote:
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 need to do so. Access automatically saves updated data to the table when you close the form, or when you move to another record. -- Steve Schapel, Microsoft Access MVP Alan wrote: 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? |
#8
|
|||
|
|||
???
"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 -- Steve Schapel, Microsoft Access MVP Alan wrote: 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??,,,,,, |
Thread Tools | |
Display Modes | |
|
|