Requery combobox

[surfbum3000]

I have a form frmDeleteRecord with a combobox Combo25. When the frmDeleteRecord is opened, the user is instructed to click on the drop down menu to make a selection. Once this is done, the other fields autofill. This works fine. The problem is after making the selection of the record to delete, the query the Row Source is based on tries to immediately run again. I would like to be able to click on the combobox, make my selection, then click the cmdDelete button. When I click on Combo25 again, I would like to perform a new search. Here is what I have so far:
_________________________________
Sub Combo25_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo25]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
_________________________________
Private Sub Combo25_Click()
DoCmd.OpenQuery "q_Delete"
End Sub
__________________________________
Private Sub Combo25_GotFocus()
Combo25.Requery
End Sub

 

[BoxHead]

You have the delete query running on your Combo25_Click event instead of on the cmdDelete.

Private Sub Combo25_Click()
DoCmd.OpenQuery "q_Delete"
End Sub

HTH

John




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[surfbum3000]

I changed the delete query from running on Combo25_Click event to cmdDelete without the desired result. After that change, when I click on the cmdDelete, the query tries to run again.

Private Sub cmdDelete_Click()
DoCmd.OpenQuery "q_Delete"
End Sub

Currently, the following occurs:
1. Open form
2. Click on Combo25
3. Query runs with criteria Like [Enter Last Name]
3. Click on selection from pop up window
4. Record then selected.
5. Form populates all fields
6. Click cmdDelete (Do you want to delete?)

Everything is fine up until this point. The problem occurs when I click on Combo25 again, all I see is the results of the 1st query in the pop up window. I need to run the query again when I click on Combo25. I tried:

Private Sub Combo25_GotFocus()
Combo25.Requery
End Sub

This causes the query to immediately run again after I make my selection from the pop up window, not allowing me to delete the record unless I cancel the window.
Here is what I have so far:
____________________________________________

Sub Combo25_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo25]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
______________________________________________

Private Sub cmdMainMenu_Click()
On Error GoTo Err_cmdMainMenu_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdMainMenu_Click:
Exit Sub

Err_cmdMainMenu_Click:
MsgBox Err.Description
Resume Exit_cmdMainMenu_Click

End Sub
___________________________________________________
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub
______________________________________________
Private Sub Combo25_GotFocus()
Combo25.Requery
End Sub

 

[stix4t2]

Maybe the form is not taking focus off the combo25, so the event is being recalled. Try cmdDelete.setfocus in the cmdDelete_Click event. Then when you click the combo25 again, the GotFocus even will fire.

 

[surfbum3000]

Tried cmdDelete.setfocus in the cmdDelete_Click event. No effect. The problem is the Enter Parameter Value window immediately pops up after a record is selected. If I cancel the window, I can delete the record. I want the Enter Parameter Value to only appear when Combo25 is clicked. This is the only problem with this form.

 

[surfbum3000]

I got this to work although it was not what I really wanted. I added a Refresh button that can be clicked to perform a new search. I wanted to be able to click on the combobox, refresh the form, then do a new search. I guess I can live with what I have unless you can come up with something else.

Private Sub cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_cmdRefresh_Click:
Exit Sub

Err_cmdRefresh_Click:
MsgBox Err.Description
Resume Exit_cmdRefresh_Click

End Sub

 

[stix4t2]

surfbum,

Are we trying to push the square peg through the round whole here. I had hope the quick fix would work, but it seems like life has gotten even more complex with adding another button. Also, adding criteria like [Last Name] to a query can be open to typos and confusion.

You may consider using a second unbound combo box listing [Last Name] groupby query that lets your users select the criteria. You can use this combo's on click event to combo25.requery Then insert into combo25's rowsource query a reference to the LastName combo box Forms!FormName!cboLastName. In the delete button on click to requery both combo boxs.

(note: cboID is a easier to recognize, and the new combo cboLastName) Remember to change the control name before creating event procedures so they can be tied to the control. Access will not rename procedures when you rename controls.