Modules & VBA :: Calling Function From Sub Routine
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
I am trying to call a subroutine from a VBA routine to append a record to an existing table. Below is the code for opening my recordsets for reading followed by the code creating my table to append to:
Public Sub UpdateLotsizes() Dim curDatabase As DAO.Database Dim ProdPlanDB As DAO.Database Dim rstQryLot As DAO.Recordset Dim rstInvLoc As DAO.Recordset Set curDatabase = CurrentDb
[Code] ....
To call my subroutine, I simply put in the code:
ExceptionReportAddRecord
And this is the code of the subroutine:
Public Sub ExceptionReportAddRecord() Dim rstExcRpt As Recordset Set rstExcRpt = curDatabase.OpenRecordset("tblExceptionReport") With rstExcRpt .AddNew .Fields("item_no").Value = txtItemNumber
[Code] .....
I am receiving "Run-time error 434: Object required" at the line I marked with ...
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))
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
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".
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
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.
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 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? "
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
The problem is lately; while testing I am running multiple queries in a routine. When it fails; its hard to identify which query has the problem. So I hit control break; debug and try to find it. After I fix it; I debug and reset; i get this continuing hourglass thing in the form of a spinning circle until I close and reopen the database. I think I need better error handling but not something really complicated because I need to put it in quite a few routines throughout the database.
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.
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
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"
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?
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).
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?
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
Ok I am exporting some data to a plain text file and one of the fields I am outputing can vary in the number of characters and I need they to have the same number of characters all the time.
Ex: 123.00 => 000012300 456789.00 => 045678900
I had heard of a command, but I can't remember what it was...I could do a loop till LEN() = 9 but that would cause undue clock cycles (aka performance hit) on the machine when running through the export routine.
