Query Parameter from a form

[RayMilhon]

I have a query that uses a combo box on a form as a parameter. I've done this before with no issues however in Access 2010 I'm having an issue the parameter is coming across empty. I'm using [Forms]!form1!combo0 The combo box has 43 Items from a table and there are 2 items in the combobox the 2 fields are the primarykey and description the primary key length is set to 0 and the description only is shown. I've done this before with no issue. Anything else I can look at to solve this?

[ssanfu]

Is the column count set to 2 ?

[CJ_London]

Please confirm:

1. form1 is open when you run your query

2. your combo is called combo0 (better to give them meaningful names)

3. your combo is bound to the primary key column?

4. you have selected an item in the combo

5. you say the parameter is coming across empty - how are you determining that?

[RayMilhon]

Is the column count set to 2 ?

Yes it is. Column Count is 2 and the column sizes are set to 0;2.734

[RayMilhon]

Please confirm:

1. form1 is open when you run your query.
Yes

2. your combo is called combo0 (better to give them meaningful names)
Actually the combo is called cboPCPs. I was just being generic in my original post

3. your combo is bound to the primary key column?
No the combo uses a query to populate the choices. The form is used to generate a report that is based on a query that uses the selected item in the combo as the criteria so that the data is for a specific PCP.

4. you have selected an item in the combo
YEs

5. you say the parameter is coming across empty - how are you determining that?

Re-ran the query moving the criteria forms!frm_report!cbopcps to a column I get all of the pcps but the column for forms!formreport!cbopcps is blank. It should be the pcp's ID

[mike60smart]

Can you upload a copy of the database?

[RayMilhon]

Can you upload a copy of the database?

Unfortunately no. It contains Patient Information most of the tables are linked but not all. I can upload the query and the data structure if that would help.

[RayMilhon]

**UPDATE**
We do a Patient Wellness Exam annually for all Senior Patients. The first query gets those members that have completed the Exam during the current year
The second query gets all Senior Members that are not in the first query. The Report gets all Members that have not completed the exam in the current year by provider.
The Form allows us to run the report for a specific provider. We've been doing this for sometime but lately the Access Database has had performance issues. If I open the main Navigation Menu the database freezes for the entire day.
I don't know why it started doing that and I've been unable to fix it. There are a number of forms and functions in the original that are no longer used So I decided to redo the db and fix that performance issue.
Now it only has the 1 form for this report as it's the only report currently in production. But the report keeps coming up blank. Any help is appreciated.


