Hi
I have a matrix variable (ex: test(7,3)) define as private in a form's code vba. In this form, I open a report in which I would like to show the values of my matrix variable. How can I do this efficienly ... what i use now is a public buffer variable in a module(it eats memory for nothing)
can someone tell me a trick ...
tanks a lot
I have a form with 5 buttons on it. Each button is meant to select a warehouse location, so a query can be run to give an inventory report for that location. There is a separate query for each button and the OnClick event does properly modify the recordsource to give the appropriate data to the report for the location selected.
To this functionality I want the OnClick event VBA to pass the warehouse location to a textbox on the report, so the title of the report reflects that inventory location.
My code thus far is:
Private Sub Command5_Click()
Dim mySQL As String
Dim WHSE As String
mySQL = "SELECT [Master Part List].[Part Number], [Master Part List].Category, [Master Part List].Description, [Master Part List].MaterialCost, [Master Part List].Inventory, [Master Part List].Update, [MaterialCost]*[Inventory] AS [Total Cost], [Master Part List].Warehouse"
[Code] ....
When I get the report, the textbox is empty, instead of containing the text value for the warehouse location.
I have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct -
SELECT * from tblIncidents WHERE [Nature] = 'Hover';
(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')
I want to pass the variable strSql to my report rptIncident in the following command:
Private Sub CmdPrintReport_Click()
If Right(strsql, 1) <> "'" Then 'check if statement was built
Else
strsql = strsql & ";" 'add trailing ; to statement
MsgBox strsql
DoCmd.OpenReport "tblincidents", acViewNormal, , strsql
End If
End Sub
I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.
how can i pass a variable from one form to another
this works fine
Tracking_Number = Forms("Main").Control("mytext")
but if "mytext" was in vb (rather than from a text box as shown above) how can i do this
I'm attempting to pass a variable from form to form...I'm having trouble doing it...I checked the forum and read a little that the form I'm passing it from must stay open...is this correct? I have my variable as: Public strUserName as String. It does work fine if I leave the form open then I can pass it...
But I want to be able to close the form and still pass the variable...How can this be acheived?
Thanks in advance.
Kacy
In form1 I've a command button to open form2.
Code in form1:
Private Sub button_Click()
On Error GoTo Err_button_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "form2"
stLinkCriteria = button.Caption
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_button_Click:
Exit Sub
Err_button_Click:
MsgBox Err.Description
Resume Exit_button_Click
End Sub
button.Caption contains a valid date value
In form2 I want to display the date in a label.
Code in form2:
Private Sub Form_Load()
Dim DatePassed As Date
DatePassed = CDate(Me.OpenArgs)
label.Caption = DatePassed
End Sub
On executing I get an error like "Invalid use of null value."
Who helps?
Hello,
I have made one form for report purpose. When I enter Start date and End date, I get report of the date range. What I am looking that I want to print the start date and end date in my report. How can I reference date text box in my report?
thanks
mithani
I want to build an SQL statement in code which includes the value stored in a string variable where the variable name includes a loop counter.
This is a much simplified example of what I am trying to do:
Dim i as integer
dim Strtable1 as string
dim Strtable2 as string
dim Sqlstring as string
...
Strtable1 = "tblEmployees"
Strtable2 = "tblSales"
...
Sqlstring = " select * from ... where....."
for i = 1 to 10
DoCmd.RunSQL "INSERT INTO " & StrTable & i & sqlstring
Next i
I am not sure how to get the table names stored in the string variables into the SQL statement. When I try the above it looks for a variable named StrTable, not StrTable1, StrTable2 etc.
Hi Everyone,
I'm trying to pass a variable called MyFilter between forms but am having problems. I have created a Module and declared MyFilter as a public string.
The original code in my first form is:
Private Sub Command65_Click()
Dim MyFilter As String
If Me.Filter = "" Then
MsgBox "Please apply a filter to the form first."
ElseIf Me.Dirty Then
' Make sure the record is saved
RunCommand acCmdSaveRecord
Else
MyFilter = Me.Filter
DoCmd.OpenReport "Temp", acViewPreview, , MyFilter
End If
End Sub
How to I change this so that it now stores the value in the Public variable instead of the Private one which it is doing above?
Thanks,
I have an event on a form kicked off by On Delete and I have an event on the same Form kicked off by After Delete Confirm. I need to pass a variable from the On Delete Event to the After Delete Confirm Event. I have set up the variable as public, but it keeps getting reset inbetween the two events. Any ideas?
View 1 Replies View RelatedHi all!
I have a college project were we have had to code a database for a fictional hospital to hold patient, doctor, consultant and appointment information. I am struggling with one particular problem.
I am trying to pass a variable from a combobox of results to another form to display a certain record.
I am trying to send:
stLinkCriteria = "[NHS_Number]=" & "'" & Me![lstSearch].Value & "'" & " AND [AppointmentID]=" & "'" & AppointmentID & "'"
to the form but it fails, yet if I "hard code" the variable it works:
stLinkCriteria = "[NHS_Number]=" & "'" & Me![lstSearch].Value & "'" & " AND [AppointmentID]=23"
Can anybody shed any light upon this please?
I need to pass a variable from one Access Database to another
The scenario is I have a item number in one database that I need to use to open a form in another database,
I can open the database using vba, currently launching a BAT file that copies the latest version database to a local drive and then opens it,
Is there an easy way to pass the variable after this has completed?
I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.
Here is the query created as a stored procedure:
SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];
What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?
Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?
Thanks.
I am using an on-click event on my form to call up a report for the current record. It has been a while since I've used the form to call the report, but I always remember it working correctly. Today when I try to call up the report, it opens but no record is being pulled up so the report pulls up blank.
Code:
DoCmd.OpenReport "rptSupplierInformation", acViewPreview, , "[OperationID]=" & [cboMoveTo], acWindowNormal
I am trying to pass a boolean variable to a class module
Code:
Set rps.ViewS = View
the code in the module that this in theory is calling reads as
Code:
Private ViewC As Boolean
Public Property Set ViewS(ByRef ViewA As Boolean)
Set ViewC = ViewA
End Property
Public Property Get ViewS() As Boolean
Set ViewS = ViewC
End Property
However I am getting the error message
Quote:Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalid Set final parameter.
I have a navigation form that will have 6-8 tabs. We were using about that many databases, but we are finally consolidating them into one. The result of us using so many databases has been the multitude of forms and reports that were necessary for each database prior to merging them together.
The problem: There will be anywhere from 12-20 (text boxes) that the user can use to search anything in our database. What we need to have happen, if possible, is for those search parameters to show up in the header of our report if they have text in them. If the text box is blank, it should not show up in the header of the report.
I have read how to to do the start/end date technique, but I do not know if that would work for what we are doing since the boxes would only show up if they are populated by the user.
I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value.
View 4 Replies View RelatedI've done it before, but I'll ba damned if I can find which DB and where, but here's what I'm shooting for:
I have a DB program with 3 existing reports that show all employees and the corresponding records (all) in three different catagories, one per report.
The boss has asked for a report where it shows a specific employee and only records for said catagory for a given year.
What i'm thinking about doing, is a form with a cbo supplied with the employee names (EmpID) as the underliying PK and a text box where the user types in the desired year.
I would then put 3 cmd buttons, each to open the corresponding existing report, but they would pass the criteria of the EmpID and the year so the report only shows those records.
As a bonus, it'd be nice if the year txt box was empty, it'd show all the records for the employee, but that's extra bonus.
Anyway, I can't find the format as I'm pretty sure I've done it before. Anyone know the format for, what I think, is the "where condition" in the open form command?
Thanks
I have a switchboard to print some labels based on info pulled from a database server. What I want to do is vary the color of a particular box on the labels (a Report) that varies depending on the string value from one of the fields of the pulled data.
I have everything up to the docmd.openreport and a sketchy code outline for the "on format" event in the report detail that includes a select case statement and backcolor assignment. All I need is to figure out a way to pass the string value (or alternatley define a global variable).
Any suggestions? Thanks.
-Brian.
OK, I am trying to build sql strings from a form buy the user selecting criteria from boxes on a form.
I was given some code which I have adapted very slighty
Dim strCrit As String
If IsNull(Me.server) Then
' proceed
Else
strCrit = strCrit & " And [Server type] = '" & Me.server & "'"
End If
If IsNull(Me.RAM) Then
' proceed
Else
strCrit = strCrit & " And [Ram] = " & Me.RAM
End If
DoCmd.OpenReport "Your Results", , acPreview, strCrit
End Sub
however when I run this I get the following error
http://i145.photobucket.com/albums/r232/stuartkeith/errorsyntax.jpg
or trying to edit the code this error
http://i145.photobucket.com/albums/r232/stuartkeith/error.jpg
how do I pass the built SQL string directly into the query defs?
the strcrit when displayed looks like this
http://i145.photobucket.com/albums/r232/stuartkeith/stricrit.jpg
Thanks
How can I pass data (an employee's first and last name) to a report ? I captured the employee's name from the listbox, but can't seem to pass it to the report. The desired report will only have the employee's name and records for related fields on the report. The table (contains emloyees' history data), form name, listBox (contains employees' names), and variable (contains the employee's name) are listed below.
Table_Employee_Detail_History
Form_Employee's Reports
Report_Attendance_Report
stremployee (variable
I have a query that sums up the number of parts used. This works fine.
I want to be able to limit this query to parts used after a specific date.
I have in my report
DoCmd.OpenReport "Part Totals Report", acViewPreview, , "[Part Date])>= " & SQLDate
The report is bound to a query that has 2 group by fields, 1 count field a a further field, a date field ([Part Date], that I put a default criteria on. This field is not displayed. If I don't put a criteria on this field disappears when I close and open again.
I pass a date to the program via a form and this ultimately ends up in SQLDate. When I run this I get promted to enter [Part Date] even though I'm setting it equal to SQLDate above. I can out garbage to a proper date in here either way the report picks up the default date entered in by the query.
1. Get rid of all of the parameters off the query.
2. Then you can use the Where Clause of the DoCmd.OpenReport code to specify the parameters based on your variables.
1.Not sure what this means but when I get rid of the criteria for the parameter the field disappears (I'm setting the show field to no as I don't want totals group by date). Getting rid of the field gives me all parts used.
2.I think I'm doing this in the above but will bow to superior knowledge!!
or is it I can't pass a parameter to a report run by a query that is grouping fields together to produce a count.
Incidentally once the report has been run (albeit with the wrong parameters) and I go into design mode and look at the property sheet for the report the correct filter is there (i.e., the date that has been input) but it quite clearly ignores this.
I am trying to create a report which is sorted by KeyID, CustID. I don't want to display every Customer record but in the KeyID Footer I would like to display all CustID's associated with the KeyID. I have attached a sample mdb.
TIA,
Valerie
I have some code that will allow a user to pass to it the name of a report. The code will do various things to that report before presenting it for Preview, opening in Word etc.
At one point in the code I would like to refer to some control or property of the report. If I was to include this directly for one report, I might type:
Reports!Annual_Budget_Report.Recordsource = xyz
But instead of a specifc report, it would be the report as a variable (srReport_Name). However, if I type:
Reports!stReport_Name.Recordsource
The code complains, rightly, that there isn't a report called stReport_Name. The stReport_Name variable is currently set as String. I *think* the answer might lie in the variable type, but I'm still struggling.
I use a macro that attaches a document automatically to an e-mail address ready to send this report is governed by a query of which the criteria is " loss order number " this is in a pdf format of which I need if possible
The problem I have is the same pdf file keeps attaching itself to the e-mail no matter which loss order number I select. The reason I think is because the report is called NCR so it is not changing from the previous one. What I think can solve it but not sure and don't know how to do is get the " loss order number " within the report name.
If I just send the report to a file direct it does work as I am prompted for a file name
Can the loss order number get into the file name within a macro/vba
Code:
Name="EMailDatabaseObject
Name="ObjectType">Report
Name="ObjectName">SEND NCR
Name="OutputFormat">PDF Format (*.pdf
Name="To">=[E-mail Address] & IIf(Nz([E-mail Address
I have a report that looks at a query. The base data contains a field called 'functlocation'. Now this data can vary in length and looks something like the following
12080026335PDPV5448-1
12080025507PUMP
12080004003BUILDINGS
12081925507PUMP
12081925507PUMP
Now the first 6 digits represent a particular building no. There are actually 6 buildings. When I run the report I am trying to get the report only to show all tasks against 120819 but with a wildcard at the end. I created a mini table with the 6 building numbers in, but i can't work out how to incorporate the wildcard into a parameter query.
regards
vipa:)