Automated Form with DB and Tables ??? TRUBLE LOL

[redhead]

Hi Everyone,

I'm so new, so bare with me...

ok, here is my story, i created an automated form template so as soon as it's open the vb form opens and the user fills it out and it puts it into the document for me...

there is a section of the form that would require sever or little amounts of data, that would go into a table, so i set up a access db and have the one form, so when the user can enter the info, then add more data or go to the next user form. I have it adding to the db, and removing from the db. but i just can not figure out how to get the data out and into a table in the document... all i want is all the data thats in the one table (db only has one table) and for that info to be put on page 5 of the document in a table form... i just can not figure this out... had alot of coffee trying to... LOL

here is what i have...

Private Sub BTNNext_Click()
'declare variables for new connection and recordset and declare variables

Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim pstrSql As String
Dim strSql As String
Dim myCell As Word.Cell
Dim myRow As Integer
Dim myCol As Integer

'provide connection string for data using Jet Provider for Access database
vConnection.ConnectionString = "data source=c:\db\datadb.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
'open connection

vConnection.Open
vConnectionState = vConnection.State

strSql = "SELECT * FROM datatable"
vConnection.Execute strSql

vRecordSet.MoveFirst
With wordApp.ActiveDocument
For myRow = 2 To 23
For myCol = 1 To 2
Set myCell = ActiveDocument.Tables(1).Cell(myRow, myCol)
If Not vRecordSet.EOF Then
If myCol = 1 Then
myCell.Range.Text = CStr(vRecordSet!section1 & " " & vRecordSet!row1)
Else
myCell.Range.Text = Right(vRecordSet!section1, 2)
ExamInfoRst.MoveNext
End If
'End With
Else
myCell.Range.Text = " "
End If
Next myCol
'MsgBox "in the loop " & CStr(myRow)
Next myRow

End With
If vRecordSet.EOF Then
vRecordSet.Close
Set vRecordSet = Nothing
End If
pstrSql = "DELETE * FROM datatable"

vConnection.Execute pstrSql

'get next set
UserForm5.Hide
UserForm6.Show
End Sub


can anyone help me... please

[XLGibbs]

I think the excel cell references and method won't work in Word...

I think you need to do

If Not vRecordSet.EOF Then

.Text:= vRecordSet!section1 & vbTab & vRecordSet!row1 & vbCrLF
'for your header row

.Text = Right(vRecordSet!section1, 2)
'for the other rows....

ExamInfoRst.MoveNext
End If


I am not sure of the correct syntax for word/access...
but I am fairly sure the excel references are unique to excel

[TonyJollans]

I haven't done this for a while and can't remember exactly what you do but I think you need to changevConnection.Execute strSqltoSet vRecordSet = vConnection.Execute(strSql) before you can use the vRecordSet variable.

I'm also suspicious ofExamInfoRst.MoveNext

[XLGibbs]

ExamInfoRst.MoveNext

Should be

vRecordset.Movenext

If it is meant to cycle through the vRecordset established...good spot Tony..

I am still unsure of writing the recordset to word though, i just haven't had to do that yet

[redhead]

Hi I found this walkthrough to creat a table and add data to the table with access but i can not get it to work

what do you guys think of this?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/odc_VSTWordtbl.asp

[TonyJollans]

Hi redhead,

That link is about coding with VSTO in .NET. I was a bit confused earlier about exactly where your code was running with ADO and also references to the Word Application. Can you give a little more detail about what you're trying to do. Do you want to develop code in Word? Or somewhere else to run in Word? Or to run somewhere else?

[redhead]

Hi I'm trying to develop the code in word, a word macro, all i want is a user form, something like what i have attaced.. with the data coming from an access db..

i went created a new template, then created a macro, in there using the vba i created a user form, with 8 files, this macro runs when the document is loaded.. what i want to is with this form add the data to the datadb, to the datatable and then when the person selects done, the form loads all the data from datatable into the active word document into a table form and then delets .. and loads a new form...

i have the form adding and deleteing just not getting the info into the active word document onto page 5 with the datafrom the db...

here is what i have for when the user selects the next button...

Private Sub BTNNext_Click()
'declare variables for new connection and recordset and declare variables

Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim pstrSql As String
Dim strSql As String
Dim myCell As Word.Cell
Dim myRow As Integer
Dim myCol As Integer

