I replacing data periodically into a linked Excel file and depending on the month the analysis is taking place it uses a different query as a record set. If I could change the record set with a case statement and use this as a function that would be nice. I just don't know if it can be done.

Function ExportQueryToLocation1()
On Error GoTo ErrorHandler
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = False
Set xlWB = objXL.Workbooks.Open("C:\Inventory\InventoryAnalysisLoc1")
Set xlWS = xlWB.Worksheets("Location1")

Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Usage1P1", , dbOpenDynamic)
' Can this be set in a Case statement?
rs.MoveFirst
i = 2
Do Until rs.EOF
With xlWS
' assign records to specific cells
.Range("A" & i).Value = rs.Fields("Field1").Value
.Range("B" & i).Value = rs.Fields("Field2").Value
.Range("C" & i).Value = rs.Fields("Field3").Value
.Range("D" & i).Value = rs.Fields("Field4").Value
.Range("E" & i).Value = rs.Fields("Field5").Value
.Range("F" & i).Value = rs.Fields("Field6").Value
.Range("G" & i).Value = rs.Fields("Field-etc").Value
.Range("H" & i).Value = rs.Fields("Field-etc").Value
.Range("I" & i).Value = rs.Fields("Field-etc").Value
.Range("J" & i).Value = rs.Fields("Field-etc").Value
' etc
End With
i = i + 1
rs.MoveNext
Loop

'Manipulate Excel
objXL.Application.Run "InventoryAnalysisLoc1!CalcOrderPoint"
objXL.activeworkbook.Save
objXL.Application.Quit
MsgBox "New data has posted."
rs.Close
db.Close
Set xlWB = Nothing
Set xlWS = Nothing
Set objXL = Nothing

Exit Function
ErrorHandler:
' Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
' Resume with statement following occurrence of error.
Resume Next

End Function

Use a different query based on month? It would be fairly simple:


Dim strQuery as String

Select Case Month(Date())
Case 1
strQuery = "QueryToUseForJanuary"
Case 2
strQuery = "QueryToUseForFebruary"
...
End Select

Set rs = db.OpenRecordset(strQuery, , dbOpenDynamic)

Thanks Paul.
This question will indicate my lack of knowledge. Do I use this in the Option box case statement or in the function module?

If the choice is to be determined automatically based on the date, put it in the function. If the user is to pick the month, either do it in the option group code and pass the name of the query to the function, or just pass the value of the option group to the function and do it there.