UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Queries From Vba Do Not Execute The Same As From Ui, Access 2016    
 
   
whatacutup
post May 28 2020, 08:02 PM
Post#1



Posts: 226
Joined: 25-May 05



Hello all,

I hope you are keeping safe and do not have the covid-19 blues.

I have a problem with some VBA code. Well, I think it is in the VBA and not the query.

I have a query which pulls up a bunch of records from a table:

SELECT tbl_AdjustableForms.ControlName
FROM tbl_AdjustableForms
WHERE (((tbl_AdjustableForms.ControlName) Like "cbo*") AND ((tbl_AdjustableForms.FormName)="frm_Items"));



When I run the query from the usual user interface, it runs just fine, as expected. When I run this query from VBA code, I use an ADODB.Recordset. When I run it with the query SQL embedded in the code or by invoking a query that has the same code, it comes up with an empty recordset.

So, the query is good but the recordset does not return any rows.

Any idea why a query would work when run from the navigation pane and not work when invoked via VBA?

Thanks,
-Steph
Go to the top of the page
 
RJD
post May 28 2020, 08:39 PM
Post#2


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hi Steph: Could we see your VBA code? Maybe we can spot what is causing the issue. Often it is a quotes issue, but we'd need to see how you are coding this to tell.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
whatacutup
post May 29 2020, 09:36 AM
Post#3



Posts: 226
Joined: 25-May 05



I ended up using just one part of my WHERE clause... I had two conditions in it and it could test either one but not both. Very strange. It is not a large number of records so I test for the first condition in the query to build the recordset and then test the second condition in the processing loop. Thanks for offering.
Go to the top of the page
 
RJD
post May 29 2020, 09:51 AM
Post#4


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
cheekybuddha
post May 29 2020, 10:27 AM
Post#5


UtterAccess Moderator
Posts: 12,997
Joined: 6-December 03
From: Telegraph Hill


>> I use an ADODB.Recordset <<

>> WHERE (((tbl_AdjustableForms.ControlName) Like "cbo*") <<

The wildcard character in an ADODB rs is '%' instead of '*'

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
whatacutup
post May 29 2020, 10:54 AM
Post#6



Posts: 226
Joined: 25-May 05



Wow! All this time I never learned that. Using the % did the trick. Thanks!
Go to the top of the page
 
cheekybuddha
post May 29 2020, 12:06 PM
Post#7


UtterAccess Moderator
Posts: 12,997
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

It's rare to see ADODB used much these days, so the knowledge is not shared so often.

Good luck with your project!

--------------------


Regards,

David Marten
Go to the top of the page
 
whatacutup
post May 29 2020, 12:53 PM
Post#8



Posts: 226
Joined: 25-May 05



Thanks for your help. This project is huge. It includes about 800K records and a whole lot of hardcoded rules from another system that need to be generalized and modifiable by the users. I am always haooy when I can learn something new... I guess I can stop work now since I learned something new today. :-)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 01:59 PM