Query Results on A Form Through Expression Builder

[kostaskir]

Hello everybody,

I created a query where it counts some rows and the WHERE Clause comes from an edit box on a form.

I count the number of the Cd’s in a Table “TableA” Where Artist = Forms!FormA!Textbox_Artist.

The Good News: The query works !! Every time the Form is active and I run the query, it gives me a result.

Now my problem is that I want to bring this result on the same Form. I used expression builder but it gives me #Name?

Expression Builder: =[CDCount_Per_Artist]![CountOfCDs]

I am desperate!!!

Any thoughts?

Thanx !!!

 

[KenHigg]

Here's a stab in the dark:

=forms![CDCount_Per_Artist]![CountOfCDs]

??

 

[kostaskir]

Dear Kenhigg,

Unfortunately it didn’t work. I would like to specify that CDCount_Per_Artist is the Query Name and CountOfCDs is the field from the query that gives me the result.

So, what I want is to connect this field “CountOfCDs “ with an edit box on a form called Artists.

Thank you so much for your help.

 

[DCrake]

Use

CDCount_Per_Artist is the Query Name and CountOfCDs
TextBox = Dlookup("CountOfCDs","CDCount_Per_Artist","Artist = '" & Me.Textbox_Artist & "'")

 

[kostaskir]

I guess it worked. It gives me back a number. Number 1. It is correct for the first Artists. I guess now I have to do something for refreshing every time I press Next Recordset.


Requery is the correct way for running my query every time I press next ?
Thanx.

 

[DCrake]

What you need is a query that groups by artist and counts Cd's. so what you end up with is a query that looks someting like

SELECT Artist, Count(1) AS Cnt
FROM TableA
GROUP BY Artist;

So this will give you a total for each artist.

Then in your form use the DLookup()

Me.txtTotal = Nz(DLookup("Cnt","TableA","Artist='" & me.txtartist & "'"),0)

David

 

[kostaskir]

The query worked fine but when I link it with vba I get the following error:

Run-time error: '3075'

Syntax error (missing operator) in query exxpression 'Artist = 3 Doors Down'.


I feel it, I am so close.....

 

[DCrake]

You need to wrap your criteria in quotation marks, as shown

 

[kostaskir]

I did exactly what you proposed

This is what I wrote :
Me.CDNum = Nz(DLookup("Cnt", "CD_Count_Artists", " Artist = '" & Me.Artist & " ' "), 0)


And the error message now is : Run-time error 3464:
Data type mismatch in criteria expression.

The query is correct & the VBA code looks correct.

 

[DCrake]

Post a copy of your mdb for me to look at

 

[kostaskir]

The Form is Artists & the Field is Total Cds:

THANK YOU SO MUCH

 

[DCrake]

Can you save it pre 2007, not got that installed on this pc at present.

 

[kostaskir]

The save us menu has the feature "Save us Access 2002-2003 Database" but it gives me the following error:

You cannot save this database in an earlier version.... because it uses features that require the current file format....


I guess I have to erase some features and then to save it on an older format.

Please give me 10 minutes...


Thanx.

 

[kostaskir]

2003 Version is ready

 

[DCrake]

Made minor changes:

The main jist was that your artists table is using a lookup for the artists name but it is the number that is being stored in the table. So your Dlookup was trying to comare number with text (data type mismatch) Have alook at the revised query and form for differences

David

 

[kostaskir]

Thank You So Much !
You Helped Me To Understand Much Better The Whole Concept ! :d