Modules & VBA :: Calling Variables From Other Subs
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 Replies
ADVERTISEMENT
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
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
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 2, 2013
I have code for calling stored procedure with parameters,which is as follows
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long
IdValueToProcess = 221177 ' test data
Debug.Print (IdValueToProcess)
Set qdf = CurrentDb.CreateQueryDef("")
[Code] ....
And my stored procedure is
Code:
ALTER PROCEDURE [dbo].[spItemDesc]
@ItemNo varchar(200) ,
AS
BEGIN
set nocount on ;
select ProductDesc1,ProductDesc2 from ProductDatabase.dbo.tblProductInfo where ProductNumber = @ItemNo
END
The error comes on debug.print(rst!ProductDesc1)
And it says "Item not found in the collection"
View 2 Replies
View Related
Apr 20, 2015
I want to call the "On Current" event of another form.
I have a parent(mainform) form and then I have a subform(CurrentForm) and in that subform(CurrentForm) I have another subform(mysubtab01). From the mySubTab01, I want to call the On Current of the parent form. How can i do this?
This is an example of how I am setting the record source of the deepest subform where I want to call the parent on current from:
Forms(mainform)(CurrentForm)(mySubTab01).Form.Reco rdSource = "Select * from subQrytab03"
View 12 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
Nov 27, 2013
I am trying to call a stored procedure from access ,but it is giving me this runtime error :
Code:
2147217900
Syntax error or access voilation
I am doing this first time so i dont know about how to pass parameters (IN and OUT)..
My code is
Code:
Public Sub createDataToAnalyze()
Dim objConnection As New ADODB.Connection
Dim objCom As ADODB.Command
[Code].....
View 1 Replies
View Related
Feb 9, 2014
I have created a custom message box form that suits my needs for this particular situation. It is called (opened) in the middle of a bunch of other VBA procedures. What I am having a problem with is how do I "pause" the rest of the code from running after the message box call?
For example, with a regular msgbox() function, once called, the code will wait for the user input or click of a button, then continue. My problem is that I am using the basic docmd.openform procedure, and it opens the message box form just fine, but then continues on with the rest of the code after the form is opened.
How would I "pause" the code after the call of opening my custom message box form, then continue it after the user clicks one of my closing buttons from that form?
Furthermore, although its not entirely needed in this situation, for future reference, how would I have that message box form return a value to the calling procedure?
View 14 Replies
View Related
May 30, 2014
I've written a function which opens the Outlook GAL dialog, allows users to select recipients and passes back a 2-D array (recipients and type i.e. To, CC or BCC).Here is the code :
Code:
Public Function GetContactsFromOutlookGAL() As Variant
Dim appOutlook As Object ' Outlook Application
Dim objNameSpace As Object ' Outlook NameSpace
Dim objSelectNamesDialog As Object ' Outlook Select Names Dialog
[code]...
The only problem I have is that when I 'display' the SelectNamesDialog, it doesn't make it the active window and bring it to the front - it just opens the dialog in Outlook and you have to navigate to the main Outlook window to find it.I'd like for the dialog to 'popup' - ideally, without the main Outlook window dragging along behind
I believe it's possible (judging from this article) to achieve this using API's - specifically, a combination of FindWindow (to retrieve the window handle) and ShowWindowLong (to set it to a topmost window in front of all other windows).
View 12 Replies
View Related
Oct 4, 2005
Not been doing this too long, but have a question. Have a sub that determines which checkbox(s) are selected. I then want to get the value from that sub and use it in another sub that runs a query to populate listboxes.
Code so far
Public Sub TransportMode()
If Me.checkAir = True Then
strtypes = strtypes & "'Air',"
End If
If Me.checkCar = True Then
strtypes = strtypes & "'Car Hire',"
End If
If Me.checkAll = True Then
strtypes = "'Air','Car Hire',"
End If
strtype = Left(strtypes, Len(strtypes) - 1)
MsgBox strtype
End Sub
Public Sub FromToUpdate()
TransportMode
strSQL = "SELECT DISTINCT MasterTable.From FROM MasterTable WHERE MasterTable.Type IN (" & strtype & ")"
If Len(strbgs) > 0 Then
strSQL = strSQL & " AND MasterTable.BGID IN (" & strbgs & ")"
End If
MsgBox strSQL
How ever when get the message box, the value from first sub is not used by the second. How can I call it across? I'm trying to put bits of code like the first sub into many different sub as they are used again when buttons are pressed on the form etc.
cheers
Matt
View 1 Replies
View Related
Nov 13, 2006
I wish to in a subform where you can enter a image for a item.
How do you go about having a box you click, find on your HD the image and then have it display for that record you select?
View 4 Replies
View Related
Dec 2, 2014
So Access was unable to manage the amount of content that I needed to produce in a single report. The reports contain 12 charts/graphs all of which I needed on separate pages (one chart per page). As such I could not create large enough charts in access to fit to each page, as Access limits the amount of space one can use in a report.
I downloaded Microsoft Reports Builder 3.0 and built my reports as needed and can generate them as desired at current. I'm wondering if there a way to call that RDL file I created to generate my reports from a form control button in Access?
View 3 Replies
View Related
Oct 9, 2013
We have an ActiveX grid control, 10Tec iGrid, written in VB6. One of its method, Group, used to group rows, raises the AfterAutoGroupRowCreated event so the developer can adjust the look and contents of every group row created during the automatic process of grouping.
When our ActiveX grid is hosted on an MS Access form and we call the Group method while populating the grid in the form's Open event, the AfterAutoGroupRowCreated event isn't triggered. But this event definitely works as expected in other development environments, and even in MS Access if we call Group from the form's Load event.
Is it a well know issue of MS Access, when any events of ActiveX controls aren't triggered while "executing" the Open event
View 1 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 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
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
Apr 9, 2015
I have a Change Management database with several fields, the key fields that I need to talk to work together are as follows:
[Patch] , [CVE] & [Patch Approved]
When opening a form I have I can sort it by the patch number which will then sort out all other CVE's that are not associated with that patch. What I would like to be able to do is approve one patch under the [Patch Approved] field and have it carried through the rest of the filtered CVE's that have the same [Patch] number/ field.
View 1 Replies
View Related
Jan 27, 2005
The title of this thread was hard to nail!!
I have a 2 paged Form. frmCustomer (page1) and frmCustomerOrderHistorySub1 and frmCustomerOrderHistorySub2 (Page 2)
On customers form I have a command 'Customer Orders'. When clicked focus is set to 'Page 2'. Page 2 should (and does) display ALL orders placed by the Customer currently being viewed on Page 1.
Problem: frmCustomerOrderHistorySub2 ONLY displays the details for the first order?
If you select the second order then the order details do not update?
I hope the attcahed DB will explain all?
I have spent a good few hours on this. Happy I got most of it to work unaided, could do with a nudge on this final issue.
Thanks for any help in advance.
Phil.
View 2 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
Sep 30, 2013
I have a simple date stamp that works great in a private sub within a form. (error handling removed for clarity)
Code:
Private Sub btnDateStamp_Click()
' UserInit is global variable
Me!Notes.SetFocus
Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & UserInit & _
" -" & vbCrLf & Me!Notes
[code]...
I am rewriting it as Module function that is Called from various forms, to save space. The function receives the parameters varFormName and varControlName. I wish to write the results of the function back to a memo field on the form.I am stumped at the get go by the need to refer to the Forms controls with a full reference instead of the Me command.
Code:
Function DateStamp(varFormName As String, varMemoName As String)
'varInitials, varFormName, varMemoName are global variables
Forms!varFormName.Controls!varMemoName.SetFocus ' Error here
'Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & varInitials & _
[code]...
how to refer to the forms control's with their full reference, from within the Module's function, the rest will fall into place.
View 3 Replies
View Related
Jun 6, 2013
I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).
I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.
Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.
If Me.txtRole = "Agent" Then
DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())"
AgentFilter = "CASEOWNER ='" & Me.txtName & "'"
DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter
Exit Sub
End If
View 13 Replies
View Related
