Errors in saving to a table (1 Viewer)

[kermit5]

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.

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

 

[Pat Hartman]

I don't mean to be rude Scott, but if you don't know how to code, you're in way over your head. Do you realize that you are attempting to replicate components of Access itself?

What are you trying to accomplish by allowing the users to make their own tables and forms via your VBA code?

 

[kermit5]

I have posted several topics trying to determine the best way to accomplish my task. Here it is in a nutshell:

I am creating a door estimating database. The first step in the process is to enter information about each door from a set of plans. Each plan has it's own format. That is to say, that an architect can provide information about an opening in virtually an infinite number of ways.

Generally speaking, each door has a number, a door type, a door material, a frame type, a frame material, various details, fire ratings, hardware groups, notes, etc. The architect may use one or all of the above fields.

I want to be able to select from my tblOpeningTakoff, the fields that are on the plan before me, in the same order that the architect has drawn his schedule. Each job I take off will have a different sequence of various fields. Right now, I can use the software, but each time I use it, I must manually add, delete and/or reorder the fields. While this is somewhat time consuming and a bit of a pain, I am the only person in the company for which I am designing the software that can use it because nobody else knows anything about Access (or programming, in general)

My solution is, therefore, to create a pre-form form whereby the user selects the required fields, for a selected job, input desired default values and then create a usable form.

Here are my past ideas, about which I have received no assistance:

First, I created a finite number of fields that can be selected (pre-determined) and called each field txtField1, txtField2...txtFieldn, where n is predetermined by the program. When the user selects a field from lstAvailableFields, it saves the value in the first available txtField. This does not provide the flexibility that I require.

I then wrote VBA code for a multi-select array. The code works fine, but I am not sure how to display the contents of the array to the user as the fields are selected for verification. Additionally, I am not sure how to associate the array to a specific project for future use, and I do not know the best way to associate the control type with the Field for my form specification (ie text , cmb, lst, etc.)

I then received the above code as a possible suggention which led to the error topic curently posted.

My programming experience is in C++, but I am learning the VB processes. I have not begun to study SQL, yet.

Any suggestions would be appreciated.

Scott
Past Postings
"Saving Data to a Table" 4/25/2002 10:42 a.m.
Forms: "Arrays as sources for List Boxes" 4/19/2002 12:53p.m.
Forms: "Field Selection" 4/19/2002 2:23p.m.


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

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

 

[dynamictiger]

Hi Scott,

I am working on a similar idea in an unrelated field. I have well over 40 parameters that may or may not be required.

My solution is different to yours and may work for your application.

I have set up a stock table recording whether the data is required, not required, optional or interchangeable in the 10 plus groups. All my stock belongs to a group.

Any group can assign a required value to any of the 40 plus fields.

In this was I scan the required fields table and populate my form using different colours according to the various levels of requirement by group. Finally I make invisible the not required fields, and then simply move all the fields around on the form to make the form look like a designed form.

Whilst this is a bit how you going, it works fine and was fairly simple to code, but a bit time consuming.