Synchronize a form with a combox box

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
frank_guessAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
In the form current event procedure add your version of the code...

if not me.newrecord then Me.comboboxname = me.[Vendor_number]
0
Helen FeddemaCommented:
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
Helen FeddemaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.