Solved Parameter prompt When sorting a form, after changing forms RecordSource (1 Viewer)

marlan

Registered User.
Local time
Today, 10:29
Joined
Jan 19, 2010
Messages
409
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:
Code:
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:rolleyes:

Any Ideas?:unsure:
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:29
Joined
Sep 21, 2011
Messages
10,561
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

I’m here to help
Staff member
Local time
Today, 00:29
Joined
Oct 29, 2018
Messages
18,992
Hi. Are you able to post a demo db? It might be easier to troubleshoot the problem with a sample file.
 

marlan

Registered User.
Local time
Today, 10:29
Joined
Jan 19, 2010
Messages
409
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:29
Joined
May 21, 2018
Messages
6,371
Try
Code:
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.
 

Users who are viewing this thread

Top Bottom