Autofill in Access data entry forms

[LLJ]

Is there a way to 'autofill' data entered in an Access datasheet, similar to how it works in Excel? I'm using this definition of the Excel functionality:



"Automatically repeat values that already exist in the column: If the first few characters that you type in a cell match an existing entry in that column, Excel automatically enters the remaining characters for you.'

[isladogs]

The autofill functionality isn't available in Access apart from this useful but limited example:
If you click in a field in a table or query datasheet and use the keyboard shortcut Ctrl + ' (apostrophe), Access copies the value of the same field from the record above

[LLJ]

Well, rats! (And yes, I'm looking for something beyond the ctr + ' shortcut.)

How about something like this: Is there a way to use a list or combobox, with "limit to list" set to 'no', that would automatically add new values to the list without having to take a extra step to add new items?

I'm working on a data entry form where I am collecting votes by votername, and where the voter is allowed two and only two votes. I'm trying to prevent data entry errors where I might mistype one of the entries and then not get an accurate count by votername.

[June7]

Only if the combobox list source is the field the value is saved in, which means cannot be an alias lookup.

SELECT DISTINCT fieldname FROM tablename ORDER BY fieldname;

Would need code to Requery the combobox. Can use AfterUpdate event.

Another common solution is code that sets combobox DefaultValue property. Again use AfterUpdate event.

Note these solutions require use of form which can be in datasheet view.

[LLJ]

I think I can meet all those conditions. I'll try it out tomorrow and let you know - thanks so much!

[Micron]

So there can be only 2 instances of a name, with 2 votes (possibly the same vote, or not). But which is it that you might err on while entering data - the first input of the name? Or the second input of the name? The votes? Each voter has the same vote value options? Some data entry errors can be reduced by allowing only combo choices (such as only Vote A or Vote B), or prevent names from being misspelled by choosing from combos or list boxes, but you can't prevent every error. Without preventive measures, an incorrect choice of any option is still a possibility, as is adding a name (or other option) where that option already exists.

[LLJ]

Almost there! Built combobox and event procedure as described. When I use F9 after adding a new value, the comboxbox is correctly refreshed. But I can't get the code to work in the AfterUpdate event. What am I doing wrong?

[LLJ]

These are all valid points, but June7's suggestions are going to meet my needs (if I can get the requery to work!). Thanks.

[isladogs]

I can't get the code to work in the AfterUpdate event. What am I doing wrong?

If you need to requery at all, just add the line Me.Requery in the After_Update event.
Or if you don't want to requery the entire form, use Me.cboVoterID.Requery

You don't need to include the form name as you are in that form and no need to define the control

[LLJ]

Me.Requery and Me.cboVoterID.Requery don't work either. Tried those first, the image showed my third attempt. Can't get it to work on AfterUpdate or GotFocus events.

But like I said, F9 function key works just fine.

[isladogs]

You may need to ensure the new record has been saved before requerying.
So explicitly save e.g. If Me.Dirty Then Me.Dirty=False
Or try setting the focus to another control, then requery
OR try moving the requery to Form_Current

[LLJ]

Duh! I should have thought of that. That did the trick! Thanks to you and June7 I'm good to go, ready to start tallying votes on Tuesday! Thanks so much!