Add to table from a form

[tonysegerdahl]

Hi I have a projekt including "products" and "Vendors" where each
vendor got several products. I have created a form where the user can
seach for product information by typing or klicking in a dropdownlist
containing for example the different names of the vendors e.t.c. I also
created a form where the user can add new products by for example
typing the new product name in a textbox and then see if the vendor
exists in the data base by klicking at a combobox-dropdown list for
vendors. If the vendor exist and is clicked the info about "adress",
"telephonenumber" and " hompage" apears in respectivevly field in the
form. I would like to make it possible both to type new data (if vendor
does not exist in the database) or choose from the dropdown list (if
vendor allready exists in the database). Now, the problem is that when
choosing the dropdown list a errormessage apeares stating " the changes
in the table was not executed because it would doublicate index,
primarykeys or relations" but i do not want to add new data to the
table but simple state - " the vendor for this product is this one and,
keep it in the table as it is" and if not type the new vendor by hand
in the combobox window and then it would be added in the table. I have
been able to make it work without errormessages but then the info added
in the form wasn`t saved in the tables and couldn`t of course be found
when serching for it from the other form. Do you understand the
problem? what shall I do to make it work??

 

[Barry Gilbert]

A better approach would be to use the NotInList event on the combobox.
Set the combobox's LimitToList to True and add code similar to this in
the NotInList event:

Private Sub Vendors_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me.cboVendors
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
Response = acDataErrAdded
'code here to insert new value into table.
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

HTH,
Barry