Using a List Box

[Rhonda]

Using Access 2002 VBA

I'm trying to post events in my program as they occur, using a list
box(named ev_log).
With:
(In Module1)
Public Function StatusOut(LogItem$, ByVal bLogFile As Boolean) As Boolean
Forms!frmTest.ev_log.AddItem LogItem$, 0
Forms!frmTest.ev_log.Selected(0) = True
If bLogFile Then
If bLogging Then
PrintString$ = LogItem$ & Chr(13) & Chr(10)
If bLogFileChgBusy = False Then
Err.Clear
Put #hLogFile, , PrintString$
If Err Then
LogItem$ = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR -
Can't Save to Log File: " & Error$
Ret = StatusOut(LogItem$, 1)
bLogging = False
Err.Clear
bError = True
End If
End If
End If
End If

To post to the log from anywhere I use:
LogItem$ = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR - Can't Save to Log
File: " & Error$

This works perfect in VB6, but in VBA It won't display anything.

A test:
Private Sub Command41_Click()
LogItem$ = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR - Test Log Display "
End Sub

Any suggestions appreciated.

 

[storrboy]

1) There are a number of variables (I assume they are that) that are
not definded in that function so I have no idea what effect they may
have on the outcome. Nor did you post the full function, so I can't
comment on anything else that may be failing.
2) In your "test" example, you are only trying to set the value of
LogItem$ and not calling the function that uses it. I would venture
that your test should look more like

Private Sub Command41_Click()
Dime testVar As String
testVar = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR - Test Log
Display "
MsgBox "Function Ran: " & StatusOut(testVar, False)
End Sub

 

[Rhonda]

1) There are a number of variables (I assume they are that) that are
not definded in that function so I have no idea what effect they may
have on the outcome. Nor did you post the full function, so I can't
comment on anything else that may be failing.
2) In your "test" example, you are only trying to set the value of
LogItem$ and not calling the function that uses it. I would venture
that your test should look more like

Private Sub Command41_Click()
Dime testVar As String
testVar = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR - Test Log
Display "
MsgBox "Function Ran: " & StatusOut(testVar, False)
End Sub

1. Sorry, I left off the "End Sub" off the function. It is complete except
for that.
All variables are defined.
2. The "test" example depicts the Exact syntax used in the VB6 version that
does work.

I see what you mean about not calling the function.

I found a: Ret = StatusOut(LogItem$, 1) after one set of messages.

Very strange how well this works in vb6 & how flaky it is in vba.

I tried:
LogItem$ = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR - Test Log
Display "
StatusOut(LogItem$, False)

But returns: a syntax error.

 

[Rhonda]

Using Access 2002 VBA

I'm trying to post events in my program as they occur, using a list
box(named ev_log).
With:
(In Module1)
Public Function StatusOut(LogItem$, ByVal bLogFile As Boolean) As Boolean
Forms!frmTest.ev_log.AddItem LogItem$, 0
Forms!frmTest.ev_log.Selected(0) = True
If bLogFile Then
If bLogging Then
PrintString$ = LogItem$ & Chr(13) & Chr(10)
If bLogFileChgBusy = False Then
Err.Clear
Put #hLogFile, , PrintString$
If Err Then
LogItem$ = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR -
Can't Save to Log File: " & Error$
Ret = StatusOut(LogItem$, 1)
bLogging = False
Err.Clear
bError = True
End If
End If
End If
End If

To post to the log from anywhere I use:
LogItem$ = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR - Can't Save to Log
File: " & Error$

This works perfect in VB6, but in VBA It won't display anything.

A test:
Private Sub Command41_Click()
LogItem$ = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR - Test Log Display "
End Sub

Any suggestions appreciated.

So the Big question is then:

How do I write to a List Box in vba, the way I would in vb6?

Using commands like .AddItem, Clear, List, ListCount, or MoveItem doesn't
work.

 

[storrboy]

1. Sorry, I left off the "End Sub" off the function. It is complete except

for that.
All variables are defined.

Since you don't have any Dim statements in that function, you are
either not using Option Explicit (which usually helps in debugging
code), or they have been defined as public or global somewhere else
that I don't see. Be careful when reusing variables.

2. The "test" example depicts the Exact syntax used in the VB6 version that
does work.

While VBA and VB share the core language, there are many diferences
between them. Not all conventions are compatible with each other. I
have to say, I'm a little skeptical about them being identical. If so,
I can't really imagine how it works.

I see what you mean about not calling the function.
I found a: Ret = StatusOut(LogItem$, 1) after one set of messages.

In all my VB programming experience, the only way I've seen to execute
a function is to call it. I can't imagine any function running by only
refering to it's arguments, without being called in some fashion.

LogItem$ = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR - Test Log
Display "
StatusOut(LogItem$, False)

But returns: a syntax error.

Because this is still very different from what I suggested. A function
must be called by either assigning it's return value to something as
in Ret = StatusOut(LogItem$, 1) or when not assigning the return as in
StatusOut LogItem$, False. The ( ) are not used without the value
assignment.

Using commands like .AddItem, Clear, List, ListCount, or MoveItem doesn't
work.

