David-
So, simply referencing [OpenArgs] embedded using concatenation in the SQL string works? Not surprised. OpenArgs is a property of the Form, and any control on a Form can access any exposed property. Your lngLocationID doesn't work because its scope is only within your code. It *might* have worked if you had put the declaration of that variable in the module section (right after the Option statements and before the first procedure) and declared it Public. Any variable you declare as Public automatically becomes a property of the Form and can be referenced from anywhere else as long as the form is open.
Yes, the query engine - or more correctly the database engine - runs independently of Access. That's why you can open an Access table from other products such as Excel simply by loading the Access database engine. The engine doesn't really care who is calling it, but it has no visibility of any objects in the caller - not variables in VBA or controls on forms or cells in a spreadsheet.
John Viescas, author
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Apr 19, 2017, at 15:30, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I should have done this before I replied, but I just tried using OpenArgs and it works easily, just using the brackets as [OpenArgs].
I tried multiple ways to use the variable lngLocationID and without any success. Always empty query results.
---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :
John, the code for the variable was quite simple, and I used Debug.Print to verify that the ID was properly loaded into the variable.
In the Open_Event of the called form I put:
Dim lngLocationID as Long
lngLocationID = Forms!frmConfigureSampleGroupsAndUsers!cboSelectSite.column(1)
DeBug.Print "LocationID when frmSampleGroupsAndUsers opens is: " & lngLocationID
And then in the combo box query design grid I merely put lngLocationID in the criteria row. Access immediately placed lngLocationID in quotes, treating it as string criteria, so I tried using the equal sign ahead of the variable as =lngLocationID and I tried brackets as in [lngLocationID]. Did I need to identify the form as in your #1 reply?
And if I passed the LocationID value to the called form as OpenArgs, how would I type it into the criteria row? Just in brackets or would still need to identify the form?
BTW, your answer to #1 that the SQL engine does not recognize Me, tells me that I am missing some very fundamental understanding of Access. The "SQL engine" is a separate entity from the "Access engine", and does not operate with the same rules?
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
1) Unless you embed the value for Me.txtLocationID in SQL generated in code, you have to use the full Forms! Reference. The SQL engine has no clue what Me is.
2) I would have to see your code to explain why using a variable didn't work.
3) Yes, but see the answer to question 2.
John Viescas, author
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Apr 19, 2017, at 00:00, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
OK, so I did this and it works but it leads me to a couple of questions for the purpose of education. I made a text box on the form frmSampleGroupsAndUsers (this is the "called" form), and named it txtLocationID and assigned it the value Forms!frmConfigureSampleGroupsAndUsers!cboSelectSite.column(1). For now I have left the control visible so I can see that it is working.
In the query criteria I first tried Me.txtLocationID and I got an empty query result. I changed to "long Hand" and put Forms!frmSampleGroupsAndUsers.txtLocationID and it works fine. question 1 is why would Me.txtLocationID not work?
Question 2 is why would the text box method work, but creating a variable in the event code not work. I had already tried creating a variable lngLocationID, assigning the column(1) value to the variable, and using the variable in the query. No joy there.
Question 3 is, could I have passed the value I needed with OpenArgs and used OpenArgs as the query criteria?
So much to learn...
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar