Modules & VBA :: Arguments - How To Pass From Private Sub To Function
Apr 22, 2014
I have a after update event that will match the written record with any exist record in a table in the field "OrgName". If it doesn't find exact match, will call a function with a "soundex" algorithm to see if there is only a misspelling or another name altogether.
In the afterupdate event, I have a string called strOrg (wich is the name I want to compare).
I have the soundex function in a module, so I can use it for several form generally.
What I want is to pass the strOrg to the soundex function, however I don't know how to declare the variables. however I keep having this error:
"compile error: Argument not optional"
and goes to the line tagged as 1 in the private sub afterupdate
The afterupdate sub is the following:
Code:
Private Sub tOrgName_AfterUpdate()
Dim strOrg As String
strOrg = Me.tOrgName.value
If IsNull(DLookup("orgID", "torg", "OrgName = '" & strOrg & "'")) Then
resMsg = MsgBox("This organization name is not in the list. If you want to look for similar names press YES, if you want to register a new organization press NO.", vbYesNoCancel, "Organization not found")
[Code] ....
and the soundex function is declared as
Code:
Public Function Soundex(strOrg As String) As String
Dim Result As String, c As String * 1
Dim Location As Integer
I want to be able to pass arguments to an access file on start up. I want to be able to grab this value and then perform an action based on this.
Background: The program will send an email with details for a change request, the recipient will then need to click on a link to accept or reject this change. So I want them to be able to click the link which will trigger the program to save accept or reject.
Code: Private Sub SetCheck212() If Me.[Check161] And Me.[Check169] And _ Me.[Check167] And Me.[Check181] And _ Me.[Check261] And Me.[Check189] And _ Me.[Check187] And Me.[Check195] And _ Me.[Check203] And Me.[Check201] Then Me.Check212 = True
[Code] .....
Some of the checkboxes are locked as I want to force the user to check them on another form (InvoicedF) but I want them to be displayed PlotF as well.
So I want to call the Private Sub from another form. So that the Check212 still automatically checks to true with out PlotF open on the screen.
Is this possible, Do I have to make this private sub a public sub? If so how do I do this?
I've got a private sub that runs when the form loads. I want it to get the id of the record which has opened this new form.
The public sub populates the variable but it's Empty going back into my private sub. I'm sure its a simple school boy error but it has me stumped!
I want it public as I need to keep that id as new records are added in the form.
Private Sub Form_Load() FRStudentFundingRequestID 'fundingrequest = FundinRequestID Me.txtFKFReq = FundingRequest End Sub __________________________________ Public Sub FRStudentFundingRequestID() FundingRequest = [Forms]![frmFundingRequest]![txtpk] End Sub
I have a python script "runAll" that takes two arguments: processID (the primary key of one table as a string) and a filename (a full file name path as a string).
Is it possible to run a python method with arguments from inside a VBA script (activated by a button press)?
My python script is called XMLGenerator.py, and the method I want to run is called runAll(processID,filename).
I am getting something wrong but I don't know what - date related.
Take this code for example:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=" & varDate) > 0 Then MsgBox "Go Away", vbOKOnly Else Go on and do the real work End If End Sub
The DCount line is being completely ignored, no error message, it just cracks on as though there were no records found (but they are definitely there!) I have tried:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate='" & varDate & "'") > 0 Then MsgBox "Go Away", vbOKOnly
But that throws up a Type Mismatch (which is what I would expect). Then I tried
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=#" & varDate & "#") > 0 Then MsgBox "Go Away", vbOKOnly
And it gets ignored again.
I am having similar trouble trying to open another form using the same field as the open argument, and getting similar results, so it is clearly me that is wrong.
I am trying to check for when a user trys to enter a duplicate number.
The control that I am checking is in a subform on the main form:
Main: frmCandidateInfo Sub: frmTestInfo
Control on the subform is: RankOrder
I am trying to check the control entry against the table entry:
Table: tblTestEvents Field: RankOrder
Here is what I have:
Code: Private Sub RankOrder_BeforeUpdate(Cancel As Integer) Dim lngRankDup As Long lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0)) If lngRankDup <> 0 Then MsgBox TestEventID & " already exists in the database" End If End Sub
I know that the error is due to the argument not being correct, but I am not sure how to fix it.
I've a method **querylistboxitems** and i want to call this method in several click events, only difference is listbox,dropdown values change based on the event i call.
Code:
Public Sub querylistboxitems(lstbox As listbox, dropdown As ComboBox) Dim drpdwnvalue As String drpdwnvalue = dropdown.Value With lstbox //do something End with End Sub
And I'm calling this in the buttion click event by passing the listbox names as **List_Compare** and **Select_CM_Compare**
Code: Private Sub Command_compare_Click() Call querylistboxitems_1(List_Compare, Select_CM_Compare) End Sub
But the values passing to the function are not control names, control values i.e corresponding control selected values. I want to use listbox name in **lstbox**, not the value.
This is my first time writing a pass through query pinging sql tables using an input parameter from a form. I have gotten as far as executing the query but I can't seem to display the result to ensure it's pulling the right records. I also want to be able to append the records to a table. Below is the code I have written so far:
Sub GETRT()
Code: Dim db As DAO.Database Dim QDF As QueryDef Dim STRSQL As String Dim RS As Recordset STRSQL = "SELECT * FROM LAB_MESR.ODM_RT_DAYS" & _ "WHERE LOCATION_ID=" & [Forms]![PARMS]![STR_NBR] Set db = CurrentDb Set QDF = db.QueryDefs("001:GET_LT") QDF.SQL = STRSQL End Sub
I have a subform (this subform's source object can change to one of three diffrent forms) that allows the users to filter. I have a requirement to output the filtered data to excel. I am creating a query based on the subforms record source called qryExport. I now want to pass the subforms filter to qryExport. I have the string setup now I just need to know how to pass the data into the filter property of the query.
Good afternoon, I have a form with a subform and in the first txtbox of the subform in the GotFocus event I have a little procedure which checks the txtboxs on the parent to make sure that there is data in all four of the txtboxes. This works great the first time and it pops up a msgbox and it even setsfocus on the txtbox with no data in it, but if I tab into the subform a second time and there still is no data in one of the txtboxs on the parent form, nothing happens, no message and no setting focus on the txtbox with no data in it. Does anyone know of a way to get this procedure to re-set everytime a user tries to enter the subform? Thank you in advance to anyone offering and ideas and suggestions.
I have two subforms in a tab formation sitting on a main form. They can both open up a single popup form (via a button) and that popup form is opened via openargs with the autoID field.
What I need to do is pass back a value on the popup forms [On Unload] event to the subform which opened it.
As the popup form is Modal, the subform which opened it can't be changed.
Am I right in thinking I can pass back the value to the form which had previous focus?
Previously, to pass a value from a popup form to a single form I've been using
Code:
If CurrentProject.AllForms("MyForm").IsLoaded Then do this else do this end if
But in this instance, how do I code the On Unload event in which to tell Access which form opened up the popup form, and pass a value back to it?
I'm guessing Screen.PreviousControl.SetFocus has something to do with it?
Table: DailyExport Field: FailureGrouping (actually is offices)
I want to export all the fields from DailyExport each of our 9 offices to Excel, whetjer or not they have date in the DailyExport table. So if not, the exported workbook would only have columns headings.
I need to loop through the nine offices and export each office report to Excel.
Dim StrQry As String Dim strfullpath As String Dim SOffice as String strSQL = "SELECT * FROM DailyExport WHERE FailureGrouping = " & SOffice strfullpath = "Y:" & SOffice &" "& Format(Date,"mm-dd-yy") & "_Failures.xlsx" DoCmd.TransferSpreadsheet acExport, , FailureGrouping, strfullpath, False
I want to pass the Combobox value of one form to a textbox on another using the where condition. Using the break in VBA, the value of the combobox (in this instance "two") shows as null. the control of the combobox is [cboSubCategory] and I want it's value to pass to another form "tblCategoriesSub" (aware of the incorrect reference, as used wizard quickly - but will change if I can get it to work) to the "SubCategory" field.
I'm working on a database that will be used when maintenance needs to be performed on our test equipment. So far, I have a form set up for the associates to track when a maintenance action has been performed. The top section of the form contains basic data about the part and there is a subform that lists the actions that need to be taken on the piece of equipment. In the subform, the associate can mark whether the action is complete or not.
If the action is marked incomplete, I wanted a separate form to appear for them to track why an action cannot be performed. If possible, I would like this separate form to automatically show the equipment ID and the action that was missed. However, I'm having difficulty getting the data to copy from the subform to the Exception form. Is this something that is possible to do?
I am trying to pass the results of what is selected in a list box to sql through an ado statement, when i select one result i can pass the value with no issue, when i select multiple item i get a run time error incorrect syntax near ';' i am guessing it is passing the data as a csv to sql and the where statement does not like that as criteria but i am not sure how to handle that either in vba or on the sql side.here is my vba code from access
Private Sub Command49_Click() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim strItems As String Dim intItem As Integer For intItem = 0 To List45.ListCount - 1 If List45.Selected(intItem) Then strItems = strItems & List45.Column(0, intItem) & ";" End If
I have a button on my main db, that opens a second db using hte following code:
---------------------- 'Dealing with external objects, use inline error trapping On Error Resume Next Dim appAccess As Object
Dim db As Database Dim strAppPathName As String Dim strAppName As String Dim strTimesheetPathName As String
[Code] ....
This code works great to open the other db, and handles wheter the other db is already open or not, but I cant seem to pass the variable to the other db using the startup switch /cmd.
I suspect if I used the shell method it would pass the cmd , but I havnt found any way to test if the db is already open with the shell method.
How can I pass a variable to the other db when opening it using VBA?
I have code that will create an email and prepare it for sending.It will create a table inside the email and fill it in with some text and underscore characters to be replaced by data from the database. So far the data has to be done manually. I would like to know if it is possible to use the values from some fields inside the select record in the current form. So, if Me.Status would be "New" it should pass this to the table in the email.So far I have the below code:
Code: Private Sub Command280_Click() 'send email with table Dim objItem As Object Dim oMail As Outlook.MailItem Set oMail = objItem Dim oApp As Object
