Editing Lookup Tables 1

[JJOHNS]

I have a combo box filled from a lookup table. The way it works is: To document medications administered, a nurse selects the particular medication from a combo box. If the medication is not on the list, then the nurse types it in. No problem. However, I would like that medication to be added to the lookup table so the next time it is administered, it appears on the list. Anyone know an easy way (or even a hard way) to do this?

 

[pdupreez]

Hi JJOHN
I found this in the solution.mdb from Microsoft (I can e-mail to you if you wish) Hope this will help

Private Sub CategoryID_NotInList(NewData As String, Response As Integer)

' Add a new category by typing a name in CategoryID combo box.

Dim intNewCategory As Integer, intTruncateName As Integer, strTitle As String, intMsgDialog As Integer

' Display message box asking if user wants to add a new category.
strTitle = "Category Not In List"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
intNewCategory = MsgBox("Do you want to add a new category?", intMsgDialog, strTitle)

If intNewCategory = vbYes Then
' Remove new name from CategoryID combo box so
' control can be requeried when user returns to form.
DoCmd.RunCommand acCmdUndo

' Display message box and adjust length of value entered in
' CategoryID combo box.
strTitle = "Name Too Long"
intMsgDialog = vbOKOnly + vbExclamation
If Len(NewData) > 15 Then
intTruncateName = MsgBox("Category names can be no longer than " _
& "15 characters. The name you entered will be truncated.", _
intMsgDialog, strTitle)
NewData = Left(NewData, 15)
End If

' Open AddCategory form.
DoCmd.OpenForm "AddCategory", acNormal, , , acAdd, acDialog, NewData

' Continue without displaying default error message.
Response = acDataErrAdded
End If


End Sub


THE HELP FILE CONTAINED THE FOLLOWING:

Add a New Record to a List


As you enter or edit records, you might need to add a new item to the list in a combo box. For example, on the EnterOrEditProducts form, you can add a new category to the list by typing the name in the combo box. The form displays a message box asking if you want to add a new record. If you choose Yes, it displays a form in which you enter the information for the new category.

Overview

Create an event procedure for the OnNotInList property of the CategoryID combo box on the EnterOrEditProducts form. (This event procedure displays a dialog box in which you can confirm that you want to add a new category; if you click Yes, it opens the AddCategory form.) Create an event procedure for the OnClose event of the AddCategory form that uses the Requery method to requery the CategoryID combo box on the EnterOrEditProducts form.

Objects Used in This Example

Object Name

Table Products, Categories
Form EnterOrEditProducts, AddCategory
Module ModIsLoaded


Step by Step

1 On the EnterOrEditProducts form, create an event procedure in the OnNotInList property of the CategoryID combo box.
2 Create the modIsLoaded module, if you haven't already done so.

a) Create the IsLoaded functionIsLoadedFunctionX>example.

3 Create the AddCategory form.

a) Set form properties and create event procedures as follows.

Property Setting

RecordSource Categories
Caption Add Category
DefaultView Single Form
ViewsAllowed Form
DataEntry Yes
ScrollBars Neither
RecordSelectors No
NavigationButtons No
AutoResize Yes
AutoCenter Yes
OnActivate [Event Procedure]


b) In the detail section, add the CategoryName, Description, and Picture fields from the Categories table.

Cross-references

For more information on the following subjects, see Building Applications with Microsoft Access 97.

Subject Chapter

Creating event procedures Chapter 2, "Introducing Visual Basic"
Working with forms Chapter 3, "Using Forms to Collect, Filter, and Display Information"

 

[JJOHNS]

Thanks pdupreez,

Your answer is good for Access 2000, but I'm using Access 97. Thank goodness. I figured out a pretty simple way to do it. I abandoned the lookup table and started pulling my list from the field on the form. Then, AfterUpdate, I requery the field. That updates the list as soon as the information is entered. One line of code and it works great.