Rabu, 19 April 2017

Re: [MS_AccessPros] combo box filter criteria

 

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
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: david.pratt@outlook.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

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