How to Reference Multiple Tables/Queries to one Form 1

[IanNav]

Hi,

I have an Access Database that I am playing around with, I need some help to display information I have generated via queries.

I am fairly new to access itself (so please be patient), but I do know VB ok. I mainly work with PICK databases though and am getting frustrated not knowing the access tools.

Here is the Scenario.

I have 2 Queries that produce a result when a Change() event is forced on a combo box (i.e. depending on the text in the combo box, the query returns different information, in this case “Media Source”).

I would like to show the results of these 2 queries data field in textboxes on the form.

so....

Qry1 fields (this is done sorted by date and selected TOP 5 then averaged on records that match the media source from the Combo box)
-----------
Average_Sales_Val
Average_Sales_Qty
Average_Media_Cost
Average_Media_AR


Qry2 fields (this is done sorted by date and selected TOP 1 on records that match the media source from the Combo box)
-----------
MostRecent_Sales_Val
MostRecent_Sales_Qty
MostRecent_Media_Cost
MostRecent_Media_AR


Both of these work off the same Master table with contains lots of fields (I’ll just list the appropriate ones)

Tab1 fields
-----------
Sales_Val
Sales_Qty
Media_Cost
Media_AR


On the form I want to display every field in qry1 & qry2 individual txt box, but when I set the record source for the form to be both of these queries the form goes blank when I try and run it.

I have tried to figure out the VBA code to do the same, but it still doesn’t look right.

Any Ideas?


Also : something I don’t know about? What is the Detail object on the form? What purpose does it have?

Cheers

Ian

 

[PHV]

Why not using 2 ListBox bounded (RowSource) to each query ?

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

 

[IanNav]

Hi,

Thanks for the reply.

I've done that and it works (ish), but its not very eligant. I don't mind using VBA if there is a better way.

Can i ask why a listbox?

is it becuase that it is the only way you can directly bind a specific field from a query to a control on the form (with the row source property).

Ideally I would like to do it using a textbox or Label... is this possible?

Thanks

Ian

 

[PHV]

Take a look at the ControlSource property and the DLookUp function.

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

 

[IanNav]

Thank you, i believe the Dlookup function is what i was looking for.

Are there any dangers i need to look out for when using this command?

Thanks again

 

[PHV]

This function is not known to be fast ...

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

 

[IanNav]

Ok,

I've tried dLookup and it works ok in the code, going to a label caption.

When i try to bind it to a control source on a textbox it doesn't like it, it is equalling the control source to the literal value of the result of the dlookup

eg.

Dim ActMedCost As Variant

ActMedCost = DLookup("[AvgOfMediaCost]", "qryTop5ActualAvg")

Form_frmBooking.txtActMedCost.ControlSource = ActMedCost

This equals = 1192

When i look in the Control source box 1192 is in there and it then says it can't reference 1192...?

also if i try to equate the value the .text property it doesn't like it becuase it is readonly as (after setting the focus manually to it as well...).

How hard is it to say text1.text = "BLAH"!!!

Another problem i thought about using dlookup is the overheads... I will be doing around 20 dlookups on change of the value of the combobox, that surely is gonna slow things down.

The next thing i could do with a 'point in the right direction' is creating a new recordset (results from a pre-saved query called 'qryTop5') in VBA then extracting out each field and populating each textbox (if i can get the .text property to work!) or label (using .caption, which i don't like).

Can you tell me how i would do this?

would i use a docmd.openquery() if so how do i populate my new recordset with these results.

Sorry to bombard you, you just seem to know alot about this, and you are so fast to reply.

If you want i am on msn all day ian_navran@hotmail.com - feel free to msgme if you need clarification.

Many Thanks

Ian

 

[PHV]

Have you tried this ?
Form_frmBooking.txtActMedCost.ControlSource = "=DLookup(""[AvgOfMediaCost]"", ""qryTop5ActualAvg"")"

The .Text property is available only when the control has the focus, use the .Value property instead.

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