UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Public Vba?, Access 2016    
 
   
jackjsmith88
post Jun 25 2019, 08:24 AM
Post#1



Posts: 45
Joined: 28-October 18



Hi All,

Just quickly, say i have a textbox control i enter the value "test" and i click a button

is there anyway to declare a public value? and by public i mean, usable across the whole database?

For example.

Form1
Textbox2.value = "Test"
BtnRun

Private Sub (btnRun_OnClick)
Dim textbox2.value as Public1
End Sub

Report1
Label1
Private Sub (Report1_OnLoad)
me.label12.caption = Public1

everything i just wrote, is hypothesis and not tested. hopefully from what ive written you can see what im trying to achieve. i'm trying to declare a string that can be pulled from anywhere within the database. i just wanted to know if it was:

A: Possible
B: best method?

Thanks in advance for the help
Go to the top of the page
 
Daniel_Stokley
post Jun 25 2019, 08:29 AM
Post#2



Posts: 312
Joined: 22-December 14
From: Grand Junction, CO, USA


Since you want this "usable across the whole database", how about a one row table? Maybe call it tbl_DBParameters. It could contain any number of columns but absolutely only one row. Your button could have VBA to update a value in one column in that table. From that point on, you could use DLookup to get the value in any query or in any piece of VBA code.

This post has been edited by Daniel_Stokley: Jun 25 2019, 08:40 AM
Go to the top of the page
 
jackjsmith88
post Jun 25 2019, 08:35 AM
Post#3



Posts: 45
Joined: 28-October 18



how would this work though considering that:

A: 2 parameters (start date) And (end date)
B: The paramters change based on user selection.

Go to the top of the page
 
nuclear_nick
post Jun 25 2019, 09:17 AM
Post#4



Posts: 1,759
Joined: 5-February 06
From: Ohio, USA


So... safe to assume that you're using the text boxes to filter a report, and you want the filter to show on the report?

This can be done without VBA, and I'll use the 'start date' and 'end date' scenario you provided.

First, I'll assume a table like this...
CODE
tblMyTable
-------------
RecordID
RecordName
RecordDate


Our first simple report query, bringing back all the records...
CODE
SELECT RecordID, RecordName, RecordDate FROM tblMyTable


Now, with date criteria, from 'frmMyForm', beginning and ending date text boxes...
CODE
SELECT RecordID, RecordName, RecordDate FROM tblMyTable WHERE RecordDate Between frmMyForm.txtStartDate And frmMyForm.txtEndDate


Let's save that last bit as 'qryDateReport'. We can now write two more queries to get the start date and end dates...
qryFirstReportDate...
CODE
SELECT Min(RecordDate) AS FirstDate FROM qryDateReport AS FirstReportDate

qryLastReportDate...
CODE
SELECT Max(RecordDate) AS LastDate FROM qryDateReport AS LastReportDate


Now in a query I'll call 'qrptDateReport', put all the queries together...
CODE
SELECT RecordID, RecordName, RecordDate, FirstDate, LastDate FROM qryDateReport, qryFirstReportDate, qryLastReportDate


When you run the query, you'll get 5 columns back... the last two will match for each record, and will be the first date and last date of all the records, which you can now put in your report header... "Contains all records for [FirstDate] to [LastDate]." (Or something like that...)

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
GroverParkGeorge
post Jun 25 2019, 09:18 AM
Post#5


UA Admin
Posts: 35,318
Joined: 20-June 02
From: Newcastle, WA


I prefer TempVars for this purpose.

When your user enters dates in the two controls on the form from which you launch your report, set one tempvar to the value of the "from date" and another tempvar to the value of the "to date".

You can then reference those tempvars anywhere you need them, i.e. in the criteria of a query, in the control source of controls on the report, etc.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
nuclear_nick
post Jun 25 2019, 09:21 AM
Post#6



Posts: 1,759
Joined: 5-February 06
From: Ohio, USA


GPG's way is good, too.

There are a few other ways as well. Depends on how many records, how big the database, expertise with VBA, expertise with SQL...

There isn't a 'best way', but one way you'll probably find pushback on (as in, 'No, don't do it that way') is a public variable.

smile.gif

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
BruceM
post Jun 25 2019, 09:47 AM
Post#7


UtterAccess VIP
Posts: 7,898
Joined: 24-May 10
From: Downeast Maine


I agree with George. With stable TempVars available I see no reason to use public VBA variables.

Go to the top of the page
 
jackjsmith88
post Jun 25 2019, 09:56 AM
Post#8



Posts: 45
Joined: 28-October 18



Hi George,

This sounds promising.

to a beginner, how would i use these functions please?

Go to the top of the page
 
theDBguy
post Jun 25 2019, 10:25 AM
Post#9


Access Wiki and Forums Moderator
Posts: 75,729
Joined: 19-June 07
From: SunnySandyEggo


Hi Jack. See if this Wiki article helps. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jun 25 2019, 10:27 AM
Post#10


UA Admin
Posts: 35,318
Joined: 20-June 02
From: Newcastle, WA


I suggest you invest some time reviewing MS documentation to get your feet wet.

Basically, as noted, you can use the AfterUpdate event of a control on a form to set a tempvar like this

CODE
Private Sub txtFromDate_AfterUpdate()

    TempVars.Add Name:="FromDate", Value:=Me.txtFromDate
    
End Sub


Once the TempVar value is set, you can use it in other places, such as a criteria in a query:

SQL
SELECT Field1, Field2, Field3
FROM tblYourNameNameGoesHere WHERE tblYourNameNameGoesHere.DateField Between TempVar!FromDate and TempVar!ToDate


Or, you could use it in the control source for a control on the report.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st July 2019 - 08:17 PM