Solved Parameter prompt When sorting a form, after changing forms RecordSource

[marlan]

Hi All You experts!

A section in my system is there for linking products from the same category:
There is a maidisplan Products table, and other Tables with lists of products in other countries named by convention CountryNmaeProducts. All these tables are linked by a CategoryID field.
In the Main form: Products form displays a product (with a CategoryID), and a list of buttons, one for each country, displays the products from that country, filtered by CategoryID.
The list from the countries is displayed in a general form, frmLinks, and each of the above buttons sets the form's RecordSource property to a query named by convention CountryNmaeLinks. All these queries have the same field names, to match the form field's ControlSource properties.

Up to here it is all fine. Here is the Code running this:

Public Sub changeLinksForm(countryID As Integer)
    Dim countryName As String
    Dim recSource As String
    Dim filtOn As Boolean
    
    filtOn = Me.FilterOn   
    countryName = getcountryName(countryID)
    recSource = countryName & "Links"
    If Me.RecordSource <> recSource Then Me.RecordSource = recSource

OpenLinksForm_Exit:
    Me.FilterOn = filtOn
    Exit Sub
OpenLinksForm_Err:
    MsgBox Err.Description
    Resume OpenLinksForm_Exit
End Sub

The issue is: When the client sorts according to a field, Access prompts for the value of the field In the Previous CountryNmaeLinks query.
Say: The form displayed Germany, and I click on England, and the sort by ProductName, I will be prompted for the value of GermanyLinks.ProductName

Any Ideas?

 

[Gasman]

Sounds like you are not waiting for the control to be updated?
Walk through your code with F8 and see what gets produced, what countryID is actually supplied to that code?

 

[theDBguy]

Hi. Are you able to post a demo db? It might be easier to troubleshoot the problem with a sample file.

 

[marlan]

Sounds like you are not waiting for the control to be updated?
Walk through your code with F8 and see what gets produced, what countryID is actually supplied to that code?

Thanks for replying,
The sort is manual (Right-click -> Sort A to Z), long after setting the record source.

 

[marlan]

Hi. Are you able to post a demo db? It might be easier to troubleshoot the problem with a sample file.

I'll try to, though it's not my data

 

[theDBguy]

I'll try to, though it's not my data

Just put some dummy data. Doesn't have to be a lot.

 

[MajP]

Try

If Me.RecordSource <> recSource Then
  me.filter = ""
  me.orderby = ""
  Me.RecordSource = recSource
end if

If you have an orderby or filter property of the form set and you apply a new recordsource that recordsource inherits the filter or orderby.

 

[marlan]

me.orderby = ""

This seems to have been the issue, Thanks a lot!!