Form Command to refresh Query

[TDC21]

I have a query that is referencing the entries made in a Form. On the Form I have a command button that will open the query. The query is referencing form entries and filtering as expected now but the query has to be either closed or refreshed each time new data is entered onto the form. Could the Run query command be altered to Run query or if Query is open, then refresh query? If not is there a Command that I could add to the form to refresh the query?

I have looked online and all I found was a reference from 2007 saying that Access dose not contain any refresh commands, is that still the case or is there anything I can do to simplify this operation?

Thanks again everyone

 

[alansidman]

On the particular control that is updated, use the afterupdate event and in the event type

me.requery

 

[Micron]

is this not the same issue described in or around post number 3 here?
https://www.mrexcel.com/forum/microsoft-access/1024946-report-macro-apply-filter.html

 

[TDC21]

Thanks again Micron, yes, that was the same issue, it came up as a response to a previous question so I started a thread on this one specifically. I have been trying the macro route, I dont have a ton of experience there but I have done some. My VBA knowledge is pretty minimal. Im not afraid to try but my confidence drops pretty significantly.

 

[TDC21]

I added the requery code to the after update event (quite possibly incorrectly). The form has 4 data fields that filter the results from 2 other queries, wouldn't the queries need to be added as refresh target? This is how it looks currently but admittedly I dont know much about the code side of access.

Option Compare Database


Private Sub Form_AfterUpdate()


End Sub


Private Sub HEAT_SLAB_Search_AfterUpdate()
Me.Requery
End Sub


Private Sub MTR_Search_AfterUpdate()
Me.Requery
End Sub


Private Sub PART_Search_AfterUpdate()
Me.Requery
End Sub


Private Sub THICK_Search_AfterUpdate()
Me.Requery
End Sub


Thanks again, all the help has been greatly appreciated

 

[Micron]

I have to admit, I wasn't sure if that would work on a query that is already open and had to test it.
Me. or Me! refers to the form, report or class that the code belongs to, thus Me.Requery will perform the requery action of the form, not the control. If the form is based on a table, Access will look to see if any table data has been changed by anyone since the form has been opened, subject to any filter you may be applying to the table records. My uncertainty was that if based on a query and that query is already open, would the form simply reload the query contents, which might be static because it isn't being refreshed? To my surprise, the requery action seems to re-run the query even though it's already open. Reports do not exhibit this behaviour (i.e. a call to open a report that is already open doesn't show new data). Not sure why you have a button to open a query unless you intend to view it as a data sheet. Any form or report based on a query automatically runs that query in the background in order to load the report or form. Opening the query first is not only not necessary, it uses resources and can expose its data, allowing anyone to mess with it.

 

[TDC21]

I was using the form as a Query sort because there are two Queries, that I am trying to return results from, both of which may return multiple results, so the data sheet format is needed. I had previously been working with 2 forms, one for each query but in a split form format, this was satisfying the need for a data sheet format. So if I am reading your description of the me.requery operation correctly, I should go back to the split forms.

Sorry for so many novice questions but the info has been very helpful and is defiantly appreciated.

 

[Micron]

No problem.
No, I'm not saying what type of form to use. I'm saying that usually, if you want to open a form or report which is based on a query, your button simply opens the form or report. There's no need to open the query first, and IMHO, you shouldn't (outside of just testing the query). When you open the object, it runs the query in the background, so to speak. If that query is looking at form control values for criteria, that form needs to be open.

Typically, your form controls provide the criteria values and a command button opens the object - that's it. In many/most cases those 'criteria' controls are not bound to a table or query since changing their values to feed different criteria to a query can change those values in their respective fields in the underlying table. These criteria controls would therefore usually be in the header part of a form if that form contains records. You can also use a search type of form to provide the means for criteria input, and your button would open the needed form as previously mentioned. The advantage to this is that you can create a very robust search form, sometimes with a tab control where each page is meant for a different type of search and/or resulting form/report.
Hope that makes sense.

 

[TDC21]

Thanks again Micron and yes, that made perfect seance. I have tried to come at this problem several different ways and it has deffiantley been more of a challenge than I expected, If I quickly described the situation I am trying to address, I would be curious to know how you would go about it if you would like to share your thoughts.

We have raw material, steel plate, when material is received it is entered into our database with a [Part_ID], [THICK], [MTR], and [HEAT/SLAB]

Part ID and thickness are group entries we may have numerous pieces for either but these are frequently referenced values for production planning and control.

The [MTR] and [HEAT/SLAB] are NOT unique but there would generally not be many duplicates of either.


The plate yard is outside, so any or all of the identification tags physically applied to the plates can be partially or completely worn of. When production pulls plate from inventory it becomes a process to try to identify what specific plate has actually been used in these circumstances.

I am trying to create a form/report that will allow for partial information in any of these fields to be entered, and return all database results that have a like match.

This way if the MTR has been worn off and only a portion of the Heat/Slab is still visible, that portion could be searched against current inventory and return matched values. This might return a single result which would be easy but it could return several as well. In most cases the search would be done by either MTR or HEAT/SLAB but if multiple results are returned then the thickness and Part ID fields may be able to be applied as well to narrow the results.


If you have any thoughts on what might be the best way to go about that I would be happy to hear them.

 

[alansidman]

If you have partials for those fields, in a query you could use the like function with asterisks. Look at this link for examples

https://www.techonthenet.com/access/queries/like2007.php