Autofill function for Access 2010

Question

I have a form that I enter all in-coming documents into.  One field is the name of the person the document is from.  We get a lot of documents from the same people.  How can I get Access to autofill in this field so I don't have to re-enter the same information?

Answer 1

As a rider to John's reply, you should not be storing the same name in multiple rows in one table.  This risks inconsistencies, something to which I can personally testify, having in one database found three versions of my own name as author of technical articles in my own field of work.  The people should be in a separate table with a numeric (e.g. autonumber) primary key and text columns for the names.  This table will have one row per person so there can be no inconsistencies.  The table to which your form is bound should have a long integer number PersonID (or similar) foreign key which references the key of the people table.

It's a simple task to insert the distinct names from your current table into a new people table with an 'append' query.  You can then easily update a new PersonID column in your current table with an 'update' query.  An enforced relationship between the tables can then be created.  Once you are satisfied that the new data is correct you can delete the redundant name(s) columns from the current table.

In your form you can use a combo box for the person, and put code in its NotInList event procedure to enable you to enter a new name into the people table directly from your form.  You'll find examples of how to do this in various contexts in the file NotInList.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

You might have to copy the text of the link into your browser's address bar (not the link location).  For some reason it doesn't always seem to work as a hyperlink.

To carry the person forward in your form from one record to the next will still use John's code exactly as he posted it.

Answer 2

You can put a little bit of VBA code  or a Macro in the AfterUpdate event of the form control setting its DefaultValue property. Open the control in design view; view its Properties; select this control and find the AfterUpdate line on the Events tab. Click the ... icon by it. The VBA code would be

Private Sub controlname_AfterUpdate()

Me.controlname.DefaultValue = """" & Me.controlname & """"

End Sub

The Macro would probably use the Setvalue action, I haven't done much with 2010 Macros though.

Answer 3

Please don't piggyback your question on another thread. Use the Participate link above to start your own thread. Give a detailed explanation of your data structure so we can help.

Answer 4

am looking for code to auto fill row in sub from after i select the row from comb box its update the whole row with selected item

Answer 5

As a rider to John's reply, you should not be storing the same name in multiple rows in one table.  This risks inconsistencies, something to which I can personally testify, having in one database found three versions of my own name as author of technical articles in my own field of work. 

And if Kenn Shurdan and Jon Vincent both tell you to do something it would be good to listen! <g, d & r>