'provide connection string for data using Jet Provider for Access database
vConnection.ConnectionString = "data source=c:\db\datadb.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
'open connection

vConnection.Open
vConnectionState = vConnection.State

strSql = "SELECT * FROM datatable"
vConnection.Execute strSql

vRecordSet.MoveFirst
With wordApp.ActiveDocument
For myRow = 2 To 23
For myCol = 1 To 2
Set myCell = ActiveDocument.Tables(1).Cell(myRow, myCol)
If Not vRecordSet.EOF Then
If myCol = 1 Then
myCell.Range.Text = CStr(vRecordSet!section1 & " " & vRecordSet!row1)
Else
myCell.Range.Text = Right(vRecordSet!section1, 2)
ExamInfoRst.MoveNext
End If
'End With
Else
myCell.Range.Text = " "
End If
Next myCol
'MsgBox "in the loop " & CStr(myRow)
Next myRow

End With
If vRecordSet.EOF Then
vRecordSet.Close
Set vRecordSet = Nothing
End If
pstrSql = "DELETE * FROM datatable"

vConnection.Execute pstrSql

'get next set
UserForm5.Hide
UserForm6.Show
End Sub


i have attaced a copy of the db and the template...

[TonyJollans]

Hi redhead,

All that you seem to be doing is collecting data in a Userform and trying to add it to a table in your document - why are you trying to use a database for this?

[redhead]

just becasue there is no set amount of data that will be entered,

could this be done in an array of some type..

i just want them to add data, as much as they want and put it into a table.. i just thought the db would work..

is there a better way?

[TonyJollans]

Why not just put it straight into the table in the document? One row at a time.

In fact, unless you have some validation to run, I'm not even sure what you gain by having the Userform at all. What is the problem with simply typing into a table?

[redhead]

well it's a long long story.. its a way to help a child with a learning problem to sort out stuff they type in and they the see everthing they get.. they wanted a form so they could sit with him and tell him 8 words or numbers and stuff like that.. and he types them in, and they go throught it.. they wanted to be able to do it as many times as long as he is sitting still.. so there is no real rim or reason for the table colums names or anything like that i just really wanted to help this kid out..

the first part of the form has sections where he can type in a paragraph, short story.. there is one form where he works on typing his name address and stuff... thats why i wanted to be able to put this table into page five...

how can i do it with one form, add to a table, and then let him add more or not...

[redhead]

after many hours of looking and playing i came up with this code but even this does not work LOL

all i want is for this kid to enter in a bunch of stuff, i have 8 cols and ??? rows..

how can i do with a form, eather with or without a db... i'm so confused.. dont ask why i'm trying but i guess they seam to think this will help... i'm not worried about the table names or text box lables that can be changed latter.. does anyone have and idea or suggestions...




Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
rs As Recordset
MyRange As Range
i As Integer
Set vConnection = OpenDatabase("data source=c:\db\datadb.mdb;")
Set rs = db.OpenRecordset(Name:="datatable")
Set MyRange = ActiveDocument.Content
MyRange.Collapse wdCollapseEnd
MyRange.InsertAfter Text:=rs.Fields(1).Name & vbTab & rs.Fields(2).Name & vbCr
Set MyRange = ActiveDocument.Content
MyRange.Collapse wdCollapseEnd
For i = 0 To rs.RecordCount - 1
'Insert the data as tab-delimited text
MyRange.InsertAfter Text:=rs.Fields(1).Value & vbTab & rs.Fields(2).Value & vbCr
rs.MoveNext
MyRange.Collapse Direction:=wdCollapseEnd
Next i
rs.Close
db.Close
'Now convert to table
MyRange.Start = ActiveDocument.Range.Start
MyRange.ConvertToTable
Set db = Nothing
Set rs = Nothing

[TonyJollans]

Hi redhead,

Seems you were busy while I was asleep :)

I have done this rather quickly just to give you an idea. I'll come back and tidy it up later - if it's the sort of thing you want.

[redhead]

wow thats' great thats want i need, i see how it is working, it adds a text file called table and it adds lines, but no data? i dont see anything else in the actuall document..

any sugestions..

[TonyJollans]

The data is added when you press the Add button on the Form.

[TonyJollans]

Sorry, perhaps a bit more information. The code does not add a text file - it keeps a reference to the table but it adds the data directly to the document. I'll tidy it up and add some comments to the code and upload a new one for you.

[redhead]

