use a form to add a new record to a table

[Jess]

I have construted a form to edit records in a table. I use an unbound combo
box to navigate through the records. The combo box LimitToList is set to
Yes. If the value entered is not in the list, I want to be able to add the
record to the table. I have tried the NotInList Event. I cannot figure out
the code to use to add a new record to the table. Please help

 

[fredg]

I have construted a form to edit records in a table. I use an unbound combo
box to navigate through the records. The combo box LimitToList is set to
Yes. If the value entered is not in the list, I want to be able to add the
record to the table. I have tried the NotInList Event. I cannot figure out
the code to use to add a new record to the table. Please help

There are several methods. Here is one which will add a new city to
the txtCity field in the tblCity table.

Try it this way in the City NotInList event.
Change City to whatever the actual name is of your combo box.

On Error GoTo Err_City_NotInList
Dim IntResponse As Integer
' Prompt user to verify they wish to add new value.
IntResponse = MsgBox("You entered a City which is not in the list." &
vbNewLine _
& "Do you wish to add this City? Y/N ", vbQuestion + vbYesNo +
vbDefaultButton2, _
"City Not Listed")
If IntResponse = vbYes Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to City table
NewData = StrConv(NewData, vbProperCase) 'This line is Optional

CurrentDb.Execute " INSERT INTO tblCities(txtCity) SELECT " &
chr(34) & NewData & chr(34) & ";",dbFailOnError

Me!City = NewData
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
Me!City = Null
End If

Exit_City_NotInList:
Exit Sub

Err_City_NotInList:
If Err = 2113 Then
Err = 0
Resume Next
Else
MsgBox Str(Err) & " " & Err.Description
Resume Exit_City_NotInList
End If

 

[B. Comrie]

This will do it.....

In the not in list event.... Change "Customer(s)" to reflect your own Table
and Field names.

Private Sub MyComboBox_NotInList(NewData As String, Response As Integer)
Beep
MyComboBox = AddToList(NewData, "Customers", "Customer", True)
Response = acDataErrContinue
End Sub

this function can be called from any ComboBox. Place it in a Separate Module
in the Modules Window.


Public Function AddToList(ByVal NewData As String, ByVal TableName As
String, ByVal FieldName As String, Optional PromptUser As Boolean) As Long
On Error GoTo errTrap
Dim rs As New ADODB.Recordset
Select Case PromptUser
Case True
If MsgBox("Add this data to the list?", vbYesNo, "Not In List") = vbNo Then
Screen.ActiveControl.Text = ""
Exit Function
End If
Case False
'Do Nothing
End Select
Dim SQL As String
SQL = "Select * From [" & TableName & "]"
rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With rs
..AddNew
..Fields(FieldName) = StrConv(NewData, vbProperCase)
AddToList = !CustomerID
..Update
Screen.ActiveControl = AddToList
Screen.ActiveControl.Requery
End With
Exit Function
err_Exit:
If rs.State = 1 Then rs.Close
Set rs = Nothing
errTrap:
Resume err_Exit
'Error Handling Code Goes Here
End Function
'/////////////////
B Comrie
http://www.codewidgets.com

 

[Jess]

Thank you both so much for your help. Its working with a few modifcations.
This is definitely something I need more practice with.

Thanks again,
Jess

This will do it.....

In the not in list event.... Change "Customer(s)" to reflect your own Table
and Field names.

Private Sub MyComboBox_NotInList(NewData As String, Response As Integer)
Beep
MyComboBox = AddToList(NewData, "Customers", "Customer", True)
Response = acDataErrContinue
End Sub

this function can be called from any ComboBox. Place it in a Separate Module
in the Modules Window.


Public Function AddToList(ByVal NewData As String, ByVal TableName As
String, ByVal FieldName As String, Optional PromptUser As Boolean) As Long
On Error GoTo errTrap
Dim rs As New ADODB.Recordset
Select Case PromptUser
Case True
If MsgBox("Add this data to the list?", vbYesNo, "Not In List") = vbNo Then
Screen.ActiveControl.Text = ""
Exit Function
End If
Case False
'Do Nothing
End Select
Dim SQL As String
SQL = "Select * From [" & TableName & "]"
rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With rs
..AddNew
..Fields(FieldName) = StrConv(NewData, vbProperCase)
AddToList = !CustomerID
..Update
Screen.ActiveControl = AddToList
Screen.ActiveControl.Requery
End With
Exit Function
err_Exit:
If rs.State = 1 Then rs.Close
Set rs = Nothing
errTrap:
Resume err_Exit
'Error Handling Code Goes Here
End Function
'/////////////////
B Comrie
http://www.codewidgets.com

I have construted a form to edit records in a table. I use an unbound combo
box to navigate through the records. The combo box LimitToList is set to
Yes. If the value entered is not in the list, I want to be able to add the
record to the table. I have tried the NotInList Event. I cannot figure out
the code to use to add a new record to the table. Please help