Solved

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

Posted on 2015-01-06
3
272 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

770 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