Variables when using a Module

  • Thread starter Thread starter Jon Poste via AccessMonster.com
  • Start date Start date
J

Jon Poste via AccessMonster.com

Hi,
I am running a module in an AC97 database and require a little
assistance. I have a module that runs throught and create a number of word
documents that we have to send to our suppliers. There is no problem with
this process, however each time the documents have to be saved into a
different place depending on the order numbers.

I have been thinking and the best way I can come up with is to have the
file paths and SQL statements variable in the module. I have achieved this
with the SQL source but unsure how to add any more parameters to the query.

********************************************************
Sub CreateWordReportIndustry(Optional SQLSource)

Dim strURL$, strFileName$, strFilePath$
Dim objWord As Word.Application
Dim db As Database, rs As Recordset

Set db = CurrentDb()
'Set rs = db.OpenRecordset("SELECT * from qryFeedIndustryUpdateAll")
Set rs = db.OpenRecordset(SQLSource)
*********************************************************

The rs is using the (Optional SQLSource) parameter OK but I cannot add any
more.

I am not sure if this is the right way, but any help would be greatly
appreciated.

Cheers
Jon
 
Hi,
Just me again, thought of another way to do it that may get around the
variable being passed through.

The location of each file is based on the first two digits of the order
number. Would it be a better solution to get these two digits and then do
an if statement i.e.

If (Left(OrderNo), 2) = 11 then
Do Something
Else
Do something else
End If

I am really just asking which is the best way round this.

Cheers Again.
Jon
 
Hi,
You can use objWord.SaveAs to save to any path you need.
say:

If (Left(OrderNo), 2) = 11 then
objWord.SaveAs "c:\" & OrderNo & ".doc"
Else
objWord.SaveAs "D:\" & OrderNo & ".doc"
End If

also - you can't have SQLSource as optional, as it is requred for
db.OpenRecordset()
 
Cheers for the reply Alex.

I have used the optional statement as that is the only way I know how to
pass through a variable to the module. Because the module is always being
called by code I can assure that SQLSource will always be present.

I am now using a case statement to check the first two digits of the
OrderNo and it is working fine.

Thanks for your help.

Jon.
 
Back
Top