473,327 Members | 1,919 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Can you spec code in an SQL statement or query?

JodiPhillips
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
5 1436
Rabbit
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
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

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

Similar topics

7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
5
by: Amit | last post by:
Greetings all, I am writing some code somehwat similar to the test code I have below. I am having a variety of issues with template specialization. I am not sure if this is related to something...
7
by: mark | last post by:
Access 2000: I creating a report that has a record source built by the user who selects the WHERE values. An example is: SELECT * FROM CHARGELOG WHERE STDATE Between #10/27/2003# And...
3
by: Miya | last post by:
I am building a calendar to enter and display training courses. I have a table with the fields CourseID, TrainingDate, TrainingStartTime and TrainingEndTime. Some courses are one day while others...
3
by: Andy_Khosravi | last post by:
I have been trying to build a user friendly search engine for a small database I have created. I'm having some particular problems with one of my date fields. Here's the setup: I'm using...
2
by: Dixie | last post by:
Hi, I am tyring to write some generic code that will send the source SQL for a mailmerge to a Word template. I am trying to use DLookup to insert the query name that is the record source for the...
61
by: John.L.Henning | last post by:
The new CPU benchmark from the Standard Performance Evaluation Corporation is announced http://www.spec.org/cpu2006/ Readers of comp.arch and comp.benchmarks may recall that I posted a call...
2
by: Peter Michaux | last post by:
Hi, I am reading the ECMAScript specs trying to figure out if the next line is a legal statement or not new Foo(); I think the above code may only be legal as an expression and not as a...
2
by: chromis | last post by:
Hi there, I've been reading an OOP book recently and it gives some nice Adaptor / Template patttern code to wrap around the php Mysql functions. I thought that I'd try and create a Simple Address...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.