Hi I am having trouble pulling information from a MS access table and populating a report:
I have a form called "frmFindApplicant" with a field called "Applicants Name" the main table of contents which has valid data is a table called "tblApplicantList" with a column matching "Applicants Name" and I'm trying to pull data from that table for a report that has field names that match that of the main table. It comes up blank in report. The SQL code being used is:
SELECT tblApplicantList.[Applicants Name], tblApplicantList.[Telephone Number], tblApplicantList.Position, tblApplicantList.Location, tblApplicantList.Field1.FileData, tblApplicantList.[Issue Date]
FROM tblApplicantList
WHERE (((tblApplicantList.[Applicants Name])=[Forms]![frmFindApplicant]![Applicants Name]));
can anyone see anything wrong with this query? I am a newbie so sorry for my explanation.
I never hard code a filter like that. You can use everything but the WHERE clause as the source for your report, and then pass the filter part in the OnOpen event of the report, or with a button. One of the arguments you can pass is a where clause. (see DoCmd.OpenReport).
Also, if your listbox is set to multi-select (well, anything other than Single), you have to loop through the ItemsSelected collection and create the filter in code because it's a collection and not a simple value. If the user can only ever select a single item, I'd just use a combobox, which you can use directly.
SELECT ... FROM ... WHERE SomeField = Forms!MyOpenForm!cboPickAValue
Or just pass [SomeField] = Forms!MyOpenForm!cboPickAValue in the where clause like I said before.
listboxes are tricky unless you really understand what you're doing and know VBA. (And even then they can be tricky.)
March 17, 2022 at 2:52 am
Thanks now project has been scrapped....indecisive staff 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply