Updating rows in combo box dropdown list

[Guest]

my database has two tables -Boats and logsheets with a one to many
referential join on the BoatID field
the logsheet form has a combo box for entering the boat id using a select
query from the boats table to populate the list which works fine. If the
logsheet is for a boat which is not in the Boats table there is a button
which opens the Boat Entry form so that it can be entered. Again this works
fine except that when it is closed and the combo button is activated the new
entry does not appear. Is there a neat way of forcing the select query to
update itself?

Thanks in advance,

Fran

 

[Mark]

You could set the combo box's LimitToList property to True. Then program
the NotInList event to open your other form, have the user enter the info
and close the form when they're done. Using "Response = acDataErrAdded" in
the procedure automatically requeries the combobox. Here's a sample from a
database I'm working on...

*********code start***************
Private Sub cboBoatList_NotInList(NewData As String, Response As Integer)
Dim strPrompt As String

On Error GoTo Err_NotInList
strPrompt = "'" & Me.cboBoatList.Text & "' is not in the list." & vbCr &
vbCr _
& "Do you want to add it?"
If MsgBox(strPrompt, vbExclamation + vbYesNo, "Not In List") = vbNo Then
Response = acDataErrContinue
Me.cboBoatList.Undo
MsgBox "Please select an item from the list"
Exit Sub
End If

DoCmd.OpenForm "frmBoats", , , , acFormAdd, acDialog
Me.cboBoatList.Undo
Response = acDataErrAdded

Exit_NotInList:
Exit Sub
Err_NotInList:
MsgBox "Error " & Err.Number & ": " & Err.Description, ,
"cboBoatList_NotInList"
Resume Exit_NotInList
End Sub
**************code end******************

 

[DebbieG]

Just a guess ... In the Logsheet form's On Activate or On Got Focus use:

'to stay on current record
Dim rst As Object, ID As String
ID = Me.BoatID
Me.NameOfComboBox.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "BoatID = '" & ID & "'"
Me.Bookmark = rst.Bookmark
Me.NameOfComboBox = Me.BoatID

if ID is numeric then rst.FindFirst "BoatID = " & ID

Or you can try in the Boat Entry form's Form_Close or Form_Unload use:

Forms!NameOfLogsheetForm!NameOfComboBox.Requery

HTH,
Debbie


| my database has two tables -Boats and logsheets with a one to many
| referential join on the BoatID field
| the logsheet form has a combo box for entering the boat id using a select
| query from the boats table to populate the list which works fine. If the
| logsheet is for a boat which is not in the Boats table there is a button
| which opens the Boat Entry form so that it can be entered. Again this
works
| fine except that when it is closed and the combo button is activated the
new
| entry does not appear. Is there a neat way of forcing the select query to
| update itself?
|
| Thanks in advance,
|
| Fran

 

[Guest]

Thanks to both of you -that should sort it
Fran