Passing variables to Function Modules

[RichF01]

Hi All, Rich the Muppet here again.

I have a module (constructed by Access by Save Macro as Module) as shown below. However, I am trying to make the input and output locations variable. The input files (highlighted in red) are all related by date, and are different in number, depending on date. The number of files at each date, and the file names are stored in a table within the database.

The outputs are variable in year and month (shown in blue)(again, stored in a table in the database)

Function TCG_Domestic()
On Error GoTo TCG_Domestic_Err

    DoCmd.TransferText acImportDelim, "", "TCG Domestic", [COLOR=red]"F:\Current Snapshots\TCG Domestic Pt1.txt" [/color], False, "", 50001
    DoCmd.TransferText acImportDelim, "", "TCG Domestic", [COLOR=red] "F:\Current Snapshots\TCG Domestic Pt2.txt" [/color], False, "", 50001
    DoCmd.OpenQuery "Pcode truncation", acNormal, acEdit
    DoCmd.OpenQuery "more pcode", acNormal, acEdit
    DoCmd.OpenQuery "Query TCG Domestic", acNormal, acEdit
    DoCmd.TransferSpreadsheet acExport, 8, "Summary TCG Domestic", "M:\Gas\Portfolio\Analysis\Date\[COLOR=red]2005\Jun[/color]\TCG_Domestic.xls", False, ""


TCG_Domestic_Exit:
    Exit Function

TCG_Domestic_Err:
    MsgBox Error$
    Resume TCG_Domestic_Exit

End Function

I assume that a for loop will sort out the number of files imported.

What I don't understand (and can't find in any help file/book/website) is the syntax of calling a table - record - fieldname[content of field]

I am familiar with Excel VBA and the format workbook.("")worksheet("").cell() and naively assumed it would be something like that.

Oh, I know I'm not meant to do this so don't mind if it doesn't get answered now, but would also like a new database generated for each date - rather than one huge database with lots of data.

Please help . . .

Cheers

Rich

 

[KenReay]

Hi

Someting Like:

Function TCG_Domestic(Pt1 As String. Pt2 As String)
On Error GoTo TCG_Domestic_Err

DoCmd.TransferText acImportDelim, "", "TCG Domestic", Pt1 , False, "", 50001
DoCmd.TransferText acImportDelim, "", "TCG Domestic", Pt2 , False, "", 50001
DoCmd.OpenQuery "Pcode truncation", acNormal, acEdit
DoCmd.OpenQuery "more pcode", acNormal, acEdit
DoCmd.OpenQuery "Query TCG Domestic", acNormal, acEdit
DoCmd.TransferSpreadsheet acExport, 8, "Summary TCG Domestic", "M:\Gas\Portfolio\Analysis\Date\2005\Jun\TCG_Domestic.xls", False, ""


TCG_Domestic_Exit:
Exit Function

TCG_Domestic_Err:
MsgBox Error$
Resume TCG_Domestic_Exit

End Function

If TCG_Domestic("F:\Current Snapshots\TCG Domestic Pt1.txt","F:\Current Snapshots\TCG Domestic Pt2.txt") Then ...

Is that what you mean?


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

For the variable number of input file, have a look at the Dir function.
To retrieve by code infos stored in a table, have a look either to Recordset (DAO or ADODB) or at the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886