Solved

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

Posted on 2015-01-06
3
276 Views
Last Modified: 2015-01-07
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
Comment
Question by:ExpressMan1
3 Comments
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 40535094
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 40535492
here test this
Invoice-Rev2.zip
0
 

Author Closing Comment

by:ExpressMan1
ID: 40535750
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question