Solved

How create table via VBA code that only includes fields with values from another table

Posted on 2014-07-30
3
406 Views
Last Modified: 2014-08-04
I have a table named tblMatlSpecsChemical.  Some of the fields have a value in them and some of them don't have a value.  What I want to do, via VBA code, is create a 2nd temporary table that only contains the fields from the 1st table that have values in them.

So if for example table one has a field named C_MIN and that field has a value in it, then include the field name and the field value in the temporary table.  But if another field for example named Mn_MIN has no value in the 1st table, then do not include the field name or field value in the temporary table.

I've tried variations of this code but it isn't working so I'm sure I've got something wrong.

    Dim RS As DAO.Recordset
    Dim rst As DAO.Recordset

    Set rst = Nothing

    Set RS = CurrentDb.OpenRecordset("tblSpecReportChemical")

    RS.AddNew

    If Me!subfrmMatlSpecsChemical.Form!txtC_MIN > 0 Then
        RS!C_MIN = Me!subfrmMatlSpecsChemical.Form!txtC_MIN
    End If

    If Forms!subfrmMatlSpecsChemical.Form.txtMN_MIN > 0 Then
        RS!MN_MIN = Forms!subfrmMatlSpecsChemical.Form.txtMN_MIN
    End If

    RS.Update

    MsgBox "The spec data has been entered into the spec report data table."

Open in new window

0
Comment
Question by:SteveL13
3 Comments
 

Author Comment

by:SteveL13
ID: 40230306
The above code in in an onclick event of a command button on a form with the hopes of having the code create the temporary table named "tblSpecReportChemical".
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40230348
What you are asking is not possible.  Before you can populate a table, it has to be defined.   You can't define columns as you append rows.  Even a spreadsheet won't solve your problem.  Given fields a, b, c, d, e, are you really saying you want to end up with:
a, b, d
b, c, d, e
a, c, d, e
a, b, c, d, e
b
d, e

No human or program will ever be able to make sense out if this array.  What is the meaning of the first column?  What is the meaning of the second? etc.

In any recordset, it is reasonable to have some columns of some records "empty" but you cannot eliminate the column because some other record may have data for it.

I prefer "empty" columns to be null.  That way it doesn't matter what data type the field is, if it's empty, it's null.  For text fields, you have the option of using a ZLS string.  In code or a query this equates to - "".  Do not confuse it with null.  They are different.  I never even allow ZLS unless I am importing data from an old mainframe or UNIX app.  In that case, you frequently get fixed width records that always contain "" as a filler to make fields a fixed width.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40230872
Does this table only contain 1 record, ...or are we talking about multiple records, ...all with Fieldx empty?

In a nutshell, I am with Pat...
So while it is certainly possible to loop the fields and collect the name of any non empty field(s), ...
then build an SQL statement in code, ...to only include the non empty fields, ...I don't see the point either...

As always, ...it is always helpful to know the ultimate need here, ...in case there is an easier/alternate way to do get what you need...
Outstanding questions:
Whats the reason for the temp table?
Why is it imperative that the blank fields be removed?

JeffCoachman
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now