I have a a form with a combobox that is supplied by the table "tblCourses". It is provided with a list of course names
The report that is opened after one is selected, looks at all employees who are currently trained in that course.
In the criteria section, I have (under Course Name) - [Forms]![frmTrainingLookUp]![NameofCourse]
However, when I run the report, no data shows up what so ever.
As an additional bit of troubleshooting, if I remove the criteria, it returns data, seemingly at random (i.e. it randomly selects a course to return) as well as all employee training information (i.e. not only for the selected course)
While it may not be the best way to do it, I found a solution...
I passed the value from the combo box to an invisible textbox on the form, and set the criteria to use the value from the textbox intsead.
8 1374
We'd have to see the code.
Hi Rabbit,
What code is it you'd have to see?
Here's the code to call open the report: - Private Sub Command22_Click()
-
On Error GoTo Err_Command22_Click
-
-
DoCmd.OpenReport "rptEmployeesCurrent", acViewReport
-
-
Exit_Command22_Click:
-
Exit Sub
-
-
Err_Command22_Click:
-
MsgBox Err.Description
-
Resume Exit_Command22_Click
-
-
End Sub
-
Anything else you need to see?
- SELECT tblPeople.[PeopleSoft Number], tblPeople.[First Name], tblPeople.[Last Name], tblPeople.Department, tblPeople.Picture, tblPeople.Shift, tblCourses.ID, tblCourses.CourseName, tblCourses.[Renewal Period], tblCourses.Category, tblTrained.[Training ID], tblTrained.[Date Trained], IIf([Renewal Period]=0,DateSerial(Year([Date Trained])+100,Month([Date Trained]),Day([Date Trained])),IIf([Renewal Period]<1,DateSerial(Year([Date Trained]),Month([Date Trained])+[Renewal Period]*12,Day([Date Trained])),DateSerial(Year([Date Trained])+[Renewal Period],Month([Date Trained]),Day([Date Trained])))) AS [Renewal Due]
-
FROM tblCourses INNER JOIN (tblPeople INNER JOIN tblTrained ON tblPeople.[PeopleSoft Number] = tblTrained.[Peoplesoft Number]) ON tblCourses.ID = tblTrained.[Training ID]
-
WHERE (((tblCourses.CourseName)=[Forms]![frmTrainingLookup]![NameofCourse]) AND ((IIf([Renewal Period]=0,DateSerial(Year([Date Trained])+100,Month([Date Trained]),Day([Date Trained])),IIf([Renewal Period]<1,DateSerial(Year([Date Trained]),Month([Date Trained])+[Renewal Period]*12,Day([Date Trained])),DateSerial(Year([Date Trained])+[Renewal Period],Month([Date Trained]),Day([Date Trained])))))>Date()))
-
ORDER BY tblPeople.[PeopleSoft Number] DESC;
-
-
Aside from the course name, you have a complicated nested iif condition, what is that supposed to do?
The nested iif does the following:
Calculates the date that the course needs to be renewed based upon the courses "renewal period" (which is a number 0-2, depending on how often the employee must take a course)
In general:
If the number is 0, add 100 years as the course never has to be retaken
If the number is between 0 and 1, multiply it by 12 and add it to the month.
If the number is 1 or 2, add it to the year.
In practice, this works well.
The report will populate as long as I don't have criteria under the Course Name.
While it may not be the best way to do it, I found a solution...
I passed the value from the combo box to an invisible textbox on the form, and set the criteria to use the value from the textbox intsead.
Glad you found a solution.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Steve |
last post by:
I am using Access 2000 and I have a DB that is currently running four
different queries from some Forms and subforms. These queries each
have a date field that are seperate from each other that are...
|
by: Matt Alanzo |
last post by:
On another newsgroup an Access knowledgable party posted:
>You should be able to connect an Access ADP to an existing SQLExpress
>database running in SQLS 2000 compatibility mode. The only thing...
|
by: dmckee |
last post by:
hello, I have a access 2003 application that uses the sql server for
the database the application has an extension of adp. I have wrote
many reports from views as the recordset and all works fine...
|
by: nepifanio |
last post by:
Hi Guys,
I'm trying to import data out of MySQL db to MS Access 2003 using
ImportXML. I don't have any problems doing the import, except for one
table with a "text" field defined. I'm getting...
|
by: keliie |
last post by:
Hello
Just a quick question that I've spent a few hours trying to solve with
no luck (although one would think this should be fairly easy).
I have a form with a subform. The subform is based...
|
by: vishwa Ram |
last post by:
Hi all,
I want sql query to rename a tablename from 'tbl1' to 'table1' globally.
My DB tables
tbl1,2,3......
Going to Rename
table1,2,3.....
|
by: DrJarmin |
last post by:
Hello
The problem is this: in the criteria for a list box I reference the
parent form - and Access KEEPS changing the criteria for one that
won't work. Details below:
I have a couple of list...
|
by: Chad Brewer |
last post by:
I have a form that has two tabs. One is called New Request and the second tab is called Follow Up. Each of these tabs have fields on it that come from a query that has linked two tables (New...
|
by: Blake Rice |
last post by:
Hi everyone! This is my first time posting here, so here goes.
THE SITUATION:
I have a form that has a start date, end date, project number, and total volume. These values are pulled from another...
|
by: Louise Green |
last post by:
Hi,
I need to create a query so that when a user logs into the database a form pops up showing them a list of work that will need to be updated in the next two months?
I have a bit of knowlege...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |