jw said:
Thank you for your response! As you suggested I was
confusing unbound and bound. What I would like to know
is how to allow the user to add a value to the list when
the list is based on a query (based on a table).
For my forms, generally I can adjust the value list
myself for the forms with the rowsource based on a value
list, becaues these change very infrequently.
If the recordsource is a query, the end user will
frequently want to add the new value to the list and it
is this I most want to automate.
If I can figure out how to have both of these situations
automated, that would be great! I would like to set it
up so the end user is asked if they would like to add the
value to the list or not. I think my usefulness would
improve if I knew how to do these two tasks.
I would like to check if I understood what you
suggested. I think you suggested the easiest way to add
new values permanently to the rowsource is to base the
rowsource on a table and to add the value to that table.
I currently have a query (based on a base table) as
rowsource. I could create a table from this query and
then delete the query but use the table as row source.
(As tables can have values added as needed.)
That's not exactly what I meant. The combo box's rowsource may be a
query, and provided that the query is updatable, you can treat the query
as though it were a table, for the purpose of adding an item to the
combo box. You don't have to create a table from the query or anything
like that. Furthermore, the query is still based on one or more tables,
and it's just as easy to add a new item directly to that table (or
tables) as it is to add items to the query. That's something you can do
even if the rowsource query itself is not updatable, so that's what I'd
recommend.
What would be involved in the more difficult way, to set
it up where the user is asked if they would like to add
the value to the list? Is this process the same for a
rowsource based on a query or table and a rowsource based
on a value list?
For most practical purposes you can't really add items to a value-list
rowsource and have them stick. That is, you can add them for the time
the form is open -- the example in the help file under "NotInList
Event - Event Procedures" shows this -- but after you close the form,
the next time you open it the values you added at run time will be gone
again. So you're better off sticking with RowSourceType="Table/Query"
for lists you want to be able to add to on the fly.
In adding items to a table-based list (and that includes query-based
lists), the main question is whether you need to add more information to
the table than the user has already typed. Consider the following
simple case:
Table: tblCountries
Field: CountryID (autonumber primary key)
Field: CountryName (text)
Combo box: cboCountry
RowSource: SELECT CountryID, CountryName
FROM tblCountries
ORDER BY CountryName;
BoundColumn: 1
ColumnWidths: 0"; 1.3"
LimitToList: Yes
The combo box stores the country ID, but displays the name of the
country.
This combo can easily have a country added in its NotInList event,
without having to open an edit form:
Private Sub cboCountry_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_Handler
Select Case _
MsgBox("The country '" & NewData & "' is not in the
list. " & _
"Do you want to add it?", _
vbQuestion + vbYesNo, _
"Not In List")
Case vbNo
Response = acDataErrContinue
Case vbYes
' Add the new country directly to the table.
CurrentDb.Execute _
"INSERT INTO tblCountries(CountryName) " & _
"VALUES(" & Chr(34) & NewData & Chr(34) & ")", _
dbFailOnError
' Tell Access we've added an item, so that it requeries
' the combo box.
Response = acDataErrAdded
End Select
Exit_Point:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbExclamation, "Unable to Add Item"
Resume Exit_Point
End Sub
That takes care of the simple case, where no additional information is
needed to add a new item.
When adding an item requires more information -- for example, other
required fields in the table where you want to insert the new record --
then what I do is open an edit form on that table in dialog mode, and
pass the NewData to that form in via the OpenArgs argument; e.g.,
'...
Case vbYes
' Open a form to add the country and other required
fields.
' This code will pause until the form is closed.
DoCmd.OpenForm "frmCountries", _
WindowMode:=acDialog, _
OpenArgs:="ADD=" & NewData
' Tell Access we've added an item, so that it requeries
' the combo box.
Response = acDataErrAdded
Code in that form's Open event checks the OpenArgs and sets up the form
appropriately to add the new item, leaving only the other required
fields for the user to fill in. By opening this form in dialog mode, I
make the code in the NotInList event pause until the user closes that
form, and then the code proceeds as usual.
