Question Use Form to update table automatically

[mmor79]

Hi,
I'm a new user of MS Access and would like to have support on the following.
I saw some discussion related to use FORM and update TABLE but not as what I want which is to be available to paste more than one value ( a list ) into a FORM

I would like to have on a "FORM" in Acces the opportunity to paste a list of values ( copied from excel more than 1 cell from a column ) and this would automatically update a "TABLE" on one column.

Example
on Excel I've on one column a list of numbers. After copying several cells I would like to paste this in "somewhere" in a "FORM". After this the "TABLE" in Acces should be updated with these numbers.

I don't want to copy and paste to a Table directly and don't want an import table. FYI

What kind of "FORM" I should use? What kind of command need to be entered in Visual B?

I would appreciate if someone can assist me with this.

 

[Galaxiom]

That is not the way to do it. Just import the Excel spreadsheet into Access as a table and use queries to process it.

 

[Atomic Shrimp]

I don't want to copy and paste to a Table directly and don't want an import table. FYI

Why not? Are you rearranging the data as you go? If so, rearrange it by copying and pasting into a different spreadsheet, then import that into Access.

Access tables are not like spreadsheets - and trying to treat them as such will only cause misery, pain and error.

 

[boblarson]

While some versions of Access actually support the pasting in of data from Excel into an Access table, it is not a good method to use. Also, that is no longer possible to do in Access 2007, so it would be best to come up with a method which is supported in future versions.

 

[mmor79]

Thanks for your replies allready.
The reason I don't use the table is that because the database is using a main form with different commands. I don't want people going to the tables or queries to update or change.

I see there's also a possibility to create something on visual basic which makes working copying data from a clipboard. Maybe this is an option but I don't know how to built it.

Another option would be a macro maybe. So a command button on my "FORM" which runs the macro by copy the list from Excel and paste on the table. Also need more information on this.

I know Access has a lot of opportunities but I'm not a real pro ( at the moment ;-) ).

I still need your support. Thanks...

 

[dbDamo]

Create a macro that imports the data into your table, then just add a command button to your form which runs this macro.

Users do not need to go anywhere near your tables.

 

[mmor79]

Hi DbDamo
an you help me with a macro? I'm not so expert.
with regards

 

[dbDamo]

Of course I will. I'm leaving work now, but will be home in 15 minutes. In the meantime you can upload a copy of your Database (minus any sensitive data) and give me the file name and path of the file to be imported.

 

[mmor79]

Hi DbDamo,
See attached test database with one TABLE and a FORM with a Command button ( not defined yet ). Also attached an excel file.

The most important part is that the values on column A on excel having Text format. This is very important to have the upload to Access also like that.

Looking forward for your reply.

 

[dbDamo]

Here you go. All I have done is create a macro called mcrImportInvoices that will import the data from your Spreadsheet into your OPP table. I have then placed some simple code in the OnClick event of your button that will run this macro.

All you need to do is open the macro in design view and change the File Name argument to be the location that your Spreadsheet is held in.

You will have an issue though if you do not set the invoice number as the primary key in this table. Without doing this the button can be pressed time and time again, each time adding your data as new records, thus creating duplicate data. Set the invoice number as the primary key and this will not be an issue.

Please let me know if you need any more help.

 

[mmor79]

Thanks a lot but I've problem with having it working properly. First I changed the File Name location on the Macro as you said and tried out running the macro (not using the command button on the form). When I look on the Table than the data is there available but with "wrong" format. Example "5.57321e+007". How can I solve this?

 

[dbDamo]

Which field are you having problems with, invoicenr or invoice amount?

It worked fine for me..

 

[mmor79]

field Invoicenr .
Macro is also uploading a lot of empty fields on both columns.

 

[dbDamo]

All data is fine in the Database I am using. Are you using the same Spreadsheet?

When I first did the import I had the empty rows too, but the problem was with the Spreadsheet which I fixed by deleting rows 56-280.

All of the 55 records imported from your Spreadsheet are displayed in exactly the same way in the Table in my Database.

 

[mmor79]

ok, I tried again from beginning with zip file you sent to me. After deleting the spreadsheet row 56-280 and changing the file name location on the Macro and running the macro I've a message like

"A Value you entered for the spreadsheettype argument is invalid" "You used the Transferspreadsheet method, and an expression in the spreadsheettype argument doesn't evaluate to a valid numeric value.""Valid values are 0,2,3,4,5,6,7, and 8. Note 1 is an invalid value, you can't import or export to a Lotus.wks format file."

On Design page of this macro I changed the Spreadsheet type to "Microsoft excel". Now the macro is working but not uploading with the right format to the Table.

When I go back to the Design page of this Macro and I leave now the Spreadsheet type empty than the Macro still working but still with wrong format. hmmmm

Still need help with this, we're almost there

 

[mmor79]

By the way the macro is not uploading the empty rows anymore after editing the excel spreadsheet. Thanks for that

 

[dbDamo]

Can you upload your Database again, with the data imported so that I can see the incorrect formatting

 

[mmor79]

Here you go.

 

[dbDamo]

Format your Invoicenr field in Excel to General - this will fix it

 

[mmor79]

HI dbDamo,
it's working now, Thank you so much for your excellent support.

This was my first time adding a topic (question) on this website. I'll definitely look more often and use this website.

And I'll keep in touch I think with other topics to come ;-)

With regards