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
