Edit Combo Box Values and Save Changes

[alexbel]

Hi,

I have a combo box on a form that lists Item Classifications:

Math
Science
Writing

I want to be able to select, say math, and be able to edit it and then save the changes.

Here's the row source of the combo box:

SELECT DISTINCT [tblICL 1].[ICL 1], [tblICL 1].ItemID2 FROM [tblICL 1] WHERE [ICL 1] Is Not Null And [ICL 1]<>"None";

Here's its control source:

ItemID2

Here's its other information:

Column Count: 1
Column Widths:
Bound Column: 2
List Rows: 8
Limit to List: Yes

How can I edit values in this combo box and then save the changes?

Thank you

 

[Schof]

I don't think you can do it because you have two columns in your combobox. Are both [ICL 1] and ItemID2 required fields in the [tblICL 1] table? If not, you might be able to change the combobox to make the required field displayed first and make sure you change the comboboxes bound column property. Then you could use the comboboxes _NotInList event to capture a new entry, add a new record in the [tblICL 1] table (but would be missing the second column value), and finally cancel the response to the user that the item is not in the comboboxes list. Make sense?

 

[willir]

alexbel

Normally, a combo or list box get their data from...
- Value list (where the list is hard coded). The idea here it to a) minimize typing by the end user; b) ensure the end user only selects specific entries as determined by the developer; c) avoid typo's and inconsistent typing - ex. Mr. and Mr or Ms. and Ms, etc.
- Grab a key value such as a foreign key from an associated table and use it to link to the associated / related table for the current record.
- Find a record.

...None of these reasons suggest there is a need to "add" or "change" the value in the combo box.

However, there are very valid reasons to add to a list. For example, you are categorizing types of Absentees - SICK, VAC. But you need to add a new one - BERIEVEMENT. There are several approaches to this. Look up "Call Back" functions.

Also, provided you have setup your SELECT statement correctly, you can choose not to use any of the options presented in the list. Using column 2 as your bound column may present problems for this approach.

...BUT none of these approaches let you EDIT the value.

So here is one more approach.
Add a small command buttun next to your combo box, and use the command button wizard to open up the form for the record source to the specific record. Then use a ReQuery afterwards to reflect the changes. This approach works in most cases except if you break data integrity rules (such trying to change a primary key).

Richard

 

[TheAceMan1]

How are ya alexbel . . . . .

[blue]I want to be able to select, say math, and [purple]be able to edit it and then save the changes[/purple][/blue]

Your question is wide open for interperation. save the edited values in the Combobox or the record?

See Ya! . . . . . .

 

[KenReay]

Hi

The following from FAQ on this forum may help

Combobox - Add New Item to List
faq702-4283

Adding new entries to a Combo Box based on a query or table.

Say you have a combo box with entries taken from a table or query, where you wish to limit the user to choosing entries from the list, but allow the user the option to add new entries to the list.

Assumptions:

the combo box to be added to is called cboLastName.

The form to Maintain the table (or query) on which cboLastName is based is called frmPeople.

For cboLastName,

LimitToList : Yes

In the NotInList Event put code so:


Private Sub cboLastName_NotInList(NewData As String, Response As Integer)
If MsgBox("Add to List?", vbYesNo, "Warning") = vbYes Then
DoCmd.OpenForm "frmPeople", acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
End If
End Sub


Ie ask the user if they want to make a new entry

If yes, open the appropriate form, as a modal form, in Add record mode, and pass the entered data (NewData) to the form frmPeople

On returning from the form frmPeople, inform combo box that the data was added.

For the Form frmPeople

In the Form Open Event extract the data passes using the OpenArgs property:


Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) Then
Else
cmdAddNew_Click
Lastname = Me.OpenArgs
End If
End Sub


Ie Extract the passed data from the ‘Sending’ form

Make a new record (using standard wizard code as shown below), and populate the new data


Private Sub cmdAddNew_Click()
On Error GoTo Err_cmdAddNew_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmdAddNew_Click:
Exit Sub

Err_cmdAddNew_Click:
MsgBox Err.Description
Resume Exit_cmdAddNew_Click

End Sub


but like AceMan I am not entirely clear on what you are trying to do

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK