Auto Populate Field with same data

[jjb373]

Hello all -

I am currently creating a database which askes the user to enter data in about 10 fields. These fields are related to a call the representative receives from a customer.

To speed up the data entry process, I would like my form to have the same functionality as excel does in that if someone starts to type a name, the name will automatically populate in the field giving the user the ability to keep typing if the name is different or if it is the same hit tab/enter to accept that name and go to the next field.

Excel Example:
Sue Smith is in cell A1
Mike Brown is in cell A2
As the user starts to type in A3 Su --> e Smith auto populates with a black foreground giving the user the option to hit tab or enter to accept the value or keep typing.





-- JJB373 --

 

[Remou]

You can create a combobox that refers to a table of names (best bet) or refers back to the data-entry table (Select Distinct ...). The Not In List event can be used for the first option and the Limit To List Property can be set to 'No' for the second option.

You may wish to look at

http://www.r937.com/relational.html,

to avoid committing the sin of spreadsheet

 

[jjb373]

Remou - Thank you for the help.

I plan to use your first suggestion. My second question is: Do I add code or use the expression builder in the Not In List event? What am I adding to that event to make this functionality happen?

Thanks!


-- JJB373 --

 

[jjb373]

I have that combobox working now - sorry about the second reply.

So currently, I have the combobox looking up the values (employee name) in another table (employee name table). The employee name is then stored in my main table. If an employee is not in the combobox list (employee name table) and the user needs to add a new employee how would this be accomplished? I would like the new name to be added to both the main table and the external lookup table (employee table).

Thanks!


-- JJB373 --

 

[Remou]

The combo should be bound to the external table and that will fill the name into the main table.

Here is quite an elaborate example from Microsoft:
Use NotInList Event to Add a Record to Combo Box

http://support.microsoft.com/kb/197526

You will find a number of simpler examples in these fora. The simplest is probably an append query to add the name to the external table:

[tt]strSQL="Insert ( [NameOfField] ) Into NameOfExternalTable Values ( '" & NewData & "' )"[/tt]

Don't forget to include:

[tt]Response = acDataErrAdded[/tt]

 

[jjb373]

To do this append query - would the user have to exit or leave the form they are already in to accomplish this update? The goal is to not have another window/form to open and remain only in the current form they are entering the names and other data in.

If this statement is on an event property which one would I use? Maybe an if statement saying if name is not in table allow the user to free type the new name and store in both tables else use lookup table list.
Would this be a possible solution. If so any idea on the first part about writing to both tables?


-- JJB373 --

 

[Remou]

The first line above should have read "the combo should be bound to a field in the main table", otherwise, I think I covered all your points in my previous reply.

You can run an append query from code, it only needs a line added to what I showed above:

[tt]strSQL="Insert ( [NameOfField] ) Into NameOfExternalTable Values ( '" & NewData & "' )"

CurrentDB.Execute strSQL

Response = acDataErrAdded[/tt]

The code should be added to the NotInList event for the combo as I said, and as is illustrated in the link I posted. Make sure that limit to list is set to Yes (True).