Solved

Problem getting 2nd form field to populate with value of combobox on 1st form

Posted on 2015-02-11
5
143 Views
Last Modified: 2015-02-12
On a main form I have a combobox.  The record source is a query and the bound column is the key field of the table.

The combobox properties are:
Limit to List = Yes
Allow Value List Edits = No
Inherit Value List = No

and I have a form, (modal), which opens when the user enters a name in the combobox that isn't in the table.

I cannot get the 2nd form to populate the customer name field with the value the user entered in the combobox.

What am I doing wrong?
0
Comment
Question by:SteveL13
5 Comments
 

Author Comment

by:SteveL13
ID: 40603890
By the way, it does populate with a "0".  ?????????
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40604197
Can we see the code you are using?
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40604365
> I cannot get the 2nd form to populate the customer name field with the value the user entered in the combobox.

The Value of the combobox is probably Null at this moment as the content is neither found nor stored.
Try catching the Text property of the combobox. It must have retained focus to allow this.

/gustav
0
 
LVL 84
ID: 40605382
If you use the NotInList event, you can pass in the missing value:

https://support.microsoft.com/kb/197526
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40606116
It may be picking up the (null) value of the combo box's bound field.  Here is some code I use for adding a value on-the-fly.  The first code (for simple lookup tables) just adds the new value to the lookup table and requeries the combo box so the new value is selected; the second code opens a form where other data can be entered.  After closing that form, the combo box is requeried and the new value selected.

No Form version:
Private Sub cbo________NotInList(strNewData As String, intResponse As Integer)
'Set Limit to List to Yes
'See Add-to Combo Boxes (AA 161).mdb
'Created by Helen Feddema 24-Apr-2011
'Last modified 7-Apr-2010

On Error GoTo ErrorHandler
   
    Dim cbo As Access.ComboBox
    Dim intMsgDialog As Integer
    Dim intResult As Integer
    Dim rst As DAO.Recordset
    Dim strEntry As String
    Dim strFieldName As String
    Dim strMsg As String
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strTable As String
    Dim strTitle As String
  
    'The name of the table that is the combo box's row source
    strTable = "________________"
  
    'The type of item to add to the table
    strEntry = "_____________"
  
    'The field in the lookup table in which the new entry is stored
    strFieldName = "_______________"
  
    'The add-to combo box
    Set cbo = Me.ActiveControl

    'Display a message box asking whether the user wants to add
    'a new entry.
    strTitle = strEntry & " not in list"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intResult = MsgBox(strMsg, intMsgDialog, strTitle)

    If intResult = vbNo Then
        'Cancel adding the new entry to the lookup table.
        intResponse = acDataErrContinue
        cbo.Undo
        GoTo ErrorHandlerExit
    ElseIf intResult = vbYes Then
        'Add a new record to the lookup table.
        Set rst = CurrentDb.OpenRecordset(strTable)
        rst.AddNew
        rst(strFieldName) = strNewData
        rst.Update
        rst.Close
   
        'Continue without displaying default error message.
        intResponse = acDataErrAdded
     End If
   
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


Form version:
Private Sub cbo________________NotInList(strNewData As String, intResponse As Integer)
'Created by Helen Feddema 7-May-2007
'Last modified 7-May-2007

On Error GoTo ErrorHandler
   
   Dim cbo As Access.ComboBox
   Dim frm As Access.Form
   Dim intMsgDialog As Integer
   Dim intResult As Integer
   Dim rst As DAO.Recordset
   Dim strDescription As String
   Dim strEntry As String
   Dim strFieldName As String
   Dim strFilter As String
   Dim strForm As String
   Dim strMsg As String
   Dim strMsg1 As String
   Dim strMsg2 As String
   Dim strTable As String
   Dim strTitle As String
      
   'The name of the table that is the combo box's row source
   strTable = "_______________"
  
   'The name of the form
   strForm = "_______________"

   'The type of item to add to the table
   strEntry = "_______________"
  
   'The field in the lookup table in which the new entry is stored
   strFieldName = "_____________"
  
   'The add-to combo box
   Set cbo = Me.ActiveControl

   'Display a message box asking whether the user wants to add
   'a new entry.
   strTitle = strEntry & " not in list"
   intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
   strMsg1 = "Do you want to add "
   strMsg2 = " as a new " & strEntry & " entry?"
   strMsg = strMsg1 + strNewData + strMsg2
   intResult = MsgBox(strMsg, intMsgDialog, strTitle)

   If intResult = vbNo Then
      'Cancel adding the new entry to the lookup table.
      intResponse = acDataErrContinue
      cbo.Undo
      GoTo ErrorHandlerExit
   ElseIf intResult = vbYes Then
      'Add a new record to the lookup table.
      Set rst = CurrentDb.OpenRecordset(strTable)
      rst.AddNew
      rst.Fields(strFieldName) = strNewData
      rst.Update
      rst.Close
   
      cbo.Undo
      
      'Continue without displaying default error message
      intResponse = acDataErrContinue
   
      'Open form for adding more data for new item
      DoCmd.OpenForm strForm
      Set frm = Forms(strForm)
      strFilter = "[" & strFieldName & "] = " & Chr$(39) _
         & strNewData & Chr$(39)
      Debug.Print "Filter string: " & strFilter
      frm.FilterOn = True
      frm.Filter = strFilter
   End If
   