Hey that would be a great help would it be ok, after you did that to ask some more questions??

[TonyJollans]

I have made a couple of small changes and added several comments.

I have also changed all the controls on the form to be the same size (the only noticeable effect of this is that using the down arrow now always goes to the next field).

Ask anything you like :) - I'll do my best to answer it.

[redhead]

wow, this is so neet,


i'm going throught each line by line,

is there a way to make it a sertin page with this line, can i say page 5 or something like that



Set TableRef = .Tables.Add(.Bookmarks("\page 5").Range, 2, 9)

[TonyJollans]

Unfortunately it isn't that easy. Pages in Word are not fixed things and there isn't a straightforward way to identify positions by page.

If you want to identify a specific place in the document the thing to do is create a bookmark. If you want to be able to specify, when you run the userform, which page to use and it may be different each time then you'll need some extra code. At the moment the code doesn't use the Selection, which is the way I like it, but it will probably need to to identify pages.

[redhead]

ok so i can use that line, but if i put a text filed in, the active document called table how does that work

Set TableRef = .Tables.Add(.Bookmarks("table").Range, 1, 9)

[redhead]

All i have done was add a text filed into the active document... and named it table

and chaged the following to:


With ActiveDocument
.Range.InsertParagraphAfter
Set TableRef = .Tables.Add(.Bookmarks("\table").Range, 1, 9)
End With


but i'm getting - The requested member of the collecion does not exist.. any ideas??

[TonyJollans]

Hi redhead,

Text formfields can (and do, by default) have a bookmark associated with them but if you just want a bookmark you don't want to use a text field. Just Insert > Bookmark from the menu.

There are a dozen or so special built-in bookmarks which all begin "\" - including the one I used "\EndOfDoc". You can't make up your own like them - you must go and add what you want to the document as above.

A word of warning. Addind text (or tables etc.) at bookmarks tends to overwrite the bookmark - even when using the UI so you need to take care (and maybe recreate it) if you will want to use it again.

[redhead]

Ok so i added a text filed named it datatable, then i went to insert bookmarkand saw my datatable so i selected it and hit the add button...

changed the code to say this:
Set TableRef = .Tables.Add(.Bookmarks("\datatable").Range, 2, 9)

but im still getting this error:
The requested memeber of the colletion does not exist..

[TonyJollans]

You don't want the backslash.
ChangeSet TableRef = .Tables.Add(.Bookmarks("\datatable").Range, 2, 9)ToSet TableRef = .Tables.Add(.Bookmarks("datatable").Range, 2, 9)
But you still don't want a text field - just a bookmark on its own.

[redhead]

Hi

that work but know i'm see the problem when you enter fill out the form the first time it creates the table, then the second time you fill out the form it makes a table again in the first table firt colum and firt row...



' Add Button Click Routine
' ========================
Private Sub BtnAdd_Click()
' If this is the first time the button has been clicked, the table
' will need creating. The global flag tells us whether to do it.

If TableCreated = False Then

' Add a paragraph first - this avoids problems of multiple
' consecutive tables being joined together by Word.
' Then add a 2-row, 9-column table and save a pointer to it.

With ActiveDocument
.Range.InsertParagraphAfter
Set TableRef = .Tables.Add(.Bookmarks("datatable").Range, 2, 9)
End With

' Copy the captions from the labels to the column headings

'With TableRef.Rows(1).Range
' .Cells(1).Range.Text = Me.Label2.Caption
' .Cells(2).Range.Text = Me.Label3.Caption
' .Cells(3).Range.Text = Me.Label4.Caption
' .Cells(4).Range.Text = Me.Label5.Caption
' .Cells(5).Range.Text = Me.Label6.Caption
' .Cells(6).Range.Text = Me.Label7.Caption
' .Cells(7).Range.Text = Me.Label8.Caption
' .Cells(8).Range.Text = Me.Label9.Caption
' .Cells(9).Range.Text = Me.Label10.Caption
' End With

' Set the global flag so that we know not to do this again next time

TableCreated = True

End If


' Now for the Add proper ...

' Add a row to the table and save a pointer to it
' Note that this row is added before the last row (which is empty)
' Becuase of the way I have coded I can't add a row right at the end
' .. so I keep a dummy empty row which is deleted at the end.

With TableRef.Rows.Add(TableRef.Rows(TableRef.Rows.Count))

