Access Help - Pulling data from Query into a form

[mela]

Access Help - Pulling data from Query into a form
We have a database that we keep track of all of our projects and the percentage they are complete. I have created a form that runs a query to pull up all the projects that are not yet complete based on the employee you choose. I now what to update this form to add up the hours of work remaining on each project, from the query and display the results back on the original form, but I have no idea how to access this data.

Here is the code I have to run the query now I want to add the additional code to access the query data and put it back into the form...any suggestions would be greatly appreciated....FYI I am very new to Access so I have no clue what I am doing! I would think there is some way to loop through the query to add up the values but I'm not sure about the codes??

Private Sub Run_AllDates_AllEmployees_Click()
On Error GoTo Err_Run_AllDates_AllEmployees_Click

Dim stDocName As String

stDocName = "Manpower_AllDates_AllEmployees"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Run_AllDates_AllEmployees_Click:
Exit Sub

Err_Run_AllDates_AllEmployees_Click:
MsgBox Err.Description
Resume Exit_Run_AllDates_AllEmployees_Click


End Sub

 

[wiklendt]

Access Help - Pulling data from Query into a form
We have a database that we keep track of all of our projects and the percentage they are complete. I have created a form that runs a query to pull up all the projects that are not yet complete based on the employee you choose. I now what to update this form to add up the hours of work remaining on each project, from the query and display the results back on the original form, but I have no idea how to access this data.

you can change your form to include a subform which houses the query results. so each time you choose an employee, the completed hours are automatically displayed for you.

you can the use a textbox in the main form which sums up the hours from the subform

i use the following code to total a field in a listbox control and display in a textbox whose control source is:

=SubTotalCost()

and then in the form module, you need the following code. perhaps you can adapt it for using a subform instead of a listbox...

Function SubTotalCost() As Variant
On Error GoTo Err_SubTotalCost
    
    Dim i As Integer, j As Integer, ctl As Control

    Set ctl = Forms![frmYourMainForm].Form![sfrmYourSubForm]![txtTheFieldYouWantTotalled]
    j = ctl.ListCount - 1
    
    SubTotalCost = 0
    For i = 1 To j 'for each row
        SubTotalCost = SubTotalCost + ctl.Column(5, i) 'base 0 for column count
    Next i
    
    SubTotalCost = Format(SubTotalCost, "currency")

Exit_SubTotalCost:
    Exit Function

Err_SubTotalCost:

    Msg = "Error # " & Str(Err.Number) & Chr(13) & " (" & Err.Description & ")"
    Msg = Msg & Chr(13) & "in Form_sfrmOrders_2Convers | SubTotalCost"
    MsgBox Msg, vbOKOnly, "RLS Order Records", Err.HelpFile, Err.HelpContext
    Resume Exit_SubTotalCost

End Function

 

[smig]

not realy sure why you jump into code writing.
you have this query (Manpower_AllDates_AllEmployees) ready as saved query, no ?
the easiest way will be to use the form wizard to create a form that will be based on this query.
if you don't like the wizard start a new form, in it's rowsource property put the query name, open the fields selector window and put the fields you want on the form.
make the form as continious form, or it will only show you the first record.

after you'r happy with the result you can make this form into a sub_form.
the main form is not bounded, and has no rowsource.
on the main form you will put this sub form, and an employee selector. the employee selector will have it's source from the employees table.
you link the sub form to the main form using the employee. so changing the employee on the main form employee selector will change the data presented in the sub form.

 

[wiklendt]

not realy sure why you jump into code writing.
you have this query (Manpower_AllDates_AllEmployees) ready as saved query, no ?
the easiest way will be to use the form wizard to create a form that will be based on this query.
if you don't like the wizard start a new form, in it's rowsource property put the query name, open the fields selector window and put the fields you want on the form.
make the form as continious form, or it will only show you the first record.

after you'r happy with the result you can make this form into a sub_form.
the main form is not bounded, and has no rowsource.
on the main form you will put this sub form, and an employee selector. the employee selector will have it's source from the employees table.
you link the sub form to the main form using the employee. so changing the employee on the main form employee selector will change the data presented in the sub form.

that's all well and good, but the OP wanted to total a field in that query. besides, my post went through and explained that a subform would be a good idea already - see the first line

 

[smig]

that's all well and good, but the OP wanted to total a field in that query. besides, my post went through and explained that a subform would be a good idea already - see the first line

I saw you'r post
but as I saw he wrote this I thought we better go into the basics

FYI I am very new to Access so I have no clue what I am doing

sometimes I try to search for help, and even tough I'm creating Access applications for 15 years (since Access2 ) it's hard for me to understand the examples cause people make things much complicated then needed
if you realy want to help put the most simple example and explanation you can. advanced users will have no problem going from there.

 

[wiklendt]

I saw you'r post
but as I saw he wrote this I thought we better go into the basics


sometimes I try to search for help, and even tough I'm creating Access applications for 15 years (since Access2 ) it's hard for me to understand the examples cause people make things much complicated then needed
if you realy want to help put the most simple example and explanation you can. advanced users will have no problem going from there.

good points. i often forget how little someone can know about access and i think it's worse coz i still consider myself a newbie :-/

mela, i would suggest you first get your setup working as smig explains in post 3, then go to post 2.

another option for you, instead of that code, is to use this source in the textbox control that you want to show your total - i don't remember if this works for subforms, but it's worth a shot:

=sum(Forms![frmYourMainForm].Form![sfrmYourSubForm]![txtTheFieldYouWantTotalled])

 

[wiklendt]

(or you could use the search facility on the forums and find a solution there - this questions gets asked at least once a week here).

 

[smig]

put the sum field on the header/footer of your sub form.
it's rowsource should look like :
Sum(TheFieldIWantToSum)

and don't forget to enable showing this header/footer.

BTW - a very basic trick for you:
if you put your Key field on the header/footer it will show the currently selected record in a continious form. this is very very usefull

 

[wiklendt]

put the sum field on the header/footer of your sub form.
it's rowsource should look like :
Sum(TheFieldIWantToSum)

and don't forget to enable showing this header/footer.

BTW - a very basic trick for you:
if you put your Key field on the header/footer it will show the currently selected record in a continious form. this is very very usefull

i think this would only work in a continuous form scenario as headers and footers of forms don't display when form is in datasheet view. you could, i suppose, try to reference that sum field anyhow from a textbox control on the main form (this is if you decide you prefer datasheet view - i normally like datasheet view over continuous form b/c of the ease of creating a datasheet view form - of course, you can DO lots more with a continuous form, but they can get complex - not that SUM is complex...).

 

[smig]

I never use datasheet view, so I have no idea if you can see the header/footer or not
I don't like users to be able to change the width of a column and after that calling me for help to show this column again. and though it require more work I much prefer the ability to control exactly how the form will look like.
if you prefer the data sheet style you can also use a list box that will show all the columns, and save yourself a sub form.