Coding to make AddItem work for 2000

[OMS]

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime. The main
sticking point is the AddItem in a listbox. I've tried a few things but the
closest I get is it populating only one row in the box with all items. I've
included the code with remmed lines to give you a better idea of where I
was, where I am and where I need to be. What am I doing wrong? Oh, please do
reply telling me AddItem doesn't work in 2000. I know that, I just want the
same or similar effect and really don't know enough lingo to reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, " &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," & _
"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " & Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " & Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " & IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " & Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[Guest]

Hi OMS,

The additem command should work! I think if you perform the following steps
your code should work

1) Determine how much columns you would like to use and set it in the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable) contraining the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime. The main
sticking point is the AddItem in a listbox. I've tried a few things but the
closest I get is it populating only one row in the box with all items. I've
included the code with remmed lines to give you a better idea of where I
was, where I am and where I need to be. What am I doing wrong? Oh, please do
reply telling me AddItem doesn't work in 2000. I know that, I just want the
same or similar effect and really don't know enough lingo to reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, " &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," & _
"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " & Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " & Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " & IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " & Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[OMS]

HI,

Thanks but I think you missed the premise or I threw you off by mentioning
the AddItem. AddItem doesn't work with 2000. I need this to work in 2000.
I need a way to do it without AddItem in the code.

Hi OMS,

The additem command should work! I think if you perform the following
steps
your code should work

1) Determine how much columns you would like to use and set it in the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the properties
or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable) contraining the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime. The
main
sticking point is the AddItem in a listbox. I've tried a few things but
the
closest I get is it populating only one row in the box with all items.
I've
included the code with remmed lines to give you a better idea of where I
was, where I am and where I need to be. What am I doing wrong? Oh, please
do
reply telling me AddItem doesn't work in 2000. I know that, I just want
the
same or similar effect and really don't know enough lingo to reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, " &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " & Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " & Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " & Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[Douglas J. Steele]

Theoretically, if you've got RowSourceType set to "Value List", you should
be able to simply concatenate the new entries to the existing string that
makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can be used
for the RowSource in Access 2000 that was increased when the AddItem method
was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI,

Thanks but I think you missed the premise or I threw you off by mentioning
the AddItem. AddItem doesn't work with 2000. I need this to work in 2000.
I need a way to do it without AddItem in the code.

Hi OMS,

The additem command should work! I think if you perform the following
steps
your code should work

1) Determine how much columns you would like to use and set it in the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the properties
or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable) contraining the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime. The
main
sticking point is the AddItem in a listbox. I've tried a few things but
the
closest I get is it populating only one row in the box with all items.
I've
included the code with remmed lines to give you a better idea of where I
was, where I am and where I need to be. What am I doing wrong? Oh,
please do
reply telling me AddItem doesn't work in 2000. I know that, I just want
the
same or similar effect and really don't know enough lingo to reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, "
&_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " & Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " & Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " & Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[OMS]

HI,

That was very helpful. I used below in my form but returned a runtime error
"3021 No Current Record" on a debug message box. If I choose debug it
highlights the code below. If I stop or cancel the debug the form shows up
populated with the correct drugs (there is also a blank between populated
rows.)

Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " & Trim(rst.Fields("ROUTE").Value) & "
" & Trim(rst.Fields("Mpmp").Value) & ";"

Theoretically, if you've got RowSourceType set to "Value List", you should
be able to simply concatenate the new entries to the existing string that
makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can be used
for the RowSource in Access 2000 that was increased when the AddItem
method was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI,

Thanks but I think you missed the premise or I threw you off by
mentioning the AddItem. AddItem doesn't work with 2000. I need this to
work in 2000.
I need a way to do it without AddItem in the code.

Hi OMS,

The additem command should work! I think if you perform the following
steps
your code should work

1) Determine how much columns you would like to use and set it in the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the
properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable) contraining the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

:

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime. The
main
sticking point is the AddItem in a listbox. I've tried a few things but
the
closest I get is it populating only one row in the box with all items.
I've
included the code with remmed lines to give you a better idea of where
I
was, where I am and where I need to be. What am I doing wrong? Oh,
please do
reply telling me AddItem doesn't work in 2000. I know that, I just want
the
same or similar effect and really don't know enough lingo to reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, "
&_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," &
_

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " & Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " & Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[Douglas J. Steele]

What's the code around that statement? How did you open the recordset? Are
you sure you do have a current record in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

HI,

That was very helpful. I used below in my form but returned a runtime
error "3021 No Current Record" on a debug message box. If I choose debug
it highlights the code below. If I stop or cancel the debug the form shows
up populated with the correct drugs (there is also a blank between
populated rows.)

Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " & Trim(rst.Fields("ROUTE").Value) &
" " & Trim(rst.Fields("Mpmp").Value) & ";"

Theoretically, if you've got RowSourceType set to "Value List", you
should be able to simply concatenate the new entries to the existing
string that makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can be used
for the RowSource in Access 2000 that was increased when the AddItem
method was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI,

Thanks but I think you missed the premise or I threw you off by
mentioning the AddItem. AddItem doesn't work with 2000. I need this to
work in 2000.
I need a way to do it without AddItem in the code.



