Update Combo box through entry form (1 Viewer)

[eildon]

I have a form that lists all my properties that we manage. I have a combo box that lists the property owners(landlords) I have found and adapted some code that when you add a new landlord that is not in the list it asks you if you want to update, This code works very well and does a requery perfectly. However when the control activates the notinlist argument I would like it to Open a new form "frmLandlordEntry" put the NEWDATA I ahve just added to the LLName field and allow me to update the rest of the fields on the frmLandlordEntry form wich updates the Landlords Table, Then return to the Property Table to finish completing the fields. I have attached the code I am using now but all it does is it updates the the one field on the Landlords Table. I am still getting my head around the basics so your help would be great.

Private Sub Combo49_NotInList(NewData As String, Response As Integer)

Dim dbsProperty As DAO.Database
Dim rstLandlords As DAO.Recordset
Dim intAnswer As Integer

On Error GoTo ErrorHandler

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

If intAnswer = vbYes Then

' Add Landlords stored in NewData argument to the Landlords table.
Set dbsProperty = CurrentDb
Set rstLandlords = dbsProperty.OpenRecordset("Landlords")
DoCmd.OpenForm , "Landlords"

rstLandlords.AddNew
rstLandlords!LLName = NewData
rstLandlords.Update

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

rstLandlords.Close
dbsProperty.Close

Set rstLandlords = Nothing
Set dbsProperty = Nothing

Exit Sub

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

 

[RuralGuy]

There are several ways to do what you want but I prefer to use the OpenArgs argument to pass the value(s) to the next form.

Private Sub Combo49_NotInList(NewData As String, Response As Integer)

   On Error GoTo ErrorHandler

   If MsgBox("Add " & NewData & " to the list of Landlords?", _
             vbQuestion + vbYesNo) = vbYes Then

      DoCmd.OpenForm "Landlords", , , , acFormAdd, acDialog, NewData
      Response = acDataErrAdded   ' Requery the combo box list.
   Else
      ' Require the user to select an existing Landlord.
      Response = acDataErrDisplay
   End If
Combo49_NotInList_Exit:
   Exit Sub

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

Then you want to complete the control in the next form in the Load event.

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
    '-- Form is being passed a name
    Me.LLName = Me.OpenArgs
End If

End Sub

That's it.

 

[eildon]

Update Combo box through Entry Form

Thanks RG. Perfect and was exactly what I was after. Now I have to work through it and try and understand it all. You guys make it look all so easy

.
Until next time. PR- Oz

 

[RuralGuy]

Glad I could help.