Adding to a Look up table...

[Mari]

Hello

I have a relational database including look up tables (i.e. departments for employees). On the form, I am then able to "pick from list" of the available departments. Is there any way in which another entry can be entered "on the fly" with a caption such as "add new" to append the look up table via the form

Thank you in advance

Mari

 

[Allen Browne]

If your lookup tables contain just the text (not a hidden autonumber field)
and you only need to add the one field, you can use the NotInList event of
the combo to add the new value. Example in article:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

If you need more than that, you might want to use the DblClick event of the
combo to open the form where you enter the new department information. Then
in the AfterUpdate event of the departments form, requery the combo so the
new value shows up.

In a sizeable application where there are lots of combos, it takes a bit of
work to make sure they all work like this, but it's worth the effort: users
love the ability to work non-procedurally and just modify the lookup lists
"on the fly" like that. What we do is create a function named (say)
"NotifyCombos" in a standard module. Every form's AfterUpdate and
AfterDelConfirm events call this code. Towards the end of the development
process, we code a huge Select Case block in this routine that handles all
the dependencies.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a relational database including look up tables (i.e. departments

for employees). On the form, I am then able to "pick from list" of the
available departments. Is there any way in which another entry can be
entered "on the fly" with a caption such as "add new" to append the look up
table via the form?

 

[Scott McDaniel]

I often use a UnionQuery to do this:

SELECT 0,"<Add New Department>" FROM tblDepartments UNION SELECT lngDeptID,
strDeptName FROM tblDepartments

In the AfterUpdate event of your combo, use a select case:

dim strDept As String

Select Case Me.Combo1.Column(0)
Case 0
strDept = InputBox("Enter a name for the new Department: ", "Adding
New Department")
<you may want code here to verfiy the entry is unique>
CurrentProject.Connection.Execute "INSERT INTO
tblDepartment(strDeptName) VALUES(' " & strDept & " ')"
Combo1.Requery
Case Else
End Select

Cavaet: Your tblDepartment ID field must NOT contain a 0, and must not be
able to insert the value of 0 for a new row.

Note that you'll have to change the field/table names in the above string to
match your own.

Hello

I have a relational database including look up tables (i.e. departments

for employees). On the form, I am then able to "pick from list" of the
available departments. Is there any way in which another entry can be
entered "on the fly" with a caption such as "add new" to append the look up
table via the form