Update combo box according to an entry in a new form

[Guest]

I have a database to enter new books in our resource centre. I've created a
form to enter new books purchased. The title of the book must be taken out
of a limit to list combobox. To add a new entry, I have added a button that
opens a form. On OK, I am going back to the initial form but the combo box
is not updated automatically. I is updated only if I close and reopen the
form, which I obviously don't want to have to do every time. Can you help?
Thank you
Sandra

 

[Guest]

Sounds like you need to requery your combo box. If you are using an "OK"
button to close the second form and return to the first form, then put this
in the OnClick event of that button;

Forms!NameOfYourFirstForm![NameOfYourComboBox].Requery

HTH

 

[Guest]

Sandra:

If you put code in the combo box's NotInList event procedure you can do
without the button and enter the book title into the combo box itself. The
following code does this fro cities and opens another form to enter other
data about the new city:

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

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

With the above the Cities form's open event procedure also contains the
following code:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub


The following does it where only the city name is stored in the Cities
table, so opening a second form to add further data is unecessary as the name
is simply typed into the combo box:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

Set cmd = Nothing

Doing it your present way you need to requery the combo box on the original
form and set its value to the new book after adding the book in the second
form with:

Forms.[YourFirstForm].[YourComboBox].Requery
Forms.[YourFirstForm].[YourComboBox] = Me.[YourBookField]

Put this in the AfterInsert event procedure of the second form.

Ken Sheridan
Stafford, England