• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Combo Box on subform not updating for certain records unless selected twice.

Small DB example attached.

Combo box on subform updates records in conjuction  records in main form "frmInvoice" except for the  last record in combo box "222211111242"
If record "222211111242" is chosen for a second time it updates to the correct record.

Tried Me.Combo15.Requery in various places but not working.

Private Sub Combo15_AfterUpdate()
 
    Me.RecordsetClone.FindFirst "[ShipmentID] = " & Me![Combo15]
    If Not Me.RecordsetClone.NoMatch Then
        Me.Bookmark = Me.RecordsetClone.Bookmark
        Else
        Me.Parent.RecordsetClone.FindFirst "[InvoiceNumber] = " & Me.Combo15.Column(1)
            If Not Me.Parent.RecordsetClone.NoMatch Then
                Me.Parent.Bookmark = Me.Parent.RecordsetClone.Bookmark
                Else
                MsgBox "Record Not found!"
            End If
    End If

End Sub
Invoice-Rev1.zip
0
ExpressMan1
Asked:
ExpressMan1
2 Solutions
 
Gustav BrockCIOCommented:
It was not the only selection.
First find the invoice, then the shipment.
Here is how:
Private Sub Combo15_AfterUpdate()
 
    Dim rst         As DAO.Recordset
    Dim InvoiceID   As Long
    Dim ShipmentID  As Long
    
    InvoiceID = Val(Me!Combo15.Column(1))
    ShipmentID = Val(Me!Combo15.Value)
    
    If Me.Parent.InvoiceNumber.Value <> InvoiceID Then
        Set rst = Me.Parent.RecordsetClone
        rst.FindFirst "[InvoiceNumber] = " & InvoiceID & ""
        If Not rst.NoMatch Then
            ' This will requery this subform.
            Me.Parent.Bookmark = rst.Bookmark
        End If
    End If
    
    If Me!InvoiceNumber.Value = Me.Parent.InvoiceNumber.Value Then
        Set rst = Me.RecordsetClone
        rst.FindFirst "[ShipmentID] = " & ShipmentID & ""
        If Not rst.NoMatch Then
            Me.Bookmark = rst.Bookmark
        End If
    End If
    
    If Me!ShipmentID.Value <> ShipmentID Then
        MsgBox "Record Not found!"
    End If
    
    Set rst = Nothing

End Sub

Open in new window

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
here test this
Invoice-Rev2.zip
0
 
ExpressMan1Author Commented:
Both work great. Thank You Rey and Gustav.

Thanks again to Rey as it was his original code I was using. Interesting how this unusual problem can be solved two totally different ways.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now