Populate excel data into access forms

[STAN]

Hello!

I have an access database that is used to generate microbiology test reports; i want to be able to tweak it a little bit so that i can avoid redundant data entry. The way it is being done now is, data from food samples are entered(by sample collectors) into an excel file and sent to the 'data entry person'/'lab technician' who then enters the excel data in an access form along with her own test result data and then saves this as a new record into the access table. What i would like to avoid here is having to manually enter the excel data onto the access forms. I want to be able to import the data from the excel files to populate onto the new access form and then have the 'data entry person'/'lab technician' enter her test results onto the remaining fields in the form and then save all this as a new record into the access table.
How i visualize this is, using a command button that,when clicked, opens up a "browse" window from where we choose the excel file to import; after the file is chosen it populates the excel-data onto the appropriate fields in the access form and then the 'data entry person'/'lab technician' can then enter the test results portion of the form and then save all this into the database.
Will anybody lead me in the right direction here?

Many Thanks!

 

[DCrake]

What you are asking is performed on a regular basis and there are many examples of this on this forum do a search to see if you can find examples.

 

[STAN]

I did do a search, and i only found examples of importing the excel data into tables but not about populating it into forms..

 

[boblarson]

I did do a search, and i only found examples of importing the excel data into tables but not about populating it into forms..

Data is not stored in forms. It is stored in TABLES. The forms are only a way of interacting with the tables.

 

[DCrake]

Think of it this way A form displays data in a form which inturn has a record source. This can be either a query or a table. A query is an alternative view of a table. So firstly you have to get the data into a table. You do this by either importing the data from Excel using the TransfeSpreadsheet command or phyically linking the Excel workbook to Access.

In a majority of cases users tend in import the Excel worksheet into a temporary table, then perform some manipulation/validation on the data then append it to a live table. This is then used to form the underlying recordsource for the form, where the user can add/edit/delete data rom the table itself.

 

[STAN]

I understand... but is there a way to achieve the population of data on the forms?
I'd like the "import" command-button to populate the excel-data onto the appropriate fields on the form first, and then manually enter some more data onto the same form and save all this data as a new record.

 

[DCrake]

The only way to do this, and a far more complicated way is to use unbound forms and alot of VBA. Are you up it it or not?

 

[STAN]

Yes, I'd like to take a stab at it.

 

[DCrake]

There would be about a full days programming to do it the way you want. And would not like to go down that route via AWF. However, here is an insight of what you need to do.

Create a listbox or other control that enumerates through a folder looking for excel files only

Prompts the user to select one
blindly read the contents of the excel to make sure it is the type of file you want.
Check to see if the file has not been previously selected.

Determine the number rows, cols exist
Pass each cell on a row to an array
Populate controls on a form with the array elements
Add additional data to the form
Save the record.

Repeat for the next line in the Excel file

One major problen you will hit is the situation when the user opens a valid Excel workbook and row by row appens data to Excel. But then closes the form before completing all the rows in the worksheet. How are you going to know that that has happened. Full of danger if you ask me.

 

[MSAccessRookie]

The only way to do this, and a far more complicated way is to use unbound forms and alot of VBA. Are you up it it or not?

Wouldn't it be just as easy to import the data into a table, and then bind the form to that table? The import procedure could clear existing records as well if that is a requirement.

 

[DCrake]

That's what I tried to get over to him but his reaction was to want to bypass that important part of the process.

 

[Rabbie]

Think of it this way A form displays data in a form which inturn has a record source. This can be either a query or a table. A query is an alternative view of a table.

A query can be a view of data in more than one table.

 

[Mike375]

I use this to get a cell value back to an Access field on a form and it is run as Excel is closed

DoCmd.SelectObject acForm, "GoToExcel", False

[Forms]![GoToExcel]![FLName] = Range("A1")

But you would need one of those for each cell to be transferred.

Actually I am doing the opposite to you. That is, I have Access open Excel and insert values into cells and the cell values are sent back to Access on closing Excel and that is for when the values have been changed in Excel.

If you had a series of Excel files to be opened then Access can do that if you have a table with a list of the Excel file names. It will open the nominated one.

 

[STAN]

Ok, This is how i did it:
Coded a 'browse' command button and 'import' command button to browse for and import an excel file. Did this by Copying and modifying bits and pieces of code from the "Browsing2" example by ghudson.
But then i had a problem where the file would import to the table but wouldn't reflect on the form; For example if the number of records in the table were 10 before importing the excel file, the form would still show 10 after the file is imported, though the number of records in the table would change to 11.
Added a 'Me.Requery' line right after the 'DoCmd.TransferSpreadsheet' file-import code which took care of the form-not-reflecting-database-changes problem.
Also got the finally added record to show up in the appropriate fields in the form(the fields on the form and table are identical) by using the 'Me.Form.Recordset.MoveLast' line in the 'Form_Load()' Event.
Now i can add values to the remaining fields of the form and save it(with the help of a 'save' command button) to the database.
Hope this would help anyone who is looking to find a solution for the same kind of problem.

Thank you all for taking time and responding to my request.