Special Query is it possible?

[sterlecki]

I have a table "defaults" that is intended to be populated by user supplied defaults for several forms and their record source tables.

By the structure below not all fieldnames are associated with all tables.

ie. tbl_State might contain data for StName but no other data would appear for anything but that StName field.

Example Table: "defaults"

[li]TableName PipeType StName PipeSize CountyName [/li]
[li]State null CO null null[/li]
[li]County null null null Denver[/li]
[li]Pipe Surface null 18 null [/li]
[li]Pipe Intermed null 7.5 null [/li]

Objective:

I would like to create an SQL query on the fly from a command button. The command button already collects the form name and the underlying recordsource which is the tableName.

I would like to select from this "defaults table" rows that match the corresponding table name and then only the fields in those rows that contain data. Almost like an array.

This way I would have 2 rows from the table "Pipe" and display only the fields "PipeType" and "PipeSize" as shown in the last two lines of the example table

I would like to use this recordset to insert these default values into another table using these field Names and values and the Primary key appropriate to that table.

So How can I create this on the fly,
Capture the field names and row data and then insert this into another table?

The goal is to make this code very generic so that which ever form or subform the user is on when they use a cmd button "Use Defaults" the code will execute.

Setting defaults in tables or forms will not work as some tables will require multiple rows to insert. Not all users of a form will have the same defaults.

I think this is an interesting problem help or suggestions are welcome! thanks

 

[SkipVought]

Hi,

You would be a whole lot better off if structure your table in a normalized structure, IMHO...
[tt]
TableName FieldName FieldDefault Row UserID
State StateName CO 1 Skip01
County CountyName Denver 1 Skip01
Pipe PipeType Surface 1 Skip01
Pipe PipeSize 18 1 Skip01
Pipe PipeType Intermed 2 Skip01
Pipe PipeSize 7.5 2 Skip01
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sterlecki]

Hi Skip!

I think that structure will help alot. A couple of questions though.

1. I intend to create a form for user default input.

I envision them selecting the formname they are used to seeing but in reality they would be selecting the data table that is the record source for that form.

Next they would select the desired fieldname and enter the field default value.

2. Do you have a suggestion as to how to create the Row#? Some how I feel this will have to be forced fed automatically to the user.

Now all I have to do is figure out how to INSERT these into the actual data table. If you know of a thread that you think would help I'd appreciate it.

 

[sterlecki]

Skip,

One other thing. If I use a Text data type for the FieldDefaultValue and I have to insert that into a field in the data table that is numeric will that work?

The user will not know which data type to select if I give them that option. Using the pipe example they would just know to input 7.5 for a size and Intermediate for a PipeType.

Your Thoughts?