Saving data to a table

[kermit5]

Here is my goal:
I have a list box, lstAvailableFields, which contains fields that are available to the user. I would like the user to move the focus to a specific field, click on a button which would add the field name to a table which is the record source for another list box, lstSelectedFields, on the same form.

It is similar to the way the form wizard operates when the user selects from the available fields by clicking the right arrow key and de-selects them by clicking the left arrow.

I am open to any suggestions as to the best way to accomplish this.

Thanks!
Scott

 

[scottfarcus]

In the OnClick event of the button, place the following code...


'Where tblField is the table to which you are appending and FieldName is the name of the field in tblField

DoCmd.SetWarnings(False)
DoCmd.RunSQL("INSERT INTO tblField (FieldName) VALUES ('" & lstAvailableFields & "')")
lstSelectedFields.Requery
DoCmd.SetWarnings(True)

'As long as tblField is the rowsource for the second list box, it should work. Some error checking would be necessary to make sure a valid selection had been made.

 

[kermit5]

The following code works except that rather than saving the name of the field in the table, it is saving an index number (and I'm not sure what is generating the index unless qryAvailableFields is automatically using it).

DoCmd.SetWarnings (False)
DoCmd.RunSQL ("INSERT INTO tblSelectedFields([Selected Field]) VALUES('" & _
lstAvailableFields & "')")
lstSelectedFields.Requery
DoCmd.SetWarnings (True)

How do I get it to save the actual name of the field instead of the index.

Additionally, I need to save a reference to the selected job (via cmbProjectName) in tblSelectedFields for future use. How does that fit into the above code (I don't understand, yet, the in's and out's of the code.)

Finally, the fields that are selected for a specific job will be used to create a form. Each field will have a control type. For example, Door Number will be a text box, Door Type will be a combo box, Door Elevation may be a list box. How do a create this association to enable me to specify this when creating my form (via VBA Code?)

Scott

[This message has been edited by kermit5 (edited 04-25-2002).]

[This message has been edited by kermit5 (edited 04-25-2002).]