Solved

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

Posted on 2014-07-30
3
416 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 38

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

691 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