auto add a new record to a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I got a combo on my form. Its source is a Join-Type Query.
I want to be able to enter values which are not in list, but when i do so -
i get a message that "you cannot add or change the record because a related
record is required in the table 'Numbers' "

Can i somehow auto add a new record to the table 'Numbers' using the value
entered into my combo? What shall be the code for that? Shall I put it on
"NotInList Event" or is there any other simple solution to this?

Would be greatfull for any help as i am completely lost here.

Lana
 
It sound like you combo is a bound control. This is typical behaviour when a
combo is a bound control. What is happening is you have now changed the
value in the combo box and you are trying to create a new record. Because
the value in the combo has changed, it is trying to update the current record
to the table, but it is trying to use to new value in the combo.

There are two posssible solutions. One is to make the combo an unbound
control used only for searching. Create a text box that is the bound control
for the field and make it invisible. Then, each time you add a new record or
change the value in the combo for the current record, you will need to
populate the hidden text box with the value of the combo.

The other way is to put code in your combo that will replace the new value
of the text box with the old value of the text box before you move from the
current record.

Here is an example of how you can do this in the Not In List event of the
combo:

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
'Replace the Original Value of the control so it will update properly
Me.cboActivity = Me.cboActivity.OldValue
'Creates the new record in the table
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
'Requery the table so we can find it
Me.Requery
'Make the new record the current record
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub
 
Back
Top