Importing Data to A Form - Sizing Up My Options

[cmcfarland]

Hello, Everyone!



I deal with MS SQL every day, and do many interesting and useful things with it, but I'm very new to MS Access operations.

I really like it, though, and am fascinated by the way that the database and the application itself are kind of one unit, and have seen quite a few really neat Access instances that do some pretty incredible things.

I'm faced with a project now that seems quite plausible, though I'm not sure about the nuts and bolts of getting it done.

Here's what I've got:

I'm working with a law firm that has an Access based firm management application and the part that I'm dealing with is where they input copy and print activity to be billed back to their clients.

My company installed some print and copy tracking software that will allow me to generate CSV files listing the usage data, and I want to import that data into Access. While even doing that is new to me, the tricky part is that when it's done manually the data is entered into a form and that form computes the price charged for copies according to a sliding scale.

So, instead of a more simple sounding import process where I'd arrange for an import straight into a table I'm trying to figure out if I can take the data from my CSV file and import it into that form so that the form computes the charges as if a clerk had input it manually using the nice GUI that the form provides.

That's my basic quandary, and I really appreciate you taking the time to read my post and truly value your insight.

What do you think?

Thanks!

[ssanfu]

So, instead of a more simple sounding import process where I'd arrange for an import straight into a table I'm trying to figure out if I can take the data from my CSV file and import it into that form so that the form computes the charges as if a clerk had input it manually using the nice GUI that the form provides.

Just to be sure we are on the same page, forms do not store data. Tables store data. Forms are used to display data from a query (or table). I use queries because I can limit records returned and can order (sort) the records.

OK, so you can import CSV data into a table. I do it all of the time. Much easier that typing about 2000 lines of data every month.
If you have calculations in a form control, when you open a form that has a record source (query), the calculated control will display the result of the calculation.
But if the result of a form/control is the result of an event, the event is not triggered when importing dat

So when I am importing the data in a CSV file, I do many calculations before I insert the results in the correct table(s). I can pick the CSV file I want to import and the code does the rest. LOTS of validation and calculations. Way easy!!

You can also use an import specification the import the data into a table. Depends on what you want to happen.


I know this is vague, but I don't know what your process is. Importing a CSV file is easy, but might require VBA code - again, it depends on how much calculating is required..

[cmcfarland]

OK, that doesn't sound too bad!

Sounds like I need to look into the form to get the calculation information that computes the cost based on quantity, and then I can have that calculation run on the data from my CSV file before it goes into the Access table.

I don't think I'll have too much trouble getting into the form to get that calculation, so, assuming that much, what are the basic steps to setting up this intermediate step of having the calculation run on the data from my CSV before it goes into the table?

Thanks so much for your reply!

[ssanfu]

I have used two methods to import CSV files.

Method 1) Open the CSV file, read a line, parse the line by
.....a) finding the delimiters one at a time, putting the data into variables, then doing the calculations and inserts
OR
.....b) read the line into an array, then doing the calculations and inserts.

Method 2) Import the CSV file into a table using an import spec (NOTE:do not use make table code - good chance to corrupt your dB). Then you can open a record set and loop through the records in the table, doing the calculations and inserts.

I mostly use the 2nd method in Access. I used the 1st method when I was working with Excel.

There is code to select the CSV file here:
"Call the standard Windows File Open/Save dialog box"
http://access.mvps.org/access/api/api0001.htm

Post back if you have more questions...

Good luck with your project