They do, just not they way you are using them. I re-created your
function such that it ignores the file appending (I don't want to deal
with that tonight, I did not change the listbox portion of it) and it
does work the way I specified. Modify the calling test to look similar
to this

Private Sub Command6_Click()
Dim txt As String

txt = Format(Now, "m/d/yy h:mm AMPM") & ": ERROR - Test Log
Display"
StatusOut txt, False
End Sub

As long as the form is visible, the listbox rowsource is set to Value
List and the column count matches the LogItem$ format, it appended a
new row to list.

 

[John W. Vinson]

So the Big question is then:

How do I write to a List Box in vba, the way I would in vb6?

Using commands like .AddItem, Clear, List, ListCount, or MoveItem doesn't
work.

THe Listbox (and Combo Box) controls are VERY different in the two languages.

A Listbox has a Row Source property, and a RowSource Type property. The
RowSource type can be "Value List" or "Table/Query"; in the former case you
just set the RowSource to a semicolon separated string of values. Much more
commonly, you would use a Query selecting the values that you want displayed
in the control.

John W. Vinson [MVP]

 

[Albert D. Kallal]

While VB6, and VBA share the same compiler, and same syntax, the object
models are very much different.

Combo boxes in VB6 were very limited, and simple. The combo box in access
has MANY more events, and more properties also.

the "additem" methoed can ONLY be used when you bind the combo box to a text
string (not a table, or sql, or reocrdset). So, you can't use use .additem.


You have a few choices:

Leave the combo box as is, and udpate the table direclty via code. You then
do a requery on the listox box to reload, and then the value of the list box
to "highlight" the value...

You can also bind a combo box directly to recordset, and then add to that
reocrdset (but, that means you have to declare a module level recordset in
the forms code).

I think, in your case, I would just modify your code...and add to the
listbox recordset...

Try:

Forms!frmTest.ev_log.Recordset.AddNew
Forms!frmTest.ev_log.Recordset!NameOfField = LogItem$
Forms!frmTest.ev_log.Recordset.Update

Forms!frmTest.ev_log = LogItem$

If the lsitbox is multi-column, you MUST USE the first column (the column
that list box is bound to) to set the value. "setting" the value of the
control will highlight the chosen value... (you don't use selected unless
you allow multiple selections).

Also, if the log code is running in the same form, then you can replace
forms!frmTest with me!

 

[Rhonda]

While VB6, and VBA share the same compiler, and same syntax, the object
models are very much different.

Combo boxes in VB6 were very limited, and simple. The combo box in access
has MANY more events, and more properties also.

the "additem" methoed can ONLY be used when you bind the combo box to a
text string (not a table, or sql, or reocrdset). So, you can't use use
.additem.


You have a few choices:

Leave the combo box as is, and udpate the table direclty via code. You
then do a requery on the listox box to reload, and then the value of the
list box to "highlight" the value...

You can also bind a combo box directly to recordset, and then add to that
reocrdset (but, that means you have to declare a module level recordset in
the forms code).

I think, in your case, I would just modify your code...and add to the
listbox recordset...

Try:

Forms!frmTest.ev_log.Recordset.AddNew
Forms!frmTest.ev_log.Recordset!NameOfField = LogItem$
Forms!frmTest.ev_log.Recordset.Update

Forms!frmTest.ev_log = LogItem$

If the lsitbox is multi-column, you MUST USE the first column (the column
that list box is bound to) to set the value. "setting" the value of the
control will highlight the chosen value... (you don't use selected unless
you allow multiple selections).

Also, if the log code is running in the same form, then you can replace
forms!frmTest with me!

My objective is:

I needed this single column list box feature to display single line program
events, feedback, serial port status, etc. real time, not records to be also
stored.

Once the application is closed the listed items will be gone, there may be a
clear button also.

Will the Recordset allow realtime display of my info, added line-by-line as
I had before? The events listed stack up in the normal course of operation,
allowing the user to view. So with the Recordset version, I would use the
vba Listbox?

 

[Albert D. Kallal]

My objective is:

Ah, ok...

The thing that threw me off is that your listbox was based on some sql (and
therefore a table). thus, I had to assume you trying to add to a table, and
then display it on the screen.

You just need a nice scrolling "window", or box on the screen that you can
throw text, and status messages....(not a bound listbox).

you *can* continue to use a listbox, and you can feed a listbox a long
delimited string to display stuff. (however, it has a max of 4000
characters). You can also use what is called a call back unction to fill a
listbox.

However, you don't really need a control based on a table, or even internal
data structure, you simply just want to pump out text messages...and this
box "scroll" like a old dos window!!


What you need is to simply place a plane Jane text box on the form. size the
text box nice and high (however many lines you want to display).

You should only need a "vertical" scroll bar (this setting is found in the
text box property sheet - format tab, set scroll bars to "vertical").

Now, to pump out text and messages...simply go:

me.MyTextBox = me.MyTextBox & strNextLineOfText & vbcrlf
dovents

The above two lines of code is all you need...and it is dead simple. The
result will be a window that scrolls text as you feed it any value.....

Give the above a try...I done the above for continues "messages" type
screens...

