access to Datagrid...show in DBGrid
 

Hallo Friends,

I need some help from you.

i am hereby attaching some files of my project. in 1.jpeg after clicking add button the data will be automatically written in access table shown in 2.jpeg.

this is the code for add button.
-----------------------------------------------------------------------------------------
Private Sub cmdadd_Click()

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim ss As New ADODB.Recordset
Dim tt As New ADODB.Recordset
Dim sConn As String

Dim cnt As Long
Dim tmp As String

cmdadd.Enabled = False
cmdcnc.Enabled = False
cmdclear.Enabled = True

For cnt = 1 To 100000

ProgressBar1.Visible = True
ProgressBar1.Value = cnt

'needed to trap cancel click
DoEvents

Next

'ProgressBar1.Value = 100000
ProgressBar1.Visible = False
lblpro.Visible = True

Dim sPath As String
sPath = "C:\Dokumente und Einstellungen\singava\Eigene Dateien\Eigene Programme\projekt.mdb"

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
db.ConnectionString = sConn
db.Open
rs.ActiveConnection = db
rs.LockType = adLockOptimistic
rs.CursorType = adOpenKeyset

ss.ActiveConnection = db
ss.LockType = adLockOptimistic
ss.CursorType = adOpenKeyset


tt.ActiveConnection = db
tt.LockType = adLockOptimistic
tt.CursorType = adOpenKeyset


rs.Open "Projekt" '** tableName is ur table

rs.MoveFirst
'If rs.EOF = True Then

rs.AddNew
rs.Fields.Item(1) = txtbn.Text
rs.Fields.Item(2) = txtdt.Text
rs.Fields.Item(3) = txtldt.Text
rs.Fields.Item(4) = txtpn.Text
rs.Fields.Item(5) = txtbb.Text
rs.Fields.Item(6) = txtst.Text
rs.Fields.Item(7) = txttb.Text
rs.Fields.Item(8) = txtcad.Text
rs.Fields.Item(9) = txtbr.Text
rs.Fields.Item(10) = txtbs.Text
rs.Fields.Item(11) = txtvw.Text
rs.Fields.Item(12) = txtbl.Text
rs.Fields.Item(13) = txtmt.Text
rs.Fields.Item(14) = txtge.Text
rs.Update
rs.Close
Set rs = Nothing

ss.Open "Stundensatz" '** Table name for Stundensatz

ss.MoveFirst
'If ss.EOF = True Then

ss.AddNew
ss.Fields.Item(1) = txtbn.Text
ss.Fields.Item(2) = txttbss.Text
ss.Fields.Item(3) = txtcadss.Text
ss.Fields.Item(4) = txtbrss.Text
ss.Fields.Item(5) = txtbsss.Text
ss.Fields.Item(6) = txtvwss.Text
ss.Fields.Item(7) = txtblss.Text
ss.Fields.Item(8) = txtmtss.Text
ss.Update
ss.Close
Set ss = Nothing


tt.Open "Taetigkeit" '** Table name for Stundensatz

tt.MoveFirst
'If tt.EOF = True Then

tt.AddNew
tt.Fields.Item(1) = txtbn.Text
tt.Fields.Item(2) = txttbs.Text
tt.Fields.Item(3) = txtcads.Text
tt.Fields.Item(4) = txtbrs.Text
tt.Fields.Item(5) = txtbss.Text
tt.Fields.Item(6) = txtvws.Text
tt.Fields.Item(7) = txtbls.Text
tt.Fields.Item(8) = txtmts.Text
tt.Update
tt.Close
Set tt = Nothing

db.Close
Set db = Nothing

'End If
'End If
'End If

End Sub
--------------------------------------------------------------------------------------------------

when u see in 2.jpg the first ID value in something like 1111111115 which should be 4. and simultaneously some of the data from this form is also saved in other tables which r shown in following 3 and 4 jpgs.. ID values are have been wrong.

*********************************************************************

now second problem:
in 5.jpg we have a form with few combo boxes.

this is the code for 5.jpg

Private Sub Form_Load()

Dim db As New ADODB.Connection
Dim se As New ADODB.Recordset

Dim sSQL As String 'your SQL Statement
Dim sConn As String

frmstd.Height = 6360
frmstd.Width = 17925

dg1.Visible = True
dg2.Visible = False
dg1.Height = 4140
dg1.Width = 8670
dg1.Top = 1425
dg1.Left = 9025

Adodc1.Refresh
Set dg1.DataSource = Adodc1

cmdadd.Enabled = False
cmdneu.Enabled = False
txtstd.Text = ""
txttbes.Text = ""

Dim pnl As Panel
Dim btn As Button
Dim x As Long

'create statusbar

pb2.Visible = False

With pb2
.Min = 0
.Max = 100000
.Value = .Max
End With


