I have looked through the threads and have not found an answer to my question, so I post it hoping there is an answer as well as documenting useful information for other individuals.
The following code is what I am using to 'pull' data in order to print a certificate. It functions the way I designed it (verified by debug.prints and msgboxes). My question is how do I pass data to a defined report (certificate) based upon the results of a built recordset. When the report opens, the values come up as "#Name?". I'm guessing that the issue is syntax, but I just don't know. Here is the code I have so far (I've even commented it for y'all)...
Looking forward to your comments...
-BT.
Dim RSAgg As Recordset
Dim RSsrc As Recordset
Dim DB As Database
Dim strAgg As String
Dim strSQL As String
Dim strCert As String
'If there is no week number set, drop out
If Not IsNumeric(txtWeekNum) Then
MsgBox "You Must Specify a Week Number.", vbInformation + vbOKOnly, "Required Input"
Exit Sub
End If
' tblAggDesc contains the field names
' that have scores I want to evaluate.
' If someone makes a perfect score,
' then they get a certificate. Fields are
' a1, a2, a3, b1, b2, b3, c1, c2, c3.
strAgg = "SELECT tblAggDesc.AggCourse, tblAggDesc.AggDesc FROM tblAggDesc;"
Set DB = CurrentDb()
Set RSAgg = DB.OpenRecordset(strAgg)
RSAgg.MoveFirst
Do While Not RSAgg.EOF
If (Right(RSAgg!AggCourse, 1) > 1) Then 'rounds 2 & 3 contain additional information that is printed on certificate
strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & ", tblScores." & RSAgg!AggCourse & "X AS AggCourseX " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));"
Else
strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & " " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));"
End If
Set RSsrc = DB.OpenRecordset(strSQL, dbOpenDynaset)
If Not (RSsrc.BOF And RSsrc.EOF) Then
RSsrc.MoveFirst
Do While Not RSsrc.EOF
If (Right(RSAgg!AggCourse, 1) > 1) Then
strScore = RSsrc!AggCourseX & "X"
Else
strScore = ""
End If
'MsgBox RSsrc!Fname & " " & RSsrc!Lname & " " & RSsrc!WeekNo & " " & RSAgg!AggCourse & " " & strScore & " " & RSAgg!AggDesc
strCert = "Fname='" & RSsrc!Fname & "' AND Lname='" & RSsrc!Lname & "'" & " AND WeekNo='" & RSsrc!WeekNo & "' AND XCount='" & strScore & "' AND AggDesc='" & RSAgg!AggDesc & "'"
'Debug.Print strCert
' this is the point that I have problems.
' I want to pass RSsrc!Fname,
' RSsrc!Lname, RSsrc!WeekNo,
' RSsrc!AggCourseX, RSAgg!AggDesc to
' the report.
DoCmd.OpenReport "rptCleanTarget", acViewPreview, , strCert
I've done some basic work with arrays.. writing array data to form list objects.. How to use an array as a data source for a report?
Would i need to create a recordset and populate it with the array, then bind the report to it ?
The reason I am asking is the previous developer here built every app using arrays and UDT's... the apps are completely disconnected from the data. Everything is loaded in to arrays..
When attempting to open a recordset, I get "Run-time error '3709': The connection cannot be used to perform this operation. It is either closed or invalid in this context."
Any suggestions would be greatly appreciated.
Public Function GetTickets(ID, NbrTickets)
Dim rsTicket As ADODB.Recordset Dim rstAPs As Object Dim strQry As String
Dim cnnDB As ADODB.Connection Set cnnDB = CurrentProject.Connection
'Determine how many tickets have already been assigned... strQry = "SELECT count([ticket number]) as nbr FROM Tickets " _ & "WHERE [Tickets]![MailList ID] = " & ID & ";"
MsgBox (strQry)
Set rsTicket = New ADODB.Recordset rsTicket.Open strQry
With rstTicket .MoveFirst AssignedTiks = rstTicket!nbr 'This is the query result End With
MsgBox ("Number of assigned tickets is " & AssignedTiks)
If AssignedTiks = NbrTickets Then 'nothing MsgBox ("Nothing") Else If AssignedTiks > NbrTickets Then MsgBox ("Remove Tickets") 'Remove Tickets Else 'Add Tickets MsgBox ("Add Tickets") End If End If
I am using the following code to create a ADO recordset. The table in which the SQL refers to is in a front end database where the table is linked to the backend. I have just recently experimenting with ADO and need some help. The following code give me and error "Method 'Open' of '_object' failed. Is there a different way to crate a ADO recordset when the table is located in the backend. Please help
Private Sub Form_Load() Dim mysql As String Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim PersonnelInfo As String
mysql = "SELECT tbl_Personnel.last, tbl_Personnel.RANK, tbl_Personnel.SECTION " & _ "FROM tbl_Personnel " & _ "WHERE (((tbl_Personnel.RANK)='lt') AND ((tbl_Personnel.SECTION)=[Forms]![sos Personnel]![SchShiftFind]));"
Set rs = New ADODB.Recordset Set conn = CurrentProject.Connection
I have a a form which I use to populate query parameters and send the result to MS Word, where a table is created from the query. The form contains 2 controls: 1) a combo box containing the month number and 2) a text box containing a year value. The form also contains a command button that opens the query into a recordset and then creates the Word table.
The query (qryCostData) contains 2 fields that reference these form parameters. The query works when:
I open the query directly (query window) while the form is open w/ the month and year parameters selected I open the query directly (query window) when the form is closed but the month and year parameters are hard-coded into the query I open the query using the command button on the form when the month and year paramters are hard-coded into the query
The query does not work when I open the query using the command button on the form and the query containes references to the month and year parameters from the form. The following error is returned on the following command:
Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE
Any ideas on why it works using the form command button when the parameters are hard-coded but not when referencing the form fields? If I open the form and select the parameters and then open the query through the query window, it works fine.
I'm building a lab environment into one of my projects. I'm testing the distribution of values over a largish number of attempts to create a unique value.I have a table called LAB_UniqueIDTest, with two fields:
LAB_ID - the string value being tested, and LAB_UsageCount - the number of times the value has been created.
I'm trying to open the table using the following code:
Code:
' Initialize access to the LAB_UniqueIDTest table Set rs = New ADODB.Recordset rs.Open "LAB_UniqueIDTest", _ CurrentProject.Connection, _ adOpenKeyset, adLockOptimistic
I have similar snippets of code all over the application - either with a literal (as here) or as a string parameter.I do not, ever, use the options parameter of rs.open.I get error message "Run time error -2147217900 (80040e14) Invalid SQL statement - expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE
My guess is it's because somehow the Open procedure is trying to interpret the tablename as a SQL statement. But how come it isn't this obtuse at other times?
Simple example is I have say 3 recordsets open (they are opened once as they are refered to many times) - they are open early in the form (in this example 3 price lists that are applicable for a customer) (if the syntax of the select is slightly wrong I'm just showing to 'prove' the concept).
Dim db As Database Dim rs1 As Recordset Dim rs2 As Recordset Dim rs3 As Recordset Set db = CurrentDb() Set rs1 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=1));") Set rs2 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=2));") Set rs3 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=3));")
What I want to do is have a central function that I can pass a list I wish to process/do something with aka. The 3 recordsets are the same except for the where criteria.
private sub GETPRICEFROMLIST(pricelist as long)
dim rs as recordset
set rs = Recordset("rs" & pricelist) .....
I put the above to show what I'm trying to do but of course that doesn't work There won't be any updates to these recordsets only reading of data.
I am currently developing a calendar and am trying to open a recordset based off of a SQL string. When I deleted the Where part of the SQL statement, the code ran fine. So I am pretty sure that the problem lies within the Where part of the code. I use this code to filter my query based on txtTaskTypeID but if the value is null then the query is suppose to return all values. I keep getting Run-time error"3061: Too few parameters. Expected 1".
Hy,i have some ADO recordset and i want to bind it to blank report that I made in reports.How to do this. If i create report in reports section and after that i want to see it in my code as that same object and set his record or data source property how to do this?? I know that you can set the recordsource property directly in report but it includes some values from textbox in my form that doesn't show corectly(it's an array of numbers,and i don't know why it doesn't accept it). Thanks
I work with SQL Server 2008 with Access 2007 front end (using ADO). I can easily populate form controls using recordset. It does not look like I can do the same with reports.
is this right? Code:DoCmd.OpenReport "3rd Party Denial, Report, , [Claims Header].[Claim_ID]=[Forms]![claiminformation]![ReportForm]![Report_ClaimID], Normal" I get an error stating you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist.
Hey there, if anyone could help me with the code for opening a report please. I have a main page with buttons, and need a button for opening reports.. thx
Is it possible to open a report using a combo box. At the moment all my reports are opened using cmd buttons. This is just a question of curiosity, I will continue to look into it myself of course.
I have a form in Datasheet view and when you click on a record in that form I want it to take you to a report with all the details of that record number. I have the On Click event set to run a Macro. The Macro is as follows:
OpenReport Report Name Incident Report View Report Filter Name Open Where Condition =[Event ID]=[Reports]!Incident Report]![Event ID] Window Mode Normal
This Where Condition works for me open another form from the form with the same Event ID. Why does it come to a "new" Incident on the report?
Is there any way to put a shortcut on someone's desktop that will open a specific report in an Access database? I'm thinking back to the days of DOS when one simply added an argument to the command. I don't want to put it in startup and have it always go to that report. I just want non-Access users to go right where they need to without menus, etc.
I want to create a report using the data currently held in a form. I found this bit of code somewhere: DoCmd.OpenReport "report", acViewPreview, , "[job number] = " & txtFilter.Value
txtFilter is the name of textbox containing the data I want for the report. This works if in the table for txtFilter's data the field is set to a number. But if I set this field to text it comes up with a data type mismatch error. How do I solve this? (sorry new to access and vba). The reason I want to set it as a text field is so that I can limit the number of characters entered.
How do I get a query to run upon opening a report. My report has a few fields from a query that once the user inputs information the query is obviously outdated. Since it needs to be as simplistic and user friendly as possible I won't ever have them run the queries themselves so I'm trying to have them run to be updated for the report when the user goes to open the report.
I have two pop up forms called "frmRepair" and "frmPaid" with a button that opens another popup form called "frmLabels". This final form has a button to print a report with the following code:
The problem is that when this report opens, it is always behind frmRepair or frmpaid and frmLabels so it can't be seen.
I tried to use the popup and modal properties on the report, but this does not seem to do what I need.
My next step was to hide frmRepair or frmPaid when frmLabels loaded and then hide frmLabels when I was printing the report, but the problem is that when the report closes I need to also close frmLabels (easy) but then I need to make visible either frmRepair or frmPaid, depending on which one was used to open frmLabels. How can I do that? How do I know which one of the two forms opened frmLabels to make it visible again?
What are my options to have that report open on top of everything?
I want to create a query that says starting on a specific date, every 6 months a reminder will pop up when a certain report opens (or form, preferably a report). So if I said the starting date is 10/16/2013, once April 16th, 2014 hits and this user opens the report, a message pops up.
Another way would be to add that message to the report itself, so it's not technically a pop up but its built-in to the report.
I have via macro that displays the main interface to my database; frmMain (Maximize). This form frmSelectUIC (Minimize) allows me to select a department number of the data imported for analysis. All is fine, as shown here;
I have a query that searched thru records based on a person's last name. It runs fine and returns the results I want. However, the view of the results is in record-view. I'd like the results to be in a report format. Is this possible? The macro that calls the query is set to display in report view, but the query always come back showing the records. What am I missing here? Thanks a ton for your time!
I am building a faux Electronic Medical Records database for the purpose of training med students. I need a command button to pull up a report , but i want to delay the opening of the report (as if waiting for "tests" to come back or be uploaded) is there a way to do this with VBA? i read about the sleep api but i dont know how to get it to work or where to put the code
so what would i add and where would i add it to delay opening the report that is called EKG W/ Subreport ...
So I have a report that opens via Parameter. The SQL behind the query that runs the report is (I took out alot of lines that aren't necessary to answer the question)
PARAMETERS [Enter Your Box Id] Short; SELECT DocumentsTable.OrganizationalID, . . . DocumentsTable.Status FROM DepartmentsTable INNER JOIN (Year1 INNER JOIN DocumentsTable ON Year1.ID = DocumentsTable.RecordDateYearID) ON DepartmentsTable.ID = DocumentsTable.DepartmentID WHERE (((DocumentsTable.Voided)<>'Y' Or (DocumentsTable.Voided) Is Null) AND ((DocumentsTable.ID)=[Enter Your Box ID]));
So if I click on the report, I get a little popup that says "Enter Your Box ID", and when I do, the report works just as expected.
However, I also want to be able to open this report via link from another report, and pass the Box ID #. I just can't get the syntax right. I would have thought it was this:
Private Sub ID_Click() DoCmd.OpenReport "Find A Box", acViewReport, , "[Enter Your Box Id]=" & Me.ID End Sub
However when that execute, I still get the pop up asking for "Enter Your Box ID"
But it returns every record for that agent and I want to be able to specify the date that goes with the name.
For example Tom has a record for Feb 10, Jan 10, and Dec 13. I only want to see the record for Dec 13.
I am able to see this in my form by having a combo box for the agent and the date (the date box being based on the agent box). So now how can I add a condition to include the date combo box?
Adding the condition gives me and type mismatch error, which I think may come down to the date combo box on the form having 3 columns (only 1 is visible).
