[mso] Re: Word forms (from outlook forms)
- From: "Dian Chapman" <dian@xxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Fri, 3 Jan 2003 08:53:20 -0600
OK...I just downloaded the original code, followed the instructions for
the correct path and ran the code and it works fine. So we know it
works. Now it's a matter of figuring out what you did to change it.
* The first thing I notice is that some of the code lines are wrapping
at the wrong places. So I'll assume that's just the email causing that
and NOT that you hit enter at any weird places without adding a _
continuation marker at the end of wrapping code...as instructed in my
VBA book.
* The path you are using to the DB is extremely long. I've never tried a
path that long and that MIGHT be causing some issues? Also, all those
spaces in all those paths might be causing a problem. When you try to
connect, does the DB say you've connected? If you can't get that
far...comment out ALL the remaining code after the connection where it
says you've connected or not and try running the code then. As explained
in the book...you use an apostrophe to comment out code. There's are
also comment in and comment out icons on the VBE toolbar to make the job
easier when you highlight chunks of code. Looks like a red para
indented. Highlight EVERYTHING after the did/did not connect messages
and comment that out by clicking the comment out icon. Does it connect?
If so...I guess that killer path is ok. But DON'T plan on ever using a
path like that on a server. You're just asking for trouble. Two
tips...you should do all you can to NEVER use space in paths or
databases. They work...usually. (note the word...usually).
* Wait. Before you comment stuff out...I just noticed that, YES, proof
your code in the SQL statement (starting with SELECT)...it's NOT the
same and mine...you have added a couple of EXTRA double quotes that
don't belong there and your continuation markers are missing.
This is your code:
>vRecordSet.Open "SELECT * FROM ClientInfo WHERE ClientInfo!FName = " "
&
Chr(34) & vClientFName & Chr(34) & AND ClientInfo!LName = " " & Chr (34)
& vClientLName & Chr(34), vConnection, adOpenKeyset, adLockOptimistic
This is how MY code looks in the download. Compare it to yours and note
the extra quotes and missing continuation markers:
>'open a RecordSet with SQL query results...to see if first/last name
matches a record
vRecordSet.Open "SELECT * FROM ClientInfo WHERE ClientInfo!FName = " &
_
Chr(34) & vClientFName & Chr(34) & "AND ClientInfo!LName = " & _
Chr(34) & vClientLName & Chr(34), vConnection, adOpenKeyset,
adLockOptimistic
You MUST follow EXACT syntax procedures or it won't work. Programming
code is very unforgiving when it comes to syntax and you've modified
mine. So you DID do MORE than "just unzipped it and added your
path."<smirk> You also removed the underscores at the end of the code
lines, added extra quotes and wrapped the code differently without using
the correct markers. Yes, you CAN change the code...eventually, WHEN you
understand what you're doing. Until that time, you're best to use it
EXACTLY as I have it.
You should reread the sections in my book where I discuss concatenating
code and continuation markers.
The rest of it appears to be ok. So I'd suggest you fix the SQL
statement that's trashed and see if it works...chances are it will. ;-)
Dian Chapman
Technical Consultant, Instructor,
Microsoft MVP & TechTrax Editor
Word AutoForm/VBA eBook: http://www.mousetrax.com/books.html
Tutorial web site: http://www.mousetrax.com/techpage.html
TechTrax Ezine: http://www.mousetrax.com/techtrax/
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Bill Scott
Sent: Friday, January 03, 2003 7:31 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Word forms (from outlook forms)
Hi Dian ;
All I did was to upzip your code and put my path names in ... FormsFive
Download, there seems to be an open quote at
vRecordSet.Open "SELECT But I don't know vbe well enough to see it
... sorry to be such a pain .... *||:^) Can the &_ cause problems ... ??
Private Sub CommandButton1_Click()
'declare variables for new connection and recordset and declare
variables
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim vClientFName, vClientLName, vCompany, vAddress, vCity, vState,
vZip, vPhone, vNotes As String
'provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=c:\bin\windows
tools\Automate Office 2000\Source Code FormsFive\Client_Info.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
'open connection
vConnection.Open
'test connection state...this can later be commented out...but it
helps
'let you know if the connection was successful when testing the code
vConnectionState = vConnection.State
If vConnectionState = 1 Then
MsgBox "The connection to this database is working!",
vbInformation
Else
MsgBox "You were unable to connect to the assigned database!",
vbInformation
End If
'set variable equal to whatever is entered into First & LastName
form field
'so you can query the DB with this name to see if a record exists
vClientFName = ActiveDocument.FormFields("bkClientFName").Result
vClientLName = ActiveDocument.FormFields("bkClientLName").Result
'open a RecordSet with SQL query results...to see if first/last name
matches a record
vRecordSet.Open "SELECT * FROM ClientInfo WHERE ClientInfo!FName = "
" &
Chr(34) & vClientFName & Chr(34) & AND ClientInfo!LName = " " & Chr (34)
& vClientLName & Chr(34), vConnection, adOpenKeyset, adLockOptimistic
'if a match is found, display it to the user in a message box
'you'll get a match if you are NOT .EOF (end of file)
With vRecordSet
If Not .EOF Then
vCorrectRecord = MsgBox("Is this the correct record?" &
Chr(13) &
Chr(13) & _
vRecordSet("FName") & " " & _
vRecordSet("LName") & ", " & _
vRecordSet("Address") & ", " & _
vRecordSet("City"), _
vbYesNo + vbInformation, "User Record")
Else
'if you ARE *else* (are .EOF), that means no record was matched,
tell the user
MsgBox "No possible match was found."
End If
End With
'If the answer equals 6, it means they clicked Yes on the MsgBox
accepting this match
If vCorrectRecord = 6 Then
'if yes...set variables from DB fields (the name variables are
already set above)
vCompany = vRecordSet("Company")
vAddress = vRecordSet("Address")
vCity = vRecordSet("City")
vState = vRecordSet("State")
vZip = vRecordSet("Zip")
vPhone = vRecordSet("Phone")
vNotes = vRecordSet("Notes")
'set the form field's bookmarks to the results from the database
field variables
ActiveDocument.FormFields("bkCompany").Result = vCompany
ActiveDocument.FormFields("bkAddress").Result = vAddress
ActiveDocument.FormFields("bkCity").Result = vCity
ActiveDocument.FormFields("bkState").Result = vState
ActiveDocument.FormFields("bkZip").Result = vZip
ActiveDocument.FormFields("bkPhone").Result = vPhone
ActiveDocument.FormFields("bkNotes").Result = vNotes
Else
'if not 6, then not Yes, so must be NO...remind user to update
database!
MsgBox "Since this is not the correct entry..." & Chr(13) & _
"be sure to fill out remaining form fields and click *Update*
" &
Chr(13) & _
"so this person will be added to the database."
End If
'close objects
vRecordSet.Close
vConnection.Close
'clear object to free up memory
Set vRecordSet = Nothing
Set vConnection = Nothing
End Sub
Private Sub CommandButton2_Click()
'declare new connection, recordset and variables
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim vClientFName, vClientLName, vCompany, vAddress, vCity, vState,
vZip, vNotes As String
'provide same as previous connection string for data using Jet
Provider for Access database
vConnection.ConnectionString = "data source=c:\bin\windows
tools\Automate Office 2000\Source Code FormsFive\Client_Info.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
'open connection
vConnection.Open
'set variables from text enter into all the AutoForm field bookmark
results
vClientFName = ActiveDocument.FormFields("bkClientFName").Result
vClientLName = ActiveDocument.FormFields("bkClientLName").Result
vCompany = ActiveDocument.FormFields("bkCompany").Result
vAddress = ActiveDocument.FormFields("bkAddress").Result
vCity = ActiveDocument.FormFields("bkCity").Result
vState = ActiveDocument.FormFields("bkState").Result
vZip = ActiveDocument.FormFields("bkZip").Result
vPhone = ActiveDocument.FormFields("bkPhone").Result
vNotes = ActiveDocument.FormFields("bkNotes").Result
'Open a new version of the temporary RecordSet accessing the
ClientInfo table in Database
vRecordSet.Open "ClientInfo", vConnection, adOpenKeyset,
adLockOptimistic
vRecordSet.AddNew
'we can't enter "nothing", so only set variables with some data
entered info the field
If vClientFName <> "" Then vRecordSet!FName = vClientFName
If vClientLName <> "" Then vRecordSet!LName = vClientLName
If vCompany <> "" Then vRecordSet!Company = vCompany
If vAddress <> "" Then vRecordSet!Address = vAddress
If vCity <> "" Then vRecordSet!City = vCity
If vState <> "" Then vRecordSet!State = vState
If vZip <> "" Then vRecordSet!Zip = vZip
If vPhone <> "" Then vRecordSet!Phone = vPhone
If vNotes <> "" Then vRecordSet!Notes = vNotes
'update the RecordSet to the database, this adds your new client to
the DB
vRecordSet.Update
'advise the user the client has been added
MsgBox vClientFName & " " & vClientLName & " has been added to your
database."
'close objects
vRecordSet.Close
vConnection.Close
'clear object to free up memory
Set vRecordSet = Nothing
Set vConnection = Nothing
End Sub
Bill Scott
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/msaccess
-- File: Client_Info.mdb
-- Binary/unsupported file stripped by Ecartis --
-- Type: application/msword
-- File: ClientInformationUpdateForm.doc
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will
also allow you to change your email settings to digest or vacation (no
mail). //www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group,
send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions. Once you are a member of the files group,
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