Dim sPath As String ' here we put the path of your database
sPath = "C:\Dokumente und Einstellungen\singava\Eigene Dateien\Eigene Programme\projekt.mdb"

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
sSQL = "SELECT * FROM Projekt" '***testTableName is your table name
db.ConnectionString = sConn
db.Open
se.ActiveConnection = db
se.LockType = adLockOptimistic
se.CursorType = adOpenKeyset
se.Open sSQL
se.MoveFirst
While (Not se.EOF)
cmbp.AddItem se("ProjektNr") & ""
cmbp.ItemData(cmbp.NewIndex) = Val(se("ProjektNr") & "")
cmbp.Text = ""
se.MoveNext
Wend
se.Close
Set se = Nothing
db.Close
Set db = Nothing

cmbn.Clear
cmbn.AddItem "Ingo"
cmbn.AddItem "Haack"
cmbn.AddItem "Singavarapu"

cmbt.Clear
cmbt.AddItem "Technische Bearbeitung"
cmbt.AddItem "CAD Erstellung"
cmbt.AddItem "Berechnungen"
cmbt.AddItem "Besprechung"
cmbt.AddItem "Verwaltung"
cmbt.AddItem "Bauleitung"
cmbt.AddItem "Montage"
cmbt.AddItem "Miscleneous"

If IsNull(cln1.Value) = False Then
txtdt1.Text = cln1.Value
Else
txtdt1.Text = ""

End If

End Sub
........................................................
Private Sub cmdadd_Click()

Dim db As New ADODB.Connection
Dim se As New ADODB.Recordset
Dim sConn As String
Dim cnt As Long
Dim tmp As String

cmdadd.Enabled = False
cmdneu.Enabled = True

pb2.Visible = True

For cnt = 1 To 100000

pb2.Visible = True

pb2.Value = cnt

'needed to trap cancel click
DoEvents

Next

'ProgressBar1.Value = 100000
pb2.Visible = False

Dim sSQL As String
Dim sPath As String
sPath = "C:\Dokumente und Einstellungen\singava\Eigene Dateien\Eigene Programme\projekt.mdb"

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
sSQL = "Update Stunden Set ProjektNr='newprojektnr',nachname='newnachname' where nachname='Oldnachname'"

db.ConnectionString = sConn
db.Open
'db.Execute sSQL
'db.Close

se.ActiveConnection = db
se.LockType = adLockOptimistic
se.CursorType = adOpenKeyset

se.Open "Stunden" '** Table name for Stundensatz

se.MoveFirst
se.AddNew
se.Fields.Item(1) = txtdt1.Text
se.Fields.Item(2) = cmbp.Text
se.Fields.Item(3) = cmbn.Text
se.Fields.Item(4) = txtstd.Text
se.Fields.Item(5) = cmbt.Text
se.Fields.Item(6) = txttbes.Text
se.Update
se.Close

Set se = Nothing

End Sub

here i wrote a code for DBGrid to show the required details according to combobox. see 9.jpg..
i am getting empty cells..its now showing the required cells i need.


Private Sub cmdshow_Click()


dg1.Visible = False
dg2.Visible = False
dbg1.Visible = True
dbg1.Height = 4140
dbg1.Width = 8670
dbg1.Top = 1425
dbg1.Left = 9025


Set DBG = OpenDatabase("C:\Dokumente und Einstellungen\singava\Eigene Dateien\Eigene Programme\Projekt.mdb")
Set DBGrs = DBG.OpenRecordset("Stunden", dbOpenTable)

Data1.DatabaseName = App.Path & "\Projekt.mdb"
Data1.RecordSource = "Stunden"
Data2.DatabaseName = App.Path & "\Projekt.mdb"


On Error Resume Next
Set DBGrsd = DBG.OpenRecordset("select * from Stunden where Name_S='" & cmbn.Text & "'", dbOpenDynaset)
If DBGrsd.RecordCount > 0 Then DBGrsd.MoveFirst
Set DBGrsd = Nothing

Fields = "Name_S"
Recd = cmbn.Text
Data2.RecordSource = "select *from Stunden where " & Fields & "='" & Recd & "'"
Data2.Refresh
Fields = ""
Recd = ""
End Sub
when you see in 6.jpg ID value is again wrong..

when the form gets loaded i get the values from Table Project for project combo box
now when combo box Name gets changed i want that the Datagrid which i have given should show me the values which contain the Name as comboname and the date.text

cannot able to find the required sql syntax...
i have 2 Adodc..adodc1 for showing all the values from selected table
adodc2 for showing only required fields like

Select Date,Project,Name,Stunden from Stunden where Name = '" & cmbn.text & "' and Date = '" & txtdt.text & "'

but this syntax shows nothing..just an empty even if i got values in access.

.....................................................................

Problem 3

i want to print the values from DataGrid to Excel...shown in 8.jpg
only the required data should been written in this excel...i need a help for code coz i couldnt write this code...

...................................................................

Problem 4

maybe this is little bit crazy ones..when u see in 7.jpg

its the result of each project to watch the status of each project..according to hours ...
i want to show the status to be shown with percentage and something like progressbar..

example...in CAD 30% is finished...so 30% should be shown in blue color..
Berechnung something like 70% finished..one should be able to see in Progressbar..

these hours will be entered in 5.jpg.

i will attach files 6 7 8 jpg...

friends..can you please help me...

singoi