?
Solved

Synchronize a form with a combox box

Posted on 2012-03-19
3
Medium Priority
?
330 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 2000 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
You can use the network upload option and the Office 365 Import service to bulk-import PST files to user mailboxes. Network upload means that you upload the PST files a temporary storage area in the Microsoft cloud.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

569 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