autocomplete

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way to get a field to autocomplete? I do a lot of repetitive
data entry, but list and combo boxes don't work because there are many new
entries and entries that cycle out. Any suggestions or should I just switch
to filemaker.
 
Are you saying you have several fields in the record that are likely to be
the same as the last entry, and so you would like Access to carry the last
value over as the value for the new record?

If so, see:
Assign default values from the last record
at:
http://allenbrowne.com/ser-24.html

Copy the code from the article. It performs an intelligent carry over for
the controls on your form, avoiding unbound controls, controls bound to
expressions or calculated query fields, the AutoNumber, etc. And you can
specify an exception list (controls that should not have any value carried
over.)
 
Allen Browne said:
Are you saying you have several fields in the record that are likely to be
the same as the last entry, and so you would like Access to carry the last
value over as the value for the new record?

If so, see:
Assign default values from the last record
at:
http://allenbrowne.com/ser-24.html

Copy the code from the article. It performs an intelligent carry over for
the controls on your form, avoiding unbound controls, controls bound to
expressions or calculated query fields, the AutoNumber, etc. And you can
specify an exception list (controls that should not have any value carried
over.)
Click to expand...
Not quite, there will be numerous records between the fields that repeat.
For example, I'll have a work order at an address then several new ones, then
2 days later another work order for the same address. I have several fields
that follow this pattern, with hundreds of values that repeat. What I'm
looking for is an autocomplete feature such as excel's.
 
If you want to select a record, and click a button to repeat that record,
see:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

Access is not going to repeat data in the same way that Excel does. In a
relational database, repeating data should be treated as relational (from a
lookup table), so the feature is not appropriate for Access.

If that's a new concept, you might like to read about normalization. Here's
a starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
 
Allen Browne said:
If you want to select a record, and click a button to repeat that record,
see:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

Access is not going to repeat data in the same way that Excel does. In a
relational database, repeating data should be treated as relational (from a
lookup table), so the feature is not appropriate for Access.

If that's a new concept, you might like to read about normalization. Here's
a starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
Click to expand...
Thanks for taking the time to try and answer my question, I realize it must
be as frustrating to you as it is me. However being a noob I probably didn't
explain it well enough, because there is a good use for auto complete. In my
case I don't want to repeat a whole record just information in one field, but
in this particular field a lookup list would contain hundreds of entries and
be constantly changing.
I'll describe my record:
field 1 Date: date
2 invoice: num
3 customer: lookup cust records
4 contractor: lookup contractor record
5 start time: time
6 finish time: time
7 work location: location ****
this would be the field where autocomplete would be useful, example entries
smith 1234 abc st 67890 ***
jones 2345 def st 67890
brown 1357 xyz st 67891
smith 1234 abc st 67890 ***

when you have to type in hundreds of addresses it becomes repetitive. But it
is real useful to see the relationship of which customers send you where.
This is why some relational databases include this feature.
 
Thanks for taking the time to try and answer my question, I realize it must
be as frustrating to you as it is me. However being a noob I probably didn't
explain it well enough, because there is a good use for auto complete. In my
case I don't want to repeat a whole record just information in one field, but
in this particular field a lookup list would contain hundreds of entries and
be constantly changing.
I'll describe my record:
field 1 Date: date
2 invoice: num
3 customer: lookup cust records
4 contractor: lookup contractor record
5 start time: time
6 finish time: time
7 work location: location ****
this would be the field where autocomplete would be useful, example entries
smith 1234 abc st 67890 ***
jones 2345 def st 67890
brown 1357 xyz st 67891
smith 1234 abc st 67890 ***
Click to expand...

Your invoice table should NOT contain the work location.

Sorry, but it just shouldn't.

You're using a relational database - use it relationally! There should be a
Locations table with a LocationID and other (atomic!) fields: CustomerName,
AddressNo, Street, Postcode. Your Invoice table should store *only* the
LocationID; this can easily be inserted using a Combo Box on a form,
displaying enough information to identify the location but storing the numeric
ID.

If you're using table datasheets to enter data... DON'T. That's not their
function. Instead use a Form with appropriate tools - subforms, combo boxes.
listboxes, etc. And if you have Lookup Fields defined in your table, you
should be aware that most developers dislike them intensely: see
http://www.mvps.org/access/lookupfields.htm for a critique. You do want to use
the lookup *capability* for Customer and Contractor information, just as
suggested for Location information - but you can do so on a Form, without
needing the disadvantageous features of the Lookup Wizard.

John W. Vinson [MVP]
 
John W. Vinson said:
On Sun, 10 Jun 2007 21:51:01 -0700, justanoob
Click to expand...
Your invoice table should NOT contain the work location.

Sorry, but it just shouldn't.

You're using a relational database - use it relationally! There should be a
Locations table with a LocationID and other (atomic!) fields: CustomerName,
AddressNo, Street, Postcode. Your Invoice table should store *only* the
LocationID; this can easily be inserted using a Combo Box on a form,
displaying enough information to identify the location but storing the numeric
ID.

If you're using table datasheets to enter data... DON'T. That's not their
function. Instead use a Form with appropriate tools - subforms, combo boxes.
listboxes, etc. And if you have Lookup Fields defined in your table, you
should be aware that most developers dislike them intensely: see
http://www.mvps.org/access/lookupfields.htm for a critique. You do want to use
the lookup *capability* for Customer and Contractor information, just as
suggested for Location information - but you can do so on a Form, without
needing the disadvantageous features of the Lookup Wizard.

John W. Vinson [MVP]
Click to expand...
Thanks again for your time, however I'm not really interested in relational
database theory, strictly practical application. That being said, my
intention is to use the database to save time as well as gather information
about my business. the location field contains information about my customers
customers, and is therefore fairly irrelevant to mt purpose other than a
surface gloss. creating another whole record to contain and compartmentalize
that data is counterproductive and impractical for me. there are however
reports in which I would like to have that information to be up front.

Having said all that I've come to the realization that the answer I was
looking for was
no. find a different tool.

thanks again
 
Just a side note the reason the Invoice contains the location is because
that is how it is billed.
 
Back
Top