Hi, I am new to Access/VBA I am having trouble adding values to this list box from within a module. I was thinking I might use the
results of the module within other parts of the database. I'm doing something wrong but cant figure out what it is, the module
works on its own but the listbox wont add items, some details below -thanks
I have a module called - ApptStatus, within the module a subroutine named Status()
I have a form called frmNavigation with the code below attached to a button, the listbox is called
lstApptStatus and within the data tab on its property sheet Ive changed the Row Source Type to 'Value List'
' code below on button
Private Sub cmdApptStatus_Click()
ApptStatus.Status
End Sub
' code below within module (ApptStatus)
notes:
startdate = is the date I want to start looking from
mydate = is the date a record has been created
delay = is the number of days after mydate that a particular action needs to be taken.
The program works within the module and outputs the results to the screen, but I cant get the listbox in the form to be populated, Ive read different ways and just confused now, could somebody give me some help please! there may be a better way to do this.Ideally I would like this listbox to be permanently upto date without clicking the button but not sure how to do that - any suggestions welcome thanks
Option Compare Database
'''
Sub Status()
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim startdate As Date
Dim mydate As Date
Dim delay As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("PatientID")
Set rs1 = db.OpenRecordset("ReportsID")
startdate = #5/20/2021#
LOIdelay = 1
If IsDate(startdate) Then
Do Until rs.EOF
mydate = DateValue(rs![fldDateCreated])
startdate = DateValue(startdate)
If mydate > startdate Then
If Date - rs![fldDateCreated] > delay Then
'Debug.Print rs![fldPatientID]; mydate, rs1![fldStatusID]
'frmNavigation!lstApptStatus.AddItem (rs![fldDateCreated])
'lstApptStatus.AddItem (rs![fldDateCreated])
lstApptStatus.AddItem (myvalue)
End If
End If
rs.MoveNext
Loop
End If
rs.Close
End Sub
(Ideally I would like this listbox to be permanently upto date, without clicking the button, that might be the next step but not sure how to do that - any suggestions welcome thanks)
results of the module within other parts of the database. I'm doing something wrong but cant figure out what it is, the module
works on its own but the listbox wont add items, some details below -thanks
I have a module called - ApptStatus, within the module a subroutine named Status()
I have a form called frmNavigation with the code below attached to a button, the listbox is called
lstApptStatus and within the data tab on its property sheet Ive changed the Row Source Type to 'Value List'
' code below on button
Private Sub cmdApptStatus_Click()
ApptStatus.Status
End Sub
' code below within module (ApptStatus)
notes:
startdate = is the date I want to start looking from
mydate = is the date a record has been created
delay = is the number of days after mydate that a particular action needs to be taken.
The program works within the module and outputs the results to the screen, but I cant get the listbox in the form to be populated, Ive read different ways and just confused now, could somebody give me some help please! there may be a better way to do this.Ideally I would like this listbox to be permanently upto date without clicking the button but not sure how to do that - any suggestions welcome thanks
Option Compare Database
'''
Sub Status()
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim startdate As Date
Dim mydate As Date
Dim delay As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("PatientID")
Set rs1 = db.OpenRecordset("ReportsID")
startdate = #5/20/2021#
LOIdelay = 1
If IsDate(startdate) Then
Do Until rs.EOF
mydate = DateValue(rs![fldDateCreated])
startdate = DateValue(startdate)
If mydate > startdate Then
If Date - rs![fldDateCreated] > delay Then
'Debug.Print rs![fldPatientID]; mydate, rs1![fldStatusID]
'frmNavigation!lstApptStatus.AddItem (rs![fldDateCreated])
'lstApptStatus.AddItem (rs![fldDateCreated])
lstApptStatus.AddItem (myvalue)
End If
End If
rs.MoveNext
Loop
End If
rs.Close
End Sub
(Ideally I would like this listbox to be permanently upto date, without clicking the button, that might be the next step but not sure how to do that - any suggestions welcome thanks)