Create and update a table from a

[hb001]

Hi All,

INTRODUCTION
I have been following this post:

"Checkbox form to allow user to select query output fields"

(BIG THANKS TO ADezii AND ALL THE OTHER CONTRIBUTORS)

In a nut shell,
1. A user selects check boxes for the criteria he/she wants to see,
2. Using a combo box, they can choose the particular brand/family of products they want to see the above selected criteria for:

 strSQL_2 = "SELECT " & _
   Left$(strSQL, Len(strSQL) - 2) & _
   " FROM Products " & _
    "WHERE ([Family] Like [Forms]![MAC]![Combo99]);" 

3. The VBA creates a query based on the brand/family and check box criteria.

WHAT I HAVE MANGED
I have managed to create the "dynamic query" based on my check boxes and have also manged to keep changing the WHERE criteria using combo boxes.

THE PROBLEM
I now want a method of adding more records to the query. In this case, based on the same criteria but for a different brand/family.

Hence finally, a record set with selected criteria (filed headings) about several brands. All this effort is for a dynamic database & a user friendly interface.

NOTES
1) I believe you can only append records to a table and not to a query. I tried to create a "make table query", and then use an append query to append records. However I couldn't create the VBA to create a "make table query" in the first instance. The next challenge would have been to make a "dynamic append query".

2) The "dynamic query" will change several times in a single use of the database depending on what the user wants to achieve. The data will then be used to create a report/exported to [Some Application]. I believe the table can be deleted and re-created each time the "dynamic query" fields are modified (using the check box controls).

3) I don't have much experience with coding.

Thank you in advance. :)

[hb001]

Hi All,

Funnily enough, I managed to solve part of the problem shorty after posting (Thanks zmbd for the edit).

I simply changed the where code to:

"WHERE ([Family] Like [Forms]![MAC]![Combo99]
 OR [Family] Like [Forms]![MAC]![Combo113]);"

I am now faced with the next challenge, how do I move this query into a table without having it link to the original table. It's like a static table which can be deleted and re-created each time a new "dynamic query" is created?

Cheers

[zmbd]

HBB01:

I'm not really following what you are trying to do here.
You shouldn't normally be creating and deleting tables in a well designed database.
The queries can be saved or created on the fly depending on what you are trying to do. The stored queries are held in the QueryDef Object (DAO) Office 2010 and these can be altered on the fly - this is something I used to do for a report.
What I do now, is create one parameter query based on the entire recordset, build my report, and open the report by passing either a filter or parameters.
Use parameters in queries and reports I've found this to much cleaner, easier to maintain, and more stable.

[hb001]

Hi zmbd,

Thank you for your response.

I shall have a read through the links you have sent me and see if I can work something out.

I shall post back if I find a solution or get stuck.

Thanks.

[hb001]

I have had a look,

I cannot seem to see how these will help me achieve my goal (maybe I missed something).

I will attempt to explain why I want to create the table and hopefully this will help find a way forward:

Please see the attached file for a preview of my form. I can get the query to work with the two sets and get the data to one query.
Sample data:
(Brand) Apple
(Family) iPhone, MacBook, iPad etc.
(Model) 4S/5/5S, Air, Retina etc.

I am now worried because (realistically) a user could want up to 10-20 sets (maybe even more) with the same query fields.

My idea was to use the form to construct the query design,then make it create a table and finally use another query to update this table.

This would mean I only need 1 set of combo boxes that can add an infinite number of records to the table.

I hope this makes sense.

Thank you.

[zmbd]

Queries are not tables.
Queries pull information from tables.
Queries can take actions on tables.
Queries can either be stored in the database, in form control, VBA code, or built as a string in VBA code

The example you started with (ADezii Code) does this last action, building the code. Once the code is built you can store the code by adding it to the collection (not normally done - but whatever).

SO how can we use this information to help solve your question? This will depend on your database design. We'll need to see a list of your tables, their fields, and any relationships between the fields/tables. Something like this:

table1
[field_1] PK - autonumber
[field_2] text(25)

table2
[field_A] PK - autonumber
[field_B](FK 1:M with table1![field_1])

From here we maybe able to help.

If the database is normalized, then what I suspect we'll do is use your comboboxes to find the "set" by a unique primary key. We can then add this to the query string and then requery the form based on the new string. Depending on the form, we might even be able to do this using the form's filter.

[hb001]

Thank you for your response and apologies for the delay.

I have only one table that pertains to the data I am working on. There is another, but the tables are not related (so I have not included it?)

The main table has about 106 fields, so I am using the main ones. (ok?)

Products
[Field_1] Database ID - autonumber
[Filed_2] Brand - Text
[Filed_3] Family - Text
{Filed_4] Model - Text
...

Hope this helps.

Thank you.

