help with sql sentences with word text box

[irisbu]

Hello everybody!

I have created a form in word to automatically create mail merges from an access database.

I have the following code:

Private Sub CommandButton1_Click()
   Documents.Open FileName:="D:\" & ComboBox1.Text, ConfirmConversions:=False, _
        ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
        PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
        WritePasswordTemplate:="", Format:=wdOpenFormatAuto, DocumentDirection:= _
        wdRightToLeft
    ActiveDocument.MailMerge.OpenDataSource Name:="D:\managers.mdb", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=D:\managers.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locki" _
        , SQLStatement:="SELECT * FROM `managers` WHERE dereg LIKE 'management'", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
 
    On Error Resume Next
 
    Windows("madbekot.doc").Activate
    Windows("michtav1.doc").Activate
 
    ActiveDocument.Save
  ActiveDocument.Close
 
Unload UserForm1
 
End Sub
 

notice this sql sentence:

 SQLStatement:="SELECT * FROM `managers` WHERE dereg LIKE 'management'", SQLStatement1:="", SubType:= wdMergeSubTypeAccess
 

instead of the prase LIKE 'management' I would like to put the textbox1.text from the word user form...

does anyone know how I can do this?

I hope my question is clear...

[FishVal]

Shalom, irisbu.

When you add control (like textbox or other) to a document a property with the control name appears in the document class. So try something like the following.

SQLStatement:="SELECT * FROM `managers` WHERE dereg LIKE '" & ThisDocument.textbox1.Value & "';", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
 

"Value" property is a default for TextBox class - it may be omitted and if the code is being executed in [ThisDocument] module, then reference to [ThisDocument] object may be omitted too.

Regards,
Fish

[irisbu]

Thank you very much for your help!

I have now another problem... I want to choose specific names from the same access db and mail only them...
they don't have any particular characteristic... I just want to choose them by their names...
Lets say I have list of 1000 people, I want to create a macro that opens the mail merge addressee and choose the ones I want to mail to manually and continue with my macro...

How can I do this?

Thank you in advance

Iris

[FishVal]

Hello, Iris.

You need to make more clear decisions about the interface you want to build.
What about ListBox or ComboBox populated from the correspondent Access table?

Regards,
Fish.

[irisbu]

Hi Fish

Textbox or combobox would not give me the solution I was looking for... I wanted to open a list with checkboxes so I could select the names I wanted to mailmerge...
I have found the answer and it is working great!

I thank you very much for your efforts to help me!

Regards,

Iris

[irisbu]

Shalom, irisbu.

When you add control (like textbox or other) to a document a property with the control name appears in the document class. So try something like the following.

SQLStatement:="SELECT * FROM `managers` WHERE dereg LIKE '" & ThisDocument.textbox1.Value & "';", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
 

"Value" property is a default for TextBox class - it may be omitted and if the code is being executed in [ThisDocument] module, then reference to [ThisDocument] object may be omitted too.

Regards,
Fish

Hello Fishval,

I have another question and I hope you can help me to get it right this time like you have before...

I have a database in access and I want to add a picture from the data base to a WORD document...
I have created a form with combobox1, but I don't ger the syntax write....
here is what Iv'e done:

Selection.InlineShapes.AddPicture Filename:="F:\pictures\'" & combobox1.column(2)"'";,_
LinkToFile:=False, SavewithDocument:=True

again, I can't ger the syntax wright and it is so frastrating!!

I hope you can HELP me!!

Thank you in asvance,

Iris

[irisbu]

Hello FishVal,



I have another question and I hope you can help me to get it right this time like you have before...



I have a database in access and I want to add a picture from the data base to a WORD document...

I have created a form with combobox1, but I don't ger the syntax write....

here is what I've done:



Selection.InlineShapes.AddPicture Filename:="F:\pictures\'" & combobox1.column(2)"'";,_

LinkToFile:=False, SavewithDocument:=True



again, I can't get the syntax Wright and it is so frustrating!!



I hope you can HELP me!!



