alansidman
Well-known Member
- Joined
- Feb 26, 2007
- Messages
- 8,136
- Office Version
- 365
- Platform
- Windows
I have some users that do not have Access installed. There is a standard query in Access that requires a date be added to query criteria through an unbound text box on a form. The criteria is Forms!SearchForm.Mydata. In Access this works and runs fine for the users with Access.
Additionally, I have created some code that the Excel users can run to populate the criteria with a date if I chose to go that route and return the data to Excel. This would require a duplication of queries.
That code is as follows:
I would like to modify the code above to take the date in cell D3 and pass that to the unbound text box (myData) on the Access Form and run the query and return the results to an excel spreadsheet. Any thoughts on this?
Thanks
Alan
Additionally, I have created some code that the Excel users can run to populate the criteria with a date if I chose to go that route and return the data to Excel. This would require a duplication of queries.
That code is as follows:
Code:
Sub RunParameterQuery()
'Step 1: Declare your variables
Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim MyRecordset As Recordset
Dim MyPath As String
Dim MyDatabaseName As String
Dim MyQuery As String
Dim i As Integer
MyPath = "H:\CCDM Prod DB files\"
MyDatabaseName = Range("D4").Value
MyQuery = Range("D5").Value
'Step 2: Identify the database and query
Set MyDatabase = OpenDatabase(MyPath & MyDatabaseName)
Set MyQueryDef = MyDatabase.QueryDefs(MyQuery)
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Date]") = Range("D3").Value
End With
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 5: Clear previous contents
Sheets("Main").Select
ActiveSheet.Range("A9:K10000").ClearContents
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A10").CopyFromRecordset MyRecordset
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(9, i).Value = MyRecordset.Fields(i - 1).Name
Next i
MsgBox "Your Query has been Run"
End Sub
I would like to modify the code above to take the date in cell D3 and pass that to the unbound text box (myData) on the Access Form and run the query and return the results to an excel spreadsheet. Any thoughts on this?
Thanks
Alan