Hallo friends,
need help for following problem.

i have a form where i get the details of particular Combobox. Pic 2.jpg attached. here on right side i got few textboxes say txt1..txt2 .... txt7.

on left side of this jpg i got the details of a particular project number.

when you see 3.jpg its an access table with details of different projects. under taetigkeit field there are different fields with corresponding values for Stunden field.

now when i select a particular Project to show me the details this 2.jpg form appears with all the details corresponding to this project.
now i need the total stunden for each taetigkeit to be calculated and fill these txt1 txt2....so on till txt7. these txt1 till txt7 are these taetigkeit.

ex. ProjectNr : TR36
txt1 should be = 9
txt2 = 6
txt3 = 3
txt4 = 0 coz there are no Stunden for this field
txt5 = 12
txt6 = 0
txt7 = 0

please friends..need help for this..
how does this syntax looks like for commandbox click event.

thanks-singoi

First initialize all text boxes from txt1 to txt7 = ""
Then get the data from table using a query for sum of Stunden for different Taetigkeit and populate the textboxes.

Private Sub Combo1_Click()

    Dim rs As New ADODB.RecordSet

    On Error GoTo Combo1_Click_Error

    'This statement calls the Procedure to clear the textboxes.
    Call ClearTextBoxes

'Open the recordset where ProjektNr is 
'equal to the ComboBox Selection
'Here con is the Database Connection Object I have used.
'U have to replace with the one which u r using

    rs.Open " SELECT STUNDEN.Taetigkeit, SUM(STUNDEN.Stunden) as TotStunden FROM STUNDEN WHERE STUNDEN.ProjektNr = '" & Combo1.Text & "' GROUP BY STUNDEN.Taetigkeit ", con, adOpenKeySet, adLockReadOnly

    While Not rs.EOF()
        Select Case rs!Taetigkeit
                Case "Technische Bearbeitung"
                    txt1.Text = rs!TotStunden
                Case "CAD Erstellungen"
                    txt2.Text = rs!TotStunden
                Case "Berechnungen"
                    txt3.Text = rs!TotStunden
                Case "Besprechung"
                    txt4.Text = rs!TotStunden
                Case "Verwaltung"
                    txt5.Text = rs!TotStunden
                Case "Bauleitung"
                    txt6.Text = rs!TotStunden
                Case "Montage"
                    txt7.Text = rs!TotStunden
        End Select
        rs.MoveNext
    Wend
    If txt1.Text = "" then txt1.Text = "0"
    If txt2.Text = "" then txt2.Text = "0"
    If txt3.Text = "" then txt3.Text = "0"
    If txt4.Text = "" then txt4.Text = "0"
    If txt5.Text = "" then txt5.Text = "0"
    If txt6.Text = "" then txt6.Text = "0"
    If txt7.Text = "" then txt7.Text = "0"


Combo1_Click_Done:
    If Not rs Is Nothing Then If rs.State then rs.Close
    Set rs = Nothing
    Exit Sub

Combo1_Click_Error:
    MsgBox Err.Number & " : " & Err.Description
    Resume Combo1_Click_Done

End Sub

'This function is to clear the 7 Text Boxes
Private Sub ClearTextBoxes()

    On Error Resume Next

    txt1.Text = ""
    txt2.Text = ""
    txt3.Text = ""
    txt4.Text = ""
    txt5.Text = ""
    txt6.Text = ""
    txt7.Text = ""

End Sub

First initialize all text boxes from txt1 to txt7 = ""
Then get the data from table using a query for sum of Stunden for different Taetigkeit and populate the textboxes.

Private Sub Combo1_Click()

    Dim rs As New ADODB.RecordSet

    On Error GoTo Combo1_Click_Error

    'This statement calls the Procedure to clear the textboxes.
    Call ClearTextBoxes

'Open the recordset where ProjektNr is 
'equal to the ComboBox Selection
'Here con is the Database Connection Object I have used.
'U have to replace with the one which u r using

    rs.Open " SELECT STUNDEN.Taetigkeit, SUM(STUNDEN.Stunden) as TotStunden FROM STUNDEN WHERE STUNDEN.ProjektNr = '" & Combo1.Text & "' GROUP BY STUNDEN.Taetigkeit ", con, adOpenKeySet, adLockReadOnly

    While Not rs.EOF()
        Select Case rs!Taetigkeit
                Case "Technische Bearbeitung"
                    txt1.Text = rs!TotStunden
                Case "CAD Erstellungen"
                    txt2.Text = rs!TotStunden
                Case "Berechnungen"
                    txt3.Text = rs!TotStunden
                Case "Besprechung"
                    txt4.Text = rs!TotStunden
                Case "Verwaltung"
                    txt5.Text = rs!TotStunden
                Case "Bauleitung"
                    txt6.Text = rs!TotStunden
                Case "Montage"
                    txt7.Text = rs!TotStunden
        End Select
        rs.MoveNext
    Wend
    If txt1.Text = "" then txt1.Text = "0"
    If txt2.Text = "" then txt2.Text = "0"
    If txt3.Text = "" then txt3.Text = "0"
    If txt4.Text = "" then txt4.Text = "0"
    If txt5.Text = "" then txt5.Text = "0"
    If txt6.Text = "" then txt6.Text = "0"
    If txt7.Text = "" then txt7.Text = "0"


Combo1_Click_Done:
    If Not rs Is Nothing Then If rs.State then rs.Close
    Set rs = Nothing
    Exit Sub

Combo1_Click_Error:
    MsgBox Err.Number & " : " & Err.Description
    Resume Combo1_Click_Done

End Sub

'This function is to clear the 7 Text Boxes
Private Sub ClearTextBoxes()

    On Error Resume Next

    txt1.Text = ""
    txt2.Text = ""
    txt3.Text = ""
    txt4.Text = ""
    txt5.Text = ""
    txt6.Text = ""
    txt7.Text = ""

End Sub

Thankssssssssssss....working....
thanks my friend

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.