Setting session constant based on query result

[Tskutnik]

There are results of a query that I wish to use as contants through my database and I'm not sure how to set this up.
Background: The database will support different users who will request different data. For example, user1 may request data from "Test Cycle A" which is from 6/30 - 7/31, and user2 may request data from "Test Cycle B" which is 9/30 - 10/31.

I already have it setup so each user can select the Test Cycles when they open the database, but what I need is to take the beginning and ending dates for the selected test cycle and save them as constants, so that I can plug those dates into some of the forms, queries, etc...

Assume the start and end dates can be captured in a query (Query1). If I can save those Query1 date results as constants I'd be set. I dont want to link Query1 to every other query in the database since once selected the dates are static for the session.

Again, each user will have a different start and end date for their session so the values cannot be stored in a table.

Thanks for the help.

 

[RainLover]

Again, each user will have a different start and end date for their session so the values cannot be stored in a table.

Why would that stop you from using a Table. After all a Table is used to store different Data. That is what it is for.

 

[Tskutnik]

Thanks for the reply.
How can I store a constant in a table when I have a split/shared database with many users accessing the same tables? If there is a single table with the selected date range then every user's session will be forced to use the selection in that table. I want each user to be able to select a different time period based on a list of available periods, more like a query.
I also dont want to setup specific user ID's. I only need to capture the start and end dates selected as a variable that I can use throughout the app.

 

[MSAccessRookie]

Thanks for the reply.
How can I store a constant in a table when I have a split/shared database with many users accessing the same tables? If there is a single table with the selected date range then every user's session will be forced to use the selection in that table. I want each user to be able to select a different time period based on a list of available periods, more like a query.
I also dont want to setup specific user ID's. I only need to capture the start and end dates selected as a variable that I can use throughout the app.

If I understand your question, One way to accomplish this would be to add an additional Field to your Table that uniquely identifies the user that entered the Record in the Table. Queries into the Table could use that Field as a Filter.

-- Rookie

 

[Tskutnik]

Defining SUers is one way to associate the values to a table, correct, but it is overkill for this app.

What I really need is the ability to assign the results of QueryX to global valiables. Specifically:

QueryX Returns [FromDate] and [ToDate]. What is the Public Function code to capture the current value of those two fields? It would have to also be dynamic so whatever the query value is at that moment is captured.

Something like (for the FromDate):

Public Function FromDateSelected () as Date
FromDateSelected = [QueryName].[FromDateFieldName]
End Function

I have no idea (obvisously) what the syntax is.

 

[nanscombe]

I would set up the two dates as Global variables, of variant type.

Global gvFromDate as variant, gvToDate as variant

I would then write a function, which takes the Cycle name as a parameter, to use the DLookup() function to get the dates I'm after.

Syntax: Result = DLookup(<fieldName>,<tableName>,[criteria])

Public function getCycleDates(byval theCycle as string)
gvFromDate = DLookup("fromDateFieldName","dataTableName", "yourCycleFieldName = '" & theCycle & "'"
 
gvToDate = DLookup("toDateFieldName","dataTableName", "yourCycleFieldName = '" & theCycle & "'"
End function

Alternatively, if the cycle dates were fixed you could simplify it by hard coding the values.

Public function getCycleDates(byval theCycle as string)
Select case lcase(theCycle)
case "cyclename1"
  gvFromDate = #cycleName1FromDate#
  gvToDate = #cycleName1ToDate#
 
case "cyclename2"
  gvFromDate = #cycleName2FromDate#
  gvToDate = #cycleName2ToDate#
 
...

case "cyclenamenn"
  gvFromDate = #cycleNameNNFromDate#
  gvToDate = #cycleNameNNToDate#
 
End Select
End function

Once I had captured the two values I would then write a couple of functions to allow me to pass them into queries as criteria.

Public function cycleDateFrom()
cycleDateFrom = gvFromDate
End function

Public function cycleDateTo()
cycleDateTo = gvToDate
End function

The functions cycleDateFrom() and cycleDateTo() can then be pasted into the criteria of any queries that you want.

 

[Tskutnik]

anscombe - This is exactly the right idea, thanks so much

Syntax: Result = DLookup(<fieldName>,<tableName>,[criteria])
Code:
Public function getCycleDates(byval theCycle as string)gvFromDate = DLookup("fromDateFieldName","dataTableName", "yourCycleFieldName = '" & theCycle & "'" gvToDate = DLookup("toDateFieldName","dataTableName", "yourCycleFieldName = '" & theCycle & "'"End function
Really sorry to be dense - but I am not a coder. I'm not sure what you mean by the Criteria or by your (byval the cycle as string). Sorry. Based on what you sent I'd write it like this.

Public Function FromDateSelection() as Date
FromDateSelection = Dlookup("Queryx","FromDateField")
End Function

Public Function ToDateSelection() as Date
ToDateSelection = Dlookup("Queryx","ToDateField")
End Function

If you could be so kind (again) to get me as much of the specifics as possible that I'd be greatful.
I aready have these variables "talking" to the other queries, so that part is done.

 

[nanscombe]

Syntax: Result = DLookup(<fieldName>,<tableName>,[criteria])

[criteria] being in square brackets means it's optional, but in your case you probably want to use it. It's like what you would put in a query to narrow the results.

Syntax: Result = DLookup("nameOfFieldYouWant","nameOfTableOrQuery","whereClauseYouWouldUseInA_Query")


If you wanted to get the value of fieldA from tableB but only where fieldC was equal to 'D' you would use..

Result = DLookup("fieldA","tableB","fieldC = 'D'")

 

[Tskutnik]

This is really great, thanks. Wil try it later but I'm clear so we can close this one.