[X]My Assistant

[jackmeister23]

Hi,

Its my first time posting here and I've seen a lot of useful materials in this community.
Its my first time to build an access database and I encountered a problem while coding a change in Form's record source property via VBA.
The scenario is I have two queries.

One is "qryLinkList" - this is the default source on load
Second is "qryLinkListRefresh" - which is the same query but with a filter based on the value of a combobox on the form

I have worked on the after update event to change the source to "qryLinkListRefresh" and used below code.

CODE

Private Sub cboFileType_AfterUpdate()
    Dim qry As String
    Set qry = "qryLinkListRefresh"
    Dim dqry As String
    Set dqry = "qryLinkList"
    
    Me.RecordSource = qry
    Me.Requery
    
    If Me.cboFileType = "General" Then
        Me.cboClient.Visible = False
        Me.cboClient_Label.Visible = False
    Else
        Me.cboClient.Visible = True
        Me.cboClient_Label.Visible = True
    End If
    Me.RecordSource = dqry
End Sub

I see in some forums here that approaches variable string with the SQL Statement instead of the query name.
Just for the sake of learning, I wanted to know if the method of using the query name as string is possible.

Thanks and God Bless,

Jack

[theDBguy]

Hi Jack. Welcome to UtterAccess!

If you just want to filter the data on the form based on a value from a Combobox, there's really no need to change its Record Source. For example, let's say you want to filter the records on a form based on an ID value selected from a Combobox, you could try the following:

CODE

Me.Filter = "NumberFieldName=" & Nz(Me.ComboboxName,0)
Me.FilterOn = True

[AlbertKallal]

The only real issue here is to watch for is the “compile error”.

When you set a form to a data source, then at design time Access will “generate” a set of properties for each column. This allows you to go me.SomeColumnName.

However, if you change in code, then such me.FieldNames can start to break, and even start to cause compile errors. This is due to as noted the pre-generating of fields by access (it is a developers convenience thing access does for you).

You can often see coding efforts that developers were bitten by the above issue, then then you see a “pile” of invisible text boxes up in some corner to allow use of me.FieldName.

The other way to avoid this issue, is to use me!ColumnName. This approach is “immune” to the hope on a wing and prayer that your column names in the new SQL set at runtime match what access generated at design time. Usually this is not an issue, but you will MOST common find this issue crop up when you add additional columns, and you have a form in which VBA been setting the reocrdsource – not the access form designer at design time.

As for direct SQL, or some query, or even binding directly to the table? You quite much free to pick any approach here, and what works for you is quite fine. I can’t say there is some “big developer” advice that you should say use a query, or not use SQL in your code. A query can be handy, since your VBA code to build up the string might be a little less messy. And you can then use the same query in other places in your VBA code. But, I would not outright suggest that in-line SQL (that’s what it is called) is some bad thing to avoid.

So far? I think you are all fine and dandy here.

And I do think your question is good one. As the other poster shows access has so very many ways to filter and restrict data - in fact that makes Access a challenge as to which of the huge feature set to choose for a given task.

Regards
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com

[jackmeister23]

Thanks Guys. These are really helpful advice.
Its obvious I have skipped some fundamental knowledge on access features here.

@AlbertKallal, I really appreciate you taking your time discussing how the system works there. It gave me a new path to explore.

@theDbGuy, thanks for the quick advice. That's efficient coding right there. I really have to revisit properties again.

I didn't expect to receive such quick responses in this community so I am very thankful I joined this.
Hope in some other time I'll be on the other side of a thread here giving my own advice.

Jack

[theDBguy]

Hi Jack. You're very welcome. Albert and I were happy to assist. Good luck with your project.