Modules & VBA :: Module To Modify Report Controls Based On Form?
Feb 12, 2015
I am trying to create a customizable report that would allow the users to choose fields. I have a pretty common code that I found online and adapted it, but it fails on the first SetReportControl function.
The error reads: Run-time error 2465: Application-defined or object-defined error
Could it be something as simple as an incorrect reference? I have checked multiple times, but I am stumped.
Code:
Option Compare Database
Option Explicit
Sub MakeReport()
[Code].....
This is a trial run, in the end I need to be able to open a report, then adjust the Report controls within 1 or more subreports inside the main report. That is a battle for another day.
View Replies
ADVERTISEMENT
Dec 14, 2007
I'm wondering how other members here make decisions whether they want to place codes behind form or use a standard module instead.
I understand there is a performance penalty when you add another module (and use it), but am not sure whether one big fat module would be faster than several smaller modules with identical coding.
Furthermore, I know that some members use a hidden form to deal with startup and shutdown processing. Sometimes the processing has nothing to do with forms and would make more sense in a standard module, but since the form is already loaded, does it makes more sense to use the module behind the form than calling a function in a separate standard module to execute the needed code?
So, what do you tend to do in such situation?
View 14 Replies
View Related
Jun 3, 2014
i have created a module under module section of my DB My Report Name : "PURCH VB Query"
the code is as follows : (what is want tell you later..)
Code:
Sub Erlick()
DoCmd.SelectObject acReport, "PURCH VB Query", True
'"PURCH VB Query", acViewPreview, "PURCH VB Query"
For i = 1 To 3
DoCmd.PrintOut acPrintAll, , , , i
Next i
'This is for creating Seperate Text Per Copy in RptHeader Preview End Sub
i've placed a TextBox in my report named Text25 to the value :
=+1
properties-->data-->running sum--> Set to OVer all
i've also set a textbox in my report named CpyWord to the value : =IIf([Text25]=1,"Original Buyer Copy", IIf([Text25]=2,"Duplicate File copy",))
Now what is want is Three Copies of Report with Two copies marked seperately as per above IIF statement
As i want Three Copies with each Rpt-header as Different Text
Possible solutions (ba donna no a script) How do i refer to Report's control after Docmd.SelectObj.... if any facility then i can do easily thru the dazzling for next loop How do i increase Report's control Text25's value, if three copies r there I know there are many events in Report
I've stucked over this point. My all App in db is ready
View 10 Replies
View Related
May 22, 2014
I want to put some form functions into a module.here is what I currently have in the module:
Public Sub Fun_Test()
Forms!Form_Output!Sequence.ColumnWidth = 250
End Sub
my form name is "Form_Output" but it still cant find it..
View 1 Replies
View Related
Jun 16, 2013
I have this code below that is working however the calculation are updating on my form late.
Basically, I have some calculation that are performed on a "After Update" event on some controls on my form. I wanted it to do the calculation after I update the control on the form.
The code is in a module and I just call the function after update on the control But the form is not updating when I change the value in a field. I have to change the field again for it to update.
Control
Code:
Call GeraAuditCalc
DoCmd.RefreshRecord
Function
Code:
Function GeraAuditCalc()
Dim fHrs As Double
Dim Ttl As Double
Dim Ttl1 As Double
Dim Ttl2 As Double
[Code] ....
View 4 Replies
View Related
Sep 11, 2014
I have a button on a form which calls a module to show a calendar form. The user picks date and time, and saves it. The calendar form closes and adds the date and time to a text box on the initial form.I want to save the record so that the underlying table/query is updated, but it's not working. The record is not saved until the initial form is closed.Here's the form vba...
Code:
Option Compare Database
Option Explicit
Private blnFlag As Boolean, blnSaveIt As Boolean
Private Sub btnDelete_Click()
On Error GoTo Err_btnDelete_Click
[code]...
View 3 Replies
View Related
Jan 1, 2014
An instance of a form can be opened with:
Dim frm As Form
Set frm = New Form_formname
How can this be done using a variable as the formname?
View 5 Replies
View Related
Sep 10, 2014
I have the following module in my database to allow for multiple alarms.
It all works great, except before showing the actual alarm record called, the form first pops up showing the first record in its record source.
I.e. it flashes up showing the first record in the table, then changes to the 'correct' record.
I would just like to change it so it either doesn't show until the record is loaded, or it shows blank to begin with.
Here's the module...
Code:
Option Compare Database
Option Explicit
Public clnPopUpAlarm As New Collection 'Instances of frmPopUpAlarm
[Code].....
View 2 Replies
View Related
Apr 14, 2015
I am trying to open a report (rptGMX-IRG) that includes a sub-report (srptRG) based on criteria that is set with two unbound fields on a form (frmGMXbook). This form has two dropdown boxes (cboGMX_No & cboMachSize) and a command button. The first dropdown box includes data located in the main form and the second dropdown includes data that is part of the sub-report.
I can get the report to open properly when I enter data into both fields on frmGMXbook.
However, the problem I am having is the inability to have one or both fields be "Like '*'" and show all associated records.
Here is the code I have on the command button.
Private Sub GMX_Preview_Click()
Dim strCriteria As String
If IsNull(Me.cboGMX_No) Then
strCriteria = "[cboGMX_No] Like '*'"
Else
strCriteria = "[GMX_No] ='" & Me.cboGMX_No.Value & "'"
[Code] ....
View 1 Replies
View Related
Jun 30, 2015
I have a form with a combobox, textbox, a button and a listbox.
Currently the button checks if the textbox is empty and if it is, it runs a SQL query to select data using the combobox value in the where clause, else it selects data using the textbox value in the where clause
I need to be able to type or select values in both controls and use those values in the SQL query as a where.
View 6 Replies
View Related
Sep 18, 2013
Here is the basic info:
Table has Checkbox column
BusinessDate column (mm/dd/yyyy format)
OrderTotal column (in dollars)
There are others but these are the relevant ones
In the header of the form I have a calculated control box with the following control:
=Sum(Abs(nz([Checkbox],0))*nz([OrderTotal],0)),0)
Which works just fine. The user is presented with a list of all the orders from this table in the form. They check the checkboxes and the control in the header shows the total of the records checked. I need to make 7 of these calculated controls, one for each day of the week. Is this possible? This way the user can see the order totals for each day of the week they have selected.
I tried:
=IIf(Weekday([BusinessDate])=2,Sum(Abs(nz([Pull],0))*nz([OrderTotal],0)),0)
but it still sums up the whole week as the expression holds true.I do not know VB, and I'm sure there is a really neat and easy way to do it in there. I just don't know how.
View 4 Replies
View Related
Jan 9, 2006
Hey all, I am wondering if there is a way to copy and past a control from a FORM to a REPORT? I have an activex control for my digital signatures and for some reason the value is not showing up on my report, I have tryed several different ways and I was wondering if there was a way to copy the activex control and on the open of the report the value what was copyed would appear on the report. can this be done?
I believe that somehow the value is not staying in the field provided and just going directly to the table therefore I cant see it on the report. Is this possible? Thanks in advance
View 10 Replies
View Related
Sep 15, 2013
I have tab control form with (5) tabs. For this discussion - Tabs 1 through 5. For a blank (new) form sheet tabs 4 & 5 need to be hidden. Based on what is selected via the drop down box (on tab 1) then tabs 4 & 5 may remain hidden or needed to be un-hidden. Example: [DropDown1]
Selection 1: stay hidden
Selection 2: unhide
Selection 3: unhide
Selection 4: stay hidden
Selection 5: unhide
Selection 6: stay hidden
Selection 7: unhide
Selection 8: stay hidden
Selection 9: stay hidden
I think one I figure this out then I can use the 'OnCurrent' event to check the drop down selection as a user selects the a record or scrolls through records.
View 2 Replies
View Related
Nov 14, 2012
My form-based search mechanism uses controls to set the parameters for the query data source.
I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.
HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.
The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]
When i try and run this, i get the message "THe expression is too complex to be evaluated".
View 5 Replies
View Related
Jan 9, 2007
Hi all
I have a report rptTeamPickStats which source is a query qryTeamPickStats
The query has columns such as name, id number etc which are GROUP BY and hours, cases as SUM and there is a DATE field where I originally had a WHERE statement specifying the from and to dates.
I want to be able to modify the where using VB so I don't have to have several queries.
The code below doesn't work (probably won't take you long to realise that) but just to show kind of what I'm after.
I want to query all the TLName where the date is between to dates.
DoCmd.OpenReport myReport, acViewPreview, "", _
"[TLName]='" & myFilter & "' And qryTeamPickStats!Date >= #" & myFromDate & "# And qryTeamPickStats!Date <# " & myToDate & "# And [OTcode] " & myOp & " 'NA'"
Hope this makes sense
Cheers
Kev.
View 5 Replies
View Related
Nov 28, 2004
I've been using Access for a while now, to track some names, addresses, locations, etc. Recently, when I try to generate a report, I step through the field selection, etc, and when I get to the "sort" page, if I just keep going, it proceeds fine. If I select a field to sort on, however, I get the following message:
"The expression Before Update you entered as the event property setting produced the following error: The function you entered can't be used in this expression.
*The expression may not result in the mane of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluation the function, event, or macro."
I don't think I've changed anything to do with the program, or any of its Office brethren. Can anybody give me some advice? The report is generally useless without the ability to sort/group.
Thanks, all.
View 3 Replies
View Related
Dec 3, 2013
I am trying to open a form in design mode and add controls, this i have done. I am doing this all in a class, and am having trouble saving the modified form.
I have tried using the following, both produce errors
Code:
DoCmd.Save acForm, "tmpQueryDes"
DoCmd.Close acForm, "tmpQueryDes", acSaveYes
View 3 Replies
View Related
Sep 15, 2014
I have a bit of code that uses controls on a form to batch add records to the tables. Before the code executes, it needs to do a check to make sure that all of the needed data has been supplied.
Code:
If IsNull(txtDateAdded) Then
MsgBox "Please supply the date that the Label record was created"
Exit Sub
Else
If IsNull(cboRecordCreator) Then
[Code] ....
View 1 Replies
View Related
Feb 10, 2012
I am writing a module to send information to Excel, and the information is based on a Crosstab query. But I keep getting a "syntax error in the TRANSFORM statement".
Function Edt_Royalties() As Integer
Dim AplicExcel As Excel.Application
Dim Planilha As Excel.WorkSheet
Dim stArq As String
Dim DB As Database
[Code] .....
View 1 Replies
View Related
Mar 20, 2013
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies
View Related
Jul 30, 2015
i would like to know how to replace the sales values in the quarterly sales report with tax values so as to create a "Quarterly tax report" in Northwind.
View 1 Replies
View Related
Jul 14, 2014
I have a problem with formating controls in a form. As a example, I inserted a tree view control and adobe reader control. After I've resized them manually in a design form, I switch into form view and they both resize automatically to their own size (don't know where it gets from).
In the result, I can't obtain the required width and height. How can I make them resizable to my own preference?
View 14 Replies
View Related
Dec 11, 2013
I want to modify a table called FC_TEMP via adding some columns from another table called AVERAGE_TRP. Do I have to create a new query table and then insert the output into a new table?The following code shows me : Cannot execute a selected query.Is there a way to update this FC_TEMP table without creating another temporary table?
Code:
Public Sub Update()
Dim strSQL As String
strSQL = "SELECT FC_TEMP.*,AVERAGE_TRP.[Average_new_TRP_EUR],AVERAGE_TRP.[Average_old_TRP_EUR],AVERAGE_TRP.[Average_Margin] " & _
" FROM FC_TEMP " & _
" INNER JOIN AVERAGE_TRP ON FC_TEMP.[PRODUCT_ID] = AVERAGE_TRP.[PRODUCT_ID]"
[code]...
View 5 Replies
View Related
Jun 27, 2014
My problem is simple, and i imagine it is easy to fix. I have more forms in my db than I would like to count, and I have a couple lines of code to add to the onload event of each form.
Is there an way, without actually having to edit each form myself, to add the code i need to add to all forms. Is there some code I can write that will let me modify the on load property of a form, and save it?
I know what i need to do, minus the part where i actually change the onload event.
View 5 Replies
View Related
Jul 24, 2013
I have a report that tracks scores for our employees. From the report, you can click a button to add a new score in a form or edit an existing score(frmscoretracker). On this form there are two subforms, in a tabbed control to track additional information about the score; what areas were marked down(Trends), and was it a failing score(AutoFail).
When this form opens I have it programmed to only show the subform if there is data in it. The goal being, if I am adding a new score and there is no existing trends or Autofails for this new record, neither subforms will show - I will add an after update even to show either trends or autofail depending on the score recorded. Also, if someone chooses to edit the score, whatever subform with data, will show as well.
When someone clicks to add a new score, opening this main form to a new record, both of the tabs show. However, if the form opens to an existing record, the appropriate tab shows. Here is the code
Code:
Private Sub Form_Open(Cancel As Integer)
'If the subform has a record, the tab is visible, if not, the tab is not visible
If Me.frmtrends.Form.Recordset.RecordCount > 0 Or IsNull(Me.Trends) Then
Me.Trends.Visible = True
Me.TabCtl33.Visible = True
Else
Me.Trends.Visible = False
End If
[code]...
Both tabs are set as not visible in the default settings. Is there something in this code that is triggering then to be visible when there is no record in the main form?
View 4 Replies
View Related
Nov 8, 2013
Is there any way to modify the progress bar so instead of saying "running query" it states the name of the query?
Like: Running Query [qryFRED] <progress bar>
View 1 Replies
View Related
