By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,642 Members | 1,098 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,642 IT Pros & Developers. It's quick & easy.

Can you spec code in an SQL statement or query?

JodiPhillips
P: 26
Hello again,

I'm having a lot of trouble understanding the interaction between code and SQL. I have several queries that drive various reports and forms in my db and each query contains a common parameter, that of the persons User ID. At the moment my users log in via a log in page which refers back to the logIn table where the id and password are stored. This part works fine. The User Id is captured via a cbo called cboUserId.

However, beyond the log in splash, the user has to enter their User ID at just about every form and report and this is driving me nuts let alone the users!

Is there a way to specify the value of my cboUserId as the criteria for these queries instead of having the user enter their ID a gazillion times? I've looked through all the help info I can find within Access, and everything I can find on SQL in those helpfiles.

Do I have to write more code to allow the query language to accept my cboUserId variable? Does that question make sense?

Whilst I'm loving the learning that this is bringing, I'm getting frustrated because my understanding is still very limited and is happening a lot slower than I would like. Every little bit of help or guidance anyone can give me is greatly appreciated.

Jodi
Jun 7 '07 #1
Share this Question
Share on Google+
5 Replies

Rabbit
Expert Mod 10K+
P: 12,441
Create a global variable to store the id and create 2 public functions to set and retrieve the value. All of this goes into a standard module.
Jun 7 '07 #2

JodiPhillips
P: 26
Hi Rabbit thanks so much for your help.

Do I create the global variable by just 'dim variable_name as string, int etc' in a public sub?

For the public functions would this be querydef and recordset? Gah I'm hopeless.

I'm probably thinking of this from the wrong perspective, by wanting to just enter the variable name in each query criteria box. So if I create the public functions, the code (via the functions) will supply the value to the query each time the query is run, is that right? Am I on the right track with my thinking now?
Jun 7 '07 #3

Rabbit
Expert Mod 10K+
P: 12,441
Hi Rabbit thanks so much for your help.

Do I create the global variable by just 'dim variable_name as string, int etc' in a public sub?

For the public functions would this be querydef and recordset? Gah I'm hopeless.

I'm probably thinking of this from the wrong perspective, by wanting to just enter the variable name in each query criteria box. So if I create the public functions, the code (via the functions) will supply the value to the query each time the query is run, is that right? Am I on the right track with my thinking now?
Here's a quick example you can work from.

Some standard, non-form module.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Public LoggedInUser As String
  4.  
  5. Public Function GetLoggedInUser() As String
  6.    GetLoggedInUser = LoggedInUser
  7. End Function
  8.  
  9. Public Function SetLoggedInUser(sUserID As String)
  10.    LoggedInUser = sUserID
  11. End Function
  12.  
Log-in Form.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub but_LogIn_Click()
  5.    ' Check User ID and Password
  6.    Call SetLoggedInUser(Me.txtUserID)
  7. End Sub
  8.  
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Table1
  3. WHERE UserID = GetLoggedInUser();
  4.  
Jun 7 '07 #4

JodiPhillips
P: 26
Awesome Rabbit!!

Thank you bunches!

Actually, the penny has dropped finally about functions, once you posted your solution, I really did look at it and what it was doing and YAY, I understand now!!

Hehe, it also prompted me to look at some other things - yeah I went off on a tangent, but that in itself was great. LOL now I gotta go find my last post which has a very "stupid" and "ignorant" question in it, that has probably caused more than a few raucous chuckles and many eyes to roll!!

Thanks again Rabbit! /hugs
Jun 12 '07 #5

Rabbit
Expert Mod 10K+
P: 12,441
Awesome Rabbit!!

Thank you bunches!

Actually, the penny has dropped finally about functions, once you posted your solution, I really did look at it and what it was doing and YAY, I understand now!!

Hehe, it also prompted me to look at some other things - yeah I went off on a tangent, but that in itself was great. LOL now I gotta go find my last post which has a very "stupid" and "ignorant" question in it, that has probably caused more than a few raucous chuckles and many eyes to roll!!

Thanks again Rabbit! /hugs
Not a problem, good luck. No worries, we all started there.
Jun 13 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.