Modules & VBA :: Calling A Function And Passing Variables
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 Replies
ADVERTISEMENT
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
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
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 4 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
Aug 10, 2013
I have 28 combo boxes on a form, which I want to insert data into the table as they are changed. Each one will pass the same sets of data just with different parameters which come from the form.
Rather than putting the same code to insert on each of the 28 combo boxes I thought it would be easier to create a function to do it and pass the parameters to it through a sub on the AfterUPdate event of the combo box.
I need to pass 4 parameters, if I only put 1 in there it works fine, but when I start putting more in it doesnt work and I get compile errors or syntax errors.
Sub routine:
Code:
Private Sub cboMonday1_AfterUpdate()
If Me.cboMonday1 = 1 Then
Me.cboMonday1.BackColor = vbGreen
Me.cboMonday1.ForeColor = vbBlack
[Code] .....
View 9 Replies
View Related
Dec 23, 2013
I have the following function that I found online. Unfortunately I can't remember where I got it since I've had it for a little while. Today, when I tried to actually put it to use it didn't work.
I'm calling it from a form as follows: CompactDB (tblHotword)
tblHotword is just a random table from the back end. My understanding of the function was that it would use that table to connect and get the file name of the back end.
Whenever I run it, Access pops up a window that says "Object required" and nothing else. It doesn't look like a standard error message popup. When I click 'OK', Access continues with the rest of the code as if nothing went wrong. The function doesn't run though.
Code:
Public Function CompactDB(TableName As String) As Boolean
On Error GoTo Err_CompactDB
Dim stFileName
DoCmd.Hourglass True
stFileName = db.TableDefs(TableName).Connect
[code]....
View 8 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
May 31, 2014
I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.
Here is the code:
Option Compare Database
Option Explicit
Public Function getCurrentRepDates() As Date
Dim dtBegDate As Date
dtBegDate = DLookup("BegDate", "tblCurrentRepDates")
[Code] ....
I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010
View 5 Replies
View Related
Jun 24, 2014
I am using an on-click event on my form to call up a report for the current record. It has been a while since I've used the form to call the report, but I always remember it working correctly. Today when I try to call up the report, it opens but no record is being pulled up so the report pulls up blank.
Code:
DoCmd.OpenReport "rptSupplierInformation", acViewPreview, , "[OperationID]=" & [cboMoveTo], acWindowNormal
View 1 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
Jun 7, 2012
I have created a module with a function that capitalizes the third letter in words that begin with "Mc". I have a table with the city field all uppercase letters. I created an update query that takes the field and correctly changes it. How you would go about tying this function to the textbox on the input form. So, if a user incorrectly enters "mccoy" in the City Field the function would be called and would automatically change it to "McCoy".
View 2 Replies
View Related
Oct 2, 2004
I haev a report with 15 sub reports. I have to use Visual Basic to format the sub reports. I know how to do the formatting (see below) but what I would like to do is have one function (Public) that I can pass the subReport name to and have the formatting happen. This would be mush easier to maintain than copying the code below into on_print events of all 15 sub reports.
Has anyone done this and have an example??
Thanks
--------------------------------------------------------------------------------------
Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
Dim widthOfBox, startLeftSide
startLeftSide = 0.017
widthOfBox = 0.21
Me.ScaleMode = 1
Me.ForeColor = 0
'Repeat the following line of code for each vertical line
' 1*1440 represents 1 inch
' Me.Line (0 * 1440, 0)-(0 * 1440, 14400) 'Draws line at Left Margin
Me.Line ((startLeftSide * 1440) + widthOfBox * 0 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 0 * 1440, 14400) 'Draws line at Left Margin
Me.Line ((startLeftSide * 1440) + widthOfBox * 1 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 1 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 2 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 2 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 3 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 3 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 4 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 4 * 1440, 14400) ' Draws next line
'the 14400 is an arbitrary number to increase the line to the max of a
'section.
End Sub
View 1 Replies
View Related
Jan 4, 2006
Is there a way to call a function stored in a module in the back-end access file from within a form in the front-end access file?
I want to be able to slip in a change to the code, and not have to then copy the changed module out to every front end file.
I am thinking that this is something that is in fact trivial to do, but for the life of me, I am drawing a blank on how to do it. Or even what search terms to use to search the help files.
Any help is appreciated.
Thanks,
David
View 3 Replies
View Related
Oct 5, 2005
My title may not accurately express my question. If you can suggest a better way to do this, please feel free to elaborate.
Within Access, I have an "Invoice Number" table, with a single field containing a number. I have a function that accesses the table, increments the number and returns it to the invoice number field.
I need a way to perform this same function to the same table from within WORD.
I appreciate your time and interest.
View 1 Replies
View Related
Dec 1, 2005
Hi,
In the query field i am putting: -
Consecutive Months: Query_Month_Consecutive([Report_2_group_data].[Availability], array([Monthly_availability_CT].[12],[Monthly_availability_CT].[11],[Monthly_availability_CT].[10],[Monthly_availability_CT].[9],[Monthly_availability_CT].[8],[Monthly_availability_CT].[7],[Monthly_availability_CT].[6],[Monthly_availability_CT].[5],[Monthly_availability_CT].[4],[Monthly_availability_CT].[3],[Monthly_availability_CT].[2],[Monthly_availability_CT].[1]))
Basicly the values of them fields are entered into the query, all fields exist and everything is ok.
Anyhow the function is not loaded, i have tested with a simple msgbox and it does not do it.
However when i simply do
Consecutive Months: Query_Month_Consecutive([Report_2_group_data].[Availability], Monthly_availability_CT].[12)
It works and the msg box appears?
I really need it to be an array for what im doing any help would be great
Thanks
k0r54
View 3 Replies
View Related
May 30, 2005
Dear All.
I have a visual basic function, placed in a module.
I need to call it from a form field, and pass a value to it and the function has to return a value.
I tried it but it doesn't work
the value I get on the field is " #Name? "
any help will be very appreciated.
Thanks!
CS.
View 8 Replies
View Related
Aug 1, 2005
I have written a simple module that sorts and compares two strings. Next I am trying to create an event procedure for a button that will send all the values from three selected colums to that function for comparison.
Below is function "Check" from the module
' sImp = importData
' sAns = answerData
' iOpt = option
Function Check(sImp As String, sAns As String, iOpt As Integer) As Integer
Dim lImp As Long 'Imported values (unsorted)
Dim lAns As Long 'Entered values (unsorted)
Dim lAcnt As Long
Dim lCcnt As Long
Dim sAnswer As String 'Correct
Dim iMini As Integer
Dim sImpSort As String
Dim sAnsSort As String
lImp = Len(sImp)
lAns = Len(sAns)
sImpSort = OrderString(sImp)
sAnsSort = OrderString(sAns)
For lAcnt = 1 To lImp
sAnswer = Mid(sImpSort, lAcnt, 1)
' option
Select Case iOpt
' all
Case 1
Check = 1
Exit Function
' or
Case 2
For lCcnt = 1 To lAns
If Mid(sAnsSort, lCcnt, 1) = sAnswer Then
Check = 1
Exit Function
End If
Next
' and
Case 3
If sImpSort = sAnsSort Then
Check = 1
Exit Function
End If
Case Else
End Select
Next
End Function
What would be the best way to send these values to the function? loop through a recordset?
(Note: Not sure how many records the DB will eventually store. Lets just say 10,000 for now)
Any suggestions would be greatly appreciated..
Thanks
salmonman
View 1 Replies
View Related
Feb 24, 2005
Hey long time browser, first time poster!
HELP!!!
Ok, here is the problem that I can't seem to find any posts on here talking about. I have a form, let's call it FormA and a subform (subformA). I am using a non-activeX calendar control, which I did not write. I was informed it could be used on subforms but its not working. In reviewing the code it is looking for the activeform name to be passed to it. This works when the calendar function is called in the FormA, but when it is called in SubformA, it can identify the field that I want the date selected to be entered into, but it does not recognize the subform as being the active form.
The code it's using to determine activeform name is:
Screen.activeform.name
From my research I have found that when the subform has the focus, the activeform.name returns the name of the parent form.
The question: How can I determine the subform name and pass it as a variable to the calendar program?
Mark
View 1 Replies
View Related
Jan 16, 2006
I have built a function which passes values into the criteria of a saved query. It has worked very well until I recently built a query builder based on this same concept. The user can select from a list of operators "<=", ">=", ">", "<", "=".
Example, the function will pass into the criteria the following:
<150
Unfortunately, I have exhausted all avenues for passing the "<" less than or greater than signs into the criteria. The Access SQL forces an equals sign before the passed in criteria and thus treats the "<150" as the entire criteria.
I have overcome no criteria by defaulting the function value to pass in the wildcard "*" and putting "Like" in the criteria.
Has anyone every handled a "variable" operator in a saved query in Access? :confused:
I am aware that I can change the QueryDef, use temp tables, and do several other longer methods, but the success of this concept will reduce an large amount of coding effort as well as make the software much easier to transfer knowledge on.
This overall method is really a great way to reuse criteria based on a form without hard coding.
Thank you in advance for your help,
Dave
View 3 Replies
View Related
Jan 28, 2014
I have a function in a module that looks like this (it takes 2 dates as arguments):
Code:
Function get_KPIScanAgeRange(in_ScanDate As Date, KPIDate As Date) As String
Dim ret As String
ret = "Invalid"
' return value, by default is because age is negative number
[Code] .....
I'm trying to pass the date in a text box which exists in a form called "d3FormAging". The text box name is "KPIDate". The query looks like this:
Code:
SELECT Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]) AS KPIScanAgeRange, Count(Query_d3_Open.[Scan date]) AS Scans
FROM Query_d3_Open
GROUP BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate])
ORDER BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]);
For some reason Access doesn't recognize the "[Forms]![d3FormAging]![KPIDate]" when I pass it to the function. I get run-time error 3070 with information that Access can't recognize this expression even though the code should be correct as it's made with the expression builder and I'm sure the form that contains the text box is open when the query runs.
View 2 Replies
View Related
Jan 14, 2015
Imagine I have two subrountines:
Code:
Private Sub DoSomething1()
[Do Something]
End Sub
Private Sub DoSomething2()
[Do Something]
End Sub
And this Declaration:
Code:
Dim str As String
str = 1
Is it possible to refer to/call one of these subroutines using something like this:
Code:
("DoSomething" & str)
View 1 Replies
View Related
