Stop subform requery

[SJB67]

Hi All,

There is quite a lot of info around this out there, but I have been unale to solve my issue.

I have a parent form with a child subform.

On the parent form:

I have a textbox which the after update property includes a vba query to update a list box.

There is no .requery instruction
There is no interaction with the subform at this point
The subform has no requery property or code associated with it

However every time I hit return on the textbox to run the query to populate the listbox the subform requeries.

I would really prefer to keep the parent child links in tact if possible (some online suggestions include the removal of these).

Anyone have any ideas?

 

[RuralGuy]

What code do you have in the AfterUpdate event of the TextBox?

 

[SJB67]

Hi

The code is below, basically it creates an array and then runs a query on the results which populates the listbox mentioned.

Private Sub txtSearch_AfterUpdate()
'create array for search from search field (max 3 search criteria)
Dim StockString, StockArray, Msg
StockString = Forms!sfrmsalesheader!txtSearch
StockArray = Split(StockString, , -1, 1)



'copy array values into search boxes
Me.txtSearch1 = StockArray(0)
Me.txtSearch2 = StockArray(1)
Me.txtSearch3 = StockArray(2)



'refresh list box on sfrmSalesHeader
Forms!sfrmsalesheader.lstStock.RowSource = "SELECT tblStock.StockCode, tblStock.StockName, tblStock.RRP, tblStock.TradeDisc, tblStock.Cost, tblStock.Cat, tblStock.SupplierCode, tblStock.Supplierstockref, tblStock.VATRate " & vbCrLf & _
"FROM tblStock " & vbCrLf & _
"WHERE (((tblStock.StockName) Like ""*"" & [Forms]![sfrmSalesHeader]![txtSearch1] & ""*"" And (tblStock.StockName) Like ""*"" & [Forms]![sfrmSalesHeader]![txtSearch2] & ""*"" And (tblStock.StockName) Like ""*"" & [Forms]![sfrmSalesHeader]![txtSearch3] & ""*""));"


End Sub

 

[arnelgp]

does your Master/Child link field in the above code you posted? If yes then it will requery your subform.

 

[SJB67]

Hi,

No the master child link is not in the above code. The query is run on a 3rd unrelated table.

 

[arnelgp]

you don't have navigation for do you?

 

[SJB67]

sorry don't understand the question

 

[arnelgp]

sorry, i mean navigation form

 

[RuralGuy]

This line:
Dim StockString, StockArray, Msg
...will create 3 variant variables.
IIRC you cannot put vbCrLf in an SQL string.

 

[SJB67]

Hi Arnelgp - yes I do have a navigation form, could this have an impact? I have no links from it to the child form that is re-querying but it does open the parent form.

Hi Ruralguy - yes the after update routine is creating 3 variables in an array that are then pasted into 3 text boxes, these are then searched upon and the results displayed in the listbox. This is working but could it cause the re-query problem with the subform?

I am afraid I don't know what the following means:

IIRC you cannot put vbCrLf in an SQL string.

Cheers