Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating a Text Box (A2K SR-1)

    On a form (frmSUBINFO2), I have a combo box (called 'L4') and a bound text box (called 'ShipContact'). The combo box is populated with a person's initials after the user selects a name. The table underlying the combo box (tblDVCPERSONNEL) has 3 fields: Initials, FirstName, and LastName, with Initials as the bound column. After the user selects a name in the L4 combo box and populates it with initials, I want to automatically populate the ShipContact text box with the full name equivalent of those initials, but I can't get it to work. Here's the code I've placed in the After Update event of the L4 combo box:

    Private Sub L4_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT DISTINCTROW tblDVCPERSONNEL.FirstName & Chr(32) & tblDVCPERSONNEL.LastName " _
    & "FROM tblDVCPERSONNEL " _
    & "WHERE ((tblDVCPERSONNEL.Initials)=[Forms]![frmSUBINFO2]![L4])"
    Me!ShipContact.ControlSource = strSQL
    End Sub

    This code generates a #Name! error message in the ShipContact field. What am I doing wrong? As always, many thanks for your help.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Text Box (A2K SR-1)

    You have to open the recordset and read the field. This on the fly code :
    Private Sub L4_AfterUpdate()
    Dim db as DAO.DataBase
    Dim rst as DAO.RecordSet
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "SELECT DISTINCTROW tblDVCPERSONNEL.FirstName & Chr(32) & tblDVCPERSONNEL.LastName As FullName" _
    & "FROM tblDVCPERSONNEL " _
    & "WHERE ((tblDVCPERSONNEL.Initials)=[Forms]![frmSUBINFO2]![L4])"
    Set rst = db.OpenRecordSet(strSQL, dbOpenDynaset)
    If rst.RecordCount <> 0 Then
    Me!ShipContact = rst.FullName
    Else
    Me!ShipContact = "" 'Fill in what you want if no reords are selected
    End If
    Set rst = Nothing
    Set db = Nothing
    End Sub

    Don't forget to add a reference to Microsoft DAO 3.6 Object Library in Tools/References
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Text Box (A2K SR-1)

    Thank you, Francois. It's great to hear from you again. I'll give this a try.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Text Box (A2K SR-1)

    Francois,

    When running your code, I'm presented with an error message on the 'Me!ShipContact = rst.FullName' line that says 'Compile Error: Method or data member not found'. I double-checked to ensure the code was typed correctly and I added the MS DAO 3.6 Object Library reference. Suggestions?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Text Box (A2K SR-1)

    Try to add a space after FullName in the strSQL line :
    strSQL = "SELECT DISTINCTROW tblDVCPERSONNEL.FirstName & Chr(32) & tblDVCPERSONNEL.LastName As FullName " _
    Sorry, my mistake.
    Francois

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Text Box (A2K SR-1)

    Also replace the dot by a bang in the line:
    Me!ShipContact = rst!FullName
    Second mistake, sorry again <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Francois

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Text Box (A2K SR-1)

    Here's the code:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = "SELECT tblDVCPERSONNEL.FirstName & Chr(32) & tblDVCPERSONNEL.LastName As FullName " _
    & "FROM tblDVCPERSONNEL " _
    & "WHERE ((tblDVCPERSONNEL.Initials)=[Forms]![frmSUBINFO2]![L4])"
    Set rst = db.OpenRecordSet(strSQL, dbOpenDynaset)
    If rst.RecordCount <> 0 Then
    Me!ShipContact = rst!FullName
    Else
    Me!ShipContact = ""
    End If
    Set rst = Nothing
    Set db = Nothing
    End Sub

    Now I'm getting 'Run-time error: '3061', Too few parameters. Expected 1.' for the 'Set rst=db.OpenRecordSet(strSQL,dbOpenDynaset) line.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Text Box (A2K SR-1)

    New correction:
    <pre>& "WHERE tblDVCPERSONNEL.Initials = '" & [Forms]![frmSUBINFO2]![L4] & "'"</pre>

    Francois

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Text Box (A2K SR-1)

    That did it, Francois. Works perfectly. Many thanks.

Posting Permissions

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