Is it possinle to copy and paste into multiple new records?

[EvanRosenlieb]

Hello all,

I am entering data that is the output of a machine that measures concentrations of different gasses in the air. The data is output by the machine into an tab delimited text file that I use Excel to open and Access. Each run the machine does had data that needs to go into three different tables in the database, one parent and two children of that parent table. Making a form is to support this is not too difficult, as I just need to make a form that has two sub forms, and this allows me to enter data into all three tables at once, whereas doing them all separately is obviously quite a mess.



However, the problem with this sort of method is that each Excel file has over twenty records to enter into each child form. In order to copy and paste the relevant records from Excel into Access, I need to first create the records in the Access table, by pressing space in the first field and then pressing the down arrow twenty-some times. Then, there are twenty-some blank records into which I can copy and paste all the data from the Excel file. I am going to have to do this hundreds of times, and having to create the records by pressing the space bar seems a bit silly and time-prohibitive.

Importing and appending the files directly from Excel skirt this problem, but I do not know how I could do this for three tables at a time like the form allows me to do.

So, can anyone think of a way to solve this problem?

Thanks,

--Evan

[pbaldy]

I would probably write a procedure to import the records. I don't know the structure of the text file, but likely I'd use a recordset to add the parent record. That would let me get the key field value (which I assume isn't in the text file), which cn then be used to append the child records.

[John_G]

Hi -

You may be able to do this with forms at all, but first a couple of questions about the format oaf the tab-delimited file.

- Does each line in the tab-delimited file contain about the parent as well as one of the child records? In other words, is the data about the parent repeated (and identical) for each measurement? In the scenario I am thinking of, this is a good thing.

- Does each set of data in the file contain a unique (for that data set) identifier, such as a run number? Again a good thing.

So for example two sets of three might look like this:

1 A11 A12 A13 B11 B12 B13
1 A11 A12 A13 B21 B22 B23
1 A11 A12 A13 B31 B32 B33
2 A21 A22 A23 B41 B42 B43
2 A21 A22 A23 B51 B52 B53
2 A21 A22 A23 B61 B62 B63

If you have a setup like this, the solution might be fairly easy.

John