Add new item in Combo

  • Thread starter Thread starter Samantha
  • Start date Start date
S

Samantha

I am using MsAccess 2K as the front end and MySQL as the backend.
I have a combo box in a Datasheet form which is bound to a table so that new
data can be added and updated. The combo's rowsource is from a query and
it's Limit To List property is set to No so that the user can have the option
to choose from the list or just type new items. Everything works fine if the
user selects an item from the list. I have added some DLookUp statements to
the AfterUpdate event of the combo box.
But where it goes wrong is when the user types in something NOT on the
list. In this case, I can type a new item, but once the user moves out of the
new record, all the fields have "#DELETED" values, even if I press on the
Save button. But if I close the form and re-open it, the values display fine
and the "#DELETED" are gone.
Is there any properties that I'm not setting correctly?
Any pointers are very much appreciated. Thanks in advance.
 
The better way to do this is to set the Limit To List property to Yes.
Then use the Not In List event to add the new record. Here is an example:

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
With Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

What will happen is the if the user enters a value not in the list, she will
get a message stating the record doesn't exit and be given the option to
either add the record or not.

If she chooses to add the record, the new record will be added to the table
with only the primary key field in the record. Then the new record will
become the current record so the new information can be added to the table.
 
Thanks Dave for the quick response. I actually do not want the new items to
be added. But, I found out what my problem was. It was the dlookup
statements that caused the error. I added the isnull function and it works
fine.
Thanks again for your time. This post really rocks!
Samantha

Klatuu said:
The better way to do this is to set the Limit To List property to Yes.
Then use the Not In List event to add the new record. Here is an example:

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
With Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

What will happen is the if the user enters a value not in the list, she will
get a message stating the record doesn't exit and be given the option to
either add the record or not.

If she chooses to add the record, the new record will be added to the table
with only the primary key field in the record. Then the new record will
become the current record so the new information can be added to the table.
--
Dave Hargis, Microsoft Access MVP


Samantha said:
I am using MsAccess 2K as the front end and MySQL as the backend.
I have a combo box in a Datasheet form which is bound to a table so that new
data can be added and updated. The combo's rowsource is from a query and
it's Limit To List property is set to No so that the user can have the option
to choose from the list or just type new items. Everything works fine if the
user selects an item from the list. I have added some DLookUp statements to
the AfterUpdate event of the combo box.
But where it goes wrong is when the user types in something NOT on the
list. In this case, I can type a new item, but once the user moves out of the
new record, all the fields have "#DELETED" values, even if I press on the
Save button. But if I close the form and re-open it, the values display fine
and the "#DELETED" are gone.
Is there any properties that I'm not setting correctly?
Any pointers are very much appreciated. Thanks in advance.
Click to expand...
Click to expand...
 
Back
Top