Help please with Forms (Lost two days with that :(")

[Melhem]

Hello
Please help,
I have this table ‘Source_RM’:
Source.......Raw Material.....Waste Date..... Waste Quantity
Store ..........Sugar.............13/06/2013.................10
Store...........Salt...............12/06/2013.................13
Production.....Sugar............17/06/2013.................10
Vendor..........Sugar............15/06/2013.................10

I have a main form where the client can perform queries passed on controls (text box, combo box ..)
In the main form I need to display a query result passed on combo box value:
The result wanted: the client choose the source from a combo box so the result will be a table contain the source and total waste i.e.:

Source ...Total
Store.......23

What I did is:
1. Created a form (“Blank Form”) name it: frmResult
2. In the main form I added Subform, In subform wizard in use an existing form I chose ‘frmResult’
3. After I choose the source from ‘cmdSource ‘ and click the button’ cmdFilter’
4. I wrote this code in VBA :

Private Sub cmdFilter_Click()
Me.FormTemp.Form.RecordSource = "SELECT Source_ID, SUM(Quantity) AS Total FROM Source_RM WHERE Source = ' " & Me. cmdSource.Value & " ' GROUP BY Source_ID"
End Sub

After I click the button the FormTemp has the RecordSource (I know from the record number at the bottom bar’ 1 of 2’) but the FormTemp still blank .. I need to show the result of query in FormTemp
Note : Date entry in FormTemp is set to NO.

Please any Ideas

 

[CJ_London]

Me.FormTemp.Form.RecordSource = "SELECT Source_ID, SUM(Quantity) AS Total FROM Source_RM WHERE Source = ' " & Me. cmdSource.Value & " ' GROUP BY Source_ID"

You don't need the .Value. Also, what is the rowsource for cmdSource - if it is something like

SELECT Source_ID, Source From tblSources

and you are trying to link on source then you need to set the bound column to 2.

 

[pr2-eugin]

Melhem, First Welcome to AWF..

I think I am a bit lost with your description.. You set the Recordsource for FormTemp, but there is not any description of such form in existence before that... Make sure you refer to the Form by its right name..

I know this sounds a bit stupid but did you add the fields to be displayed? As you did mention a BLANK form.. You did not just leave it blank did you?

 

[Melhem]

You don't need the .Value. Also, what is the rowsource for cmdSource - if it is something like

SELECT Source_ID, Source From tblSources

and you are trying to link on source then you need to set the bound column to 2.

Thank you CJ_London
I meant by cmdSource is combo box with values : Store , Production , Vendor, and no problem in this part .

My problem is who to display the records.

 

[Melhem]

Melhem, First Welcome to AWF..

I think I am a bit lost with your description.. You set the Recordsource for FormTemp, but there is not any description of such form in existence before that... Make sure you refer to the Form by its right name..

I know this sounds a bit stupid but did you add the fields to be displayed? As you did mention a BLANK form.. You did not just leave it blank did you?

The name of Blank form I created is FormTemp
this FormTemp will be used in the main form to display query result

I attached a photo to clarify

Thank you

 

[pr2-eugin]

Just as I thought it might be..

Well there is no Control like a Text box or ComboBox or ListBox for the Results of the Recordset to be displayed into..

The Form is Loaded with the Records whihc is why you see the Recordcount as 1 or 100 but if there are no Controls then your data cannot be displayed; to show them you need to add those fields .. Create two TextBox controls and set their control source to be SOURCE_ID and Totals..

 

[Melhem]

Just as I thought it might be..

Well there is no Control like a Text box or ComboBox or ListBox for the Results of the Recordset to be displayed into..

The Form is Loaded with the Records whihc is why you see the Recordcount as 1 or 100 but if there are no Controls then your data cannot be displayed; to show them you need to add those fields .. Create two TextBox controls and set their control source to be SOURCE_ID and Totals..

Thank you pr2-eugin .. sorry I'm new on this but I'm wondering:

In my case the result is one record .. what If the query returns 30 record ?
There is no control that can display the query result as a table ??

Thanks again

 

[pr2-eugin]

In your case it would never happen as the Result will always be Grouped by as per your selection.. So the set will be One record..

But if in case it comes up with 30, then you need to change the Form view to be Datasheet to get the table like view..

 

[Melhem]

In your case it would never happen as the Result will always be Grouped by as per your selection.. So the set will be One record..

But if in case it comes up with 30, then you need to change the Form view to be Datasheet to get the table like view..

Hi again
I changed the Form view to be Datasheet and the same problem
I attached a photo to clarify

Thanks

 

[pr2-eugin]

Okay, for the first time to initialize the controls, set the Form's recordsource manually and link the controls with the appropriate fields, then you can add the fields.. Once the fields are added then you can see them..

 

[Melhem]

Okay, for the first time to initialize the controls, set the Form's recordsource manually and link the controls with the appropriate fields, then you can add the fields.. Once the fields are added then you can see them..

Sorry I didn't get it :banghead:
I know this is stupid but can you tell step by step how to display query result built based on controls on form!!

Thanks a lot

 

[pr2-eugin]

Okay here it goes, create the Query, as CJ has shown.. Give it a name and save it.. Then use that Query as the recordsource.. Then go to the Form design properties, and there should be an option "Add existing fields", add the fields you wish to appear on the Form, then you are good to go..