ComboBox, OnNotinLIst and Adding additional info

Bobbye R · May 23, 2008

I have a Client form with 3 fields. cboName, txtAddress, txtID
When I go to a blank record to type in a new client. OnNotInList I'm asked
if I want to add the new client, I select yes. When I try to add the
additional info on the form and close the form I'm told the changes will not
be successful because it will cause a duplicate record. I undo the current
record and when I look in my combobox the new name I entered is there.

I simply want the user to be able to look at the combo listing to be sure
the client(s) aren't already listed, add a new client(s) and their info,
save the record and have the new client listed in the combo box. --

Access 2000 User, Thanks in advance Bobbye

strive4peace · May 23, 2008

NotInList
~~~

Hi Bobby,

if the client is already there, the NotInList event will not fire ...
unless you are not requerying the combo when new records are added by
other processes

here is 'shell' NotInList code I use -- assuming you are actually
storing a ClientID that is a long integer/autonumber and client name is
what shows...

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub ControlName_NotInList( _
NewData As String, _
Response As Integer)

'assumption:
'the combobox controlname is
'RecordID_controlname
'and its first column (hidden)
'is the Autonumber record ID for the source table

Dim s As String _
, mRecordID As Long _
, mText As String

'if NewData needs to be parsed,
'seperate it and
'modify the SQL accordingly

'~~~~~~~~~~~~~~~~~~~~~~~~
'Choose ONE of these code blocks

'--------------------------------------------------------

'if you want to convert to ProperCase
'mText = StrConv(NewData, vbProperCase)

's = "INSERT INTO Tablename(SomeName) " _
& " SELECT '" & mText & "';"

'---------------
'if data is text

s = "INSERT INTO Tablename(SomeName) " _
& " SELECT '" & NewData & "';"

'---------------
'if data is numeric
'
s = "INSERT INTO Tablename(SomeName) " _
& " SELECT " & NewData & ";"

'--------------------------------------------------------
'~~~~~~~~~~~~~~~~~~~~~~~~

'comment or remove next line after this works correctly
Debug.Print s

CurrentDb.Execute s

CurrentDb.TableDefs.Refresh
DoEvents

mRecordID = Nz(DMax("RecordID", "Tablename"),0)

If mRecordID > 0 Then
'this does a requery
Response = acDataErrAdded

'assuming the first column of the listbox
'is the RecordID, RecordID and is a Long Integer

me.ActiveControl= mRecordID
Else
Response = acDataErrContinue
End If

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

WHERE
- Tablename is the name of the lookup table
- SomeName is the name of the field with the text or numeric values to
display
- RecordID is the name of the ID field in the table
- RecordID_controlname is the NAME property of the control on your form
with RecordID

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*

have an awesome day

*

Bobbye R · May 23, 2008

Thanks for your response. I'll have to study it a while as I'm not very good
at this. Getting the ID and the name isn't really my problem. It's getting
the other information onto the form e.g. (address and account number, etc) at
the time that I add it. When I enter a new name on the form and am asked if
I want to add it I say yes and I'm unable to input the rest of the info for
the client unless I close the form and reopen it. At that point I can select
the new client from the combobox and put the rest of the info in.

strive4peace · May 23, 2008

Hi Bobbye ,

sounds like your form has more than one table in its RecordSource and
you are trying to add a related record at the same time as the parent
record which must come first...

each form/subform should be based on just one table

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*

have an awesome day

*

Bobbye R · May 23, 2008

My form is very simple and is based on one table tblClient (record source).
To prevent duplicate entries I list the clients in a combo box (based on
tblClient in a query) and allow the user to add new clients as needed and
then be able to add other info for the client. This sounds like such a basis
form. Can you suggest another way this should be done? I want them to be
aware that they're adding a new client so I have limit to list = yes. Maybe
I'll see what happens if I say no.

strive4peace · May 23, 2008

FindRecord
~~~

Hi Bobbye,

"I list the clients in a combo box"

pardon my confusion... is the purpose of your combobox to FIND a record?
Otherwise, I can see no reason to have it...

If your combobox is designed to FIND a record... Make one or more
unbound (no ControlSource) combos on your form (like in the header).
Let the first column be invisible and be the primary key ID of the
RecordSource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the
RecordSource of the form -- assuming your primary key is a Long Integer
data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can
pull from multiple tables or only use one ... just make sure that the
first column is the primary key ID of the table you want to search (and
that field is part of the RecordSource for the form you are searching).

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*

have an awesome day

*

Bobbye R · May 24, 2008

Thanks Crystal. I had two combo boxes. One unbound to find clients and
once found I had a bound combo box. I changed the bound combo box to a
textbox after reading your post and realizing I only needed the unbound
combo. Thanks for sticking with me.

strive4peace · May 24, 2008

Great! -- you're welcome, Bobbye

happy to help

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*

have an awesome day

*

Jan Baird · May 27, 2008