help creating macro in word

[cc.caroline15]

Hello everyone I'm trying to create a macro(CreateTables) in word that when User enters Windows or MSWord or DB, each student enrolled in the respective class (Windows XP Level 1, MSWord Level 1, Database Programming – VBA) class is displayed in a separate table containing rows for ID, First, Last, Department name, Telephone and Classname. All values are placed in fields and records (tables) are sorted by last name ascending. Depicted here after DB is entered: like the one in the picture. This data will be extracted from a access file. but my code gives me the following error: Compile error: user defined type not defined. Can anyone please help me?? I have attach the word document and a picture of how the table should look in word. thank you

[macropod]

You could do this quite easily with either a Letter or Directory mailmerge. No code required.

Simply create a document with a single table as per your layout, start the mailmerge wizard and connect to the data source, then insert the mergefields in the second column. Once you've done that, 'Finish' the merge. If needed, you can sort the list (before finishing) via Edit Recipient List > Sort.

[Guessed]

As macropod suggests, this would be a lot easier to do as a mail merge.

FWIW, your code has obvious errors which cause compilation errors but I can't test to iron out any other issues related to the things we can't see there.

DefaultTableBehaivor is not spelt correctly and needs to say DefaultTableBehavior
strPassword -InputBox("Password Please?", "Lock Forms Password") should have an equals sign instead of the minus

Add Dim strEmps As String, strPath As String to the start of the Sub Connect macro

Public connEmp As ADODB.Connection - this line fails to compile because you haven't added a reference to one of the Microsoft ActiveX Data Object x.x Library

[cc.caroline15]

Hi macropod I'm required to do it the hard way with code
Guessed I made the changes you suggested the code doesn't give me that error any more but now I get the following error:
Run Time error 91 Do you have anymore advice?? I have attach the word document with the changes and the access database as well. Thank you

[Guessed]

I can't see the attachments

[cc.caroline15]

here are the files

[Guessed]

Caroline

I don't want to do your homework for you. I don't mind helping you past sticking points but you need to make a reasonable effort first.

First, do a 'Compile Project' and resolve all of those errors. For instance sngRecords is not a Connection and is not required in the Connect sub.

Second, build an example of the SQL query you want in your access database. Go to SQL view and look at what it looks like. Now compare it to the strEmps that your code creates - you will spot several errors that need resolving.

Once you have those resolved, we can move forward with the rest of what you need to do.

[cc.caroline15]

Hi Guessed,
I re-wrote the SQL statement I think it's right this time and I also notice I was running the wrong macro the one that I was suppouse to run was the ListEmps but when I run it I get the run time error: Not a valid file name. Could you help me please??
Thank you

[Guessed]

If you are getting a 'not valid file name error' then it is likely that a file with that name doesn't exist.

There is a difference between cstrPath and strPath - are you sure you are using the right variable when you open the connEmp? It looks like you intended to build the full path by getting the current path and appending the constant to point inside a folder BUT your code is not quite doing that.

Add the following lines before the connEmp.Open line to check the value of cstrPath and strPath

Debug.Print cstrPath
Debug.Print strPath

[cc.caroline15]

Hi Guessed
I was storing both files on my one drive cloud storage from outlook and as soon as I move them to my C drive on the computer that error stop! I don't understand why was this? now I'm getting the following error so I made some changes to the SQL statement again but still not working.. do you have any more advice?? I'm new to programming and SQL statements has been a hard subject for me
Thank you.

[Guessed]

Build the sql query in your access database before attempting to replicate it in your code. If you use the interface of the Access query wizard then you don't need to write this using sql directly. Once the query is built, you can look at it in SQL view and then copy that across to your VBA code.

At the very least you need to get the spaces right in your SQL string. The square brackets are useful when field names have spaces but are optional in what you appear to be dealing with however you MUST have spaces in the right places and can't truncate field names like you do.