too many options

[paulold]

I had been using Excel to keep track of my checking account but I am now
wanting to work in Access so that I can produce a data-entry form. I have
most of my form created but I'm not sure how to handle my main description
field. This is the field that identifies the transaction. It's not the
category, like "Groceries" or "Restaurants", but rather the specific
description like "Giant Supermarket" or "Pizza Hut". Currently in my table, I
have over 500 unique descriptions, many of which I could probably clean up
and consolidate. But I doubt I'll be able to reduce that number
significantly. I need this specific information to help with reconciling my
checkbook. My question is, what's my best choice for adding this field to my
form so that I could use either an Option Group or a drop-down list or
whatever to ease my data-entry? I would like to be able to simply select the
description from my more common values instead of having to type it in every
time, but I also need the field to be open to accepting new values. With
Option Groups, the various options selected enter a number into a field I
link it to. But I'm not sure if that approach would work in this case. For
one thing, I'd have to create way too many options. Maybe a drop-down box
could be created with my most common values, while still allowing me to fill
in whatever text I wanted? If so, could someone please explain how? The goal
is to speed up data-entry.

 

[Maarkr]

I would want something like in Excel where you type in the first few
characters and it filters out from there in a combo, similar to using the
wizard to find a record on the form... see Allen Browne's access site for the
code for this (somehting like combo for many records). Your next question
will be about building a running sum on the form to see your balance. You
should be able to search this site for that info.

 

[paulold]

Thanks for your help! I tried to follow the instructions at Allen Browne's
website, where it talks about "Combos with Tens of Thousands of Records".
Here's the link:

http://allenbrowne.com/ser-32.html

I'm having a little trouble adjusting this approach to my specific project.
I tried writing an email to Mr. Browne, but I'm not sure if he responds to
requests for help. Thus here's what I sent him below, in case anyone else can
help:

Dear Mr. Browne:
I am trying to apply your tip on "Combos with Tens of Thousands of Records"
to my database form but I'm having some trouble getting it to work. First
off, I should say that I developing a simple one table database to serve as a
check register for my checking account. I used to use Excel for this but
decided I wanted to try to create a data-entry form in Access to help speed
up the process of entering my many transactions. The part I am stuck on is
the main Description field for the record. This is the field that identifies
the transaction at the lowest level, meaning, if the category is Restaurants,
this Description might be "Pizza Hut", for example. This field helps me to
reconcile my database with my bank statement. The functionality I need is to
be able to use a combo box that will help me look up previously-entered
values to select one for the Description field and if it's not there, to be
able to enter a new one. So while you example has me choosing a Suburb and
then filling in the values for State and Postcode, I would like to be able to
choose a Description (using the three character search approach) and then
populating the Description field with the Description I selected. But if I
can't find one that matches, I want to be able to just type it in myself.

Please note that my table is called tblchecking and I created a combobox
that only has one boundcolumn and one columncount which ties to the
Description field in my main table.

I tried following your instructions by entering the following code into the
General Declarations portion of my form's code, while changing Suburb to
Description but it's not working. Access keeps telling me it can't find the
macro named 'Call ReloadDescription (Nz(Me.'

Here is the code I copied from your website and altered:

Dim sDescriptionStub As String
Const conDescriptionMin = 3
Function ReloadDescription(sDescription As String)
Dim sNewStub As String ' First chars of Description.Text
sNewStub = Nz(Left(sDescription, conDescriptionMin), "")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sDescriptionStub Then
If Len(sNewStub) < conDescriptionMin Then
'Remove the RowSource
Me.Description.RowSource = "SELECT Description FROM tblchecking
WHERE (False);"
sDescriptionStub = ""
Else
'New RowSource
Me.Description.RowSource = "SELECT Description FROM tblchecking
WHERE (Description Like """ & _
sNewStub & "*"") ORDER BY Description;"
sDescriptionStub = sNewStub
End If
End If
End Function
End Function

Also, for Step 3 of your instructions, you say I could "use a single line"
in the combo's Change event procedure, but you don't say what that line would
be. The code that follows didn't apply to my situation, so I'm not sure what
to stick into the Change event, or in the AfterUpdate event, for that matter
as well.

Any assistance would be greatly appreciated!

Paul