Adding records to a lookup table through a combo box (1 Viewer)

[yahazim]

I'm creating a database to help automate generation and tracking of ticket numbers for a help desk database.

I have three tables:
* Ticket table (ticket_ID = PK)
* Applications table (App_ID = PK)
* Vendor table (Vendor_ID = PK)

My primary keys are Autonumbers, and, the Vendor table is a lookup table for the Applications table, which in turn, is a lookup table for the Ticket table.

Ticket (M:1) Application
Application (M:1) Vendor

A ticket comes in, the Help Desk Associate generates a new ticket number, and clicks the combo box to find the referring application.

The combo box is setup with two columns (App_ID & App_Name, respectively). App_ID is the bound column, of course, and hidden from view so not to confuse the Help Desk Associate with "undefined numbers" by making the column width 0". I cannot set "Limit to List" to NO because of this factor.

Here are my questions:

#1) If the Application is not listed in the combo box, how can I setup the combo box to accept a new entry into the Application lookup table?

#2) Can I do this WITHOUT referring to another form for sake of simplicity, having all the referring Application fields listed on the Ticket Form; so that if a NEW entry is typed into the combo box, the fields referring to the application CLEAR so that the new entry can take place?

How can I do this with the combo box setup with the Bound Column 0"? Remember, Limit to List is set to YES because of this and cannot be changed.

I have the relationship Application:Vendor setup the same way, as the person entering in a NEW APPLICATION will of course have the possibility of entering in a NEW VENDOR.

THANK YOU IN ADVANCE! ANY SUGGESTION IS APPRECIATED!

Jim Meyer
NMCI Information Strike Force
EDS, Inc.

 

[charityg]

Private Sub cmbApp_NotInList(NewData As String, Response As Integer)
' Ask the user whether to add a value to the list.

Dim strMessage As String
Dim dbsHelpDesk As Database
Dim rstApp As Recordset

strMessage = "Do you want to add '" & NewData & _
"' to the Application list?"

If Confirm(strMessage) Then

' Open the Applications table and add the NewData value.
Set dbsHelpDesk = CurrentDb()
Set rstApp = dbshelpdesk.OpenRecordset("Applications")
rstApp.AddNew
rstApp!Applicaton = NewData
rstEmployees.Update
cmbApp = rstApp!AppID

Response = acDataErrAdded ' Requery the list.
Else
Response = acDataErrDisplay ' Display the error.
End If

End Sub

not debugged but should work!

 

[omccabe]

Hi I came across your code when trying to search for this problem I was wondering if you could explain some of the code for me as there are parts I don't understand how they work.
I don't understand:
rstApp!Application = NewData
rstEmployees.Update
cmdApp = rstApp!AppID

Any help would be appreciated thanks