[hb001]

strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 
2) & " FROM Products " & "WHERE ([Database ID] = [Forms]!
[MAC]![Combo125]
 OR [Database ID] = [Forms]![MAC]![Combo128] 
OR [Database ID] = [Forms]![MAC]![Combo129] 
OR [Database ID] = [Forms]![MAC]![Combo130] 
OR [Database ID] = [Forms]![MAC]![Combo131]);"

That pulls the information for many records together but it requires multiple combo boxes. I would like to have one combo box with an "add to comparison" button.

Thanks.

[zmbd]

(...)The main table has about 106 fields(...)

It so sounds like the database isn't normalized.

Hmmm....
combobox
rowsource = select the table that has the fields of interest
rowsource type = "field list"

The combobox will now show the field names in the dropdown list. The name of the selected field would be combobox.value which can be appended to a string.

Your next thing will be to get the comparison value for the field - this may not be so simple unless you have a list of values somewhere in the db.

Anyway say in the first combobox your client has selected combobox.value="brand" then your next combobox would then need to have its rowsouce filtered down to the brands in your database. I'm guessing that these brands are in one of the 106 fields of your table - yes?! You could then use a command button to add the string "[Brand]= 'selected'" to your WHERE clause.

I think that this is really clunky, and will lead to some really awful field names that will violate the restricted tokens list. Making your report is going to be a real nightmare as you have no set field structure

I really think you need to Normalize your database.
To use your example:
You could have a table: (Brand) Apple
You could have a table: (Family) iPhone, MacBook, iPad etc.
You could have a table :(Model) 4S/5/5S, Air, Retina etc.
Then you then might have a related table that has the details...
table
tbl_specs
[specs_pk] autnumber
[specs_fk_brand] numeric long fk 1:m w/brand
[specs_fk_family] numeric long fk 1:m w/family
[specs_fk_model] numeric long fk 1:m w/model
[specs_memory]
[specs_height]
[specs_length]
[specs_(....other information....)]

Now the customer would select from three comboboxs that are linked to your three tables (Brand)(Family)(Model) the comboboxes could be cascaded as in this example ([*]Cascaded Form Filtering) from there you would be able to pull the [specs_pk] and add it to an SQL using the IN() function within the WHERE clause.
Once the customer is happy, they could then press the compare button and the database would pull up the report.

[hb001]

Thank you for your response zmbd. I really appreciate your effort.

In theory I think I can follow what you mean. But when it come's to writing the code, I quite frankly do not have the knowledge of VBA to write it up.

In terms of normalisation I think it is a good idea too, but I think at this point it would be best for Version 2.0

As of now, I have modified the design of the form and I no longer use the 3 combo box "filters", but rather a single combo box with 4 columns to easily identify the product.

My understanding from what I have done and the suggestions you have made is now that (correct me if I am wrong):
I can store all the [Database ID]'s as a single string variable (they are all numeric though) which is created/added together when the "add to comparison" button is clicked, then set the following:

.... "WHERE ([Database ID] = [MyString]);"

Is this correct? If so how can I do this?

I can foresee the same problem with the reports, but this is a big, specific and ambitious project and I have no shortage of motivation to see it 100% complete. :)

Thank you.

[zmbd]

you'll build your string just as you've shown in the first post's code block by appending to the where clause using "&" and then you will append that string to the SQL.

Frankly, I've been normalizing databases for so long, it will take me awhile to figure out the mishmash you are trying to do. ADezii is most likely a little quicker on the code.

Honestly, the effort you are making here would be better spent normalizing the database and creating it properly. V2.0 is "too" late. Quantity vs. Quality is what gets programers in trouble.

[hb001]

Thanks for your response.

By V2.0, I meant I will build this database up get it working perfectly and then start again from scratch, so my V2.0 is the users V1.0.
This (maybe) is strange but it's what works best for me...

For the minute though I will try and resolve this mishmash (new word for me) by creating a string variable, then calling it in the where statement following your advise.

Thanks.

[hb001]

Hi all,

So this is what I have manged

'Dynamic WHERE
For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Then
        If ctl.Value Then
            strWHERE_D = strWHERE_D & "[Database ID] 
= " & "[Forms]![MAC]!" & 
"[" & ctl.Name & "]" & " OR "
            End If
        End If
    Next
 
'Create the WHERE criteria
strWHERE = strWHERE_D & "[Database ID] = 1"
 
'Build complete SQL Statement
strSQL_2 = "SELECT " & Left$(strSQL, 
Len(strSQL) - 2) & " FROM Products WHERE " 
& strWHERE & ";"

It uses about 20 combo boxes with 4 columns each. It does the job & I am quite happy with it. But it still lacks the ability to do an infinite number of comparisons. I would really like to know how to do that.

Cheers!