Transferring data from one form to another

[gsandy]

I have a form (frmJob) that has a combobox (cmbCompany). If a new company is not in the table the NotInList code opens another form (frmCompany) and a new company is added into the textbox (txtCoName).

The code below is the code that closes the frmCompany after entering new company.

The first part of the code (Add new company into table) works and adds the new company into table but the second part (Add new company into frmJob) does not, and the new company does not appear in the cmbCompany combobox. Note: frmJob remains open while data is entered into frmCompany.

How can I get the second portion of the code to work? Thanks Sandy

Public RemCompany As String
  Private Sub cmdCloseCompany_Click()
   ‘‘FIRST PORTION - Add new company into table
  Dim dbCom As Database
  Dim recCom As Recordset
  Set dbCom = CurrentDb
  Set recCom = dbCom.OpenRecordset("Select * from tblCompany")
  recCom.AddNew
  recCom("CoName") = Me.txtCoName
  recCom.Update
  Set recCom = Nothing
  Set dbCom = Nothing
   
  ‘‘SECOND PORTION - Add new company into frmJob (which is open)
  RemCompany = Forms!frmCompany.txtCoName
  DoCmd.Close acForm, "frmCompany"
  Forms!frmJob.Undo
  Forms!frmJob.cmbCompany = RemCompany
  Me.Refresh
  End Sub
   [\Code]

 

[madefemere]

Hello,
Use Requery method before assign value to your combo "cmbCompany :

Public RemCompany As String
  Private Sub cmdCloseCompany_Click()
   ‘‘FIRST PORTION - Add new company into table
  Dim dbCom As Database
  Dim recCom As Recordset
  Set dbCom = CurrentDb
  Set recCom = dbCom.OpenRecordset("Select * from tblCompany")
  recCom.AddNew
  recCom("CoName") = Me.txtCoName
  recCom.Update
  Set recCom = Nothing
  Set dbCom = Nothing
   
  ‘‘SECOND PORTION - Add new company into frmJob (which is open)
  RemCompany = Forms!frmCompany.txtCoName
  DoCmd.Close acForm, "frmCompany"
  Forms!frmJob.Undo
Forms!frmJob.cmbCompany.Requery
  Forms!frmJob.cmbCompany = RemCompany
End Sub

Good continuation

 

[gsandy]

Hi Madefemere, unfortunately that did not work. The new company has definitely been entered into the table but it just doesn't show in cmbCompany!

 

[madefemere]

Hi,

I suggest you to use this code which is simpliest if you have to add only the name of company in the table to actualise the combo. You have not to open another form "frmCompany" with this code.

Private Sub cmbCompany_NotInList(NewData As String, Response As Integer)
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim intAnswer As Integer

On Error GoTo ErrorHandler

   intAnswer = MsgBox("Add " & NewData & " to the list of Company ?", _
      vbQuestion + vbYesNo)

   If intAnswer = vbYes Then

      ' Add shipper stored in NewData argument to the Shippers table.
      Set db = CurrentDb
      Set rst = db.OpenRecordset("tblCompany")
      rst.AddNew
      rst!CoName = NewData
      rst.Update

      Response = acDataErrAdded         ' Requery the combo box list.
   Else
      Response = acDataErrDisplay       ' Require the user to select
                                        ' an existing shipper.
   End If

   rst.Close
   db.Close

   Set rst = Nothing
   Set db = Nothing

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

The original is in the help of "NotInList" Method of VBA and use DAO to add record in a form that depends on the same table is not a good idea I think.

Have a good continuation

 

[gsandy]

Hi Madefemere, I have been struggling for weeks to find the right code to do what your code does. It worked perfectly. Many thanks for your help, Sandy.