Use the filter of a form in a SQL query via VBA

Question

I'm having trouble using the filter by form in a SQL query via VBA in Access 2013. I didn't create the Access forms but was commissioned to correct an issue. Also the client told me that it worked in the previous Office versions and that the Access-Database hasn't been changed in the past few years. So it seems, that Access 2013 does something differently. But I couldn't figure out what.

As you may understand below, the red highlighted button should un-/check all the yellow highlighted checkboxes. This works perfectly fine until I add a filter by form (red box at the bottom of image). Ironically I encountered this issue only by filtering the red underlined field Programm_IDFS. Filtering by other fields works fine. Form whose filter acts strange This query should uncheck the checkboxes but it fails because the value of strFilter is:

((Lookup_Programm__IDFS.Name="ad1incl"))

This may work for filtering, but it doesn't work as SQL restriction.

UPDATE dbo_tbl_ThisForm
SET dbo_tbl_ThisForm.Checkbox = 0,
    dbo_tbl_ThisForm.Statusoffen = '0'
WHERE dbo_tbl_ThisForm.Testfall_ID NOT IN
    (SELECT dbo_tbl_Restrictions1.Testfall_ID
    FROM dbo_tbl_Restrictions1
    WHERE dbo_tbl_Restrictions1.Auftrags_ID = " & gsVariable5 & ")
AND dbo_tbl_ThisForm.Testfall_ID NOT IN
    (SELECT dbo_tbl_Restrictions1.Testfall_ID
    FROM dbo_tbl_Restrictions1
    WHERE dbo_tbl_Restrictions1.Auftrags_ID IN
        (SELECT Auftrag_ID
        FROM dbo_tbl_Restrictions2
        WHERE Auftragstyp = " & Me.kfAuftragstyp & "))
AND " & strFilter & "

This query should check all the checkboxes. It works because I hard-coded all the values (actually this is only necessary for strFilter).

UPDATE dbo_tbl_ThisForm
SET dbo_tbl_ThisForm.Checkbox = -1,
    dbo_tbl_ThisForm.Statusoffen = '-1'
WHERE dbo_tbl_ThisForm.Testfall_ID NOT IN
    (SELECT dbo_tbl_Restrictions1.Testfall_ID
    FROM dbo_tbl_Restrictions1
    WHERE dbo_tbl_Restrictions1.Auftrags_ID = 544)
AND dbo_tbl_ThisForm.Testfall_ID NOT IN
    (SELECT dbo_tbl_Restrictions1.Testfall_ID
    FROM dbo_tbl_Restrictions1
    WHERE dbo_tbl_Restrictions1.Auftrags_ID IN
        (SELECT Auftrag_ID
        FROM dbo_tbl_Restrictions2
        WHERE Auftragstyp = 9))
AND dbo_tbl_ThisForm.Programm_IDFS = 35

If you need more information feel free to ask.

Any help/suggestion is appreciated. Thanks in advance.

EDIT:

When running the query with strFilter=((Lookup_Programm__IDFS.Name="ad1incl")) I get the following error:

"Run-time error '3061'. Too few parameters. Expected 1."

I now just figured out, that the un-/checking also doesn't work for the field Funktion. This and the Programm_IDFS field are both foreign keys of data type int in the table dbo_tbl_ThisForm.

When filtering by the field Fachbereich it works to un-/check the checkboxes, since that field is of data type varchar, so strFilter is set to a valid value:

((dbo_tbl_ThisForm.Fachbereich="Steuern"))

Those foreign keys both link to separate tables. Now how can I solve this? Do I need to include those tables in my query? Can I change something on the form?

Thank You

kgander42 · Accepted Answer · 2014-04-10 16:08:12Z

1

Recently I ran in to a similar problem with an application that was upgraded to Access 2013 after running fine under Access 2003. I noticed that the form's combo box controls shared the exact same name as their source fields. Suspecting that name ambiguity might be confusing Access when it generates the filter, I renamed the controls (gave them a "cbo" prefix). That seemed to fix the problem for most cases.

Some users still see it happen at times, but then I haven't removed all the ambiguous names yet: I fixed only the ones that are being used for filtering. I plan to do that others in the next release.

It shouldn't hurt to give the control's different names from their data source fields, and I've always found it makes it easier for me to understand the applications.

answered Apr 10, 2014 at 16:08

kgander42

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Johnny Bones · Accepted Answer · 2014-03-13 14:36:12Z

0

Have you tried repositioning the filter, like:

