qry get integer from txtbox on form, the qry will NOT display anything but first value used UNLESS i click on Refresh All on ribbon, refresh does NOT update qry either if i click on ribbon ro use in after event of cbo

Question

i use this to enter a few chars of description of what I want in txtPartOfDescp. 

I use to fill the cboSelectWantedItem with only record(s) that have those chars in the description.

Private Sub txtPartOfDescp__AfterUpdate()

Dim SQL As String

SQL = "SELECT tblWanted.Wanted, tblWanted.ID FROM tblWanted Where Wanted like '*" & Me.txtPartOfDescp & "*' order by WANTED"

Me.cboSelectWantedItem.RowSource = SQL
Me.cboSelectWantedItem.SetFocus
Me.cboSelectWantedItem.Dropdown
End Sub

i then select one form the cboSelectWantedItem, after_update event puts the ID # (a long integer) in the txtNumOfWantSelected that is used as a criteria in a QryItemsByVendor.

qry's sql is

SELECT tblWanted.ID, tblWanted.Wanted, Contacts.Company, Contacts.[Last Name], Contacts.[First Name]
FROM Contacts INNER JOIN (tblWanted INNER JOIN tblVendorCarries ON tblWanted.ID = tblVendorCarries.ItemCarriesIS) ON Contacts.ID = tblVendorCarries.VendorIS
WHERE (((tblWanted.ID)=[Forms]![VenByWant]![txtNumOfWantSelected]));

Private Sub cboSelectWantedItem_AfterUpdate()
'Me.Refresh

Me.txtNumOfWantSelected.Value = ""

 Debug.Print "col(1) of cboSelectWantedItem is " & Me.cboSelectWantedItem.Column(1) & "THE id # OF WANTED ITEM IN TBL!"
Debug.Print "Me.cboSelectWantedItem.Column(0) = " & Me.cboSelectWantedItem.Column(0)
Debug.Print "-----"

Me.txtNumOfWantSelected = Me.cboSelectWantedItem.Column(1)
'Me.Refresh

DoCmd.OpenQuery "QryItemsByVendor", acViewNormal

'Me.Refresh_ALL     and  me.ReFreshall   gives me method or data member not found
Me.Refresh
Me.Repaint  'did not help

End Sub

first time works exactly as wanted and expected. showing the qry.

next time enter a different string of chars in txtbox all works, BUT qry ONLY shows last qry run.  Does NOT show the correct results of new qry criteria UNLESS click on Refresh All, on the ribbon

from the time when XP was new, i had to sometimes use Refresh  and sometimes use me.refresh.  This is the FIRST time ever i had to use Refresh All   and was unable to use that in my VBA code it is 7.1   win 10 pro 4 bit.

IF I CLICK ON THE REFRESH ALL BUTTON ON THE RIBBON QRY THEN SHOWS CORRECT RESULTS.

I CANNOT SEE WHERE I GOOFED.  I THOUGHT I COULD JUST PUT IN CODE TO GET RESULTS OF PRESSING THE REFRESH ALL BUTTON. HAVEN'T FOUND A WAY.

ANY help or ideas will be great.

Mark J

---

Mark J

Answer 1

You can try:

DoCmd.RunCommand acCmdRefresh

Answer 2

Lawrence Ellefson,

thanks for the reply so quickly.

i put that line as the last line in the cbo After Event.

and it acted just like i had clicked on "Refresh" not "Refresh All" nothing happen different then before.

i thought "acCmdRefresh" was ONLY for the "Refresh" not "Refresh All" on the ribbon.

could i be putting the "refresh" code in wrong spot?

---

Mark J

Answer 3

You might also try a Me.Recalc just before you run the query to make sure the query is picking up the new values of the input form.

Answer 4

Mark,

Always use the "OnClick" event of the ComboBox to requery or filter your results from the TextBox.

Like....

 

Private Sub cboSelectWantedItem_OnClick()

Me.cboSelectWantedItem.Requery

HTH

Answer 5

AccessVandal,

thanks for the idea.

did not work.

it might be none of the code or events are bound to the form. before, during or after any events.

The txtbox is only used as a holding spot to hold a partial string of what is in the description of what i want.  after update event of that textbox is used to assign the sql to the cbo.RowSource so if i enter "BO" cbo would show Book stores, book publishers, and archery bow in cbo's box.

after update event of cbo, i would then see results of qry showing me who handles item i selected.

thanks for the idea, i did try it.

Mark J

---

Mark J

The answer was to close the qry between the times i ran it with a new criteria .   What worked best was to used DoCmd.Close acQuery, "QryItemsByVendor", acSaveNo     as FIRST line in cbo.After_Update Event.   worked great then. 

Answer 6

Hi Mark,

I would recommend you to read up cascading comboboxes which might help to better understand on how to Refresh/Requery a combobox when a Textbox or ComboBox is/are changed.

As for your Query, I'm not sure what you mean. Once the Query is opened, you will need to close it and open it again if the criteria is changes.

I would suggest that you use a Form in "Continuous Form" view instead and put a button(Form's Footer/Header) to Requery/Refresh the Form once the criteria is changed. 

HTH

Cascading ComboBoxes

Cascading ComboBoxes

PS. I'm not affiliated to these sites.