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

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

SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
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
PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.