Hi OMS,

The additem command should work! I think if you perform the following
steps
your code should work

1) Determine how much columns you would like to use and set it in the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the
properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable) contraining
the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

:

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime. The
main
sticking point is the AddItem in a listbox. I've tried a few things
but the
closest I get is it populating only one row in the box with all items.
I've
included the code with remmed lines to give you a better idea of where
I
was, where I am and where I need to be. What am I doing wrong? Oh,
please do
reply telling me AddItem doesn't work in 2000. I know that, I just
want the
same or similar effect and really don't know enough lingo to reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, "
&_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," &
_

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " & Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[OMS]

Hi Doug,

All data is live. I have other reports that generate the same info to
confirm.
Recordset opened by Set rst = db.OpenRecordset(strSQL) See below for
Form_Load code.
I found that it only tries to debug if I go directly from design mode, if
just opening as a user would, no debugger. I'll have to add some error
handling.

Lines are now populating every other one ie.
drug
blank
drug
blank

Unfortunetly when I do choose one of the items in the listbox and click my
print command I get "Invalid use of Null". The report it was calling did
have a "Null" expression in it, took it out but still didn't work. I then
tried to "clean" the SQL in the Form_Load below of the Null statement but
Access didn't like it. Red. That corrupted something and db didn't work at
all. Luckily I always start with a fresh copy.
Any ideas?
Thanks,
OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient


strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, " & _
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," & _
"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " & Trim(rst.Fields("ROUTE").Value) & "
" & Trim(rst.Fields("Mpmp").Value) & ";"
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

What's the code around that statement? How did you open the recordset? Are
you sure you do have a current record in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

HI,

That was very helpful. I used below in my form but returned a runtime
error "3021 No Current Record" on a debug message box. If I choose debug
it highlights the code below. If I stop or cancel the debug the form
shows up populated with the correct drugs (there is also a blank between
populated rows.)

Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " & Trim(rst.Fields("ROUTE").Value)
& " " & Trim(rst.Fields("Mpmp").Value) & ";"

Theoretically, if you've got RowSourceType set to "Value List", you
should be able to simply concatenate the new entries to the existing
string that makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can be
used for the RowSource in Access 2000 that was increased when the
AddItem method was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

Thanks but I think you missed the premise or I threw you off by
mentioning the AddItem. AddItem doesn't work with 2000. I need this to
work in 2000.
I need a way to do it without AddItem in the code.



Hi OMS,

The additem command should work! I think if you perform the following
steps
your code should work

1) Determine how much columns you would like to use and set it in the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the
properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable) contraining
the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount to
2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

:

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime. The
main
sticking point is the AddItem in a listbox. I've tried a few things
but the
closest I get is it populating only one row in the box with all
items. I've
included the code with remmed lines to give you a better idea of
where I
was, where I am and where I need to be. What am I doing wrong? Oh,
please do
reply telling me AddItem doesn't work in 2000. I know that, I just
want the
same or similar effect and really don't know enough lingo to
reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME,
" &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp,"
& _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " & Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[Douglas J. Steele]

Try moving your rst.MoveNext statement to just before the Loop statement.

You're moving past the end of the recordset before you try to use the values
in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

All data is live. I have other reports that generate the same info to
confirm.
Recordset opened by Set rst = db.OpenRecordset(strSQL) See below for
Form_Load code.
I found that it only tries to debug if I go directly from design mode, if
just opening as a user would, no debugger. I'll have to add some error
handling.

Lines are now populating every other one ie.
drug
blank
drug
blank

Unfortunetly when I do choose one of the items in the listbox and click my
print command I get "Invalid use of Null". The report it was calling did
have a "Null" expression in it, took it out but still didn't work. I then
tried to "clean" the SQL in the Form_Load below of the Null statement but
Access didn't like it. Red. That corrupted something and db didn't work at
all. Luckily I always start with a fresh copy.
Any ideas?
Thanks,
OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient


strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, " & _
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION])) AS
modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR = tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " & Trim(rst.Fields("ROUTE").Value) &
" " & Trim(rst.Fields("Mpmp").Value) & ";"
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

What's the code around that statement? How did you open the recordset?
Are you sure you do have a current record in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

HI,

That was very helpful. I used below in my form but returned a runtime
error "3021 No Current Record" on a debug message box. If I choose debug
it highlights the code below. If I stop or cancel the debug the form
shows up populated with the correct drugs (there is also a blank between
populated rows.)

Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " & Trim(rst.Fields("ROUTE").Value)
& " " & Trim(rst.Fields("Mpmp").Value) & ";"



Theoretically, if you've got RowSourceType set to "Value List", you
should be able to simply concatenate the new entries to the existing
string that makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can be
used for the RowSource in Access 2000 that was increased when the
AddItem method was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

Thanks but I think you missed the premise or I threw you off by
mentioning the AddItem. AddItem doesn't work with 2000. I need this to
work in 2000.
I need a way to do it without AddItem in the code.



Hi OMS,

