Eliminate Not In List Info Message

[Kevin Sprinkel]

In a database tracking our firm's estimates of
construction costs vs. actual contractor bids, we have a
detail table with the following structure:

tblBidDetails

Field Type Example(s)
----------- --------- ----------
BidDetailID AutoNumber 1
JobNumber Text "00003"
Category Text "HVAC", "Electrical", "Demolition"
Amount Currency $2,900,00
Divisor Number 3,500
DivisorUnit Number "SF", "Parking Space", "Apartment"

Note that Category is a text field.

Category is entered through a combo box on a continuous
subform tied to a main form by JobNumber. The lookup
tblCategory List has a Text primary key. To allow for
special values (about 10% of the data), I had set the
Limit to List property to No.

Even though there will only be a few thousand records per
year, it seems inelegant and potentially error-prone to
store this category as text, particularly since we'll be
doing a lot of report selection and grouping by this
field. So I'd like to change the lookup table key to an
Autonumber. Then, if the user enters a value that's not
in the list, I want to use the Not In List event to give
him the option of saving the new value to the lookup table
or just storing it as special text in another field.

I've tested the following code, and it successfully adds a
new record to the lookup table if the user selects "Yes"
from the MsgBox, and writes the value correctly to the
SpecialCategory field if he/she selects "No". However, in
the latter case, Access displays the message on completion
of the procedure "The text you entered is not a value in
the list. Select an item from the list, or enter text the
matches one of the listed items." What I'd like to have
happen is to just move to the next control as if I'd
entered a listed value.

Any ideas?

Thank you for any and all help.

Private Sub cboCategory_NotInList(NewData As String,
Response As Integer)
On Error Resume Next

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & " is not in the stored Category
list. " & vbCrLf & vbCrLf
strMsg = strMsg & "Add to lookup table? "

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add to List or
Store Special Text?") = vbNo Then
With Me.cboCategory
.Value = Null
End With
With Me.txtSpecialCategory
.Value = NewData
End With
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCategoryList",
dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Category = NewData
rs!CSIIndex = 99
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

' Clean up
rs.Close
Set rs = Nothing
Set db = Nothing

End If

End Sub

 

[Ken Snell]

You need to set the Response variable in that block of code as well. Here is
modified code from your post:

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add to List or
Store Special Text?") = vbNo Then
With Me.cboCategory
.Value = Null
End With
With Me.txtSpecialCategory
.Value = NewData
End With
' This is the new line of code that I've added
Response = acDataErrContinue

 

[Kevin Sprinkel]

Thanks, Ken.

I think I will be sure to doublecheck all optional
parameters in the future.

Best regards.

Kevin Sprinkel

 

[dabeck]

I have a similar but, I think, simpler problem. I have Combo box on a
form. I would like to be able to enter new text values (values that are
not already in the table) into the referenced text field of the underlying
table. I understand that in order to do so I will have to run some kind of
macro or event procedure. As a user, not a programmer, I have little
knowledge of these. What is the easiest method for me to accomplish this?
I have been going back to the table and entering the new values then
returning to the form and editing the record by selecting the values form
the combo box but this is very cumbersome. Seems like this would be a
common need and Access would facilitate this. Thank you

 

[Kevin]

-----Original Message-----

I have a similar but, I think, simpler problem. I have

Combo box on a form. I would like to be able to enter new
text values (values that are not already in the table)
into the referenced text field of the underlying table. I
understand that in order to do so I will have to run some
kind of macro or event procedure. As a user, not a
programmer, I have little knowledge of these. What is the
easiest method for me to accomplish this?

....
The following procedure is written for a lookup table that
stores Bid Types (LOW, HIGH, etc.). It allows the user to
either add the newly typed value to the underlying lookup
table, or return to the combo box to enter a new value.
The LU table has the following structure:

BidTypeID Autonumber
BidType Text

The combo box on the form, created with the wizard hides
the key field, so that it displays the text field to the
user, but actually stores the numeric code in the field
assigned to the form control's record source.

Assuming you have this situation, open your form in design
view. Display the properties for your combo box by right-
clicking on it, and choosing Properties. The LimitToList
property should be set to Yes, if not, change it. Click
on the Event Tab, then click on the OnNotInList property.
Click the ellipsis button to the right, and select Code
Builder. This will create the first and last lines of a
subroutine that will be called if a user enters a line not
in the lookup table.

Copy the code below (except for the first and last lines),
and paste it into your subroutine. Where strTblName and
strThing are assigned a value, put your tablename and the
type of data it is onto the right hand side of the
equation, replacing what's there, keeping the enclosing
quotes.

Then on the line that says:

rs!BidType = NewData

replace BidType with the name of the text field where
you'd like the entered text added. Access will take care
of adding an autonumber key.

Good luck!

Best regards.

Kevin Sprinkel
Becker & Frondorf

Private Sub cboBidType_NotInList(NewData As String,
Response As Integer)
' When copying to new procedure, change strTblName,
strThing, and fieldname
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
Dim strTblName As String

strTblName = "tblBidTypeList" ' Your Lookup Table Name
strThing = "Bid Type" ' The type of "thing" in
the Lookup table
strMsg = "'" & NewData & "' is not in the lookup list
stored in " & strTblName & vbCrLf & vbCrLf
strMsg = strMsg & "Add to lookup table? "

' Ask the user if he'd like to add the new value to the
lookup table
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New " &
strThing & "?") = vbNo Then
' Don't add it, return user to the combo box
Response = acDataErrContinue
Else
' Create a recordset clone, and add the new record
Set db = CurrentDb
Set rs = db.OpenRecordset(strTblName, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!BidType = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

' Clean up
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub