Solved: Working with MS Query/ SQL

[RoyLiam]

I am working with a userform to prepopulate addressee details eg in a letter, and have been trying to get the useform to talk to an ODBC connection to get the relevant information (address etc) based on the unique key for the recipient which is stored with the addressees.

I can get the various information returned into a one row table using the DATABASE command, and if only one item/column of data eg name is asked for then it seems to return a string which is helpful. I have been trying to get it to do this look up when the code box is exited in the userform which it does, but I am struggling to get this text into the default text for the next input boxes in the form for the user to confirm, as I can only seem to get it to write the results into the current selection.range using something along these lines Private Sub code_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If code <> "" Then
ActiveDocument.Bookmarks("to").Select
Selection.Range.InsertDatabase Format:=0, Style:=191, LinkToSource:=False, _
Connection:= _
"DSN=clients;Driver=Firebird/InterBase(r) driver;Dbname=srv01:e:\ibdata\livev6.fdb;CHARSET=NONE;PWD=123;UID=ABRO" _
, SQLStatement:= _
"SELECT ENTITY.EN_NAME FROM ENTITY ENTITY WHERE (ENTITY.EN_KEY='" & code & "')" _
& "", PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument _
:="", WritePasswordTemplate:="", DataSource:="", From:=-1, to:=-1, _
IncludeFields:=False

faxto.Text = Selection.Range ' this doesnt work as the insertdatabase function writes to the right of the bookmark
ActiveDocument.Bookmarks.Add "to", Selection.Range
End If
End Sub

I think I am going about this the wrong way and either there is a simple way of returning all of the 5 or 6 values into an array which can then be called upon for the relevant bits of it to populate the userform, after all if you request more than one at a time it returns them into a one row table format which feels like it should be close to an array format. Is it possible to return this straight into an array or do i need to return it into the blank document and then use a function to suck it into an array to then populate the userform for the users confirmation of the data before generating the letter.

Alternatively if the information needs to be called one by one then the insertdatbase functions seeme to be a bit rich each time and i wonder if there is a better command or way of doing this.

Has anyone looked at this recently, or is able to help in returning the database table into eg an array?

Any help is much appreciated

RoyLiam

[fumei]

1. I have a great deal of difficulty reading the post as it stretches twice the size of my screen width. If possible PLEASE use the underscore character in your code. Thanks.

2. I am confused by multiple things you seem to be asking about.

BTW: the only way I could comfortably read the post was to copy and paste the post into Word.

Is faxto.Text as textbox on a userform?

I don't see why you can not return whatever values you get into an array.


but I am struggling to get this text into the default text for the next input boxes in the form for the user to confirm, I have no idea what "the next input boxes" actually means. Perhaps you could clarify.

I would suggest not selecting the bookmark. Use the Range instead. So:ActiveDocument.Bookmarks("to").Select
Selection.Range.InsertDatabase Use:ActiveDocument.Bookmarks("to").Range.InsertDatabase etc

[RoyLiam]

Thanks Fumei the range insert works great.

Sorry for the delay in this reply and for the poorly laid out posting. I hope this is better.

Your code for using the range to insert the database works well. The table is then converted into text, and using the SPLIT command this populated into an array for reference. This allows the fields in the user form for fax number etc to be populated from the results.

Here is the code

Private Sub GetEntity_Click()
If code <> "" Then 'If the user has input a code to look up then
Dim oTable As Word.Table
Dim BMRange As Range
Dim oResults, oLogin, oSQL As String
Dim oTab
Dim oItems() As String

Set BMRange = ActiveDocument.Bookmarks("text").Range

oLogin = "DSN=Clients;Driver=Firebird/InterBase(r) driver;Dbname=" & _
"srv01:e:\ibdata\livev6.fdb;CHARSET=NONE;PWD=AC;UID=DBRO"
oSQL = "SELECT EN_PRETITLE, EN_FIRST, EN_LAST, " & _
"EN_COMPANY, EN_FAX, EN_SALUTATION " & _
"FROM ENTITY ENTITY WHERE (ENTITY.EN_KEY='" & code & "')"

