Populate an Unbound Text Box in Access from Excel to return Query Results to Excel.

[alansidman]

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:

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

 

[strive4peace]

Hi Alan,

> "pass that to the unbound text box (myData) on the Access Form"

why?

you should use DAO when you dim your database and recordset objects

   Dim MyDatabase As dao.Database
   Dim MyRecordset As dao.Recordset

also, you should release your object variables when done. ie:

Proc_Exit:
   on error resume next
   set MyQueryDef = nothing
   if not MyRecordset  is nothing then
      MyRecordset .close
      set MyRecordset  = nothing
   endif
   if not MyDatabase is nothing then
      MyDatabase.close
      set MyDatabase = nothing
   endif

 

[alansidman]

you should use DAO when you dim your database and recordset objects

Code:

Dim MyDatabase As dao.Database Dim MyRecordset As dao.Recordset</pre>

Crystal, tried using DAO. Wouldn't work. Using Access 2010 and went with Library Reference
Microsoft 14.0 Access Database Engine Object Library to get it to work. Great suggestion on releasing the variables. Will add that to the code. Thank you.

There will only be two seat licenses for Access so some of the people that will need to obtain the query results (infrequently) will need to get the information through Excel. Without having to create two queries for the same information (one that has its criteria input in a search form in an unbound text box -- Access Users) and one as shown using a parameter query with the above stated code I was hoping to allow the search form to be populated by passing the criteria from Excel.

Have I confused you? Thanks for looking at it.
Alan

 

[strive4peace]

Hi Alan,

you're welcome

> tried using DAO. Wouldn't work. Using Access 2010 and went with Library Reference"

that doesn't make sense ... DAO is built-in to the Microsoft 14.0 Access Database Engine Object Library

the reason to specify dao when they are declared is in case you have another library with the same member referenced. For instance, there is also an ADO recordset object. Your code IS using dao ...

"unbound" means it is not stored -- so how can you change it? How about making a table where you use the first record to store the unbound values on that form so they are bound? then you can run an update query to change the table.

 

[alansidman]

Crystal, I think I see the confusion. The unbound text box on the form is only used to set the criteria (a date) for the query which is selected. There is no attempt to store any data. All data is currently in linked tables that are in a data mart which is updated monthly. I don't have any control over the data. I am only looking to extract information. It is important to specify the date of the snapshot in the data warehouse. The text box is cleared and reset to null after each query is run or the database is closed.

I will relook at defining the variables to DAO.

Alan

 

[strive4peace]

Hi Alan,

> "There is no attempt to store any data"

yes, but if you want to store the value then you have to store it in a table or a database property or put that value somewhere. If you don't want to bind the control, you can lookup the value when the form is Loaded and populate it with a default value

> " All data is currently in linked tables that are in a data mart which is updated monthly"

I was suggesting that you create a table in ACCESS not your linked back-end ...

 

[alansidman]

Crystal,

Thanks for the input. I have to give this some effort when I get back to the office. Really appreciate the lesson on DAO already in Microsoft 14.0 Access Database Engine Object Library. Explains why I was having some difficulty earlier.

Alan

 

[strive4peace]

you're welcome, Alan, please let me know if you need any more help