Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-06
3
Medium Priority
?
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1000 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 1000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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