Hello ChrisE
Hello VbForums community
ChrisE
I have been playing with your Demo http://www.vbforums.com/showthread.p...=1#post5459647
I thank you again and I must say that I could understand everything.
The only thing I could not understand is how you added the dates of vaccination.
Please sir I attached a sample demo of my application and I commented my worries
Would you please have a look?
thank you
I just added those Dates in the Database table for testing. You will have to create a Form and Load a Record
say with the ID 7 and add the Vac_DateOfVacctination and then Update that Record.
I will take a look at your program tomorrow.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
thank you sir
you'll understand my worry when you see my demo
I don't undestand, because there in nothing in the Database tables
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Ok
I click on the Save button and the Data is saved to the Table, so what is the problem ?
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
first
this is how I would Calculate and save the Data to tbl_possibleVacDate
I hard coded the Birthdate and ChildID
you need a Listview, 2 Commandbuttons and 2 Textboxes
Code:
Option Explicit
Dim adoConnection As ADODB.Connection
Private Sub Command1_Click()
CalculateVacctinations
End Sub
Private Sub CalculateVacctinations()
Dim rs As ADODB.Recordset
Dim Li As ListItem
Dim IntervalType As String
Dim nMonths As Integer
IntervalType = "m" 'months as interval.
Set rs = GetDatafromVacTable
With ListView1
Do While Not rs.EOF
Set Li = .ListItems.Add
Li.Key = rs.Fields("Vac_ID").Value & "x"
Li.Text = rs.Fields("Vac_ID").Value & vbNullString
Li.SubItems(1) = rs.Fields("Vac_Description").Value & vbNullString
Li.SubItems(2) = rs.Fields("Vac_Intervall").Value & vbNullString
Li.SubItems(3) = DateAdd(IntervalType, nMonths + Li.SubItems(2), txtBirthdate.Text) '+ 2 Months
Li.SubItems(4) = txtChildID.Text
rs.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Sub
Private Sub Command2_Click()
'add Data to tbl_possibleVacDate
Dim rs As ADODB.Recordset
Dim i As Long
Dim strSQL As String
Dim itm As ListItem
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM tbl_possibleVacDate", adoConnection, adOpenStatic, adLockOptimistic
With rs
For Each itm In ListView1.ListItems
rs.AddNew
.Fields("Vac_ID").Value = Val(itm.Text)
.Fields("Vac_possibleDateOfVaccinaton").Value = Format$(itm.SubItems(3), "dd.mm.yyyy")
.Fields("Vac_ChildID").Value = Val(itm.SubItems(4))
Next
.Update
End With
rs.Close
Set rs = Nothing
End Sub
Private Sub Form_Load()
Label1.Caption = "opening Database..."
If (Not openTheDatabase()) Then
Label1.Caption = "Database error..."
Exit Sub
End If
Label1.Caption = "Database open..."
With ListView1
.View = lvwReport
.ColumnHeaders.Add , , "Vac.ID", 800
.ColumnHeaders.Add , , "Vac. Description", 2500
.ColumnHeaders.Add , , "Intervall", 800
.ColumnHeaders.Add , , "Posible Vac. Date", 1500
.ColumnHeaders.Add , , "ChildID", 800
End With
txtBirthdate.Text = CDate("02.02.2020")
txtChildID.Text = 1
End Sub
Public Function openTheDatabase() As Boolean
'-- DB öffnen
Dim sConnectionString As String
On Error GoTo dbError
'-- New connection --
Set adoConnection = New ADODB.Connection
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\Vac.mdb"
adoConnection.Open sConnectionString
openTheDatabase = True
Exit Function
dbError:
MsgBox (Err.Description)
openTheDatabase = False
End Function
Public Function GetDatafromVacTable(Optional ID As Long = 0, _
Optional LockType As ADODB.LockTypeEnum = adLockReadOnly) _
As ADODB.Recordset
'Load all Data from tbl_Vac
Dim rs As ADODB.Recordset
Dim sSQL As String
If ID = 0 Then
'all
sSQL = "Select * From tbl_Vac Order By Vac_ID"
Else
'single
sSQL = "Select * From tbl_Vac Where tbl_Vac = " & ID
End If
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sSQL, adoConnection, adOpenKeyset, LockType
If rs.RecordCount > 0 Then
Set GetDatafromVacTable = rs
End If
Set rs = Nothing
End Function
the image after you clicked Command1
Command2 will save the Data to the Database table
Last edited by ChrisE; Mar 21st, 2020 at 10:17 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
well I am located in Germany dd.mm.yyyy, you will have to change the Date to your Location
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Last edited by ChrisE; Mar 21st, 2020 at 01:37 PM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
what Table are you calculating from ? and what Data is in that Table ?
nMonths can't be 0
my Table looks like this
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
well I changed my Regional setting to USA and it works, you will have to check your Database tables
and look at the code you changed
the code changes
Code:
Option Explicit
Dim adoConnection As ADODB.Connection
Private Sub Command1_Click()
CalculateVacctinations
End Sub
Private Sub CalculateVacctinations()
Dim Rs As ADODB.Recordset
Dim Li As ListItem
Dim IntervalType As String
Dim nMonths As Integer
IntervalType = "m" 'months as interval.
Set Rs = GetDatafromVacTable
With ListView1
Do While Not Rs.EOF
Set Li = .ListItems.Add
Li.Key = Rs.Fields("Vac_ID").Value & "x"
Li.Text = Rs.Fields("Vac_ID").Value & vbNullString
Li.SubItems(1) = Rs.Fields("Vac_Description").Value & vbNullString
Li.SubItems(2) = Rs.Fields("Vac_Intervall").Value & vbNullString
Li.SubItems(3) = DateAdd(IntervalType, nMonths + Li.SubItems(2), Me.DTPicker1.Value) '+ 2 Months
Li.SubItems(4) = txtChildID.Text
Rs.MoveNext
Loop
End With
Rs.Close
Set Rs = Nothing
End Sub
Private Sub Command2_Click()
'add Data to tbl_possibleVacDate
Dim Rs As ADODB.Recordset
Dim i As Long
Dim strSQL As String
Dim itm As ListItem
Set Rs = New ADODB.Recordset
Rs.Open "SELECT * FROM tbl_possibleVacDate", adoConnection, adOpenStatic, adLockOptimistic
With Rs
For Each itm In ListView1.ListItems
Rs.AddNew
.Fields("Vac_ID").Value = Val(itm.Text)
.Fields("Vac_possibleDateOfVaccinaton").Value = Format$(itm.SubItems(3), Me.DTPicker1.Value)
.Fields("Vac_ChildID").Value = Val(itm.SubItems(4))
Next
.Update
End With
Rs.Close
Set Rs = Nothing
End Sub
Private Sub Form_Load()
Label1.Caption = "opening Database..."
If (Not openTheDatabase()) Then
Label1.Caption = "Database error..."
Exit Sub
End If
Label1.Caption = "Database open..."
With ListView1
.View = lvwReport
.ColumnHeaders.Add , , "Vac.ID", 800
.ColumnHeaders.Add , , "Vac. Description", 2500
.ColumnHeaders.Add , , "Intervall", 800
.ColumnHeaders.Add , , "Posible Vac. Date", 1500
.ColumnHeaders.Add , , "ChildID", 800
End With
txtChildID.Text = 1
End Sub
Public Function openTheDatabase() As Boolean
'-- DB öffnen
Dim sConnectionString As String
On Error GoTo dbError
'-- New connection --
Set adoConnection = New ADODB.Connection
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\Vac.mdb"
adoConnection.Open sConnectionString
openTheDatabase = True
Exit Function
dbError:
MsgBox (Err.Description)
openTheDatabase = False
End Function
Public Function GetDatafromVacTable(Optional ID As Long = 0, _
Optional LockType As ADODB.LockTypeEnum = adLockReadOnly) _
As ADODB.Recordset
'Load all Data from tbl_Vac
Dim Rs As ADODB.Recordset
Dim sSQL As String
If ID = 0 Then
'all
sSQL = "Select * From tbl_Vac Order By Vac_ID"
Else
'single
sSQL = "Select * From tbl_Vac Where tbl_Vac.Vac_ID = " & ID
End If
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient
Rs.Open sSQL, adoConnection, adOpenKeyset, LockType
If Rs.RecordCount > 0 Then
Set GetDatafromVacTable = Rs
End If
Set Rs = Nothing
End Function
Last edited by ChrisE; Mar 21st, 2020 at 02:55 PM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Well
Are there any drawbacks the way I"m saving my data in my sample demo?
Then is it possible to keep track of the vaccination dates?
hank you
what do you mean ?
you said that the Child gets 40 Vac's over a period of 10 Years, this is the part where you calculate those
and add them at once to the Database
the other part is where you add the actual Date when the vac. took place
say the calculated value is 05/06/2021 but the Date when the vac. took place is 10/06/2021
you will use the Date 05/06/2021 to create your reminders
Last edited by ChrisE; Mar 21st, 2020 at 03:10 PM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
the other part is where you add the actual Date when the vac. took place
say the calculated value is 05/06/2021 but the Date when the vac. took place is 10/06/2021
you will use the Date 05/06/2021 to create your reminders
This is what I'm looking for but I can't find a way.
This is your code for saving data.
How do I fill my (Vac_DateOfVaccinaton field)?
My initial question was (How did you manage to add the vaccination dates?)
thanks
how you do that, read Post#2
you sent a reminder to a Parent that there is a Vaccination due ( say the Child has the ID 2)
this Vac. will take place somewhere, a Hospital or at a Doctor. They will enter the Date of Vaccination.
how can you possibly add Dates in a Loop for something that hasn't took place yet?
Post#2:
Hi,
I just added those Dates in the Database table for testing. You will have to create a Form and Load a Record
say with the ID 7 and add the Vac_DateOfVacctination and then Update that Record.
so you are loading a Form with Child ID = 2
there you see the Data from the Parents and Child
then you update that Date of Vac. and close the Form.
EDIT:
to update a single Record in the tbl_possibleVacDate, you use the Vac_DateID :
Code:
Private Sub Command3_Click()
Dim RsVac As ADODB.Recordset
Set RsVac = New ADODB.Recordset
RsVac.Open "SELECT * FROM tbl_possibleVacDate Where Vac_DateID = 5", adoConnection, adOpenStatic, adLockOptimistic
With RsVac
.Fields("Vac_DateOfVaccination").Value = Me.DTPicker1.Value
.Update
End With
RsVac.Close
Set RsVac = Nothing
End Sub
Last edited by ChrisE; Mar 22nd, 2020 at 03:00 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
to update a single Record in the tbl_possibleVacDate, you use the Vac_DateID :
Thank you a lot
I think we come to the heart of the matter
Code:
RsVac.Open "SELECT * FROM tbl_possibleVacDate Where Vac_DateID = 5", adoConnection, adOpenStatic, adLockOptimistic
Practically on the form, how do put that into practice?
I mean what do you think the best way to know which "Vac_DateID " is concerned among 40 vacs?
Can a checkbox listvew do the job?
Or maybe another idea could be better
thank you
well in the Table tbl_possibleVacDate you would use the Vac_ChildID to load the Data
then you will see all 40 Vac's
from there you use the...
Code:
RsVac.Open "SELECT * FROM tbl_possibleVacDate Where Vac_DateID = 5", adoConnection, adOpenStatic, adLockOptimistic
in the above case the Vac_DateID = 5
but it could also be Vac_DateID = 15555
depending how many records are in that Table
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
you fill a Listview or Flexgrid with Data from the table tbl_possibleVacDate
as a criteria you use the Vac_ChildID
I have already shown you how to fill both of those controls
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Thank you sir
Now I think I start to have a clear picture about my project
good !
I will help if you get stuck, but try and see if can get it going on your own
I think you owe me Vaccinations for Life by now
as I have just about wrote your program
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
Reply With Quote
Originally Posted by newbie2
