You are here:

Using MS Access/Response to Query Problem in Previous Question

Advertisement


Question
QUESTION: Hi, Scott.  You asked me about the bolded double quotes in the SQL I posted and no, they are not there.  I just stuck them in in case IE decided to run SQL in the middle of my response. (Yes, I am very, very confused when it comes to languages.)

Anyway, the Analyst Name tag is text.  Does that make a difference?  The line is----  strCriteria = "[Analyst Name] = " & Me.cboAnalyst  
...and the error is "Object doesn't support this property or method"

ANSWER: Yes that makes a difference. But I need some more info. I need to know the Rowsource for cboAnalyst and the Bound column.

The thing is if cboAnalyst is returning a text value, then the code should be:

strCriteria = "[Analyst Name] = '" & Me.cboAnalyst & "'"

The error message is inexplicable.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Well, you got me a little farther. When I checked the rowsource, I saw the name was wrong.  When I call it to populate the combo box, it's off a picklist with 'Task Owner'
RowSource=
SELECT [PickList-TaskOwner].[ID], [PickList-TaskOwner].[Task Owner] FROM [PickList-TaskOwner] ORDER BY [Task Owner];

I added the extra ' and the & and it ran but asked for the Task Owner so won't pull up the name.  How can I change the Task Owner to Analyst Name (which is what it's called on tables and reports)?  Is there a way to change something's field name in Access?

ANSWER: That's not the problem. I'll bet the Bound column for the combo is 1. If so, the combo is returning the ID value, not the name. This is SOP and it should be that way.

In the table you are querying, you may have a field called Analyst Name, but you shouldn't. You should have a field for AnalystID which should contain the ID value from Picklist-TaskOwner. This is how relational databases work. And AnalystID should match the datatype of ID in Picklist-TaskOwner. So we need to get that straightened out.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: I messed the SQL up when I created it.  I named the combox [Analyst Name] but used cboAnalyst as the name for the button instead.  Now I've gone in and corrected that so the SQL is as shown:
Private Sub cboAnalyst_Click()
Dim strCriteria As String
If IsNull(Me.[Analyst Name]) Then
  strCriteria = ""
Else
  strCriteria = "[Task Owner] = "" & Me.[Analyst Name] & """
End If
DoCmd.OpenReport "Ad Hoc Reporting", acPreview, , strCriteria
End Sub

I still get the box that asks for task owner and when I click OK without putting anything in there, it opens the report with no data.

The table I get the data from (###Master_Data###) can't be changed during work hours, I have about 30-40 users at any time in the database, so I'm not sure what to do about the AnalystID matching the TaskOwnerID.  I just created a copy of the table, naming it ReportMasterData but just matched it, didn't link it so people could still work. I thought I could have the table populated from the original master data one.  Is that possible?  Was this the right way to go or do you have a better idea?

Answer
You don't need to change anything. What you need to do is find out the correct name for the Foreign Key in your Master_data table. If its prompting for Task Owner then Task Owner is not the correct name.

But that still doesn't solve the problem of how you are storing data. If you have a lookup table for Task Owner (which you seem to have) and that has an autonumber PK (which it seems to have), and you are returning that value in your combobox (which you seem to be doing) then the Task Owner field in your main table (whatever its name) needs to match the datatype of the ID field returned by the combo.

Remember, I can't see what you see, so you have to answer my questions (the bound column of the combo?) and describe things fully.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.