?
Solved

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

Posted on 2014-07-30
3
Medium Priority
?
429 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 40

Accepted Solution

by:
PatHartman earned 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

612 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