Proper using of Combobox

Proper using of Combobox

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?

date: Sat, 4 Oct 2008 04:11:00 -0700 author: Mishanya

Re: Proper using of Combobox

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".
-- 

        Ken Snell
<MS ACCESS MVP>


"Mishanya"  wrote in message 
news:91852B04-DF56-45C9-8C21-B4D5640888D9@microsoft.com...
>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?
>

date: Sat, 4 Oct 2008 11:08:23 -0400 author: Ken Snell \(MVP\) etl

Re: Proper using of Combobox

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?

date: Sat, 4 Oct 2008 10:10:01 -0700 author: Mishanya

Re: Proper using of Combobox

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.

-- 

        Ken Snell
<MS ACCESS MVP>



"Mishanya"  wrote in message 
news:5143EC73-C51F-4AA4-9C98-60B035188BCE@microsoft.com...
>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?

date: Sat, 4 Oct 2008 13:49:09 -0400 author: Ken Snell \(MVP\) etl