Copy columns from table into datasheet form

[lexi0088]

I am fairly new to access and I am trying to create a database for our company and this website has provided me with awesome solutions to many of my problems... but I can't quite solve this one from previous posts.

OK, I have a command button on my form which opens a chosen excel file and imports the info into a table. However, this file has a lot of data that is not usefull, so the table has a lot of column in it that do not need to be in my form.

My form has a datasheet subform in it which contains fields NDC\Quantity\Amount\Item credit and saves this into a table that is linked to my main form. I would like to create some code in VBA(within my command button) which goes to the import table and pulls out the columns containing the fields above and automatically filling the info into my subform.

Here's where I run into a problem.... I have to link it to the command button because there will be two ways to fill in the information into my form.... manually and through an excel file (when available).

Any suggestions would be appreciated.

Thanks

 

[BSman]

As I understand your question, your situation is this:

Importing from an Excel spreadsheet that contains repeated data in some columns. (An example would be PO#, customer, PODate, and the rest of the columns are line item#, stock#, quantity, price....like a purchase order might be.)

You have two data tables, one which contains the repeated columns and one containing what I'll call the "detail" columns. These would correspond (using the purchase order example) to the header and detail on the purchase order.

In this type of situation (assuming the data is entered correctly into the Excel spreadsheet), you could split the data with two loads as follows:

First load the header data into the header table. You might load it into an interim table and then append to the real header table using a grouping query so there is only one record for each purchase order (for example). The PO# column would be the primary key in this table.

Second, load the detail data PLUS the PO# column into your detail table. The PO# column is the link between the header table and the detail table.

Bob

 

[lexi0088]

Thank you so much for your reply Bob, but I really don't understand. I have two tables a chargebacktbl(header) and chargebackdetailstbl. I am using a form (chargebacktbl) and a subform (chargebackdetailstbl) to enter in the info.

There may be an instance where we will use an excel file to fill in the subform (form information will already be completed). This excel file is already imported to a table through the TransferSpreadsheet method... however, I only need 4 columns from this table (out of the 20 columns) to be copied into my subform. This importtable can contain as little as 1 record and as many as 100 records.

I am just looking for some code that will copy select columns from a temp table into my subform. Please help me if you can

 

[BSman]

If you import the spreadsheet into a table, then you should append the header data into your header table and the details data to your details table.

Your form can then run off of the header and details tables.

Bob