ErrorHandlerExit:
   Exit Sub

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

End Sub


=============================
Code for form buttons
=============================

Private txt As Access.TextBox
Private cbo As Access.ComboBox
Private frm As Access.Form
Private prj As Object
Private strCallingForm As String
   

Private Sub cmdDiscard_Click()
'Created by Helen Feddema 20-Mar-2007
'Last modified 20-Mar-2007

On Error GoTo ErrorHandler
   
   Set prj = Application.CurrentProject
   
   'Name of the form with the add-to combo box
   strCallingForm = "_______________"
   
   DoCmd.SetWarnings False
   DoCmd.RunCommand acCmdDeleteRecord
   If prj.AllForms(strCallingForm).IsLoaded Then
      Forms(strCallingForm).Visible = True
   Else
      DoCmd.OpenForm strCallingForm
   End If
   
   Set frm = Forms(strCallingForm)
   
   'Name of add-to combo box
   Set cbo = frm![_______________]
   cbo.Requery

ErrorHandlerExit:
   DoCmd.Close acForm, Me.Name
   Exit Sub

ErrorHandler:
   If Err.Number = 2467 Then
      Resume ErrorHandlerExit
   Else
      MsgBox "Error No: " & Err.Number _
         & " in " & Me.ActiveControl.Name & " procedure; " _
         & "Description: " & Err.Description
   End If
   
End Sub

Private Sub cmdSave_Click()
'Created by Helen Feddema 20-Mar-2007
'Last modified 20-Mar-2007

On Error GoTo ErrorHandler
   
   Set prj = Application.CurrentProject
   
   'Name of the form with the add-to combo box
   strCallingForm = "_______________"
   
   'The textbox control on this form that holds the key value
   Set txt = Me![____________]
   
   If prj.AllForms(strCallingForm).IsLoaded Then
      Forms(strCallingForm).Visible = True
   Else
      DoCmd.OpenForm strCallingForm
   End If
      
   Set frm = Forms(strCallingForm)
   
   'Name of add-to combo box
   Set cbo = frm![_______________]
   cbo.Requery
   cbo.Value = Nz(txt.Value)
      
ErrorHandlerExit:
   DoCmd.Close acForm, Me.Name
   Exit Sub

ErrorHandler:
   If Err.Number = 2467 Then
      Resume ErrorHandlerExit
   Else
      MsgBox "Error No: " & Err.Number _
         & " in " & Me.ActiveControl.Name & " procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

------------------------------------------------------

Alternate method (see KPRS database (E&A #67))

Private Sub cboArchitect_NotInList(strNewData As String, intResponse As Integer)
'Created by Helen Feddema 21-Oct-2012
'Last modified by Helen Feddema 26-Oct-2012

On Error GoTo ErrorHandler
   
   Dim cbo As Access.ComboBox
   Dim dbs As DAO.Database
   Dim frm As Access.Form
   Dim intMsgDialog As Integer
   Dim intResult As Integer
   Dim lngID As Long
   Dim rst As DAO.Recordset
   Dim strDescription As String
   Dim strEntry As String
   Dim strFieldName As String
   Dim strFilter As String
   Dim strForm As String
   Dim strMsg As String
   Dim strMsg1 As String
   Dim strMsg2 As String
   Dim strTable As String
   Dim strTitle As String
   Dim strSearch As String
   
   'The name of the table that is the combo box's row source
   strTable = "tblConsultants"
  
   'The name of the form
   strForm = "frmConsultants"

   'The type of item to add to the table
   strEntry = "Consultant"
  
   'The field in the lookup table in which the new entry is stored
   strFieldName = "Consultant Name"
  
   'The add-to combo box
   Set cbo = Me.ActiveControl

   'Display a message box asking whether the user wants to add
   'a new entry.
   strTitle = strEntry & " not in list"
   intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
   strMsg1 = "Do you want to add "
   strMsg2 = " as a new " & strEntry & " entry?"
   strMsg = strMsg1 + strNewData + strMsg2
   intResult = MsgBox(strMsg, intMsgDialog, strTitle)

   If intResult = vbNo Then
      'Cancel adding the new entry to the lookup table.
      intResponse = acDataErrContinue
      cbo.Undo
      GoTo ErrorHandlerExit
   ElseIf intResult = vbYes Then
      'Add a new record to the lookup table.
      Set rst = CurrentDb.OpenRecordset(strTable)
      rst.AddNew
      rst.Fields(strFieldName) = strNewData
      lngID = rst![ConsultantID]
      rst.Update
      rst.Close
   
      cbo.Undo
      
      'Continue without displaying default error message
      intResponse = acDataErrContinue
   
      'Open form for adding more data for new item
      DoCmd.OpenForm strForm
      Set frm = Forms(strForm)
      strSearch = "[ConsultantID] = " & lngID
   
      'Find the record that matches the control
      frm.Recordset.FindFirst strSearch
      frm.Tag = "Projects"
      frm![cmdDiscard].Visible = True
      frm![cmdSave].Visible = True
   End If
   
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

13 Experts available now in Live!

Get 1:1 Help Now