The additem command should work! I think if you perform the following
steps
your code should work

1) Determine how much columns you would like to use and set it in the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the
properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable) contraining
the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount to
2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

:

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime.
The main
sticking point is the AddItem in a listbox. I've tried a few things
but the
closest I get is it populating only one row in the box with all
items. I've
included the code with remmed lines to give you a better idea of
where I
was, where I am and where I need to be. What am I doing wrong? Oh,
please do
reply telling me AddItem doesn't work in 2000. I know that, I just
want the
same or similar effect and really don't know enough lingo to
reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME,
" &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp,"
& _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " & Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[OMS]

That stopped the debugger. Thank you so much. I don't expect you to solve
all my problems but... I still get "Invalid use of Null" though when I click
my print command button. I've Googled it but not sure of what to do with the
info I found.
Any hints?
OMS

Private Sub cmdPrint_Click()
On Error GoTo error
Dim strITN As String
Dim intMedCounter As Integer
Dim strPMP As String
Dim strTotalPMP As String
Dim strDocName As String
Dim strPMP_Text As String
Dim strNurSta As String

strNurSta = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(4))
strITN = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(0))
strDocName = "csm_Discharge_MAR_Summary_2000"

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
strPMP = Me.lstPMP.ItemData(intMedCounter)
strTotalPMP = "PMP = """ & strPMP & """ OR "
strPMP_Text = strPMP_Text & strTotalPMP
Else
'*****
End If
Next intMedCounter
strPMP_Text = "(" & Left(strPMP_Text, Len(strPMP_Text) - 4) & ")"

DoCmd.OpenReport strDocName, acViewNormal, , "NRS_STATION = """ &
strNurSta & """ AND ITN = """ & strITN & """ AND " & strPMP_Text

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
Me.lstMedications.Selected(intMedCounter) = False
End If
Next intMedCounter

exit_sub:
On Error GoTo 0
Exit Sub

Try moving your rst.MoveNext statement to just before the Loop statement.

You're moving past the end of the recordset before you try to use the
values in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

All data is live. I have other reports that generate the same info to
confirm.
Recordset opened by Set rst = db.OpenRecordset(strSQL) See below for
Form_Load code.
I found that it only tries to debug if I go directly from design mode, if
just opening as a user would, no debugger. I'll have to add some error
handling.

Lines are now populating every other one ie.
drug
blank
drug
blank

Unfortunetly when I do choose one of the items in the listbox and click
my print command I get "Invalid use of Null". The report it was calling
did have a "Null" expression in it, took it out but still didn't work. I
then tried to "clean" the SQL in the Form_Load below of the Null
statement but Access didn't like it. Red. That corrupted something and db
didn't work at all. Luckily I always start with a fresh copy.
Any ideas?
Thanks,
OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient


strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, " & _
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION])) AS
modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR = tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " & Trim(rst.Fields("ROUTE").Value)
& " " & Trim(rst.Fields("Mpmp").Value) & ";"
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

What's the code around that statement? How did you open the recordset?
Are you sure you do have a current record in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


HI,

That was very helpful. I used below in my form but returned a runtime
error "3021 No Current Record" on a debug message box. If I choose
debug it highlights the code below. If I stop or cancel the debug the
form shows up populated with the correct drugs (there is also a blank
between populated rows.)

Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " &
Trim(rst.Fields("ROUTE").Value) & " " & Trim(rst.Fields("Mpmp").Value)
& ";"



message Theoretically, if you've got RowSourceType set to "Value List", you
should be able to simply concatenate the new entries to the existing
string that makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can be
used for the RowSource in Access 2000 that was increased when the
AddItem method was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

Thanks but I think you missed the premise or I threw you off by
mentioning the AddItem. AddItem doesn't work with 2000. I need this
to work in 2000.
I need a way to do it without AddItem in the code.



Hi OMS,

The additem command should work! I think if you perform the
following steps
your code should work

1) Determine how much columns you would like to use and set it in
the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the
properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable) contraining
the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount
to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

:

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime.
The main
sticking point is the AddItem in a listbox. I've tried a few things
but the
closest I get is it populating only one row in the box with all
items. I've
included the code with remmed lines to give you a better idea of
where I
was, where I am and where I need to be. What am I doing wrong? Oh,
please do
reply telling me AddItem doesn't work in 2000. I know that, I just
want the
same or similar effect and really don't know enough lingo to
reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME,
PHM_ORDERS.BRAND_NAME, " &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS
Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " & Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[Douglas J. Steele]

Does the error occur when you open the report without specifying a criteria?
If so, then the error's in your report, not in the code you've shown.

If not, try replacing

DoCmd.OpenReport strDocName, acViewNormal, , "NRS_STATION = """ &
strNurSta & """ AND ITN = """ & strITN & """ AND " & strPMP_Text

with

Dim strCriteria

strCriteria = "NRS_STATION = """ & strNurSta & _
""" AND ITN = """ & strITN & """ AND " & strPMP_Text
Debug.Print strCriteria
DoCmd.OpenReport strDocName, acViewNormal, , strCriteria

If the error occurs, look in the Immediate Window (Ctrl-G) to see what was
printed there for strCriteria.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That stopped the debugger. Thank you so much. I don't expect you to solve
all my problems but... I still get "Invalid use of Null" though when I
click my print command button. I've Googled it but not sure of what to do
with the info I found.
Any hints?
OMS

Private Sub cmdPrint_Click()
On Error GoTo error
Dim strITN As String
Dim intMedCounter As Integer
Dim strPMP As String
Dim strTotalPMP As String
Dim strDocName As String
Dim strPMP_Text As String
Dim strNurSta As String

strNurSta = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(4))
strITN = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(0))
strDocName = "csm_Discharge_MAR_Summary_2000"

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
strPMP = Me.lstPMP.ItemData(intMedCounter)
strTotalPMP = "PMP = """ & strPMP & """ OR "
strPMP_Text = strPMP_Text & strTotalPMP
Else
'*****
End If
Next intMedCounter
strPMP_Text = "(" & Left(strPMP_Text, Len(strPMP_Text) - 4) & ")"

DoCmd.OpenReport strDocName, acViewNormal, , "NRS_STATION = """ &
strNurSta & """ AND ITN = """ & strITN & """ AND " & strPMP_Text

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
Me.lstMedications.Selected(intMedCounter) = False
End If
Next intMedCounter

exit_sub:
On Error GoTo 0
Exit Sub

Try moving your rst.MoveNext statement to just before the Loop statement.

You're moving past the end of the recordset before you try to use the
values in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

All data is live. I have other reports that generate the same info to
confirm.
Recordset opened by Set rst = db.OpenRecordset(strSQL) See below for
Form_Load code.
I found that it only tries to debug if I go directly from design mode,
if just opening as a user would, no debugger. I'll have to add some
error handling.

Lines are now populating every other one ie.
drug
blank
drug
blank

Unfortunetly when I do choose one of the items in the listbox and click
my print command I get "Invalid use of Null". The report it was calling
did have a "Null" expression in it, took it out but still didn't work. I
then tried to "clean" the SQL in the Form_Load below of the Null
statement but Access didn't like it. Red. That corrupted something and
db didn't work at all. Luckily I always start with a fresh copy.
Any ideas?
Thanks,
OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient


strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, " &
_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR = tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " & Trim(rst.Fields("ROUTE").Value)
& " " & Trim(rst.Fields("Mpmp").Value) & ";"
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
What's the code around that statement? How did you open the recordset?
Are you sure you do have a current record in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


HI,

That was very helpful. I used below in my form but returned a runtime
error "3021 No Current Record" on a debug message box. If I choose
debug it highlights the code below. If I stop or cancel the debug the
form shows up populated with the correct drugs (there is also a blank
between populated rows.)

Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " &
Trim(rst.Fields("ROUTE").Value) & " " & Trim(rst.Fields("Mpmp").Value)
& ";"



message Theoretically, if you've got RowSourceType set to "Value List", you
should be able to simply concatenate the new entries to the existing
string that makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can be
used for the RowSource in Access 2000 that was increased when the
AddItem method was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

Thanks but I think you missed the premise or I threw you off by
mentioning the AddItem. AddItem doesn't work with 2000. I need this
to work in 2000.
I need a way to do it without AddItem in the code.



Hi OMS,

The additem command should work! I think if you perform the
following steps
your code should work

1) Determine how much columns you would like to use and set it in
the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the
properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable)
contraining the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount
to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

:

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime.
The main
sticking point is the AddItem in a listbox. I've tried a few
things but the
closest I get is it populating only one row in the box with all
items. I've
included the code with remmed lines to give you a better idea of
where I
was, where I am and where I need to be. What am I doing wrong? Oh,
please do
reply telling me AddItem doesn't work in 2000. I know that, I just
want the
same or similar effect and really don't know enough lingo to
reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME,
PHM_ORDERS.BRAND_NAME, " &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS
Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " &
Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " & Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " & Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[OMS]

Hi Doug,
No, the report opens and populates fine. I did what you recommended but
nothing showed up in the immediate window. So I moved the "criteria" code
above this:

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
strPMP = Me.lstPMP.ItemData(intMedCounter)
strTotalPMP = "PMP = """ & strPMP & """ OR "
strPMP_Text = strPMP_Text & strTotalPMP
Else
'*****
End If
Next intMedCounter
strPMP_Text = "(" & Left(strPMP_Text, Len(strPMP_Text) - 4) & ")"

And got debugger with - NRS_STATION = 2WST AND ITN = 123456 AND

Then got a message box that said: Extra ) in query expression '(NRS_STATION
= 2WST AND ITN = 123456 AND)'.

Thanks,

Does the error occur when you open the report without specifying a
criteria? If so, then the error's in your report, not in the code you've
shown.

If not, try replacing

DoCmd.OpenReport strDocName, acViewNormal, , "NRS_STATION = """ &
strNurSta & """ AND ITN = """ & strITN & """ AND " & strPMP_Text

with

Dim strCriteria

strCriteria = "NRS_STATION = """ & strNurSta & _
""" AND ITN = """ & strITN & """ AND " & strPMP_Text
Debug.Print strCriteria
DoCmd.OpenReport strDocName, acViewNormal, , strCriteria

If the error occurs, look in the Immediate Window (Ctrl-G) to see what was
printed there for strCriteria.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That stopped the debugger. Thank you so much. I don't expect you to solve
all my problems but... I still get "Invalid use of Null" though when I
click my print command button. I've Googled it but not sure of what to do
with the info I found.
Any hints?
OMS

Private Sub cmdPrint_Click()
On Error GoTo error
Dim strITN As String
Dim intMedCounter As Integer
Dim strPMP As String
Dim strTotalPMP As String
Dim strDocName As String
Dim strPMP_Text As String
Dim strNurSta As String

strNurSta = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(4))
strITN = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(0))
strDocName = "csm_Discharge_MAR_Summary_2000"

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
strPMP = Me.lstPMP.ItemData(intMedCounter)
strTotalPMP = "PMP = """ & strPMP & """ OR "
strPMP_Text = strPMP_Text & strTotalPMP
Else
'*****
End If
Next intMedCounter
strPMP_Text = "(" & Left(strPMP_Text, Len(strPMP_Text) - 4) & ")"

DoCmd.OpenReport strDocName, acViewNormal, , "NRS_STATION = """ &
strNurSta & """ AND ITN = """ & strITN & """ AND " & strPMP_Text

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
Me.lstMedications.Selected(intMedCounter) = False
End If
Next intMedCounter

exit_sub:
On Error GoTo 0
Exit Sub

Try moving your rst.MoveNext statement to just before the Loop
statement.

You're moving past the end of the recordset before you try to use the
values in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug,

All data is live. I have other reports that generate the same info to
confirm.
Recordset opened by Set rst = db.OpenRecordset(strSQL) See below for
Form_Load code.
I found that it only tries to debug if I go directly from design mode,
if just opening as a user would, no debugger. I'll have to add some
error handling.

Lines are now populating every other one ie.
drug
blank
drug
blank

Unfortunetly when I do choose one of the items in the listbox and click
my print command I get "Invalid use of Null". The report it was calling
did have a "Null" expression in it, took it out but still didn't work.
I then tried to "clean" the SQL in the Form_Load below of the Null
statement but Access didn't like it. Red. That corrupted something and
db didn't work at all. Luckily I always start with a fresh copy.
Any ideas?
Thanks,
OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient


strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, " &
_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR = tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " &
Trim(rst.Fields("ROUTE").Value) & " " & Trim(rst.Fields("Mpmp").Value)
& ";"
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
message What's the code around that statement? How did you open the recordset?
Are you sure you do have a current record in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


HI,

That was very helpful. I used below in my form but returned a runtime
error "3021 No Current Record" on a debug message box. If I choose
debug it highlights the code below. If I stop or cancel the debug the
form shows up populated with the correct drugs (there is also a blank
between populated rows.)

Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " &
Trim(rst.Fields("ROUTE").Value) & " " &
Trim(rst.Fields("Mpmp").Value) & ";"



message Theoretically, if you've got RowSourceType set to "Value List", you
should be able to simply concatenate the new entries to the existing
string that makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can be
used for the RowSource in Access 2000 that was increased when the
AddItem method was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

Thanks but I think you missed the premise or I threw you off by
mentioning the AddItem. AddItem doesn't work with 2000. I need this
to work in 2000.
I need a way to do it without AddItem in the code.



Hi OMS,

The additem command should work! I think if you perform the
following steps
your code should work

1) Determine how much columns you would like to use and set it in
the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the
properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable)
contraining the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the columncount
to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

:

Hi,

I've inherited this 2003 code that needs to work in 2000 runtime.
The main
sticking point is the AddItem in a listbox. I've tried a few
things but the
closest I get is it populating only one row in the box with all
items. I've
included the code with remmed lines to give you a better idea of
where I
was, where I am and where I need to be. What am I doing wrong?
Oh, please do
reply telling me AddItem doesn't work in 2000. I know that, I
just want the
same or similar effect and really don't know enough lingo to
reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME,
PHM_ORDERS.BRAND_NAME, " &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS
Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " &
Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " &
Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " &
Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " & Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[Douglas J. Steele]

You'll need to show more of your code, but it would appear that strPMP_Text
doesn't have a value in it, so that nothing's getting added in

strCriteria = "NRS_STATION = """ & strNurSta & _
""" AND ITN = """ & strITN & """ AND " & strPMP_Text

Try:

If Len(strPMP_Text) > 0 Then
strCriteria = "NRS_STATION = """ & strNurSta & _
""" AND ITN = """ & strITN & """ AND " & strPMP_Text
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,
No, the report opens and populates fine. I did what you recommended but
nothing showed up in the immediate window. So I moved the "criteria" code
above this:

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
strPMP = Me.lstPMP.ItemData(intMedCounter)
strTotalPMP = "PMP = """ & strPMP & """ OR "
strPMP_Text = strPMP_Text & strTotalPMP
Else
'*****
End If
Next intMedCounter
strPMP_Text = "(" & Left(strPMP_Text, Len(strPMP_Text) - 4) & ")"

And got debugger with - NRS_STATION = 2WST AND ITN = 123456 AND

Then got a message box that said: Extra ) in query expression
'(NRS_STATION = 2WST AND ITN = 123456 AND)'.

Thanks,

Does the error occur when you open the report without specifying a
criteria? If so, then the error's in your report, not in the code you've
shown.

If not, try replacing

DoCmd.OpenReport strDocName, acViewNormal, , "NRS_STATION = """ &
strNurSta & """ AND ITN = """ & strITN & """ AND " & strPMP_Text

with

Dim strCriteria

strCriteria = "NRS_STATION = """ & strNurSta & _
""" AND ITN = """ & strITN & """ AND " & strPMP_Text
Debug.Print strCriteria
DoCmd.OpenReport strDocName, acViewNormal, , strCriteria

If the error occurs, look in the Immediate Window (Ctrl-G) to see what
was printed there for strCriteria.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That stopped the debugger. Thank you so much. I don't expect you to
solve all my problems but... I still get "Invalid use of Null" though
when I click my print command button. I've Googled it but not sure of
what to do with the info I found.
Any hints?
OMS

Private Sub cmdPrint_Click()
On Error GoTo error
Dim strITN As String
Dim intMedCounter As Integer
Dim strPMP As String
Dim strTotalPMP As String
Dim strDocName As String
Dim strPMP_Text As String
Dim strNurSta As String

strNurSta = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(4))
strITN = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(0))
strDocName = "csm_Discharge_MAR_Summary_2000"

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
strPMP = Me.lstPMP.ItemData(intMedCounter)
strTotalPMP = "PMP = """ & strPMP & """ OR "
strPMP_Text = strPMP_Text & strTotalPMP
Else
'*****
End If
Next intMedCounter
strPMP_Text = "(" & Left(strPMP_Text, Len(strPMP_Text) - 4) & ")"

DoCmd.OpenReport strDocName, acViewNormal, , "NRS_STATION = """ &
strNurSta & """ AND ITN = """ & strITN & """ AND " & strPMP_Text

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
Me.lstMedications.Selected(intMedCounter) = False
End If
Next intMedCounter

exit_sub:
On Error GoTo 0
Exit Sub


Try moving your rst.MoveNext statement to just before the Loop
statement.

You're moving past the end of the recordset before you try to use the
values in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug,

All data is live. I have other reports that generate the same info to
confirm.
Recordset opened by Set rst = db.OpenRecordset(strSQL) See below for
Form_Load code.
I found that it only tries to debug if I go directly from design mode,
if just opening as a user would, no debugger. I'll have to add some
error handling.

Lines are now populating every other one ie.
drug
blank
drug
blank

Unfortunetly when I do choose one of the items in the listbox and
click my print command I get "Invalid use of Null". The report it was
calling did have a "Null" expression in it, took it out but still
didn't work. I then tried to "clean" the SQL in the Form_Load below of
the Null statement but Access didn't like it. Red. That corrupted
something and db didn't work at all. Luckily I always start with a
fresh copy.
Any ideas?
Thanks,
OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient


strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, "
& _
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," &
_

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR = tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " &
Trim(rst.Fields("ROUTE").Value) & " " & Trim(rst.Fields("Mpmp").Value)
& ";"
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
message What's the code around that statement? How did you open the
recordset? Are you sure you do have a current record in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


HI,

That was very helpful. I used below in my form but returned a
runtime error "3021 No Current Record" on a debug message box. If I
choose debug it highlights the code below. If I stop or cancel the
debug the form shows up populated with the correct drugs (there is
also a blank between populated rows.)

Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " &
Trim(rst.Fields("ROUTE").Value) & " " &
Trim(rst.Fields("Mpmp").Value) & ";"



message Theoretically, if you've got RowSourceType set to "Value List", you
should be able to simply concatenate the new entries to the
existing string that makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can
be used for the RowSource in Access 2000 that was increased when
the AddItem method was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

Thanks but I think you missed the premise or I threw you off by
mentioning the AddItem. AddItem doesn't work with 2000. I need
this to work in 2000.
I need a way to do it without AddItem in the code.



message Hi OMS,

The additem command should work! I think if you perform the
following steps
your code should work

1) Determine how much columns you would like to use and set it in
the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the
properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable)
contraining the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the
columncount to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

:

Hi,

I've inherited this 2003 code that needs to work in 2000
runtime. The main
sticking point is the AddItem in a listbox. I've tried a few
things but the
closest I get is it populating only one row in the box with all
items. I've
included the code with remmed lines to give you a better idea of
where I
was, where I am and where I need to be. What am I doing wrong?
Oh, please do
reply telling me AddItem doesn't work in 2000. I know that, I
just want the
same or similar effect and really don't know enough lingo to
reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME,
PHM_ORDERS.BRAND_NAME, " &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS
Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " &
Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " &
Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " &
Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " &
Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

 

[OMS]

That worked!
Thanks so much for your help.
OMS

You'll need to show more of your code, but it would appear that
strPMP_Text doesn't have a value in it, so that nothing's getting added in

strCriteria = "NRS_STATION = """ & strNurSta & _
""" AND ITN = """ & strITN & """ AND " & strPMP_Text

Try:

If Len(strPMP_Text) > 0 Then
strCriteria = "NRS_STATION = """ & strNurSta & _
""" AND ITN = """ & strITN & """ AND " & strPMP_Text
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,
No, the report opens and populates fine. I did what you recommended but
nothing showed up in the immediate window. So I moved the "criteria" code
above this:

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
strPMP = Me.lstPMP.ItemData(intMedCounter)
strTotalPMP = "PMP = """ & strPMP & """ OR "
strPMP_Text = strPMP_Text & strTotalPMP
Else
'*****
End If
Next intMedCounter
strPMP_Text = "(" & Left(strPMP_Text, Len(strPMP_Text) - 4) & ")"

And got debugger with - NRS_STATION = 2WST AND ITN = 123456 AND

Then got a message box that said: Extra ) in query expression
'(NRS_STATION = 2WST AND ITN = 123456 AND)'.

Thanks,

Does the error occur when you open the report without specifying a
criteria? If so, then the error's in your report, not in the code you've
shown.

If not, try replacing

DoCmd.OpenReport strDocName, acViewNormal, , "NRS_STATION = """ &
strNurSta & """ AND ITN = """ & strITN & """ AND " & strPMP_Text

with

Dim strCriteria

strCriteria = "NRS_STATION = """ & strNurSta & _
""" AND ITN = """ & strITN & """ AND " & strPMP_Text
Debug.Print strCriteria
DoCmd.OpenReport strDocName, acViewNormal, , strCriteria

If the error occurs, look in the Immediate Window (Ctrl-G) to see what
was printed there for strCriteria.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That stopped the debugger. Thank you so much. I don't expect you to
solve all my problems but... I still get "Invalid use of Null" though
when I click my print command button. I've Googled it but not sure of
what to do with the info I found.
Any hints?
OMS

Private Sub cmdPrint_Click()
On Error GoTo error
Dim strITN As String
Dim intMedCounter As Integer
Dim strPMP As String
Dim strTotalPMP As String
Dim strDocName As String
Dim strPMP_Text As String
Dim strNurSta As String

strNurSta = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(4))
strITN = Trim(Forms!usr_frm_SelectMAR!lstPatient.Column(0))
strDocName = "csm_Discharge_MAR_Summary_2000"

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
strPMP = Me.lstPMP.ItemData(intMedCounter)
strTotalPMP = "PMP = """ & strPMP & """ OR "
strPMP_Text = strPMP_Text & strTotalPMP
Else
'*****
End If
Next intMedCounter
strPMP_Text = "(" & Left(strPMP_Text, Len(strPMP_Text) - 4) & ")"

DoCmd.OpenReport strDocName, acViewNormal, , "NRS_STATION = """ &
strNurSta & """ AND ITN = """ & strITN & """ AND " & strPMP_Text

For intMedCounter = 0 To Me.lstMedications.ListCount - 1
If Me.lstMedications.Selected(intMedCounter) = True Then
Me.lstMedications.Selected(intMedCounter) = False
End If
Next intMedCounter

exit_sub:
On Error GoTo 0
Exit Sub


message Try moving your rst.MoveNext statement to just before the Loop
statement.

You're moving past the end of the recordset before you try to use the
values in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug,

All data is live. I have other reports that generate the same info to
confirm.
Recordset opened by Set rst = db.OpenRecordset(strSQL) See below for
Form_Load code.
I found that it only tries to debug if I go directly from design
mode, if just opening as a user would, no debugger. I'll have to add
some error handling.

Lines are now populating every other one ie.
drug
blank
drug
blank

Unfortunetly when I do choose one of the items in the listbox and
click my print command I get "Invalid use of Null". The report it was
calling did have a "Null" expression in it, took it out but still
didn't work. I then tried to "clean" the SQL in the Form_Load below
of the Null statement but Access didn't like it. Red. That corrupted
something and db didn't work at all. Luckily I always start with a
fresh copy.
Any ideas?
Thanks,
OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient


strSQL = "SELECT PHM_ORDERS.GENERIC_NAME, PHM_ORDERS.BRAND_NAME, "
& _
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS Mpmp," &
_

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR = tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " &
Trim(rst.Fields("ROUTE").Value) & " " &
Trim(rst.Fields("Mpmp").Value) & ";"
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
message What's the code around that statement? How did you open the
recordset? Are you sure you do have a current record in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


HI,

That was very helpful. I used below in my form but returned a
runtime error "3021 No Current Record" on a debug message box. If I
choose debug it highlights the code below. If I stop or cancel the
debug the form shows up populated with the correct drugs (there is
also a blank between populated rows.)

Me.lstMedications.RowSource = Me.lstMedications.RowSource & _
IIf(Len(Me.lstMedications.RowSource) > 0, ";", "") & _
Trim(rst.Fields("GENERIC_NAME").Value) & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")") & " " &
Trim(fDose(rst.Fields("DOSE").Value)) & " " &
Trim(rst.Fields("modSig").Value) & " " &
Trim(rst.Fields("ROUTE").Value) & " " &
Trim(rst.Fields("Mpmp").Value) & ";"



message Theoretically, if you've got RowSourceType set to "Value List",
you should be able to simply concatenate the new entries to the
existing string that makes up the RowSource property:

In other words, rather than:

Me.mylistbox.AddItem "test,sub1,test2,sub2"

try

Me.mylistbox.RowSource = Me.mylistbox.RowSource & _
IIf(Len(Me.mylistbox.RowSource) > 0, ";", "") & _
"test,sub1,test2,sub2"


Problem is, I think there may be a limit on how long a string can
be used for the RowSource in Access 2000 that was increased when
the AddItem method was introduced.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

Thanks but I think you missed the premise or I threw you off by
mentioning the AddItem. AddItem doesn't work with 2000. I need
this to work in 2000.
I need a way to do it without AddItem in the code.



message
Hi OMS,

The additem command should work! I think if you perform the
following steps
your code should work

1) Determine how much columns you would like to use and set it
in the
columncount property of your listbox
2) Set the rowsourcetype to value list (either directly in the
properties or
via runtime:Me.lstMedications.RowSourceType = "Value List")
3) Make a string (I think this is your Rowlist variable)
contraining the
items to add. Each field should be separated by ","

Your listbox should now be updated correctly.

To see how it works try a simpler code first like the following:
1) create a userform
2) add a listbox control (named mylistbox) and set the
columncount to 2
3) add a commandbutton and put the following code in there
Me.mylistbox.RowSourceType = "Value list"
Me.mylistbox.AddItem "test,sub1,test2,sub2"

The result should be that there are two rows added

:

Hi,

I've inherited this 2003 code that needs to work in 2000
runtime. The main
sticking point is the AddItem in a listbox. I've tried a few
things but the
closest I get is it populating only one row in the box with all
items. I've
included the code with remmed lines to give you a better idea
of where I
was, where I am and where I need to be. What am I doing wrong?
Oh, please do
reply telling me AddItem doesn't work in 2000. I know that, I
just want the
same or similar effect and really don't know enough lingo to
reference
correctly. Like legacy coding or some such thing.

Hope you can help.
Thanks, OMS

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim strItem As String
Dim strPMP As String
Dim strITN As String
Dim strNurSta As String
Dim strCaption As String
Dim RowItem As String
Dim RowList As String
Set frm = Forms!usr_frm_SelectMAR
Set ctl = frm!lstPatient

strSQL = "SELECT PHM_ORDERS.GENERIC_NAME,
PHM_ORDERS.BRAND_NAME, " &_
"PHM_ORDERS.DOSE, PHM_ORDERS.ROUTE, Val([PMP]) AS
Mpmp," & _

"IIf(IsNull([DESCRIPTION]),Trim([LATIN_DIR_ABBR]),Trim([DESCRIPTION]))
AS modSig " & _
"FROM PHM_ORDERS LEFT JOIN tblLatin ON
PHM_ORDERS.LATIN_DIR_ABBR
= tblLatin.[LATIN CODE] " & _
"WHERE PHM_ORDERS.ITN = """ & m_strITN & """ " & _
"AND PHM_ORDERS.MED_IV <> ""S"" " & _
"AND PHM_ORDERS.SCH_PRN_TKH = ""SCHEDULED"" " & _
"AND PHM_ORDERS.STOPPED = ""NO"" " & _
"ORDER BY Val([PMP]) DESC ;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Do Until rst.EOF
' strItem = Trim(rst.Fields("GENERIC_NAME").Value)
' strItem = strItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value)) = "", "", "(" &
Trim(rst.Fields("BRAND_NAME").Value) & ")")
' strItem = strItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
' strItem = strItem & " " &
Trim(rst.Fields("modSig").Value)
' strItem = strItem & " " &
Trim(rst.Fields("ROUTE").Value)
' strPMP = Trim(rst.Fields("Mpmp").Value)
RowItem = Trim(rst.Fields("GENERIC_NAME").Value)
RowItem = RowItem & " " &
IIf(Trim((rst.Fields("BRAND_NAME").Value))
= "", "", "(" & Trim(rst.Fields("BRAND_NAME").Value) & ")")
RowItem = RowItem & " " &
Trim(fDose(rst.Fields("DOSE").Value))
RowItem = RowItem & " " &
Trim(rst.Fields("modSig").Value)
RowItem = RowItem & " " &
Trim(rst.Fields("ROUTE").Value)
strPMP = Trim(rst.Fields("Mpmp").Value)

' strItem = RowItem
' RowItem = strItem

RowList = RowItem
RowList = RowList & RowItem
rst.MoveNext

' Me.lstMedications.AddItem strItem
' Me.lstPMP.AddItem strPMP
' rst.MoveNext

Me.lstMedications.RowSourceType = "Value List"
Me.lstMedications.RowSource = RowList
Me.lstMedications.Requery
Me.Refresh

Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub