Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Synchronize a form with a combox box

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

927 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