' Add the text from the textboxes to the columns in the new row

.Cells(1).Range.Text = Me.Text52.Text
.Cells(2).Range.Text = Me.Text53.Text
.Cells(3).Range.Text = Me.Text54.Text
.Cells(4).Range.Text = Me.Text55.Text
.Cells(5).Range.Text = Me.Text56.Text
.Cells(6).Range.Text = Me.Text57.Text
.Cells(7).Range.Text = Me.Text58.Text
.Cells(8).Range.Text = Me.Text59.Text
.Cells(9).Range.Text = Me.Text60.Text
End With

' Clear the textboxes


With Me
.Text52.Value = ""
.Text53.Value = ""
.Text54.Value = ""
.Text55.Value = ""
.Text56.Value = ""
.Text57.Value = ""
.Text58.Value = ""
.Text59.Value = ""
.Text60.Value = ""
End With


' We're done now - back to the user.

End Sub
' Done Button Click Routine
' =========================
Private Sub BTNNext_Click()
' If any data were added, do some tidying up
If TableCreated = True Then

' Delete the dummy row at the end of the table
TableRef.Rows(TableRef.Rows.Count).Delete

' Try and make it look tidy!
TableRef.AutoFitBehavior wdAutoFitContent

' Clear the global variables - we no longer need them
Set TableRef = Nothing
TableCreated = False

End If

' Unload the userform - we're done with it now
Unload Me

'get form off screen
UserForm5.Hide
'get next form
UserForm6.Show
End Sub
Private Sub Text52_Change()
End Sub
Private Sub UserForm_Initialize()

' When the form is first loaded, set the global flag to
' say that we haven't yet created a table

TableCreated = False
End Sub

[TonyJollans]

See if this works for you.

Replace:
With ActiveDocument
.Range.InsertParagraphAfter
Set TableRef = .Tables.Add(.Bookmarks("datatable").Range, 2, 9)
End With
WIth:
Dim BookmarkRange As Range

With ActiveDocument
Set BookmarkRange = .Bookmarks("datatable").Range
BookmarkRange.InsertParagraphAfter
BookmarkRange.MoveStart
Set TableRef = .Tables.Add(BookmarkRange, 2, 9)
End With

[redhead]

Hey Again

what a bug i am, i'm so very sorry, just trying to learn as much as i can... i was wondering how you can set the width of each column in the table that the script makes?

i was doing some reading and i found these few examples


If Selection.Information(wdWithInTable) = True Then
With Selection
.InsertColumns
.Shading.Texture = wdTexture10Percent
End With
End If



ActiveDocument.PageSetup.TextColumns.Add _
Width:=InchesToPoints(2.5), _
Spacing:=InchesToPoints(0.5), EvenlySpaced:=False


but truly i am not sure which method to use or how to use them the second one makes since becasue it is saying how many inches to make the coloum but it does not say how to make each coloum a different size...

do you have any ideas???

[TonyJollans]

Hi redhead,

You ask as much as you like. Your learning is my reward.

Setting column widths depends a little on what version of Word you have and you must realise that setting the width of one column (usually) means changing the width of at least one other column to make space (or use up freed space).

With your first piece of code, the easiest way is probably ..If Selection.Information(wdWithInTable) = True Then
With Selection
.InsertColumns
.Columns(1).Width = InchesToPoints(1)
End With
End Ifthis adds a column and sets it to one inch wide.

One other point, the lline:
TableRef.AutoFitBehavior wdAutoFitContentin my earlier code, tries to make the column widths fit the contents just before closing the form and so might override any settings you make.

Lastly, the second snippet of code in your post affects newspaper-style columns in a document, not columns in a table.

[redhead]

ok so i tryed the code and it does not work..

i have attaced my template for you to see the size of the coloums dont really work...

what do you think?

[TonyJollans]

Hi,

You've done two things which have changed the process:
1) Put the table headers in the document instead of creating them in the code.
2) Removed the declarations (TableCreated and TableRef) from the start of the module

The code you have for setting the column widths is never run because of (2) above, but a better idea, I think, would be for it not to be needed.

If you add rows to the existing table (with the headers) rather than creating a new table, it should be much easier and you won't need to resize the columns. This will be simpler if you (a) keep an empty row at the end of it and (b) put the bookmark somewhere inside it.

See if the attached does what you want - I've deleted a large part of the code, that's about all really :)