Unbound Form and SQL question (1 Viewer)

[spectrolab]

Hi All,

I have been struggling with this for a while, and it might not be the best way to go about it.

I have a form which is unbound, on the form I have 126 text boxes grouped in to sets of 3 each. The text boxes are named and are grouped as such:

ID_1, CW_1 and IW_1
ID_2, CW_2 and IW_2 etc

Is there a easy way to add the values in these controls to a table with out having to write a SQL command for each group?

 

[namliam]

Well you can do something like

For I = 1 to 20
   YourSQL = YourSQL & "bla bla select bla ID_" & I & ", CW_" & I & " etc etc bla bla"
next I

 

[Zigzag]

Without knowing more about what you application it is difficult to give a full answer but it sounds like a continuous form would better suit.

You table should have the following fields.
ID
CW
IW

You may want to exapand the fieldnames to a more descriptive meaning.
Once you have the table sorted try using the Form design wizard to see if it produces what you are looking for.

 

[spectrolab]

Thanks Mailman,

Worked perfectly.

Thanks Zigzag. My project is a bit complicated. The form is unbound as it is makes a temporary table on running the code, then exports the temporary table to a CSV file, appends the data to the final table, then clears the controls ready for the next batch.

One final question for everyone, in my form, 4 of the controls need to be a fixed value which the user can not change. I have tried setting the default to (in this case) reference and zero, but when they write to the table, the fields in the table are blank for only these controls. Any ideas?

 

[Galaxiom]

You could consider exporting the data with the OutputTo method from a query rather than making a table.
http://msdn.microsoft.com/en-us/library/aa220433(office.11).aspx

In your output (or make table if you continue that way) query you need to set the fixed fields.

In the query designer:
Myfield: 0

or for a text field:
MyField: "whatever"