UPDATE dbo_tbl_ThisForm
SET dbo_tbl_ThisForm.Checkbox = 0,
    dbo_tbl_ThisForm.Statusoffen = '0'
WHERE dbo_tbl_ThisForm.Testfall_ID NOT IN
    (SELECT dbo_tbl_Restrictions1.Testfall_ID
    FROM dbo_tbl_Restrictions1
    WHERE dbo_tbl_Restrictions1.Auftrags_ID = " & gsVariable5 & ")
AND " & strFilter & "
AND dbo_tbl_ThisForm.Testfall_ID NOT IN
    (SELECT dbo_tbl_Restrictions1.Testfall_ID
    FROM dbo_tbl_Restrictions1
    WHERE dbo_tbl_Restrictions1.Auftrags_ID IN
        (SELECT Auftrag_ID
        FROM dbo_tbl_Restrictions2
        WHERE Auftragstyp = " & Me.kfAuftragstyp & "))

Maybe it's having a problem with the nested query? Also, can you tell us exactly what the error your getting is? You might want to put a code break in just before the UPDATE statement and run it for something you know works, and check what Access thinks strFilter is, then do it again for the one you're having trouble with and compare the values fo strFilter. Make sure they're logical, and that the column they're restricting is spelled correctly and the value exists.

answered Mar 13, 2014 at 14:36

Johnny Bones

8,5027 gold badges61 silver badges127 bronze badges

8 Comments

Alex Over a year ago

Thanks already for your effort and thoughts. Repositioning the filter doesn't change anything. I added the error-message and the value of strFilter when it works - please take a look at the edit.

Johnny Bones Over a year ago

How about this. It requires an extra step, but set that whole Update statement equal to a variable. Like, Set strSQL = "UPDATE dbo_tbl_ThisForm...". Then, you can do a DoCmd.RunSQL (strSQL) to run the code. The benefit is, if you put a code break in there, you can see exactly what the SQL string is before it tries to run it. Make sure anything that should be an Integer is an Integer, anything that should be a Text is a Text, etc... You can also print the value of strSQL to the Immediate window, and then copy/paste that into a blank query and see if it tells you anything.

Alex Over a year ago

I'm sorry, but I forgot to mention in my question, that I have already tried this. The second query I posted is just like the query that VBA generates, just that the last line is AND ((Lookup_Programm__IDFS.Name="ad1incl")). If I run that in a blank query it just asks for the Parameter for Lookup_Programm_... and when I enter something it's like there's no restriction. The problem is the filter by form. It generates that strange Lookup_Progra...Name thingy, which I don't understand. There is no such field. Also the database is an external MSSQL-DB. But I don't think that's relevant.

Johnny Bones Over a year ago

Are you positive that Lookup_Programm__IDFS is the name of a table? I don't see it in the SELECT statement. If it's the name of the form, then it needs to be preceeded by Forms!. Or, you can replace it with Me. if it's the current form that's open.

Johnny Bones Over a year ago

Also, you haven't shown us any code detailing how strFilter is generated.

|

Alex · Accepted Answer · 2014-03-19 14:18:25Z

Since I couldn't get rid of the preceding Lookup_-thing I decided to deal with it in VBA.

I used following VBA-Code to change the part with Lookup_[field] to a corresponding SQL-query:

If InStr(strFilter, "Lookup_") <> 0 Then
    If InStr(strFilter, "Programm__IDFS") <> 0 Then
        strFilter = Replace(strFilter, "(Lookup_Programm__IDFS.Name", "dbo_tbl_ThisForm.Programm_IDFS = (SELECT ID_Programm FROM dbo_Programm WHERE Name")
    ElseIf InStr(strFilter, "Funktion") <> 0 Then
        strFilter = Replace(strFilter, "(Lookup_Funktion.Beschreibung", "dbo_tbl_ThisForm.TF_Funktion_IDFS = (SELECT TF_Funktion_ID FROM dbo_TF_Funktionen WHERE Beschreibung")
    End If
End If

So for example if I use a filter on the Programm_IDFS field it will change

((Lookup_Programm__IDFS.Name="ad1incl"))

to

(dbo_tbl_ThisForm.Programm_IDFS = (SELECT ID_Programm FROM dbo_Programm WHERE Name="ad1incl"))

This way it can update all the checkboxes. I didn't find a way how to remove the Lookup_-thing nor why it only affects certain combo boxes. The main thing is that it works now. :)

Collectives™ on Stack Overflow

Use the filter of a form in a SQL query via VBA

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related