Solved Adding values to a ListBox from a module (1 Viewer)

sbrown106

Member
Local time
Today, 09:27
Joined
Feb 6, 2021
Messages
77
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)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,173
what is myvalue variable in your code?

you change your Sub so you can pass the StartDate and the listbox:
Code:
Sub Status(ByVal startDate As Date, ByRef lstBox As ListBox)

Dim rs As DAO.Recordset
'Dim rs1 As DAO.Recordset
Dim startDate As Date
Dim mydate As Date
Dim delay As Integer
Dim myvalue

Set db = CurrentDb
Set rs = db.OpenRecordset("PatientID")
'Set rs1 = db.OpenRecordset("ReportsID")

'startDate = #5/20/2021#
delay = 1


If IsDate(startDate) Then
    Do Until rs.EOF
        mydate = DateValue(rs![fldDateCreated])
        startDate = DateValue(startDate)
        If mydate > startDate Then
            If DateDiff("d", rs![fldDateCreated], Date) > delay Then
                'Debug.Print rs![fldPatientID]; mydate, rs1![fldStatusID]
                'frmNavigation!lstApptStatus.AddItem (rs![fldDateCreated])
                'lstApptStatus.AddItem (rs![fldDateCreated])
                lstBox.AddItem (myvalue)
            End If
        End If
        rs.MoveNext
    Loop
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

you change this code to:
Code:
' code below on button
Private Sub cmdApptStatus_Click()
ApptStatus.Status #5/20/2021#, Me!lstAppStatus
End Sub


Note: do you need to reset the listbox first (remove the items) before adding new items?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:27
Joined
May 21, 2018
Messages
8,463
Make sure at the top of all code modules you have
Option Explicit
This can be set in the VBA options, and will ensure that variables are declared. Once you do that debug and compile your code.
If you are not throwing an error, my guess the code is adding items to your list but they may not be visible. You can tell this by clicking into the list. You can select a blank row.
lstBox.AddItem (myvalue)
would like add Null to every row since myvalue is an undeclared variable.
 

sbrown106

Member
Local time
Today, 09:27
Joined
Feb 6, 2021
Messages
77
what is myvalue variable in your code?

you change your Sub so you can pass the StartDate and the listbox:
Code:
Sub Status(ByVal startDate As Date, ByRef lstBox As ListBox)

Dim rs As DAO.Recordset
'Dim rs1 As DAO.Recordset
Dim startDate As Date
Dim mydate As Date
Dim delay As Integer
Dim myvalue

Set db = CurrentDb
Set rs = db.OpenRecordset("PatientID")
'Set rs1 = db.OpenRecordset("ReportsID")

'startDate = #5/20/2021#
delay = 1


If IsDate(startDate) Then
    Do Until rs.EOF
        mydate = DateValue(rs![fldDateCreated])
        startDate = DateValue(startDate)
        If mydate > startDate Then
            If DateDiff("d", rs![fldDateCreated], Date) > delay Then
                'Debug.Print rs![fldPatientID]; mydate, rs1![fldStatusID]
                'frmNavigation!lstApptStatus.AddItem (rs![fldDateCreated])
                'lstApptStatus.AddItem (rs![fldDateCreated])
                lstBox.AddItem (myvalue)
            End If
        End If
        rs.MoveNext
    Loop
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

you change this code to:
Code:
' code below on button
Private Sub cmdApptStatus_Click()
ApptStatus.Status #5/20/2021#, Me!lstAppStatus
End Sub


Note: do you need to reset the listbox first (remove the items) before adding new items?
Thanks arnelgp, yes I was trying to figure out how to reset the listbox - is it by adding lstBox.Clear above 'If IsDate(startDate) Then' statement?
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:27
Joined
Sep 21, 2011
Messages
14,050
Thanks arnelgp, yes I was trying to figure out how to reset the listbox - is it by adding lstBox.Clear above 'If IsDate(startDate) Then' statement?
If the Clear option does not come up in intellisense, then it is not valid?
I'd probably have used a query with that criteria as well?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:27
Joined
May 21, 2018
Messages
8,463
If you are using a value list to add items then you can clear as follows.
Code:
Public Sub ClearList(lst As Access.ListBox)
  Dim i As Integer
  If lst.RowSourceType = "value list" Then
    For i = lst.ListCount - 1 To 0 Step -1
      lst.RemoveItem (i)
    Next i
  End If
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:27
Joined
May 21, 2018
Messages
8,463
If this is a multi column listbox then you have to concatenate the item to add seperated by ;
lst.additem "1/2/2021; 1234; Patient 1"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:27
Joined
Feb 28, 2001
Messages
27,001
Of course this can be done as suggested by my colleagues, but I would be remiss if I did not suggest that past a certain point, it might be easier to just build a table (that doesn't get updated often) to drive the listbox. Then if you really had to add a new entry (or remove one), do a table INSERT INTO or DELETE operation followed by a .Requery of that control. One or two values in your list? No problem. However, there is a limit of just over 32,000 characters that can be in any .Rowsource for a control. (Look up "Access Specifications" to find that.)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:27
Joined
May 21, 2018
Messages
8,463
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)
Why not simply
Code:
Sub Status(ByVal startDate As Date, Delay as Integer, ByRef lstBox As ListBox)
  Dim strSql as string
  strSql = "Select fldPatientID, fldStatusID, fldDateCreated from PatientID where DateDiff('d', [fldDateCreated], Date) > " &  delay
  lstApptStatus.rowsource = strSql
End Sub
 

sbrown106

Member
Local time
Today, 09:27
Joined
Feb 6, 2021
Messages
77
what is myvalue variable in your code?

you change your Sub so you can pass the StartDate and the listbox:
Code:
Sub Status(ByVal startDate As Date, ByRef lstBox As ListBox)

Dim rs As DAO.Recordset
'Dim rs1 As DAO.Recordset
Dim startDate As Date
Dim mydate As Date
Dim delay As Integer
Dim myvalue

Set db = CurrentDb
Set rs = db.OpenRecordset("PatientID")
'Set rs1 = db.OpenRecordset("ReportsID")

'startDate = #5/20/2021#
delay = 1


If IsDate(startDate) Then
    Do Until rs.EOF
        mydate = DateValue(rs![fldDateCreated])
        startDate = DateValue(startDate)
        If mydate > startDate Then
            If DateDiff("d", rs![fldDateCreated], Date) > delay Then
                'Debug.Print rs![fldPatientID]; mydate, rs1![fldStatusID]
                'frmNavigation!lstApptStatus.AddItem (rs![fldDateCreated])
                'lstApptStatus.AddItem (rs![fldDateCreated])
                lstBox.AddItem (myvalue)
            End If
        End If
        rs.MoveNext
    Loop
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

you change this code to:
Code:
' code below on button
Private Sub cmdApptStatus_Click()
ApptStatus.Status #5/20/2021#, Me!lstAppStatus
End Sub


Note: do you need to reset the listbox first (remove the items) before adding new items?

Thanks arnelgp, yes I was trying to figure out how to reset the listbox - is it by adding lstBox.Clear above 'If IsDate(startDate) Then' statement?
sorry myvalue should have been mydate. I was trying to sanitise the code a bit, taking out things i couldnt include,.Forgot to change that
 

Users who are viewing this thread

Top Bottom