I'm fairly new to access VBA and have a fairly specific task that I'm trying to complete. Basically I have some AS400 files linked into my database...the files themselves house payroll information. I need to first run a query against a table to determine the number of existing pay locations (easy enough)...next, I have to loop through this recordset to query another table and output each locations records to text file from transfer text to another location on the network. Any code samples would be greatly encouraged.

Thanks in Advance,

I am not sure what you want.

Do you want to know how to do the query, or how to create text files, or both etc.

Do you want to create a seperate text file for each location?



mmcdonal

Both please...Thank you in advance for taking the time.

So if I understand this, you want to select a pay location from a list, then query another table and output a text file that has all the data from the second table related to that location, then go to the next location in the list, and create a second text file with all the data related to that second location, etc.?

FIRST, create a query that has this structure already in it:


tblPayLocation
PayLocationID - PK

tblSecondTable
SecondTableID - PK
PayLocationID - FK
(Other fields...)

Then perhaps create a second query that just has the PayLocations in it.

Does this work? I need to know the basic structure of the recordset. What I propose is to go to PayLocation query and select a paylocation. Then go to the query with the second table and run it using paylocation as criteria. Then just output the query results as a text file (several ways to do this) then run the query again with the next location. This could all be done as a nested loop.

HTH

mmcdonal

Basically, right now I have a make table query that builds the pay locations on the fly each time the user enters a certain menu(this is done to accomdate possible changes to the number of pay locations)...from there this table is to become the 1st recordset...(the recordset has just 2 fields loc1 and loc2).

From there I want to select all fields from another table where loc1 and loc2 are equal to the recordset loc1 & loc2. Upon retrieval the records need to be output to a file.

Lastly the recordset needs to be looped until all records have been output to specific location files (done in step 2).

detailed example: I have payroll table with 3 locations with 100 records

location A accounts for 35 of the 100 records
location b accounts for 55 of the 100 records
location c accounts for 10 of the 100 records

After running the proposed code above I end up with 3 files
(a) file - 35 records
(b) file - 55 records
(c) file - 10 records

Thanks again for taking the time-

This is relatively easy to do. I am not sure why you would choose one parameter, and then output all the data anyway.

The first thing to sort out is the combo box the user wil use to find the locations. Why are they choosing two locations? If they are, they will need two combo boxes.

The combo boxes should each look up to the field that holds loc1 and loc2 respectively.

Then the combo box is going to have to be bound to this meaningful data, and not to a primary key. Make sure to choose Unique Value = Yes for the combo box.

Will this work? If so, send me the field names. I think you should run a query and package the query results. If not, let me know why.

Also, if you are going to loop through the table anyway, you can create a query that prepackages this data for you ready for output.

We can output it using a DoCmd, or using a FileSystemObject. Which do you prefer?



mmcdonal

First off, sorry for the confusion...Loc1 & Loc2 are actually two different fields in the table...each location has two identifiers loc1 & loc2 ( which stands for company & plant) ---next, I need to be able to output all the pay locations at once...not one at a time...basically the form has download cmd button that the user will press to download all locations. To my knowledge there is no way to select * from tbl2 with a group by for loc1 & loc2 and then create individual files on the group changes, so therefore I must first gather the locations and then do a select one by one and before advancing to the next location...I need to output to file. I hope this helps...Thanks.

You need to use a cascading combo box then where the user selects loc1 from a combo box, and then based on that selection, the second combo box is populated.

Is location 2 limited to only one location 1? For example:

Loc1 Loc2
MD Baltmore
MD Rockville
VA McLean
VA Fairfax

etc? So if the user selects MD, they can then select ONLY Baltimore and Rockville? Or is it like this:

Loc1 Loc2
Plant1 Cafeteria
Plant1 RestRoom
Plant2 Cafeteria
Plant2 RestRoom

etc? where loc2 is duplicated in each loc1?

Anywho, I still am not sure why you are asking for a parameter, and then outputting ALL the data regardless of the parameters.


mmcdonal

Stay away from the form design...I simply want to parse out records from one table into a csv file using location parameters from another table. I'm not worried about the UI as they will simply select the download cmd button all locations will be parsed at once. I'm using parameters from the first table to drive the results from the second. Think of it in this way

select * from tbl2
where [tbl2]loc1 = [tbl1]loc1 and [tbl2]loc2 = [tbl1]loc2

I essentially need to repeat this query by the number of locations in tbl1.

My guts tells me that the code needs to look something like this

DIM R as recordset
R = "table1" (table1 has two fields loc1 and loc2)

With R

Until is not EOF

select * from tbl2 where loc1 = r.loc1 and loc2 = r.loc2

output record set to file

Move.Next

Loop

Thanks,
Rabu

If the location is duplicated in table 2, why do you need to refer to table 1 at all? Or are there records in table 2 with locations that do not correspond to table 1? Either way, I don't think you need VB at all until passing the info to the csv file, a simple SQL query will get you what you want.

SELECT tbl2.*
FROM tbl1 INNER JOIN tbl2 ON tbl1.loc1 = tbl2.loc1 AND tbl1.loc2 = tbl2.loc2