Proper using of Combobox

[Mishanya]

I have frmClientDetails based on tblClientDetails, where the field
cboClientName in the form is a ComboBox, based on the fldClientName in the
table. E.g. I want to be able to either look or update the details by picking
the already existing ClientName, or to add a new ClientName and all his
details.
In order to enable adding a new ClientName I try to use NotInList event of
the cboClientName in the form as follows:

Private Sub cboClientName_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
If i = vbYes Then
strSQL = "Insert Into tblClientDetails ([ClientName]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Well, it does not work - it does enable to add a new name, but it also adds
another record named by the ClientID automatic number wich was nominated to
the record I'd tried to add, and nominates it with the next automatic number.
So, by the end of the process I have 2 new ClientName-records in the
tblClientDetails (1 of wich is "fake") but still can not view them in the
frmClientDetails, as if there are no records.

I wonder, whether I have a basic mistake in ComboBox logic (e.g. it can not
be based on and store the new value in the same field, so I have to create an
additional tblClientName and base the fldClientName of the frmClientDetails
on it) or i just have to handle the NotInList event right?

 

[Ken Snell \(MVP\)]

Show us the RowSource query for the combobox. And the ControlSource for the
combobox.

Also, are you saying that your combobox's NotInList code is adding two
records to the tbClientList table? I'm not fully understanding your
reference to "two records".

 

[Mishanya]

I shall simplify my question:

Let's assume I have tblClientDetails with 3 fields: fldClientID (automatic
index), fldClientName (also uniqe-value field wich can not repeat) and
fldClientAddress.

I want to create a simple frmClientDetails with only 2 fields: cboClientName
and ClientAddress, where cboClientName is based on
tblClientDetails.fldClientName.

I want the form to be able:
1) To add a new ClientName (wich will be stored in tblClientDetails and
assigned automatic index number, unless it exists already, so the new record
will be rejected)
2) To choose an existiting ClientName from the cboClientName in order to
view or change it's ClientAddress.

1) Can I build it just like this or
2) Do I have to create a different tblClientNameList and then insert it to
the form and
3) If so, should I use additional query?

 

[Ken Snell \(MVP\)]

What you wish to do should be accomplished by the code setup that you posted
originally. I think the issue may be related to whether you want a new
record to start in the form when you add a new client name; is that the
problem that you're having -- that, when you add a new client name, that new
name replaces the name of the original client in the record? If this is not
the issue, then please do provide more details about exactly what is
happening on the form that is not good for you.