Coding to make AddItem work for 2000

O

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
 
B

Brotha Lee

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

OMS said:
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
Click to expand...
 
O

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.



Brotha Lee said:
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

OMS said:
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
Click to expand...
Click to expand...
 
D

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!)


OMS said:
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.



Brotha Lee said:
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

OMS said:
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
Click to expand...
Click to expand...
Click to expand...
 
O

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) & ";"



Douglas J. Steele said:
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!)


OMS said:
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.



Brotha Lee said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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)


OMS said:
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) & ";"



Douglas J. Steele said:
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!)


OMS said:
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
Click to expand...
Click to expand...
Click to expand...
 
O

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
Douglas J. Steele said:
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)


OMS said:
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) & ";"



Douglas J. Steele said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Hiding" field if another field is referenced in another table. 5
Pulling data from a Listbox to a table. 3
Query by Form: Output to Listbox? 4
Attachments 4
How to Reference Combo Box by Name in Word VBA 2
Appending problem after splitting field 2
OLEDB to 64bit SQL Server 4
Filtered Records as source for appending records to a table 1

Top