input also updates combo box table

  • Thread starter Thread starter Dave Eliot
  • Start date Start date
D

Dave Eliot

I have a database for my video collection. On the input form (for the
table called video) there's a field called "from" which I fill in from a
combo box that's populated from a table called studio. I do this for
consistency (i.e. 20th Century Fox vs Twentieth Century Fox). If I'm
entering a film from a studio that is not in the table, is there a way
that when I enter it into the "from" field of the video table, it can
also be added to the studio table?
 
On Wed, 19 Aug 2009 06:30:32 -0700, Dave Eliot <[email protected]>
wrote:

Yes. Check out the help page on the NotInList event.
In A2007 you have additional options; check the ListItemsEditForm
property.

-Tom.
Microsoft Access MVP
 
Yes. You use the combo's Limit To List property to cause the NotInList event
to fire. Limit To List has to be set to Yes. Then when the user enters a
value not already in the list, you give the user the option to either add the
item to the list and to your Studio table.

It will look something like this:

Private Sub cboStudio_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

On Error GoTo cboStudion_NotInList_Error

If MsgBox("Studion " & NewData & " Not Found" & vbNewLine & _
"Add This Studion?", vbYesNo + vbQuestion, "Add Studion") =
vbYes Then
strSQL = "INSERT INTO Studio ( StudioName ) SELECT " & NewData & """;"
CurrentDb.Execute(strSQL, dbFailOnError)
Me.cboStudion.Requery
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

cboStudion_NotInList_Exit:

Exit Sub
On Error GoTo 0

cboStudion_NotInList_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboStudion_NotInList of VBA Document Form_Form2"
GoTo cboStudion_NotInList_Exit
End Sub
 
Klatuu said:
Yes. You use the combo's Limit To List property to cause the NotInList event
to fire. Limit To List has to be set to Yes. Then when the user enters a
value not already in the list, you give the user the option to either add the
item to the list and to your Studio table.

It will look something like this:

Private Sub cboStudio_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

On Error GoTo cboStudion_NotInList_Error

If MsgBox("Studion " & NewData & " Not Found" & vbNewLine & _
"Add This Studion?", vbYesNo + vbQuestion, "Add Studion") =
vbYes Then
strSQL = "INSERT INTO Studio ( StudioName ) SELECT " & NewData & """;"
CurrentDb.Execute(strSQL, dbFailOnError)
Me.cboStudion.Requery
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

cboStudion_NotInList_Exit:

Exit Sub
On Error GoTo 0

cboStudion_NotInList_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboStudion_NotInList of VBA Document Form_Form2"
GoTo cboStudion_NotInList_Exit
End Sub
Click to expand...
Thanks -- just what I was looking for!
 
Back
Top