Thank you in advance,



Iris

[FishVal]

....

Selection.InlineShapes.AddPicture Filename:="F:\pictures\'" & combobox1.column(2)"'";,_
LinkToFile:=False, SavewithDocument:=True
 

....

Hi, Iris.

• First of all, what this semicolon is all about?
• Second. Underscore symbol as line continuation will work better when separated by space from the rest of symbols.
• Third. Concatenation operator is missed.
• Fourth. I'm pretty sure the previous three problems you could easily fix yourself or run macro recorder one more time. Please try next time post a code that at least passes VBA syntax check.

Now about the code itself.
Filename argument doesn't look ok.
"F:\pictures\'" & combobox1.column(2)"'"

• First. Single quotes are not needed unless they are not a part of filename.
• Second. [combobox1] control should be referred via "combobox1" property of "ThisDocument" object.
  

  ThisDocument.combobox1.column(2)
   

• Third. Make sure the above reference really returns what you are expecting. For example, in VBA immediate window (if you don't see it press Ctrl-G) type
  

  ? ThisDocument.combobox1.column(2)
   

  and press enter. Check what it returns.

Kind regards,
Fish

[irisbu]

I have a new problem with vb in word+access... hope you can help me!!

I have a table in access with 12 columns.

I have created a form in WORD with 1 combobox and 12 textboxes.

when I choose a value in the combobox - all the textboxes must be populated according to the combobox...

can you show me a code that populates all the textboxes?

I have a code that is not working so well.... If you have a better code... I will be most gratefull!!!

the problem with my code is that only combobox1.column(0) to combobox1.column(9) are updating successfully.... combobox1.column(10) to combobox1.column(11) don't update...

here is the code:

Private Sub Combobox1_Change()
On Error Resume Next
textbox1.text=combobox1.Column(0)
textbox2.text=combobox1.Column(1)
textbox3.text=combobox1.Column(2)
textbox4.text=combobox1.Column(3)
textbox5.text=combobox1.Column(4)
textbox6.text=combobox1.Column(5)
textbox7.text=combobox1.Column(6)
textbox8.text=combobox1.Column(7)
textbox9.text=combobox1.Column(8)
textbox10.text=combobox1.Column(9)
textbox11.text=combobox1.Column(10)
textbox12.text=combobox1.Column(11)
end sub


Private Sub UserrForm_Initialize()
On Error Resume Next

Dim dbDatabase As Database
Dim rst As Recordset
Dim t As Integer
Set dbDatabase=Opendatabase("f:\mine.mdb)
Set rst = dbDatabase.OpenRecordset("Select * FROM expressions ORDER BY Hebrt;", dbOpenSnapshot)

Combobox1.Clear
t=0
with rst
On Error Resume Next
Do Until .EOF
Combobox1Additem(t)
Combobox1.ColumnCount = 5
Combobox1.BoundColumn = 5
combobox1.ColumnWidths = "2 in; 0 in; 0 in; 0 in; 0 in;"
Combobox1.AutoTab = True
Combobox1.Column(0, t) = rst.Fields("hebRT")
Combobox1.Column(1, t) = rst.Fields("heb")
Combobox1.Column(2, t) = rst.Fields("engRT")
Combobox1.Column(3, t) = rst.Fields("eng")
Combobox1.Column(4, t) = rst.Fields("me")
Combobox1.Column(5, t) = rst.Fields("tuo")
Combobox1.Column(6, t) = rst.Fields("him")
Combobox1.Column(7, t) = rst.Fields("her")
Combobox1.Column(8, t) = rst.Fields("them")
Combobox1.Column(9, t) = rst.Fields("chem")
Combobox1.Column(10, t) = rst.Fields("hol")
Combobox1.Column(11, t) = rst.Fields("long")

.MoveNext

t = t + 1

Loop

End With

End Sub

Hope You can Help me with a better code that Works...

[FishVal]

Hello, Iris.

• First, comment out
  On Error Resume Next
  line in both subroutines to check when and why code fails.
• Combobox1.ColumnCount property is set to 5 while you have 12 columns. I wonder how it nevertheless works for columns 5 to 9.
• Combobox1.ColumnWidths property is tuned for 5 columns as well.

[irisbu]

It works the same when Combobox1.ColumnCount property and Combobox1.ColumnWidths property is set to 12 or to 5...

do you know how to insert the data to the formfields without using the combobox1.column and insted have an other code that opens the recordset and inserts the values I need to the formfields?

@FishVal

[FishVal]

Why do you think "other" code will work better?

[irisbu]

I think it will be less "heavy" when the database gets bigger...
@FishVal

[FishVal]

That's quite wise.

So. I suggest you the following algorithm:

• As soon as criteria for a particular record is defined (user has made a choice from combobox or entered something in textbox) construct SQL statement string that will open filtered recordset (using WHERE clause) containing one required record.
• Fill your textbox controls in a way you already do but from recordset.

P.S. While developing and testing code comment out that

On Error Resume Next
 

line.

[irisbu]

The only BIG problem I have... I DONT KNOW HOW TO DO THIS.... I am working on this since yesterday... and NADA!! - It seposed to be so easy - and I'm failing miserably again and again!!

Can I send You the code so you will be able to show me how to do this?

PLEEAASSEE!!! I'm Dying Here!! ... Sorry that I'm So dramatic... :-(

[irisbu]

In writing the code I asume that:

I have an Access database called Nihul.mdb
with one table called : expressions


I have a WORD UserForm1.
With 1 combobox -> combobox1
and 12 Textboxes

When I choose a value in combobox1, The 12 textboxes gets there values from the database.

Private Sub UserForm_Initialize()
'On Error Resume Next
Dim dbDatabase As Database
Dim rsi As Recordset
Dim i As Integer
Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rsi = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER BY hebrt;", dbOpenSnapshot)
ComboBox1.Clear
i = 0
With rsi
'On Error Resume Next
Do Until .EOF
ComboBox1.AddItem (i)
ComboBox1.ColumnCount = 5
ComboBox1.BoundColumn = 5
ComboBox1.ColumnWidths = "2 in;0 in;0 in;0 in;0 in;"
ComboBox1.AutoTab = True
ComboBox1.Column(0, i) = rsi.Fields("hebRT")
ComboBox1.Column(1, i) = rsi.Fields("heb")
ComboBox1.Column(2, i) = rsi.Fields("engRT")
ComboBox1.Column(3, i) = rsi.Fields("eng")
ComboBox1.Column(4, i) = rsi.Fields("hebMakor")
.MoveNext
i = i + 1
Loop
End With
End Sub

When I choose a Value in combobox1 one :

Private Sub ComboBox1_Change()
On Error Resume Next

textbox1.text=
textbox2.text=
textbox3.text=
textbox4.text=
textbox5.text=
textbox6.text=
textbox7.text=
textbox8.text=
textbox9.text=
textbox10.text=
textbox11.text=
textbox12.text=

THIS IS WHERE I'M STUCK....


End Sub

I really hope you can help me

[FishVal]

Dear Iris,

Looking at your code I could hardly imagine you do not know how to develop a simple procedure to query database the way you like.
The algorithm consists of the following steps:

• In procedure handling Combobox.Change event after user has chosen a value uniquely identifying particular record in database construct SQL expression opening filtered recordset containing this particular record only.
  
  Something like the following:
  

  strSQL="SELECT * FROM expressions WHERE <..unique field name ..> = " Combobox1.Column(<..relevant column number..>) & " ORDER BY hebrt;"
  Set rsi = dbDatabase.OpenRecordset(strSQL, dbOpenSnapshot)
   

• Assign field values of thus obtain recordset to textbox controls - just as you have already in code but from recordset instead of combobox or just as you have already in code but to textbox controls instead of combox.

[irisbu]

I can't use Combobox1.Column(#) because of the error in Combobox1.Column(10)...(11)....

so here is a code I wrote...

Private Sub ComboBox1_Change()
On Error Resume Next
Dim dbDatabase As Database
Dim rsi As Recordset
Dim i As Integer
Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rsi = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER BY hebrt;", dbOpenSnapshot)

TextBox1.Text = rsi.Fields("hebrt")
'MsgBox TextBox1.Text
TextBox2.Text = rsi.Fields("heb")
'MsgBox TextBox1.Text
TextBox3.Text = rsi.Fields("engRT")
'MsgBox TextBox1.Text
TextBox4.Text = rsi.Fields("eng")
'MsgBox TextBox1.Text
TextBox5.Text = rsi.Fields("hebMakor")
'MsgBox TextBox1.Text
TextBox6.Text = rsi.Fields("employeeName")
'MsgBox TextBox1.Text
TextBox7.Text = rsi.Fields("lastUpdate")
'MsgBox TextBox1.Text
TextBox8.Text = rsi.Fields("hebShort")
'MsgBox TextBox1.Text
TextBox9.Text = rsi.Fields("engShort")
'MsgBox TextBox1.Text
TextBox10.Text = rsi.Fields("englong")
'MsgBox TextBox1.Text
TextBox11.Text = rsi.Fields("heblong")
'MsgBox TextBox1.Text
End Sub

The problem with this code is that when I change selection in combobox1 the 2nd time the values in the textboxes dows'nt change.... I'm missing something here...

[FishVal]

Your code every time opens the same recordset no matter what combobox1 value has changed to.
I recommend you to build a query in Access query design view to get a whatever single particular record you want to populate your form with and check syntax of thus obtained query SQL expression.

[irisbu]

Dim rsi As Recordset
Dim i As Integer
Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rsi = dbDatabase.OpenRecordset("SELECT * FROM expressions WHERE hebRT = '" & ComboBox1.Text & "'", dbOpenSnapshot)

something is wrong with this line - and I can't fined what

Set rsi = dbDatabase.OpenRecordset("SELECT * FROM expressions WHERE hebRT = '" & ComboBox1.Text & "'", dbOpenSnapshot)

can you help me with this?

[FishVal]

Dear Iris.

Obtain what

"SELECT * FROM expressions WHERE hebRT = '" & ComboBox1.Text & "'"
 

is being evaluated to during runtime, copypaste it to Access query designer and try to execute.
It will tell what he doesn't like in your SQL expression.

Personally I like it very much but I'm not going to execute it for you. :D

[irisbu]

Hi again fishval.

I have created a userform in word that drows it's data from an access database.

I need to do a search of a string inside the value of a database...

for example -

I have a :

userform1 with textbox1 and a search button1.

the userform is fed from a database - db1, table1, City (column)

in the textbox I want to search for the City name, not only for the first letter - but according to any string I want... for example: if the name of the City is New-york, I want to search for the string "yo" or for the string "rk"...

can you show me how it's is done?

Thank You So very much!

Iris

[irisbu]

hi fishval
what is an access query designer?

[FishVal]

Hi, Iris.

It is query design view.
To test SQL sentence create a new empty query, change view to SQL, copypaste your SQL sentence and try to execute the query.

[irisbu]

but I don't know what the missing operator is....

this is the code:

Set rs = db.OpenRecordset("SELECT * FROM expressions order by heb WHERE '" &
TextBox2.Text & "' = InStr(![heb], TextBox1.Text)")

maybe you can help me with that? ... I'm probebly blind to it from looking at it so much...

[FishVal]

Trying to execute VBA code where SQL is expected will not make happy anyone. ;)
Paste to query what this expression is being evaluated to during runtime.
e.g.
put something like

debug.print "SELECT * FROM expressions order by heb WHERE '" & TextBox2.Text & "' = InStr(![heb], TextBox1.Text)"
 

to vba code before problematic line, run the code and copypaste to query designer from Immediate window what was put where by debug.print command.

[irisbu]

Problem Solved... Thank you for everything!