Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Angry Unanswered: populate fields after update

    after update of a combo box, I want the related fields on the form to show the info related to the selected value in the combo box.
    I've tried:
    Private Sub AccCode_AfterUpdate()
    Dim rs As Object
    Dim num As String
    num = Me![AccCode]

    Set rs = Me.Recordset.Clone
    rs.FindFirst "'[AccCode] = '" & num
    Me.Bookmark = rs.Bookmark
    End Sub

    I've also tried:

    Private Sub AccCode_AfterUpdate()
    Dim rs As Object
    Dim num As String
    num = Me![AccCode]

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AccCode] = " & num
    Me.Bookmark = rs.Bookmark
    End Sub

    this has worked for me in the past. why not now.
    AccCode is Text

    please help
    thanks in advance

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    maybe your problem is related to DAO->ADO migration, firts of all compile you code, if you get an errror on FindFirst (DAO), change it to Find (ADO)
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    i don't get any error on compile

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: populate fields after update

    Originally posted by actionant
    after update of a combo box, I want the related fields on the form to show the info related to the selected value in the combo box.
    I've tried:
    Private Sub AccCode_AfterUpdate()
    Dim rs As Object
    Dim num As String
    num = Me![AccCode]

    Set rs = Me.Recordset.Clone
    rs.FindFirst "'[AccCode] = '" & num
    Me.Bookmark = rs.Bookmark
    End Sub

    I've also tried:

    Private Sub AccCode_AfterUpdate()
    Dim rs As Object
    Dim num As String
    num = Me![AccCode]

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AccCode] = " & num
    Me.Bookmark = rs.Bookmark
    End Sub

    this has worked for me in the past. why not now.
    AccCode is Text

    please help
    thanks in advance
    Try Me.Recordsetclone instead of Recordset.Clone
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  5. #5
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    i've tried that too.
    doesn't work

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: populate fields after update

    Originally posted by actionant
    after update of a combo box, I want the related fields on the form to show the info related to the selected value in the combo box.
    I've tried:
    Private Sub AccCode_AfterUpdate()
    Dim rs As Object
    Dim num As String
    num = Me![AccCode]

    Set rs = Me.Recordset.Clone
    rs.FindFirst "'[AccCode] = '" & num
    Me.Bookmark = rs.Bookmark
    End Sub

    I've also tried:

    Private Sub AccCode_AfterUpdate()
    Dim rs As Object
    Dim num As String
    num = Me![AccCode]

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AccCode] = " & num
    Me.Bookmark = rs.Bookmark
    End Sub

    this has worked for me in the past. why not now.
    AccCode is Text

    please help
    thanks in advance
    How many columns in the combobox? Since this combobox is bound to a table/query why not populate from the combobox?

  7. #7
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    the combo box contains all the fields that I want to populate.
    it keeps telling me there is a syntax error in the line:
    rs.FindFirst "'[AccCode] = '" & num

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by actionant
    the combo box contains all the fields that I want to populate.
    it keeps telling me there is a syntax error in the line:
    rs.FindFirst "'[AccCode] = '" & num
    Have you tried something like:

    MyOtherTextbox.Value=MyComboBox.Column(XXX,MyCombo Box.ListIndex)

    Where you populate the other controls from the non-bound columns of the combobox ...

    A real world Example:
    Code:
    Private Sub SugSupplierComboBox_AfterUpdate()
        Dim HavePhone As Boolean, HaveContact As Boolean
        
        HavePhone = False
        HaveContact = False
        
        ' Company Name
        SupplierInfoTxt.Value = SugSupplierComboBox.Column(2, SugSupplierComboBox.ListIndex + 1)
        ' Address
        If SugSupplierComboBox.Column(3, SugSupplierComboBox.ListIndex + 1) <> "" Then
            SupplierInfoTxt.Value = SupplierInfoTxt.Value & vbCrLf & SugSupplierComboBox.Column(3, SugSupplierComboBox.ListIndex + 1)
            ' 2nd line of address
            If SugSupplierComboBox.Column(4, SugSupplierComboBox.ListIndex + 1) <> "" Then
                SupplierInfoTxt.Value = SupplierInfoTxt.Value & " / " & SugSupplierComboBox.Column(4, SugSupplierComboBox.ListIndex + 1)
            End If
        End If
        ' City, State & Zip
        If SugSupplierComboBox.Column(5, SugSupplierComboBox.ListIndex + 1) <> "" Then
            SupplierInfoTxt.Value = SupplierInfoTxt.Value & vbCrLf & SugSupplierComboBox.Column(5, SugSupplierComboBox.ListIndex + 1)
            If SugSupplierComboBox.Column(6, SugSupplierComboBox.ListIndex + 1) <> "" Then SupplierInfoTxt.Value = SupplierInfoTxt.Value & ", " & SugSupplierComboBox.Column(6, SugSupplierComboBox.ListIndex + 1)
            If SugSupplierComboBox.Column(7, SugSupplierComboBox.ListIndex + 1) <> "" Then SupplierInfoTxt.Value = SupplierInfoTxt.Value & " " & SugSupplierComboBox.Column(7, SugSupplierComboBox.ListIndex + 1)
        End If
        ' Contact & EMail
        If SugSupplierComboBox.Column(10, SugSupplierComboBox.ListIndex + 1) <> "" Then
            SupplierInfoTxt.Value = SupplierInfoTxt.Value & vbCrLf & vbCrLf & "Contact: " & SugSupplierComboBox.Column(10, SugSupplierComboBox.ListIndex + 1)
            HaveContact = True
        End If
        ' Email
        If SugSupplierComboBox.Column(11, SugSupplierComboBox.ListIndex + 1) <> "" Then
            If Not HaveContact Then
                SupplierInfoTxt.Value = SupplierInfoTxt.Value & vbCrLf & vbCrLf & "Contact: "
            Else
                SupplierInfoTxt.Value = SupplierInfoTxt.Value & " / "
            End If
            SupplierInfoTxt.Value = SupplierInfoTxt.Value & SugSupplierComboBox.Column(11, SugSupplierComboBox.ListIndex + 1)
        End If
        ' Phone
        If SugSupplierComboBox.Column(8, SugSupplierComboBox.ListIndex + 1) <> "" Then
            SupplierInfoTxt.Value = SupplierInfoTxt.Value & vbCrLf & "Ph: " & SugSupplierComboBox.Column(8, SugSupplierComboBox.ListIndex + 1)
            HavePhone = True
        End If
        ' Fax
        If SugSupplierComboBox.Column(9, SugSupplierComboBox.ListIndex + 1) <> "" Then
            If Not HavePhone Then
                SupplierInfoTxt.Value = SupplierInfoTxt.Value & vbCrLf
            Else
                SupplierInfoTxt.Value = SupplierInfoTxt.Value & Space(4)
            End If
            SupplierInfoTxt.Value = SupplierInfoTxt.Value & "Fx: " & SugSupplierComboBox.Column(9, SugSupplierComboBox.ListIndex + 1)
        End If
        ' Website
        If SugSupplierComboBox.Column(12, SugSupplierComboBox.ListIndex + 1) <> "" Then
            SupplierInfoTxt.Value = SupplierInfoTxt.Value & vbCrLf & "Web: " & SugSupplierComboBox.Column(12, SugSupplierComboBox.ListIndex + 1)
        End If
        
        If IsNewPO Then
            ' Retrieve supplier's available items
        End If
    End Sub

  9. #9
    Join Date
    Feb 2004
    Posts
    142
    With Office 2000 and beyond, you have to manually link the DAO library (3.6) AND move this reference above the Active Data Objects reference or explicitly set the DAO reference - Dim rs as DAO.recordset ir you don't. The DAO library is no longer automatically added to your projects and it will compile but error out on the first line that makes a DAO call.

    I have found that on smaller recordsets (<100k or so) on bound forms that you can use a query as the record source with the results in the combo box in the where clause. (Like forms!frmName!combobox) If you want all records to show when the box is empty then use the nz(full_ctl_ref,"*") as well. Then do a me.requery in the control's afterupdate property. Else the form will show no records until a value is selected. A bit slow for huge recordsets but bullet-proof otherwise.
    KC

  10. #10
    Join Date
    Feb 2004
    Posts
    25
    you just need to wrap your lookup TextParm in single quotes since it is a text field, numerics you don't use quotes!!

    rs.FindFirst "[TextKey] = '" & TextParm & "'"

  11. #11
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Originally posted by glnstanley
    you just need to wrap your lookup TextParm in single quotes since it is a text field, numerics you don't use quotes!!

    rs.FindFirst "[TextKey] = '" & TextParm & "'"
    Fantastic!
    Finally someone understood what I was trying to do...
    and you're only a junior member - ha ha

    works great thanks
    ActionAnt

Posting Permissions

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