Pass TextBox Value to Access Query

[jo15765]

I have this code that opens a word doc based on the value of a textbox...


Dim varProgram

varProgram = Reports_Selector.Txt_Word_Doc.Text

Set wordapp = CreateObject("word.Application")

wordapp.Documents.Open "C\Test\" & Form123.Text

wordapp.Visible = True


This code works great to open the word doc specified, what I don't know how to do, is when the word doc opens, it runs a mail merge and asks for an input parameter from the query. How can I create a textbox on the same form that will pass the value to the parameter window that will open once word opens?

[jo15765]

Upon my research, it looks like the only way I am going to be able to do what I am after is by actually modifying EVERY query to point to the txtbox on my Excel form as the reference point to receive the input parameter, is that correct?

[Kenneth Hobs]

Why would you want to enter data into a parameter window? Adapt the code posted in: http://www.vbaexpress.com/forum/showthread.php?t=39586

[jo15765]

I want to enter data into the parameter window since, once word is opened it asks for the ID. And I want to be able to input the ID into a textbox in Excel, and pass it to parameter window in Word when it displays. I can't figure out how to do it! I know I could re-write the query to accommodate to this, but this is a large scale project, I would have to re-write probably over 1,000 queries, and I don't want to have to do that. And the same situation with, I know I can use Excel to run the SQL behind the query, which that might be a viable option, but would like to steer away from it at this current time since the actual SQL varies depending on certian criteria it is never 100% the same.

From my previous post about using ADO and the SQL string, I have the queries already built out in access, and the query itself varies, so I don't want to have to bring in the SQL into Excel. I just want to try to pass the value of the textbox in Excel into the parameter window in Word.

[jo15765]

What about something like this where the code is passed from Excel to the source query back in Access:

Dim cmd As New ADODB.Command
Dim rs as New ADODB.Recordset

Dim sQueryName as String
Dim lParam1 as long


lParam1 = 100

sQueryName = "QueryName"

With cmd
'con is an active ADODB.Connection
Set .ActiveConnection = con
.CommandText = sQueryName
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("Param1", adInteger, adParamInput, ,lParam1)
End With

Would something similar to this work?

[mdmackillop]

You can pass a parameter to a macto in the Word document which could run your mailmerge

wordApp.Documents.Open Doc
wordApp.Visible = True
wordApp.Run "RunMailMerge", "Output"

[jo15765]

When my word doc opens it is automatically running the mail merge and asking me for the parameter. How would I set up a macro in word to capture data from my Excel textbox that would pass to the parameter window?

[Kenneth Hobs]

I don't use parameters myself all that much. You might be better off asking in an Access forum.

In Excel, I have used stored Access SQL queries. See the commented links for other examples.

'http://www.vbaexpress.com/forum/showthread.php?t=24118
'http://www.vbaexpress.com/forum/showthread.php?t=24575
'http://www.vbaexpress.com/forum/showthread.php?t=23783
'http://www.vbaexpress.com/forum/showthread.php?t=26145
Sub Test()
Dim mdbPath As String, dbName As String, cmdText As String
Dim rngDestination As String
'mdbPath = "E:\ADO\NWind2003.mdb" 'change the path here to suit your needs
'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
dbName = "NWind2003_1" 'change the database name here to suit your needs
cmdText = "Aug94" 'change the stored SQL here to suit your needs
rngDestination = "A1" 'change the destination range here to suit your needs

'Clear previous data
Cells.Delete

InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination

'Insert other data to the right of A1 with a blank column separating the two
rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
cmdText = "Sales by Category"
InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
End Sub

Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
cmdText As String, rngDestination As String, _
Optional bFieldNames = True)

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & mdbPath & ";Mode=ReadWrite;Extended Properties=""" _
, """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab" _
, "ase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";J" _
, "et OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co" _
, "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("" & rngDestination & ""))
.CommandType = xlCmdTable
.CommandText = Array(cmdText)
.Name = dbName
.FieldNames = bFieldNames
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = mdbPath
.Refresh BackgroundQuery:=False
End With
End Sub

[mdmackillop]

Instrad of the Word Macro asking, you provide it when calling


Sub Test()
Dim Doc As String
Doc = "C:\a\Test.doc"
Set wordApp = CreateObject("word.Application")
wordApp.Documents.Open Doc
wordApp.Visible = True
wordApp.Run "RunMailMerge", TextBox1.Text
End Sub

'In Word
Sub RunMailMerge(MyData)
MsgBox MyData
End Sub

[jo15765]

Mdmackillop ---

This piece of your code:

Sub RunMailMerge(MyData)
MsgBox MyData
End Sub


Is this the macro I would record in word that would capture the data from my Excel TextBox?

[mdmackillop]

Can you post your Word mailmerge code?

[jo15765]

Would that be the SQL from the access query that runs when the word doc is opened?

If that is the case here is the SQL from the query:

SELECT tbl_Richard.ID, tbl_Richard.FName, tbl_Richard.lName, tbl_Richard.phone...
FROM tbl_Richard
WHERE (((tbl_Richard.ID)=[ID]));

[mdmackillop]

Can you post the whole of your Word macro so we can see how the input request is handled?

[jo15765]

I checked the VB editor and there was no code in there. To actually create the mail merge, I opened word and went to Tools --- Mail Merge and opened my Database, and selected the query.

[mdmackillop]

It's a long time since I worked with MailMerge!
Can you post your Word document, removing any sensitive text.

[jo15765]

I stripped all sensitive data from the word doc, and when the word doc opens you get a pop-up from the access database query asking for the ID.

[mdmackillop]

I'm trying this but having problems. I guess a mixture of 2010, 2007 & 2000 is creating issues I can't understand

Sub Test()
Dim Doc As String
Doc = "C:\aaa\1234.doc"
Set wordApp = CreateObject("word.Application")
With wordApp
.Documents.Open Doc
.Visible = True
.DataSource = "C:\AAA\1234.mdb"
.DataSource.QueryString = "SELECT * FROM `qry_1234` WHERE `ID` = " & TextBox1.Text
End With
End Sub

[jo15765]

Would hte above code go in Word or in my Excel form? I am attaching a template as to how I am trying to set it up, and I have the code already functioning for my top textbox.

[Kenneth Hobs]

Jo15765, your xls file had nothing in it.

I am not sure how this thread is different than your thread last month. Did you decide to go ahead and use VBA rather than c#? http://www.vbaexpress.com/forum/showthread.php?t=39586

You say that you want to use the SQL string in Access which is CommandText. You also want to use a parameter. IF you insist on using a parameter in MSWord or Excel then no elegant solution is possible that I know of. The examples that I posted in this thread and the other show how to use a built SQL string or use a stored SQL in Access.

To best help, make very simple example files. Otherwise we come up with a 10 solutions and you wanted solution 11. While this does take time, it is worth it from the perspective of the scope of your need. Zip and post a DOC, MDB, and XLS file or Microsoft 2010 equivalent files. The MDB should have 1-2 tables with the stored SQL's if that is what you want to use. The DOC file would be a manually built mailmerge that could possibly be queried for it's SQL string and then add a Where clause with the value of the Excel Textbox control. To use a parameter value, you would instead be using a Where clause in your SQL string.

Mdmackillop, your MSWord QueryString should probably have the value encapsulated in slanted quotes. As I read his posts, he does not want to send a QueryString. Obviously, that is the easier method though.

[jo15765]

Attached is a zip file, with an access database, an excel spreadsheet/userform with VBA code behind, and the word doc.