Modules & VBA :: Passing Variables And Retrieving Results?
Jul 9, 2014
I wrote this module
Option Compare Database
Option Explicit
Public Function OdometerInput(varodometer As Variant) As Long
Dim varKilometres As Variant
varKilometres = varodometer * 1.609344
OdometerInput = CLng(varKilometres)
End Function
It works fine in the immediate window (although I haven't just fathomed what to do with null values and such) But my question which I am sure will be 'easy when you know' is how do I pass the variable to it from a text box on a form and retrieve the data in another text box on a form.
View Replies
ADVERTISEMENT
Jan 13, 2014
I have a public sub routine which requires parameters to be passed to it when I call it from an access form. When I try to enter the code to call the sub I get a compile error. I've also tried calling it from another sub in the same module but get the same compile error - see below.
Code:
Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String)
'code to use variables passed in
End Sub
[Code]....
View 4 Replies
View Related
May 29, 2015
I have never tried passing variables while calling a function so I don't know what the heck I'm doing. I'll give a simplified example of what I'm trying to do. The second variable vRank is reporting properly but the first one vID gets "stuck" on whatever the first item in the listbox is.
Code:
Dim vrt As Variant
Dim upSQL As String
For vrt = 0 To Me.List1.ListCount - 1
If Me.List1.Selected(vrt) = True Then
Call ChangeUp(Me.List1.Column(0, vrt), Me.List1.Column(1, vrt))
[Code] ....
View 3 Replies
View Related
Feb 25, 2014
My problem is as follows, i have created a report that calculates the total volume of FSC Materials. The user picks two dates from Calender controls that the report will range from. However the needs have now changed and i am required to make the report filter further based on user input, the problem i'm facing is that i cannot figure out a way to pass values from different variables to the report separate from another here is the code i would usually use to pass data to a query/report:
Code:
Private Sub MonthlyFSC_Click()
Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()
[Code] ....
However i am now trying to do this, but it gives me an error as it is trying to pass the values to one field:
Code:
Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()
sql1 = "[ORDER DATE]<#" & Format(Finish, "MM/DD/YY") & "#"
sql2 = "[ORDER DATE]>#" & Format(Start, "MM/DD/YY") & "#"
[code]....
It is performing incorrectly within the case select and passing the wrong criteria, as it will only display results that meet the default values' criteria. However the date criteria is not be passed either.
View 4 Replies
View Related
Sep 29, 2014
Access 2010 vba - I'm trying to pass a start date and end date to a date field in a make table query, and use the 'between' operator on that date field.
So I have a criteria on the date field like this "Between [dtStart] and [dtEnd]" and if I run the query manually it asks for 2 values and then works fine.
Here's the code I'm trying to run:-
Set qdef = db.QueryDefs("qryTest")
qdef.Parameters("dtStart") = StartDate1
qdef.Parameters("dtEnd") = EndDate1
Set rs1 = qdef.OpenRecordset(dbOpenDynaset, dbSeeChanges)
and I get the error "3219 Invalid Operation" on the last line.
View 2 Replies
View Related
Apr 11, 2006
Hi, I am writing a script which will retrieve all of the tracks relating to whichever cd the user has chosen. The script is being written in asp and the line which sends the variable to access looks a bit like this:
sqlQuery3 = "up_getAlbumTrackInfo " & productID
Set rs3 = dbConn.Execute(sqlQuery3)
If possible could you tell me how to retrieve this value from access as I have become lost.
At present the SQL code in the query looks like this
SELECT tblTracks.trackName, tblTracks.TrackNumber FROM tblTracks
WHERE tblTracks.productID = (** variable would go here **)
Many Thanks
Tim
View 1 Replies
View Related
Nov 20, 2007
I'm into one of the subforms that will be using the public variable from the main form and am not having success passing the data from the main form over. on the subform, i created a textbox (txt_currentyear) w/ this in the control source field: =[WrkYear] & " Golf Outing"
WrkYear was the defined in a module as:
**********
Option Compare Database
Option Explicit
Public WrkYear As String
**********
I have created a listbox w/ a few years in it (current_year_listbox). this has =[WrkYear]=Me.Current_Year_listbox.Value in the after update field. i loaded the main form, selected the year, went into the subform to see if it passed the data along w/ no luck.
do i need to call or reference that module in every form or report before i can use the data from it? right now, i just get an empty field on the subform.
View 5 Replies
View Related
Dec 8, 2005
I can't seem to get the following code to work.
Any help would be appreciated:
Public Function NonConform(strProduct, strBatch As String)
On Error GoTo HandleErr
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmProductNonConforming"
stLinkCriteria = "[ProductName]=" & "'" & strProduct & "'"
stLinkCriteria = stLinkCriteria & "AND [BatchNum]=" & "'" & strBatch & "'"
DoCmd.OpenForm stDocName, , , (stLinkCriteria)
HandleErr:
MsgBox "Error in NonConform Function : " & Err.Description
Resume Next
End Function
View 1 Replies
View Related
Sep 28, 2006
Before I go any further w/this current application, I want to make sure this will work.
I have an access database w/a few forms that will sit on a shared drive on a network. Each user will also have their own username and password because each user will have a certain level of access to what they are allowed to see. The problem I am facing is that if I put the users ID into the global variables module, and two users log on at the same time, then access seems to somehow use both of the IDs when running queries. This makes sense that all uses can see it, since it's Global... but I need a way for simultaneous users to have a persistant unique id so I can query data that's only meant for them. I found this example of code that might remedy the situation by passing variables between the forms.
Call the code below in frmOne to pass the variable.
DoCmd.OpenForm "frmTwo", acNormal, , , , acWindowNormal, "Count=2"
Call the code below in frmTwo to get the variable.
Dim i as Integer
i = CInt(GetTagFromArg(Me.OpenArgs, "Count" ))
Will I end up w/the same result? Will all users be able to see this variable as well?
View 6 Replies
View Related
Jul 16, 2015
I've got an access database that I am working on, but I have now ran into a problem where I can not figure out the correct VBA syntax to use. As a sidenote - I am using Access 2003. What I would like to do is find out the syntax to use to retrieve data from a query result.
I have a query, that when ran, searches a table that contains 4 columns. The query prompts the user to enter a number which would be found in column 1. It then searches for every match of that number that was entered, and only returns a result if there is an occurrence where column 4 is empty on the same row. The max number of occurrences where column 4 can be empty is 1. So to summarize, when I run the query, it either returns a blank record (with a 0 in the first 2 columns and last 2 columns blank) or it will return a record that matches the criteria.
What I would like to do is if no records are found, I need to go to the Time_IN form. If 1 result is found, I need to go to the Time_OUT form. I am unsure of the syntax to use and my IF statement fails every time, reverting to the else statement. Here is a copy of the current code I am using (The Me.MO_ID = Null was my attempt at retrieving the results from the query):
Code:
Option Compare Database
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
DoCmd.OpenQuery "Open MO Evaluation Query", acViewNormal, acEdit
[Code] ....
View 14 Replies
View Related
Sep 27, 2013
I got a database that has a multitab control. In the first tab there is the data and second one is a search tool. It contains a field where to type the text to be searched and a listbox where the results should show up.So far I only managed to create the query and link it to the field (textbox) where the user puts the search word. I just have no clue how to display the results on the listbox once the text is entered and the button pressed.
View 8 Replies
View Related
Nov 14, 2013
I am working on a database that contains patient demographic information. I have a form that prompts the user to enter either the medical record number or part of the patient name. Once you click search it'll then display a 2nd form with a list box outputting the results. Then from there the user can click on one of the entries in the listbox and it'll display the full demographic information on a 3rd form.
Now my problem is on the search part, it completely ignores if I have a medical record number entered. It continues to search by name only.
In my query for med rec # I have
[Forms]![frmSearch]![txtHistn]
where txtHistn is the text box field passing into the query. And for patient name column in the query under OR I have the following:
Like "*" & [forms]![frmSearch]![txtPname] & "*"
So I am confused why it ignores the medical record number entirely.
I have a test database attached. This is just a sample with dummy data entered and not designed pretty. Through this up for another issue I had yesterday that has been resolved but now discovered this query one. The frmSearch is how it begins. If you search by Smith it'll bring up the two Smiths I have entered. If I leave the field blank and enter 1 for the medical record number it treats it as null and displays everything. How can I fix this?
And while on the topic of query, the true database I am working with resides on an Power I series (formerly AS400s) and only linking to their tables. The data is entered in all Caps in the tables. How can I force whatever the user enters into the search screen that it will automatically uppercase the letters before performing the search? Without having the user to remember to enter with their cap locks on.
View 14 Replies
View Related
Mar 11, 2015
I'm trying to retrieve the value of a record in a table. Why doesn't this work:
tabName = "Plan Data"
Dim db As DAO.Database, rst As Recordset
Dim strQryWhole As String
Dim dbStatus As Integer, rcnt As Long
[code]....
View 5 Replies
View Related
Jan 23, 2014
I am new to access programming.Is it possible to store a variable in one form and then retrieve it to auto fill a field on another form .
I know hot to Dim and store on the same form but trying to retrieve from another form i cant do .
I presume the second form would be something like this OnLoad Variable=forms"customers"customersFK...
View 2 Replies
View Related
Feb 23, 2015
why the below VBA code is not retrieving the last record in a table.
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Table_Check_Number_List")
[code]....
View 3 Replies
View Related
Mar 11, 2014
I have an Access Database that I will be using on a desktop. I have a table in this database that mirrors the structure of a table on a remote server in a SQL database. I have successfully created a vba function within the Access database that uses a server-side php subroutine to select records (I usually won't know how many) from the SQL database and return them to the access database. The code I use in the access vba subroutine to access the php subroutine is:
Code:
With CreateObject("Shell.Application").Windows
Set ieWindow = CreateObject("InternetExplorer.Application")
ieWindow.Visible = False
apiShowWindow ieWindow.hwnd, SW_MAXIMIZE
ieWindow.Navigate "Web address for server-side php file"
End With
The last command in my php subroutine is "return $retrievedData." $retrievedData is a multidimensional array containing data from 42 fields in multiple records (again, I usually won't know how many). I've checked the data in php so I know it has been stored correctly.how do I access the returned data within my access vba subroutine?
I should add that my overall reason for doing it this way is that I want to maintain my server-side database as an untouched master. Users can only add data to it. My client-side database is used to update the input table and further process the data. The subroutines described above are intended to retrieve "new" records only (i.e., records posted since the last access database update) from the server-side database and transfer them to the access database for further processing.
View 3 Replies
View Related
Sep 20, 2013
Continuing with production of my database I've come across another wall that I'm trying to pass. My aim is when the user press the "Quit" button it will export everything to a file which is stored on Google Drive as google drive is installed on the laptops that will be using this database.
However the problem is Google drive is stored in the computer user files i.e C:UsersstudentGoogle Drive - is there a way to retrieve the name of the user that is logged into windows?
Code:
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal IpBuffer As String, nSize As Long) As Long
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Function ThisUserName() As String
Dim LngBufLen As Long
Dim strUser As String
[code]....
However cant get this to work - I think that is probably because i'm declaring this code in the wrong place - I've tried declaring the private functions in a class module and the functions in a module - however no success - How do you set this code up? Or is there a new way to do this?
View 14 Replies
View Related
Jan 30, 2014
Front end Access 2010, back-end SQL-server 2008 R2.
Normally I retrieve a certain value by Dlookup("myvalue", "mytable",...)
or
strSQL = "SELECT myvalue FROM mytable...;"
Set rs = CurrentDb.OpenRecordset(strSQL, 4)
But is there any faster way to retrieve a single value from an SQL-server table, beside doing doing the select by a stored procedure running through a pass through query, then open a recordset
Set rs = CurrentDb.OpenRecordset("mypassthroughquery")
just to retrieve ONE value?
I could not find something like DLookup("...) for an SQL-Server or in T-SQL.
View 3 Replies
View Related
Feb 3, 2014
Vbscript/vb macro code for retrieving the last modified date of a file uploaded in a URL.
I am able to get the file but wanted to get the last modified date of it.
View 5 Replies
View Related
Jul 11, 2015
Im a relative novice with access VBA, and I'm really struggling with using Dcount with date variable. All I want to do is count if a certain date appears in a table. Here is the extract from my code:
Dim WorkoutDate as Date
Dim datecount as integer
datecount = DCount("[WorkOut Date]", "tblworkoutlogs", "[workout date]= " & WorkoutDate)
Whatever dates are in tblworkoutlogs, datecount is still = 0...I've trawled the net and tried many variations of the code but no success!if I change all the date formats to strings in the code and the tables, it works so I know i'm looking in the correct place.
View 5 Replies
View Related
Jan 30, 2015
I am trying to set up some template emails using text someone has entered in a form with a variable indicated with a key word in brackets aka. [ChangeID] or [ChangeDate]. The field on the form is formatted as Rich Text so I am getting http code. (No problems yet) In the form the template is required, I lookup the template required and I get the string. I replace the brackets with the following
Code:
strleftB = Chr(34) & " & me."
strRightB = " & " & Chr(34)
strTemplate = Replace(strTemplate, "[", strleftB)
I then get a string but in need to convert part of the string into variables, before I capture the correct output for my email
Code:
"<div>RFC Submission: <strong>" & me.ChangeID & ", </strong> " & me.Details & "</div>"
My question is: what is the best way to split the string into strings and variables
View 7 Replies
View Related
Jul 15, 2015
Sometimes we need a one-line function to just get the database path or things like that we cannot do on a query or on a Macro Object.
Like:
Function GetDatabasePath() = currentproject.path
possible?
View 5 Replies
View Related
Sep 11, 2014
How can I assign some type of index to the below variables (data representation) ? I'm using VBA to build a .RFT file for MS Word.
"Line # 1 data"
"Line #2 data"
"Line #3 data"
. . . .
"Line #7 data"
"Line #8 data"
"Line #9"
. . . .
"Line # 22 data"
"Line #23 data"
"Line #24 data"
View 14 Replies
View Related
Oct 9, 2013
I need to set some 'global' variables with default values when my Access 2007 database is loaded. Depending on the user etc these values may be modifed after Access starts but defaults need to be set.
I declared the variables as 'Public' in a Module, then put a function in the same Module (to set the default values) then tried to call the function from an "AutoExec" macro, so it's the first thing that runs when Access loads.The macro throws an error - it can't 'find' the function(?)
View 2 Replies
View Related
Apr 10, 2014
What is the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.
Code:
Dim thisTbl as String
If Answer = vbYes Then
If MedicationInvNo2 <> "" Then
thisTbl = "tblMyMedData"
Else
thisTbl = "tblMedData"
[code]....
I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?
View 3 Replies
View Related
Oct 29, 2014
I have been tasked to create a multi-keyword search form, however, my form isn't working right and only the first record of the table is opened.
Code:
Public Sub txtSearch_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant
Dim i As Integer
Dim IngLen As Long
[Code] .....
These are the codes that I am using for my search form. I have a feeling that I am not calling the variable from the after update portion the right way.
View 14 Replies
View Related
