Collecting data using List box then populate a new table

[bdavid50]

I am stumped and need some help. I am only a moderately knowledgeable Access 97 user. I need to create a form (easy enough). In the form I need to collect data from ColumnA Table1, and display it in a List Box. Once the user picks an item form the list I want that pick to populate Field_1 on the form (which will later populate a new table). Field_2 will be provided for the user to place a correlating number and then when the user presses the enter key the two new fields will populate a new table. The focus will then be returned to the pick list at the last location and Field_2 will be reset to zero.

I have created the new Table, a form with the new Table as the record source, a list box on the form with its source from Table1. Once I select an item from the pick list I can move Focus (using a macro) to the User input field but I cannot seem to get the data from the pick list to populate required field in the form. Nor can I get the form to accept the last entry with an enter key. Any help would be appreciated.

 

[AlexMidd]

Couple of questions:

1) Are the text boxes that the values from the list box are going into bound to the fields in the new table that you want to populate? Or are they unbound?

2) Is the enter key being used to add the data from the text boxes to the new record?

It is relatively simple to select items from a list and place the value from the BOUND column, e.g. column 1, into a text box. However, if the text box into which it is being placed is bound to a field in the new table, you would need to tell the form, in code, to go to a new record (Docmd.GoToRecord acNew). Then the values placed in the text boxes "should" update the new record. Then, when the user clicks the list box again, use the goto new record method again and the text boxes should clear.

If the text boxes are UNBOUND, I would usually use code to add a new record, by opening a recordset from the new table and using AddNew. This is more involved but, as ever, the Access help files are pretty useful.

Hope this is of some help, although it may not match your requirements exactly. Have fun! )

Alex Middleton

 

[mac318]

David,
Setfocus to the receiving field. Then populate it with the value in your listbox. When I move a value to another field, I place it in a variable, then setfocus to the receiving field and place the variable in the field.

mac

 

[bdavid50]

Thank both of you for your help. Alex, to answer a few of your questions.

1.) Yes, the text boxes are bound to the new table. I want to populate that table with the data from the list box and the data the user inputs.

2.) Yes, the enter key is being used to add the data from the text boxes to the new records in the new table and then moved back to the List Box.

Where do I place the code for (Docmd.GoToRecord acNew)? Should I place it in the On Click property for the List Box? Where will that put my cursor?

Maybe I need to provide a little information to make sure I am doing this correctly. The data from the List Box (that will go into one of the new Fields) that I want to get populated into the new table will not actually be seen by the user (so I would want my cursor to move to the user input field). I know that I can set the property (Visible) for that field to "No" and the user will not see the field. I am using this approach to be able to populate the new table with the data from the existing table and and the user input. If there is a simplier way I would appreciate your input.

Mac318,

I appreciate your input but am not very good with code and am not fimiliar with setting up variables.


thank you both
Bill