BMRange.InsertDatabase LinkToSource:=False, Connection:=oLogin, _
SQLStatement:=oSQL, IncludeFields:=False

ActiveDocument.Bookmarks.Add "text", BMRange
If ActiveDocument.Bookmarks("text").Range.Tables.Count <> 1 _
Then Exit Sub
Set oTable = ActiveDocument.Bookmarks("text").Range.Tables(1)
oTable.ConvertToText Separator:=wdSeparateByTabs
Set oTable = Nothing
oResults = ActiveDocument.Bookmarks("text").Range.Text
oItems() = Split(oResults, Chr(9))
faxto.Text = oItems(0) & " " & oItems(1) & " " & oItems(2)
Firm.Text = oItems(3)
fax.Text = oItems(4)
Dear.Text = Left(oItems(5), Len(oItems(5)) - 1)
End If

The one glitch that I have got is that in running the code, the screen pops up with the window 'Select data source' with the three options '+Connect to new data source' '+New SQL Server connection' or 'Thumbs' presented. The user needs to press <Return> to allow the code to continue to run, and process the SQL.

I cannot see how to prevent this from happening, by for example simulating a carriage return press or tweaking a word configuration somewhere. Do you have any ideas?

Thanks again for your help

[RoyLiam]

I have seen some old posts regarding this window or one similar coming up, in
the instances of a mailmerge automation, and the suggested resolution was to
wat for a while and then send the required key press eg {Enter} to process
the window and continue the script

I have tried the following which should send <Alt><O> to the window after a couple of seconds but I think it is from an older version of word as it does not like the Application.Wait command

Is there an alternative to this command to do the same thing?


newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
WaitTimer = TimeSerial(newHour, newMinute, newSecond)
Application.Wait WaitTimer

SendKeys ("%O")

Regards

[RoyLiam]

A posting on the Microsoft Word Programming communities helped solve this - to avoid the select data source window appearing when using the insertdatabase command, use a FileDNS rather than a UserDNS for the ODBC driver.

For interest here is the text of the File DNS 'clients.dns' (Which is pretty standard)

[ODBC]
DRIVER=Firebird/InterBase(r) driver
UID=RO
CHARSET=NONE
Dbname=srv01:e:\ibdata\live.fdb

And here is the VBA which gets the information and inserts it into the various user form fields.

Private Sub GetEntity_Click()
If code <> "" Then
Dim oTable As Word.Table
Dim BMRange As Range
Dim oDatasource, oResults, oLogin, oSQL As String
Dim oTab
Dim oItems() As String

Set BMRange = ActiveDocument.Bookmarks("text").Range

oDatasource = "\\srv01\tf\clients.dsn (file://\\srv01\tf\clients.dsn)"
oLogin = "FILEDSN=" & oDatasource & ";PWD=IA;UID=RO"

oSQL = "SELECT EN_PRETITLE, EN_FIRST, EN_LAST, EN_COMPANY, " & _
"EN_FAX, EN_SALUTATION " & _
"FROM ENTITY ENTITY WHERE (ENTITY.EN_KEY='" & code & "')"

BMRange.InsertDatabase _
LinkToSource:=False, _
Connection:=oLogin, _
SQLStatement:=oSQL, _
DataSource:=oDatasource, _
IncludeFields:=False

ActiveDocument.Bookmarks.Add "text", BMRange
If ActiveDocument.Bookmarks("text").Range.Tables.Count <> 1 Then Exit Sub
Set oTable = ActiveDocument.Bookmarks("text").Range.Tables(1)
oTable.ConvertToText Separator:=wdSeparateByTabs
Set oTable = Nothing
oResults = ActiveDocument.Bookmarks("text").Range.Text
oItems() = Split(oResults, Chr(9))
faxto.Text = oItems(0) & " " & oItems(1) & " " & oItems(2)
Firm.Text = oItems(3)
fax.Text = oItems(4)
Dear.Text = Left(oItems(5), Len(oItems(5)) - 1)
End If

End Sub



I still need to include some error handling around it but the basics of it seem to work - thanks Fumei for your help.