The text box will thus be un-bound....

 

[Rhonda]

Ah, ok...

The thing that threw me off is that your listbox was based on some sql
(and therefore a table). thus, I had to assume you trying to add to a
table, and then display it on the screen.

You just need a nice scrolling "window", or box on the screen that you can
throw text, and status messages....(not a bound listbox).

you *can* continue to use a listbox, and you can feed a listbox a long
delimited string to display stuff. (however, it has a max of 4000
characters). You can also use what is called a call back unction to fill a
listbox.

However, you don't really need a control based on a table, or even
internal data structure, you simply just want to pump out text
messages...and this box "scroll" like a old dos window!!


What you need is to simply place a plane Jane text box on the form. size
the text box nice and high (however many lines you want to display).

You should only need a "vertical" scroll bar (this setting is found in the
text box property sheet - format tab, set scroll bars to "vertical").

Now, to pump out text and messages...simply go:

me.MyTextBox = me.MyTextBox & strNextLineOfText & vbcrlf
dovents

The above two lines of code is all you need...and it is dead simple. The
result will be a window that scrolls text as you feed it any value.....

Give the above a try...I done the above for continues "messages" type
screens...

The text box will thus be un-bound....

That worked Albert.

One more tiny thing, a Clear button, there is no .Clear for text boxes.
I've tried selecting & writing " ", but no.

 

[Rhonda]

That worked Albert.

One more tiny thing, a Clear button, there is no .Clear for text boxes.
I've tried selecting & writing " ", but no.

It is done.

To clear, I used:
Me.MyTextBox.SetFocus 'Selects all text
Me.MyTextBox.Text = ""

Thanks for your time.

 

[Rhonda]

It is done.

To clear, I used:
Me.MyTextBox.SetFocus 'Selects all text
Me.MyTextBox.Text = ""

Thanks for your time.

Sorry, a follow up...


What if it ignores the vbCrLf or a vbCr ?
and new text string write over the previous...

 

[Rhonda]

Sorry, a follow up...


What if it ignores the vbCrLf or a vbCr ?
and new text string write over the previous...

I have the List Box working!!

It didn't like the "="

So this works: Me!List4.AddItem "Line of text here"
with each on a new line like vb.

The strange thing is:
- The text wraps half way across the width of the list box, checking width.
- Can't quite Clear, or Select all & clear yet.

 

[Albert D. Kallal]

That worked Albert.

One more tiny thing, a Clear button, there is no .Clear for text boxes.
I've tried selecting & writing " ", but no.

Just use:

me.MyTextBox = null

 

[Albert D. Kallal]

To clear, I used:
Me.MyTextBox.SetFocus 'Selects all text
Me.MyTextBox.Text = ""

NO NO!!..that is incorrect...

Use

me.MyTextBox.Value = ""
or
me.MyTextBox.Value = null

Since "value" is the default property, then you can go:

me.MytextBox = null

Do NOT USE the .text property, as that is ONLY for use in the "on change"
event of the control...for all other uses...you need to use .value. further,
if you use .text, then the control has to have focus...which again makes no
sense at all. (it would be quite lame if you had to set the focus to a
control in code every time you want to change it).

In VB6, you use the .text, but, in ms-access, we use .value to modify a
contorl...

 

[Albert D. Kallal]

What if it ignores the vbCrLf or a vbCr ?
and new text string write over the previous...

Then it means you have a bug, or your syntaxt is incorrect.....

Post the two lines of code...

it should look like:

me.MyTextBox = me.MyTextBox & strSomeTextValue & vbcrlf

 

[Albert D. Kallal]

I have the List Box working!!

Gee, you mean after all this, you throw out the text box idea..and revert
back to the complex listbox???

I would have thought that a plane jane text box is the way to go here.....

I assume you just displaying some messages...right?

I am rather confused now that you dropped the simple text box, and gone back
to the complex listbox?

I don't see the need for using a listbox.....

 

[Rhonda]

Albert

I'm back to using the List Box.(as my original vb6 example did)

See my post just preceeding this.

 

[Rhonda]

Gee, you mean after all this, you throw out the text box idea..and revert
back to the complex listbox???

I would have thought that a plane jane text box is the way to go here.....

I assume you just displaying some messages...right?

I am rather confused now that you dropped the simple text box, and gone
back to the complex listbox?

I don't see the need for using a listbox.....

Albert

When I discovered my List box problem with the "=" you had not yet
responded to this posting.
And, I was sitting here with a text box line writing over each previous
line, with the vbCr being ignored.

I stumbled accross a forum thread discussing use of the List Box in vba with
an example, with no "=" and it worked, like my original vb6 example.

It is clearly not complex to me, as it is straightforward, and works, as
opposed to the text box.

You can check the chronology of this post if you like, I'm not trying to be
a problem for you, just accomplish an objective.

 

[Rhonda]

Albert

Forgive me, I mistated about you not sponding to this post before I found my
"=" problem.

I've had a few late nights lately struggling with these incidentals. I must
get an Access2002 application updated & going quickly with this status
indicator. Then I'll get back to converting my whole Access application to
vb6.

Thanks for your time.