Solved

Synchronize a form with a combox box

Posted on 2012-03-19
3
312 Views
Last Modified: 2012-06-21
I have a combo box on a form as a lookup values within the table.  The box works ok.  it uses an embedded macro

SearchForRecord  
Record First Where Condition , , First, = "[Vendor_Number] = " & "'" & [Screen].[ActiveControl] & "'"

I need the combo box to match the vendor number box as I move from one record to another recovery and keep synchronized between the information on the form and the combo box.

Any ideal how to make this work.

Thank you
0
Comment
Question by:frank_guess
  • 2
3 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37738688
In the form current event procedure add your version of the code...

if not me.newrecord then Me.comboboxname = me.[Vendor_number]
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37739407
Try this boilerplate code from an unbound combo box in the form header, with a row source of (generally) two fields -- the key field and one other to display in the list:

Private Sub cboSelect_AfterUpdate()
'Created by Helen Feddema 29-Apr-2011
'Last modified by Helen Feddema 29-Apr-2011

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

============Subform version=============

Private Sub cboSelectOrder_AfterUpdate()
'Created by Helen Feddema 7-Mar-2012
'Last modified 7-Mar-2012

On Error GoTo ErrorHandler

   Dim strSearch As String
   Dim frmSub As Access.Form
   
   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control on the subform
   Set frmSub = Me![sub_____________].Form
   frmSub.Recordset.FindFirst strSearch
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 37739414
Usually the combo box would be wiped of its selection from the Current event, but if instead you want it to display the current record value, write that value to the combo box from the Current event instead (presumably VendorID).
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now