SELECT dbo_RVS_PROV_COMPANY.REV_FULLNAME AS PRIMARY_CARE_PHYSICIAN, dbo_LOG_Data.pa, dbo_RVS_MEMB_COMPANY.LASTNM, dbo_RVS_MEMB_COMPANY.FIRSTNM, dbo_LOG_Data.memb_keyid, dbo_RVS_MEMB_COMPANY.MEMBID, dbo_RVS_MEMB_COMPANY.BIRTH, dbo_LOG_Data.DOS, dbo_LOG_Data.[Date Received], dbo_LOG_Data.[Blood Pressure], dbo_LOG_Data.[Valid/Invalid (Y/N)], dbo_LOG_Data.Coder, tbl_log_comments.Comment, dbo_LOG_Data.MDckreq, dbo_LOG_Data.OMckreq
FROM ((dbo_LOG_Data LEFT JOIN tbl_log_comments ON dbo_LOG_Data.Logid = tbl_log_comments.logid) INNER JOIN (dbo_RVS_MEMB_COMPANY INNER JOIN dbo_RVS_PROV_COMPANY ON dbo_RVS_MEMB_COMPANY.PROV_KEYID = dbo_RVS_PROV_COMPANY.PROV_KEYID) ON dbo_LOG_Data.memb_keyid = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs AS tbl_hpcodes ON dbo_RVS_MEMB_COMPANY.HPCODE = tbl_hpcodes.HPCODE
WHERE (((Year([DOS]))=Year(Now())) AND ((dbo_RVS_MEMB_COMPANY.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_COMPANY.PCPTHRUDT)>Now()) AND ((dbo_RVS_MEMB_COMPANY.OPTHRUDT) Is Null Or (dbo_RVS_MEMB_COMPANY.OPTHRUDT)>Now()))
ORDER BY dbo_RVS_MEMB_COMPANY.LASTNM, dbo_RVS_MEMB_COMPANY.FIRSTNM;











Qry for the report. The part bolded should be in the Where clause as dbo_current_membership.pcp = [forms]![frm_reports]![cbopcps] and will be removed from the select and group by statements as soon as I figure out what's going wrong.
and before you ask yes this works in the original but the form structure is different because there are 13 different forms all part of a navigation system. The new DB only has the 1 form.

SELECT dbo_CURRENT_MEMBERSHIP.PRIMARY_CARE_PHYSICIAN, dbo_CURRENT_MEMBERSHIP.rev_FULLNAME AS PATIENT, dbo_CURRENT_MEMBERSHIP.MEMBID, dbo_CURRENT_MEMBERSHIP.PCP, dbo_CURRENT_MEMBERSHIP.BIRTH, dbo_CURRENT_MEMBERSHIP.STREET, dbo_CURRENT_MEMBERSHIP.CITY, dbo_CURRENT_MEMBERSHIP.STATE, IIf(Len([zip])=9,Left([zip],5) & "-" & Right([zip],4),[zip]) AS zip2, dbo_CURRENT_MEMBERSHIP.PHONE, dbo_CURRENT_MEMBERSHIP.HEALTHPLAN, dbo_CURRENT_MEMBERSHIP.PCP, [forms]![frm_reports]![cbopcps] AS Expr1
FROM dbo_CURRENT_MEMBERSHIP LEFT JOIN Qry_mbrs_with_valid_assessments ON dbo_CURRENT_MEMBERSHIP.MEMB_KEYID = Qry_mbrs_with_valid_assessments.memb_keyid
WHERE (((dbo_CURRENT_MEMBERSHIP.LOB)="SENIOR") AND ((Qry_mbrs_with_valid_assessments.memb_keyid) Is Null))
GROUP BY dbo_CURRENT_MEMBERSHIP.PRIMARY_CARE_PHYSICIAN, dbo_CURRENT_MEMBERSHIP.rev_FULLNAME, dbo_CURRENT_MEMBERSHIP.MEMBID, dbo_CURRENT_MEMBERSHIP.BIRTH, dbo_CURRENT_MEMBERSHIP.STREET, dbo_CURRENT_MEMBERSHIP.CITY, dbo_CURRENT_MEMBERSHIP.STATE, IIf(Len([zip])=9,Left([zip],5) & "-" & Right([zip],4),[zip]), dbo_CURRENT_MEMBERSHIP.PHONE, dbo_CURRENT_MEMBERSHIP.HEALTHPLAN, dbo_CURRENT_MEMBERSHIP.PCP, [forms]![frm_reports]![cbopcps], DateDiff("m",[pcpFROMDT],Now()), dbo_CURRENT_MEMBERSHIP.ZIP, dbo_CURRENT_MEMBERSHIP.OPFROMDT, dbo_CURRENT_MEMBERSHIP.PCP
ORDER BY dbo_CURRENT_MEMBERSHIP.PRIMARY_CARE_PHYSICIAN, dbo_CURRENT_MEMBERSHIP.rev_FULLNAME;

[mike60smart]

Hi Ray

Sorry but I am one of those that needs to look at the actual table structures and Forms to work out these types of problems.
My reading of SQL is very poor.
I am sure that someone will be able to help.

[CJ_London]

I'm also struggling with the lack of useable information, and the problem with being generic is it just leads to confusion.

your combo is bound to the primary key column?

I was referring to the bound column property, not the controlsource

the fact your report keeps coming up null points to a data or join issue.

Your tables are prefixed with dbo which implies they are linked tables, linked to sql server. The structure of your queries implies they are access queries. So it may be something to do with your linked tables - had an odbc upgrade recently? or need to upgrade it?

I presume your second query is the recordsource to the report, no idea what the first query is about.

there is no point in ordering a query if it is a report recordsource. It takes time and the report will ignore it - it has its own way of sorting the data