Get Avg from a field in a secondary table 2

[kelfuego]

Hello all, Hopefully someone might have an idea of how to help me.

I have a form that track rare coins. The main form is used to track the number of coins graded at specific values (for a specific instance of one coin).

I have a form that has been bound to the main table. the user chooses a coin from a listbox to display record information about a particular coin.

In another table I've captured information about the sale of a specific coin. I'd now like to display the avg sales price at each graded value on the main form.

So Here is what I think I need to do to get what i want

1. need to create a connection string to the db
2. create a Recordset to pull the data based on the value in the listbox.
3. loop through data to get a count of sales entries and a total dollar amount for each value.

I'm very new to working with ADODB so any help that I could get would be greatly appreciated.

Kelly Johnson MCP
Central City Concern

 

[TheAceMan1]

How are ya kelfuego . . .

Have a look at the [purple]DAvg[/purple] domain aggregate function!

See Ya! . . . . . .

 

[kelfuego]

Ok that helps a bit, but I'm still struggling with it. I created a test item to see if I could make it work here is what I have.

Dim LTotal As Currency

LTotal = DAvg("[salesprice]", "sold", "[variety]" = "bg-765" And "[grade]" = "64")
test.Text = LTotal

I plan to replace the variety field with a value from a list box.

Kelly Johnson MCP
Central City Concern

 

[kelfuego]

Whoops unfortunately I'm getting a run time error invalid use of a null.

just a fyi


In my table:

My variety field is a text field
My grade field is a integer
my salesprice is a currency field

Kelly Johnson MCP
Central City Concern

 

[PHV]

[tt]LTotal = DAvg("salesprice", "sold", "variety='" & "bg-765" & "' And grade=64")[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kelfuego]

Thank you so much for your help


Kelly Johnson MCP
Central City Concern

 

[kelfuego]

One other question, using this method I get an error message on records that do not have sales info --ie ltotal is null.

how would I handle this problem using this function?


Kelly Johnson MCP
Central City Concern

 

[PHV]

LTotal = Nz(DAvg("salesprice", "sold", "variety='" & "bg-765" & "' And grade=64"), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kelfuego]

Thanks again

Kelly Johnson MCP
Central City Concern

 

[kelfuego]

Ok, to continue my grief on this subject--- I'm now getting a Run-time error '2115' error (the macro or function set to the beforeupdate or validationRule property for this field is preventing Access from savin the data to the field."

Here's my code.

Private Sub average()

Dim varValue As String

    Variety.SetFocus
    varValue = Variety.Text
    
    
    txtvg.SetFocus
    txtvg.Text = Nz(DAvg("salesprice", "sold", "variety='" & varValue & "' And grade <40"), 0) 

End sub

Kelly Johnson MCP
Central City Concern

 

[BoxHead]

I think the issue right now is just the order of events firing.

If you run the sub setting the text property on the before update event, I don't think the Value of txtvg has been saved.

I'd try simplifying it to:

    txtvg = Nz(DAvg("salesprice", "sold", "variety='" & varValue & "' And grade <40"), 0)

or move the sub to the After Update event of the Variety textbox.


HTH


John





When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[kelfuego]

Actually I have the form recordset being controlled by a list box. (the variety value doesn't change) When the user clicks on an item the fields are pulled based on that selection. This like I said is in a click event, not an update event. at here is the code:

Private Sub list0_Click()

Me.RecordSource = "SELECT * FROM coins " & "WHERE Id=" & Me.List0
Call average

End Sub

Kelly Johnson MCP
Central City Concern