Solved

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

Posted on 2014-07-30
3
413 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 36

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

830 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