How to create a form to use for data entry and lookups?

[ncsthbell]

I am using MS access 2007. I want to create a form that when opened will contain all blank columns (to do this I believe I do not include a record source on the forms property). I want the user to be able to enter a number in a column called 'asset', hit enter and the form populate with the data for this asset. Also allow them to type over any data column and change the values.

I also want to be able to clear the form (if any data showing) and allow user to enter a new record.

Is there any way to do this without having to create 'add new rec' or 'search' buttons?

[Stewart Ross]

Hi. Sorry for the delay in replying. Whilst it is technically feasible to do some of what you suggest it is a great deal of work, in effect recreating what Access already does for you but entirely under your control.

It is much, much simpler to use a bound form for the display, entry and editing of your records. On that form you can place an unbound combo box as a look-up for entry of the asset number, using code in its After Update event to filter the appropriate tables within the recordsource of the bound form to retrieve the correct record.

You would still need some form of Add New button on your form, and indeed you should think carefully before introducing methods of navigation which users would not be familiar with. Methods involving shortcut keypresses or unfamiliar actions used in non-intuitive ways are unlikely to be met with anything other than confusion by all but advanced users of the DB. A button labelled "New Record" is at least familiar and self explanatory!

-Stewart