Modules & VBA :: Referring To Forms Objects Using Variables
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.
A main form - named [formMain] On [formMain] I have a tab control - named [tabMain] [tabMain] has 3 pages named [pageOne], [pageTwo] and [pageThree]. On [pageThree] I have created a subform named [formSub]. On [formSub] there is a tab control named [tabSub].
Now what I would like is to make [tabSub] (in)visible if the user switches from [pageOne] or [pageTwo] to [pageThree].
Code: Sub tabMain_Change()
How do I refer to [tabSub] from within this subroutine? I am trying something like:
I am trying to use a calculated field in a recordset but I am having problem with the script trying to compile.
my record set is
Set rstPrice = db.OpenRecordset("SELECT Period_desc, Price, Round(Price/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)
The calculated filed I have added in is Round(Price/7,2) AS Daily_rate
If I let it run just with this it runs fine.
and then I try to use this calculated field:
Do Until rstPrice.EOF Temp = Temp & rstHTML!html35 & rstPrice!Period_desc & rstHTML!html36 & rstPrice![Price] & "-" & rstPrice![Daily_rate] & rstHTML!html37 rstPrice.MoveNext Loop rstPrice.Close ' Tidy up Set rstPrice = Nothing
They it stops running and it all seems to be because of the Daily_rate field
I have a part table summary with various quantities 1-1000 and want to create a new table where part number repeats with quantity of 1 corresponding back to the sum. If part 123456=20 then this would repeat 20 x and each record Qty=1
Start with summary --================ Part Qty 111000 2 222000 3
End result all Qty=1 --================ Part Qty 111000 1 111000 1 222000 1 222000 1 222000 1 --================
I stared with a loop and was able to get an append query to work referring to the quantity value (3) for one record from tbl_temp to tbl_main, but not really sure how to advance through many records.
For n = 1 To [Forms]![MainScreen]![Text7] DoCmd.OpenQuery "qry_Update_Qty" 'DoCmd.GoToRecord , , acNewRec Next n
I have a form with a subform that has a table on it. The subform table is a price list with the fields Description, Details (which is hidden as it's too big for the table) and Price.
I want to be able to double-click on a chosen item from the table to display a pop-up showing the same details but with the Details field shown. I have the pop-up in place but I am struggling with displaying the chosen information.
How do I reference the fields on the chosen line in the table?
I have a form named frmMain that contains five buttons that lead to subforms, one of which is named frmDeceased.
In frmDeceased, I needed to create a combo box that relied on selections from the combo box before it. I was able to do this successfully in frmDeceased, but then today when I opened the database and opened frmMain, I realized that the query fails when frmDeceased is opened via frmMain. It works perfectly if I just open frmDeceased directly.
The first combo box is ROHE and the second is Iwi (which is dependent on the Rohe selected).
The query for Iwi has this criteria: Forms!frmDeceased!cbo_ROHE
When I open frmDeceased through frmMain, I get the 'enter parameter value' message showing "Forms!frmDeceased!cbo_ROHE".
I tried changing this query to: Forms!frmMain!frmDeceased!cbo_ROHE, but it didn't work.
I have a form with a subform which resides in a tabbed control. In that subform, I have a textbox in the footer that sums values in the detail section of the subform.
I have a textbox on my main form that should display the same data that is in my subform footer textbox, but it is giving me the #name? error.
This is the expression in my subform footer textbox (which returns the correct result):
TxtSubtotalHQPCalc - =Sum([ActualHQPCalc])
And the expression in my main form textbox (which returns #name?):
I have verified that my subform name and textbox names are all accurate. This is very frustrating because I am using this exact same method in a different database with no problems!
I'm in the middle of writing a multi-language procedure, and in order to populate the label database I would like to cycle all forms (regardless of whether they're open or closed) and cycle all controls within the forms.
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:
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.
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
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
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(?)
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 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.
using VBA in Access and Excel. I have an Excel form that I am using to collect data as the front end and an Access database to house the data as the back end. In my Access database I want to create a module that will open the excel files and retrieve data from Active X combo boxes, option buttons and checkboxes that are on the Excel form. I have been searching for the VBA code to get data from these objects but have not been able to find any examples. How to reference these objects and get the values from the excel form
I want to export different objects (i.e. modules, reports,tables, queries...) from another ms access application. How can I list the objects so I can pick from them and then import them. I know ms access has a wizard and I am looking to do the same but a little different because I have an application that I want to filter my objects by a criteria.
I was wondering if there's a way to set objects so that they don't get pushed down if an object above is set as Yes in the CanGrow property?can a report be split into columns?
I have been asked to replicate a form (previously written up on Word) which has two columns of information. As luck would have it all the fixed fields are on the Left hand side, so that part doesn't need (or want) to grow, However, the Right hand side has several sub-reports which can display 1-5 records each.
So if any of the sub reports show more than 1 record, they push down everything below it, but this includes the Left hand objects, creating gaps and often pushing the report onto 2 pages.
I'd like to know if it is possible to copy an object (typically forms, reports and queries) by code from the acces file I'm woriking in to another one that is not opened.
I want to keep the textbox small unless a user mouses over it, and they can then see all of the textbox contents. I've got code that will change it back to it's normal size after leaving the textbox as well.
Can I make the textbox "draw over" other nearby objects, so that it is in the foreground? Currently, the box grows, but it hidden by other nearby textboxes.
Edit: Well it looks like I just wasn't setting the Height large enough. I set it at .25 thinking it was in inches like it is in the property window, but I guess it's not. Seems to be working for now.
Edit 2: Not working afterall. It looks like it's working sometimes then the next time other objects will show through it. If I take a screenshot it looks fine in the screenshot no matter what it looks like on screen. Basically the check box and text box below the textbox I am making larger can be seen through said textbox.
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.
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
I'm looking at importing data into a table from a spreadsheet. The spreadsheet will only contain a single column of data, while the table in Access will have a few more, some of which will would be will provided from the form the user is using to import the data, and some at a later date.
Is there a way to do this? I've found the "DoCmd.TransferSpreadsheet acImport" command, but not sure this fulfills what I need, as I can't see a way to set variable data.
Here is a brief idea of what my Table would look like, where where the data to import would be obtained from
