Results 1 to 12 of 12
  1. #1
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Userform and Access (Word 2002)

    Trish,

    Looks like you've already done all the heavy lifting.
    You should be able to use the combo box's Value property to provide the text for the bookmark, as in:

    ActiveDocument.Bookmarks("bmkValueFromCBO").Range. Text = cboSomething.Value

    or if you prefer:

    Set oRange = ActiveDocument.Bookmarks("bmkValueFromCBO").Range
    oRange.InsertAfter cboSomething.Value

    Gary

  2. #2
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform and Access (Word 2002)

    Hi Gary... thank you very much for the help... <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> ...I guess it would help if I explained how I want the code to work... First, there is ONE text box (ComboBox Name: LawyerCombox) on the userform to select the desired lawyer for the letter (based on their initials). The database query (qryFFLawyersDB) only contains the fields required to populate (Query Fields: LawyrInit, LawyrDirectLine, LawyrEMail and LawyrSigntr) after they have selected the lawyer's initials, I want the fields (recordsets) to then populate in the Bookmarks of the template (Bookmarks: LawyrInit, LawyrDirectLine, LawyrEMail and LawyrSigntr). I included .Value at the end of the line, but I am still receiving error msg - probably because in the code above, I was not declaring the ComboBox name and only the recordset name. I made a few changes this morning and am still receiving an error. I think I am getting confused because I am using DAO initially and then using sample ADO code for the first part of the click_event. The code is bugging out at the declaring of the database variables (it is highlighting the <span style="background-color: #FFFF00; color: #000000; font-weight: bold">oLawyrInit =</span hi> and stating "Compile Error - Ojbect Required). Also, how do I set the value to the database field for each bookmark? This is how I thought maybe but it doesn't work either! CONFUSED <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Set oRange = ActiveDocument.Bookmarks("LawyrInit").Range
    oRange.InsertAfter LawyerCombox.Value = oLawyrInit

    Here is the current code:

    Private Sub OKCmdBtn_Click()

    'UserForm Action on OK Click
    'Macro created by Trish Krokosh June 6, 2003

    Dim oLawyrInit As String
    Dim oLawyrDirectLine As String
    Dim oLawyrEMail As String
    Dim oLawyrSigntr As String
    Dim oRange As Range
    Dim oTemp As Template
    Dim sText As String

    'Set Variables from FF Lawyer Database fields
    oLawyrInit = oRecordset("LawyrInit")
    oLawyrDirectLine = oRecordset("LawyrDirectLine")
    oLawyrEMail = oRecordset("LawyrEMail")
    oLawyrSigntr = oRecordset("LawyrSigntr")

    'Set Bookmarks to the results from the FF Lawyer database
    Set oRange = ActiveDocument.Bookmarks("LawyrInit").Range
    oRange.InsertAfter LawyerCombox.Value = oLawyrInit

    Set oRange = ActiveDocument.Bookmarks("LawyrDirectLine").Range
    oRange.InsertAfter LawyerCombox.Value = oLawyrDirectLine

    Set oRange = ActiveDocument.Bookmarks("LawyrEMail").Range
    oRange.InsertAfter LawyerCombox.Value = oLawyrEMail

    Set oRange = ActiveDocument.Bookmarks("LawyrSigntr").Range
    oRange.InsertAfter LawyerCombox.Value = oLawyrSigntr

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Userform and Access (Word 2002)

    <P ID="edit" class=small>(Edited by jscher2000 on 13-Jun-03 13:56. Had another thought...)</P>> The code is bugging out at the declaring of the database variables (it is highlighting the oLawyrInit = and stating "Compile Error - Ojbect Required).

    This is an assignment of the value of a field in a recordset to a string variable. When you go into debug mode and check the Locals window, does oRecordset exist and contain relevant records? Maybe there is an issue in the sequence of executation (or a typo in a field name)?

    On second thought, what is the current record when that assignment statement is run? You had previously read through oRecordset all the way to EOF. Maybe you need to make sure the right row is "current" when you try to read out the field values.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform and Access (Word 2002)

    Hi Jefferson... unfortunately, you are talking above my knowledge <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> ... I'm relatively new to VBA (about 1-2 months). If I ' (rem) out those variables, the one field from the database, LawyrInit populates to all my bookmarks - which makes sense to me. I've been researching on web and did find others advising to use Listbox instead and then add all the items into the listbox; however, I only want the Lawyer's Initials to appear in the dropdown. I'm confused as to how to link the other fields from the database into the bookmarks of my template. I've been playing with the code all day and this is where I stand now:

    Option Explicit
    Dim oDatabase As Database
    Dim oWorkspace As Workspace
    Dim oRecordset As Recordset
    Dim strDB As String
    Dim strDBQuery As String
    Dim strDBField1 As String
    Dim strDBField2 As String
    Dim strDBField3 As String
    Dim strDBField4 As String
    Private Sub UserForm_Initialize()
    'Initialize ComboBox Dropdown List for Lawyer
    'Connecting to Access Database

    'Select database to use
    strDB = "X:ATKLawyer_DBFFLawyers.mdb"

    'Select table in database to use
    strDBQuery = "qryFFLawyersDB"

    'Select fields in database to use
    strDBField1 = "LawyrInit"
    strDBField2 = "LawyrDirectLine"
    strDBField3 = "LawyrEMail"
    strDBField4 = "LawyrSigntr"

    'Initialize ComboBox Dropdown List for Delivery
    DeliveryCombox.AddItem "Without Prejudice"
    DeliveryCombox.AddItem "Privileged & Confidential"
    DeliveryCombox.AddItem "Courier"
    DeliveryCombox.AddItem "Facsimile"
    EnclCombox.AddItem "Enlcosure"
    EnclCombox.AddItem "Enclosures"

    DBConnect
    PopulateListBoxData

    End Sub
    Sub DBConnect()
    'Initializes Jet Workspace and Opens FF Lawyer Database

    'Establish database workspace
    Set oWorkspace = CreateWorkspace(Name:="JetWorkspace", _
    UserName:="admin", Password:="", UseType:=dbUseJet)

    'Open the database
    Set oDatabase = OpenDatabase(strDB)

    'Set the record set to the specified table
    Set oRecordset = oDatabase.OpenRecordset(strDBQuery)
    End Sub
    Sub PopulateListBoxData()
    'Fill list box with data from LawyrInit in FF Lawyer Database

    With oRecordset
    .MoveFirst
    Do Until .EOF
    LawyerCombox.AddItem oRecordset.Fields(strDBField1)
    .MoveNext
    Loop
    End With
    End Sub
    Private Sub OKCmdBtn_Click()

    'UserForm Action on OK Click
    'Macro created by Trish Krokosh June 6, 2003

    Dim oLawyrInit As String
    Dim oLawyrDirectLine As String
    Dim oLawyrEMail As String
    Dim oLawyrSigntr As String
    Dim oRange As Range
    Dim oTemp As Template
    Dim sText As String

    'Set Variables from FF Lawyer Database fields
    oLawyrInit = oRecordset.Fields(strDBField1)
    oLawyrDirectLine = oRecordset.Fields(strDBField2)
    oLawyrEMail = oRecordset.Fields(strDBField3)
    oLawyrSigntr = oRecordset.Fields(strDBField4)

    'Set Bookmarks to the results from the FF Lawyer database
    Set oRange = ActiveDocument.Bookmarks("LawyrInit").Range
    oRange.InsertAfter LawyerCombox.Value

    Set oRange = ActiveDocument.Bookmarks("LawyrDirectLine").Range
    oRange.InsertAfter LawyerCombox.Value

    Set oRange = ActiveDocument.Bookmarks("LawyrEMail").Range
    oRange.InsertAfter LawyerCombox.Value

    Set oRange = ActiveDocument.Bookmarks("LawyrSigntr").Range
    oRange.InsertAfter LawyerCombox.Value

    Rest of code works

    Set oWorkspace = Nothing
    Set oDatabase = Nothing
    Set oRecordset = Nothing

    Any suggestions would be greatly appreciated... thanx

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Userform and Access (Word 2002)

    I think it's getting more confusing, rather than less. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    What is the query you are running? It seems to be a query saved in the database. What is the SQL string for that query? Let's start by making sure we know the names of the fields (columns) in oRecordset.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Userform and Access (Word 2002)

    Hi Trish,

    In the code to populate the Lawyer combo box, you loop through the records of the recordset to add the lawyer's initials to the combo box. At the end of that routine, the "record pointer" of the recordset is past the end of the recordset, so there is no "current record" any more.

    In the code behind the OK button, you don't use the selected initials to set the current record of the recordset to that lawyer.

    You have to change only two things to get it right:

    1. Replace the line<pre>Set oRecordset = oDatabase.OpenRecordset(strDBQuery)</pre>

    in DBConnect by<pre>Set oRecordset = oDatabase.OpenRecordset(strDBQuery, dbOpenDynaset)</pre>

    The addition of the dbOpenDynaset argument enables the recordset to be searched.

    2. Insert a new line<pre>oRecordset.FindFirst "LawyrInit='" & LawyerCombox.Value & "'"</pre>

    immediately before the offending line in OKCmdBtn_Click. This positions the recordset on the lawyer selected in the combo box.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Userform and Access (Word 2002)

    (Edited by HansV to activate link to MSKB)

    I am have created a Userform which connects to an Access database to populate a ComboBox. This portion of my code worked based on information which I received from <!mskb=164923>Microsoft Knowledge Base Article 164923<!/mskb>. For the life of me, I cannot figure out how to bring the user's selection from the ComboBox into Bookmarks in the template. Here is my code so far... Thanx in advance for any help you can provide... trish

    Option Explicit
    Dim oDatabase As Database
    Dim oWorkspace As Workspace
    Dim oRecordset As Recordset
    Dim strDB As String
    Dim strDBQuery As String
    Dim strDBField As String
    Private Sub UserForm_Initialize()
    'Initialize ComboBox Dropdown List for Lawyer
    'Connecting to Access Database

    'Select database to use
    strDB = "X:ATKLawyer_DBFFLawyers.mdb"

    'Select table in database to use
    strDBQuery = "qryFFLawyersDB"

    'Select field in database to use
    strDBField = "LawyrInit"

    DBConnect
    PopulateListBoxData

    End Sub

    Sub DBConnect()
    'Initializes Jet Workspace and Opens FF Lawyer Database

    'Establish database workspace
    Set oWorkspace = CreateWorkspace(Name:="JetWorkspace", _
    UserName:="admin", Password:="", UseType:=dbUseJet)

    'Open the database
    Set oDatabase = OpenDatabase(strDB)

    'Set the record set to the specified table
    Set oRecordset = oDatabase.OpenRecordset(strDBQuery)
    End Sub
    Sub PopulateListBoxData()
    'Fill list box with data from LawyrInit in FF Lawyer Database

    oRecordset.MoveFirst
    Do Until oRecordset.EOF
    LawyerCombox.AddItem oRecordset.Fields(strDBField)
    oRecordset.MoveNext
    Loop
    End Sub

    Private Sub OKCmdBtn_Click()

    'UserForm Action on OK Click
    'Macro created by Trish Krokosh June 6, 2003

    Dim oLawyrInit As String
    Dim oLawyrDirectLine As String
    Dim oLawyrEMail As String
    Dim oLawyrSigntr As String
    Dim oRange As Range
    Dim oTemp As Template
    Dim sText As String

    'Set Variables from FF Lawyer Database fields
    oLawyrInit = oRecordset("LawyrInit")
    oLawyrDirectLine = oRecordset("LawyrDirectLine")
    oLawyrEMail = oRecordset("LawyrEMail")
    oLawyrSigntr = oRecordset("LawyrSigntr")

    'Set Bookmarks to the results from the FF Lawyer database
    Set oRange = ActiveDocument.Bookmarks("LawyrInit").Range
    oRange.InsertAfter oLawyrInit

    Set oRange = ActiveDocument.Bookmarks("LawyrDirectLine").Range
    oRange.InsertAfter oLawyrDirectLine

    Set oRange = ActiveDocument.Bookmarks("LawyrEMail").Range
    oRange.InsertAfter oLawyrEMail

    Set oRange = ActiveDocument.Bookmarks("LawyrSigntr").Range
    oRange.InsertAfter oLawyrSigntr

    'Complete remainder of Form
    Set oRange = ActiveDocument.Bookmarks("FileNo").Range
    oRange.InsertAfter FileNoTxtbx

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Userform and Access (Word 2002)

    Let's take this one thing at a time. Do you still get error messages? That is not clear to me from you reply.

  9. #9
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform and Access (Word 2002)

    Hi Hans... did you miss me? Thanx for coming to my rescue... I did all the things you asked and still having problems.
    This is due to my extreme lack of knowledge on how to link the database field (i.e., LawyrDirectLine) with the bookmark (i.e., LawyrDirectLine). This is where I included the oRecordset.Find... line (directly above error msg) - Is this correct. When completing the oRange.InsertAfter ? I have no clue what should be placed here... if i put LawyrCombox the bookmark is filled with the item selected in the combo box on the userform, etc. for all others. I'm pretty sure it can't be the variable set above ('Set Variables from FF Lawyer...), i.e., oLawyrInit because the InsertAfter does not allow? I've found this done with Form Fields and it seems much easier than bookmarks! I've attached the template...

    'Set Variables from FF Lawyer Database fields
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold"> oRecordset.FindFirst "LawyrInit='" & LawyerCombox.Value & "'"</span hi>
    oLawyrInit = oRecordset.Fields(strDBField1)
    oLawyrDirectLine = oRecordset.Fields(strDBField2)
    oLawyrEMail = oRecordset.Fields(strDBField3)
    oLawyrSigntr = oRecordset.Fields(strDBField4)

    'Set Bookmarks to the results from the FF Lawyer database
    Set oRange = ActiveDocument.Bookmarks("LawyrInit").Range
    oRange.InsertAfter <span style="background-color: #FFFF00; color: #000000; font-weight: bold">LawyerCombox.Value </span hi> OR <span style="background-color: #FFFF00; color: #000000; font-weight: bold">oLawyrInit.Value</span hi>

    Set oRange = ActiveDocument.Bookmarks("LawyrDirectLine").Range
    oRange.InsertAfter LawyrDirectLine.Value

    HHHHEEEELLLLPPP! Thanx all again for your help...

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Userform and Access (Word 2002)

    Because you stored the database field value into a string variable -

    Dim oLawyrInit As String
    ...
    oLawyrInit = oRecordset.Fields(strDBField1)

    - you can insert it without using any .Value property (and in fact string variables can't have .Value properties):

    oRange.InsertAfter oLawyrInit

    As long as you're at it, maybe rename them from o prefixes (implying objects) to s or str prefixes (shorthand among Loungers for "it's a string"). This might not help you, but it might help us. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform and Access (Word 2002)

    Hi Hans... thank you for your help... it was the .Value that was getting me into trouble which Jefferson pointed out. All is working great now... thanx again and have a GREAT day! <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  12. #12
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Userform and Access (Word 2002)

    Hi Jefferson... YOU ARE RIGHT... and after reading your reply it all made sense... I took out the .value and everything works GREAT! Also, I did change the o... to an s... as you suggested... THANX for teaching me... Have a great day... trish <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •