By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Sub routine syntax errors

P: n/a
Please help me correct the statements in sub "BoundData"

The following sub is in a module. Everything runs with no error messages,
however,data is not reaching the text box. Data is being appended to
WorkingTable but not filtered by value in text box. If I input data into the
text box, it gets deleted by the sub routine, indicating that
ctla.ItemData(varItm) is null.

Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control, ctlb As Control, ctlc As Control 'List boxes, Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim <list of fields snipped As Fields
Dim MainTable, WorkingTable As TableDefs

DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;"

Set frm = Form3
Set ctla = Forms!Form3!ctla
For Each varItm In ctla.ItemsSelected
Forms!Form3.Text1 = ctla.ItemData(varItm) ' Text1 is name of text box
on form3

DoCmd.RunMacro "AppendData" ' SQL 256 characters
Next varItm

End Sub

Aug 11 '07 #1
Share this Question
Share on Google+
16 Replies


P: n/a
Chuck wrote:
Please help me correct the statements in sub "BoundData"

The following sub is in a module. Everything runs with no error messages,
however,data is not reaching the text box. Data is being appended to
WorkingTable but not filtered by value in text box. If I input data into the
text box, it gets deleted by the sub routine, indicating that
ctla.ItemData(varItm) is null.

Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control, ctlb As Control, ctlc As Control 'List boxes, Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim <list of fields snipped As Fields
Dim MainTable, WorkingTable As TableDefs

DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;"

Set frm = Form3
Set ctla = Forms!Form3!ctla
For Each varItm In ctla.ItemsSelected
Forms!Form3.Text1 = ctla.ItemData(varItm) ' Text1 is name of text box
on form3

DoCmd.RunMacro "AppendData" ' SQL 256 characters
Next varItm

End Sub
You are storing data from all items selected in ctla into Text1, over
and over. Nobody here would actually know what AppendData actually does.

Since you appear to know some VBA, why not use VBA instead of a Macro.
Dim lngID As Long
Dim strSQL As String
For Each varItem In Me.ListBoxName.ItemsSelected
lngID = Me.ListBoxName.Column(0, varItem)
strSQL = ...
Currentdb.Execute strSQL, dbFailOnError
Next

Now, the name of your sub is BoundData. Is text1 a bound field? Simply
stuffing a value into a bound textbox does nothing unless the record is
saved.

BTW, are you attempting to delete (drop) a field or update the contents
of the field to Null for records that match your criteria?

You might want to explain your problem with some additional detail. I'm
sure a lot of us use VBA because you can debug a problem far easier than
a macro we know nothing about.
Aug 11 '07 #2

P: n/a
On Sat, 11 Aug 2007 13:08:45 -0700, Salad <oi*@vinegar.comwrote:
>Chuck wrote:
>Please help me correct the statements in sub "BoundData"

The following sub is in a module. Everything runs with no error messages,
however,data is not reaching the text box. Data is being appended to
WorkingTable but not filtered by value in text box. If I input data into the
text box, it gets deleted by the sub routine, indicating that
ctla.ItemData(varItm) is null.

Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control, ctlb As Control, ctlc As Control 'List boxes, Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim <list of fields snipped As Fields
Dim MainTable, WorkingTable As TableDefs

DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;"

Set frm = Form3
Set ctla = Forms!Form3!ctla
For Each varItm In ctla.ItemsSelected
Forms!Form3.Text1 = ctla.ItemData(varItm) ' Text1 is name of text box
on form3

DoCmd.RunMacro "AppendData" ' SQL 256 characters
Next varItm

End Sub
You are storing data from all items selected in ctla into Text1, over
and over. Nobody here would actually know what AppendData actually does.
I've been testing with a single selected Item.

What I'm aiming foe is at each pass thru the for next loop a different set of
data is appended to the WorkingTable from the MainTable. Anywhere from one to
40 records could be appended on each pass.
>
Since you appear to know some VBA, why not use VBA instead of a Macro.
Dim lngID As Long
Dim strSQL As String
For Each varItem In Me.ListBoxName.ItemsSelected
If I insert Me. in front of ListBoxName, an error message says improper use of
Me.
lngID = Me.ListBoxName.Column(0, varItem)
Changing (varItm) to .Column(0, varItm) had no effect.
> strSQL = ...
Currentdb.Execute strSQL, dbFailOnError
Next
The SQL string is over 1200 characters. I thought I could only use an SQL
statement with less than 256 characters in this manner.
>Now, the name of your sub is BoundData. Is text1 a bound field?
No, it is not bound. The Where part of the SQL statement uses the value in the
text box. If I hard wire a value into the text box, the query runs just fine.
>Simply
stuffing a value into a bound textbox does nothing unless the record is
saved.
The record only has to survive until the Append query runs.
>
BTW, are you attempting to delete (drop) a field or update the contents
of the field to Null for records that match your criteria?
DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;" deletes every
field for every records in the table. It works correctly.
>
You might want to explain your problem with some additional detail. I'm
sure a lot of us use VBA because you can debug a problem far easier than
a macro we know nothing about.
The Append query exists. It as the very long SQL statement. The macro merely
runs the Append query:
Open query
Query name: AppendDataToWorkingTable
View: Datasheet
Data mode: Edit

The subject "Sub routine syntax errors" is itself in error. The syntax is
just fine. What I have is logic errors. The sub routine is doing exactly what
it is told to do. But, unfortunately, that is not what I want it to do. If I
can correct the logic I think That is all that will be needed.
Aug 12 '07 #3

P: n/a
Try this...
First create a Command Button named btnRunThis on the Form that contains
ctla and text1.
Go to the button's properties and in the OnClick event invoke Code Builder
and paste in the code below
Air Code Warning...
***********************
Sub btnRunThis_OnClick()
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
Dim strSql as String

'Delete all Records from WorkingTable
strSql = "Delete * FROM WorkingTable;"
db.execute strSql

'Me refers to the form that contains this code
For Each varItem In Me.ctla.ItemsSelected
Me.Text1 = Me.ctla.ItemData(varItem)

DoCmd.RunMacro "AppendData"
Next varItm

Resume btnRunThis_OnClick_Exit:
Exit sub

btnRunThis_OnClick_Error:
msgbox Err.Number & " " & Err.Description
Resume btnRunThis_OnClick_Exit

End Sub
**********************************

Good Luck

Len Robichaud
Aug 12 '07 #4

P: n/a
On Sat, 11 Aug 2007 23:45:20 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:

Thank you for your help.
I'm running Access 97 and it didn't recognize some of the statements.
Example: Dim varItem as Variable had to be changed to Dim varItem as Variant
because it didn't understand 'Variable'
So some lines of code were modified to make it run.

This code worked exactly the same as my original code.
Me.Text1 = Me.ctla.ItemData(varItem) is not getting any value at all from
ctla.

The list box has four choices, A, B, C, and D
If I select A, no records are appended.
If I select A and C, no records ate appended - twice. I can see that is the
append query runs twice, so the code recognizes that two choices were made.
If I change '= Me.ctla.ItemData(varItem)' to = "A", then all the records that
have the single character A in the field are copied to the WorkingTable.

Therefore, I presume that the only error is getting 'ctla.ItemData(varItem)' to
Text1.

Using ctla.ItemData colunm(0, varItem) didn't change anything.

Chuck
--

>Try this...
First create a Command Button named btnRunThis on the Form that contains
ctla and text1.
Go to the button's properties and in the OnClick event invoke Code Builder
and paste in the code below
Air Code Warning...
***********************
Sub btnRunThis_OnClick()
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
Dim strSql as String

'Delete all Records from WorkingTable
strSql = "Delete * FROM WorkingTable;"
db.execute strSql

'Me refers to the form that contains this code
For Each varItem In Me.ctla.ItemsSelected
Me.Text1 = Me.ctla.ItemData(varItem)

DoCmd.RunMacro "AppendData"
Next varItm

Resume btnRunThis_OnClick_Exit:
Exit sub

btnRunThis_OnClick_Error:
msgbox Err.Number & " " & Err.Description
Resume btnRunThis_OnClick_Exit

End Sub
**********************************

Good Luck

Len Robichaud
Aug 12 '07 #5

P: n/a
My bad on the Dim Statement it should be as Variant
I suspect the problem is with your Append query
Let's test what is happening, put
msgbox = Me.text1 in it's own line just before the Docmd
That will tell us what is being passed to text1. If the value is there then
the problem is with the query/macro.
What are the criteria for your append query?

Len

"Chuck" <li*****@schoollink.netwrote in message
news:uh********************************@4ax.com...
On Sat, 11 Aug 2007 23:45:20 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:

Thank you for your help.
I'm running Access 97 and it didn't recognize some of the statements.
Example: Dim varItem as Variable had to be changed to Dim varItem as Variant
because it didn't understand 'Variable'
So some lines of code were modified to make it run.

This code worked exactly the same as my original code.
Me.Text1 = Me.ctla.ItemData(varItem) is not getting any value at all from
ctla.

The list box has four choices, A, B, C, and D
If I select A, no records are appended.
If I select A and C, no records ate appended - twice. I can see that is
the
append query runs twice, so the code recognizes that two choices were made.
If I change '= Me.ctla.ItemData(varItem)' to = "A", then all the records
that
have the single character A in the field are copied to the WorkingTable.

Therefore, I presume that the only error is getting 'ctla.ItemData(varItem)'
to
Text1.

Using ctla.ItemData colunm(0, varItem) didn't change anything.

Chuck
--

>Try this...
First create a Command Button named btnRunThis on the Form that contains
ctla and text1.
Go to the button's properties and in the OnClick event invoke Code Builder
and paste in the code below
Air Code Warning...
***********************
Sub btnRunThis_OnClick()
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
Dim strSql as String

'Delete all Records from WorkingTable
strSql = "Delete * FROM WorkingTable;"
db.execute strSql

'Me refers to the form that contains this code
For Each varItem In Me.ctla.ItemsSelected
Me.Text1 = Me.ctla.ItemData(varItem)

DoCmd.RunMacro "AppendData"
Next varItm

Resume btnRunThis_OnClick_Exit:
Exit sub

btnRunThis_OnClick_Error:
msgbox Err.Number & " " & Err.Description
Resume btnRunThis_OnClick_Exit

End Sub
**********************************

Good Luck

Len Robichaud

Aug 12 '07 #6

P: n/a
On Sat, 11 Aug 2007 23:45:20 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>Try this...
First create a Command Button named btnRunThis on the Form that contains
ctla and text1.
Go to the button's properties and in the OnClick event invoke Code Builder
and paste in the code below
Air Code Warning...
***********************
Sub btnRunThis_OnClick()
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
Dim strSql as String

'Delete all Records from WorkingTable
strSql = "Delete * FROM WorkingTable;"
db.execute strSql

'Me refers to the form that contains this code
For Each varItem In Me.ctla.ItemsSelected
Me.Text1 = Me.ctla.ItemData(varItem)

DoCmd.RunMacro "AppendData"
Next varItm

Resume btnRunThis_OnClick_Exit:
Exit sub

btnRunThis_OnClick_Error:
msgbox Err.Number & " " & Err.Description
Resume btnRunThis_OnClick_Exit

End Sub
**********************************

Good Luck

Len Robichaud
Open the Form1.
Select 4 items from the ctla list box. (list box only has one column)
Leaving the form open.
Open the sub and step through the code.
The program properly runs thru the for next loop four times.
Each time the loop reaches "ctla.ItemData(varItem)" holding the mouse cursor
over the code shows "ctla.ItemData(varItem) = null"
If varItem is examined it shows 0, 1, 2, 3 which is correct for the 4 items
selected.

If ctla.ItemData(varItem) is replaced with ctla.ItemData(2), the code fails to
run.

What is happening to ItemData?

Chuck
--
Aug 12 '07 #7

P: n/a
Chuck wrote:
On Sat, 11 Aug 2007 13:08:45 -0700, Salad <oi*@vinegar.comwrote:

>>Chuck wrote:
>>>Please help me correct the statements in sub "BoundData"

The following sub is in a module. Everything runs with no error messages,
however,data is not reaching the text box. Data is being appended to
WorkingTable but not filtered by value in text box. If I input data into the
text box, it gets deleted by the sub routine, indicating that
ctla.ItemData(varItm) is null.

Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control, ctlb As Control, ctlc As Control 'List boxes, Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim <list of fields snipped As Fields
Dim MainTable, WorkingTable As TableDefs

DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;"

Set frm = Form3
Set ctla = Forms!Form3!ctla
For Each varItm In ctla.ItemsSelected
Forms!Form3.Text1 = ctla.ItemData(varItm) ' Text1 is name of text box
on form3

DoCmd.RunMacro "AppendData" ' SQL 256 characters
Next varItm

End Sub

You are storing data from all items selected in ctla into Text1, over
and over. Nobody here would actually know what AppendData actually does.

I've been testing with a single selected Item.

What I'm aiming foe is at each pass thru the for next loop a different set of
data is appended to the WorkingTable from the MainTable. Anywhere from one to
40 records could be appended on each pass.
OK.
>>Since you appear to know some VBA, why not use VBA instead of a Macro.
Dim lngID As Long
Dim strSQL As String
For Each varItem In Me.ListBoxName.ItemsSelected

If I insert Me. in front of ListBoxName, an error message says improper use of
Me.
I have no idea if your listbox is called ListBoxName. Or if you are
calling something from a macro.
>
> lngID = Me.ListBoxName.Column(0, varItem)

Changing (varItm) to .Column(0, varItm) had no effect.

>> strSQL = ...
Currentdb.Execute strSQL, dbFailOnError
Next

The SQL string is over 1200 characters. I thought I could only use an SQL
statement with less than 256 characters in this manner.

Go to help. Enter Specifications.
>>Now, the name of your sub is BoundData. Is text1 a bound field?

No, it is not bound. The Where part of the SQL statement uses the value in the
text box. If I hard wire a value into the text box, the query runs just fine.

Are you sure you are stuffing the text box with a value?
>>Simply
stuffing a value into a bound textbox does nothing unless the record is
saved.

The record only has to survive until the Append query runs.

>>BTW, are you attempting to delete (drop) a field or update the contents
of the field to Null for records that match your criteria?

DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;" deletes every
field for every records in the table. It works correctly.

Oh. I usually enter WorkingTable.*
>>You might want to explain your problem with some additional detail. I'm
sure a lot of us use VBA because you can debug a problem far easier than
a macro we know nothing about.

The Append query exists. It as the very long SQL statement. The macro merely
runs the Append query:
Open query
Query name: AppendDataToWorkingTable
View: Datasheet
Data mode: Edit
I didn't think Action queries had views. Maybe that's used in Macros.
>
The subject "Sub routine syntax errors" is itself in error. The syntax is
just fine. What I have is logic errors. The sub routine is doing exactly what
it is told to do. But, unfortunately, that is not what I want it to do. If I
can correct the logic I think That is all that will be needed.
Why don't you simply create a query. Set a filter to the value
contained in the textbox? Then call the query. Maybe that's what
you're doing.

Maybe when you assign the value to the textbox, prior to calling the
query, enter something like
msgbox "Value is " & ctla.ItemData(varItm)

Your problem is pretty simple. I think you need to step through your
code, line by line. See what the values are at each point. Also, look
at RecordsAffected property in help.
Aug 12 '07 #8

P: n/a
Try this:
Dim i as Integer

For i = 0 To Me.ctla.ListCount - 1
If Me.ctla.Selected(i) = True Then
Me.Text1 = Me.ctla.ItemData(varItem)
End If
Next i

"Chuck" <li*****@schoollink.netwrote in message
news:aj********************************@4ax.com...
On Sat, 11 Aug 2007 23:45:20 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>Try this...
First create a Command Button named btnRunThis on the Form that contains
ctla and text1.
Go to the button's properties and in the OnClick event invoke Code Builder
and paste in the code below
Air Code Warning...
***********************
Sub btnRunThis_OnClick()
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
Dim strSql as String

'Delete all Records from WorkingTable
strSql = "Delete * FROM WorkingTable;"
db.execute strSql

'Me refers to the form that contains this code
For Each varItem In Me.ctla.ItemsSelected
Me.Text1 = Me.ctla.ItemData(varItem)

DoCmd.RunMacro "AppendData"
Next varItm

Resume btnRunThis_OnClick_Exit:
Exit sub

btnRunThis_OnClick_Error:
msgbox Err.Number & " " & Err.Description
Resume btnRunThis_OnClick_Exit

End Sub
**********************************

Good Luck

Len Robichaud
Open the Form1.
Select 4 items from the ctla list box. (list box only has one column)
Leaving the form open.
Open the sub and step through the code.
The program properly runs thru the for next loop four times.
Each time the loop reaches "ctla.ItemData(varItem)" holding the mouse
cursor
over the code shows "ctla.ItemData(varItem) = null"
If varItem is examined it shows 0, 1, 2, 3 which is correct for the 4 items
selected.

If ctla.ItemData(varItem) is replaced with ctla.ItemData(2), the code fails
to
run.

What is happening to ItemData?

Chuck
--
Aug 12 '07 #9

P: n/a
On Sun, 12 Aug 2007 16:43:00 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>Try this:
Dim i as Integer

For i = 0 To Me.ctla.ListCount - 1
If Me.ctla.Selected(i) = True Then
Me.Text1 = Me.ctla.ItemData(varItem)
End If
Next i
Debug says improper use of 'Me.' so I've been replacing all the 'Me.' with
'Form1.'.

Me.ctla.Selected(i) does = true.
varItem = row count -1 of item selected. (zero base)
however
Me.ctla.ItemData(varItem) = null
The list has a single column of data (text) that it gets from a table.
I set the table property of 'indexed' to Yes (No Duplicates). It didn't help.
I can scroll the list box up and down and select one or more items. The items
remain selected until the form is closed.

Chuck
>
"Chuck" <li*****@schoollink.netwrote in message
news:aj********************************@4ax.com.. .
On Sat, 11 Aug 2007 23:45:20 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>>Try this...
First create a Command Button named btnRunThis on the Form that contains
ctla and text1.
Go to the button's properties and in the OnClick event invoke Code Builder
and paste in the code below
Air Code Warning...
***********************
Sub btnRunThis_OnClick()
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
Dim strSql as String

'Delete all Records from WorkingTable
strSql = "Delete * FROM WorkingTable;"
db.execute strSql

'Me refers to the form that contains this code
For Each varItem In Me.ctla.ItemsSelected
Me.Text1 = Me.ctla.ItemData(varItem)

DoCmd.RunMacro "AppendData"
Next varItm

Resume btnRunThis_OnClick_Exit:
Exit sub

btnRunThis_OnClick_Error:
msgbox Err.Number & " " & Err.Description
Resume btnRunThis_OnClick_Exit

End Sub
**********************************

Good Luck

Len Robichaud
Open the Form1.
Select 4 items from the ctla list box. (list box only has one column)
Leaving the form open.
Open the sub and step through the code.
The program properly runs thru the for next loop four times.
Each time the loop reaches "ctla.ItemData(varItem)" holding the mouse
cursor
over the code shows "ctla.ItemData(varItem) = null"
If varItem is examined it shows 0, 1, 2, 3 which is correct for the 4 items
selected.

If ctla.ItemData(varItem) is replaced with ctla.ItemData(2), the code fails
to
run.

What is happening to ItemData?

Chuck
Aug 12 '07 #10

P: n/a
Where are you putting this code? It should be in the class module of the
Form. If you are trying to run this from another module it will fail.
If you are running this from another module you need to refer to the control
as Forms!Form1!ctla.etc...

"Chuck" <li*****@schoollink.netwrote in message
news:r0********************************@4ax.com...
On Sun, 12 Aug 2007 16:43:00 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>Try this:
Dim i as Integer

For i = 0 To Me.ctla.ListCount - 1
If Me.ctla.Selected(i) = True Then
Me.Text1 = Me.ctla.ItemData(varItem)
End If
Next i
Debug says improper use of 'Me.' so I've been replacing all the 'Me.' with
'Form1.'.

Me.ctla.Selected(i) does = true.
varItem = row count -1 of item selected. (zero base)
however
Me.ctla.ItemData(varItem) = null
The list has a single column of data (text) that it gets from a table.
I set the table property of 'indexed' to Yes (No Duplicates). It didn't
help.
I can scroll the list box up and down and select one or more items. The
items
remain selected until the form is closed.

Chuck
>
"Chuck" <li*****@schoollink.netwrote in message
news:aj********************************@4ax.com.. .
On Sat, 11 Aug 2007 23:45:20 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>>Try this...
First create a Command Button named btnRunThis on the Form that contains
ctla and text1.
Go to the button's properties and in the OnClick event invoke Code Builder
and paste in the code below
Air Code Warning...
***********************
Sub btnRunThis_OnClick()
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
Dim strSql as String

'Delete all Records from WorkingTable
strSql = "Delete * FROM WorkingTable;"
db.execute strSql

'Me refers to the form that contains this code
For Each varItem In Me.ctla.ItemsSelected
Me.Text1 = Me.ctla.ItemData(varItem)

DoCmd.RunMacro "AppendData"
Next varItm

Resume btnRunThis_OnClick_Exit:
Exit sub

btnRunThis_OnClick_Error:
msgbox Err.Number & " " & Err.Description
Resume btnRunThis_OnClick_Exit

End Sub
**********************************

Good Luck

Len Robichaud
Open the Form1.
Select 4 items from the ctla list box. (list box only has one column)
Leaving the form open.
Open the sub and step through the code.
The program properly runs thru the for next loop four times.
Each time the loop reaches "ctla.ItemData(varItem)" holding the mouse
cursor
over the code shows "ctla.ItemData(varItem) = null"
If varItem is examined it shows 0, 1, 2, 3 which is correct for the 4 items
selected.

If ctla.ItemData(varItem) is replaced with ctla.ItemData(2), the code fails
to
run.

What is happening to ItemData?

Chuck

Aug 12 '07 #11

P: n/a
On Sun, 12 Aug 2007 19:15:14 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>Where are you putting this code? It should be in the class module of the
Form. If you are trying to run this from another module it will fail.
If you are running this from another module you need to refer to the control
as Forms!Form1!ctla.etc...
The code is in project module. You are correct, and yes I have been.
>
"Chuck" <li*****@schoollink.netwrote in message
news:r0********************************@4ax.com.. .
On Sun, 12 Aug 2007 16:43:00 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>>Try this:
Dim i as Integer

For i = 0 To Me.ctla.ListCount - 1
If Me.ctla.Selected(i) = True Then
Me.Text1 = Me.ctla.ItemData(varItem)
End If
Next i
Debug says improper use of 'Me.' so I've been replacing all the 'Me.' with
'Form1.'.

Me.ctla.Selected(i) does = true.
varItem = row count -1 of item selected. (zero base)
however
Me.ctla.ItemData(varItem) = null
The list has a single column of data (text) that it gets from a table.
I set the table property of 'indexed' to Yes (No Duplicates). It didn't
help.
I can scroll the list box up and down and select one or more items. The
items
remain selected until the form is closed.

Chuck
>>
"Chuck" <li*****@schoollink.netwrote in message
news:aj********************************@4ax.com. ..
On Sat, 11 Aug 2007 23:45:20 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>>>Try this...
First create a Command Button named btnRunThis on the Form that contains
ctla and text1.
Go to the button's properties and in the OnClick event invoke Code Builder
and paste in the code below
Air Code Warning...
***********************
Sub btnRunThis_OnClick()
On Error goto btnRunThis_OnClick_Error
Dim db as CurrentDB
Dim varItem as Variable
Dim strSql as String

'Delete all Records from WorkingTable
strSql = "Delete * FROM WorkingTable;"
db.execute strSql

'Me refers to the form that contains this code
For Each varItem In Me.ctla.ItemsSelected
Me.Text1 = Me.ctla.ItemData(varItem)

DoCmd.RunMacro "AppendData"
Next varItm

Resume btnRunThis_OnClick_Exit:
Exit sub

btnRunThis_OnClick_Error:
msgbox Err.Number & " " & Err.Description
Resume btnRunThis_OnClick_Exit

End Sub
**********************************

Good Luck

Len Robichaud
Open the Form1.
Select 4 items from the ctla list box. (list box only has one column)
Leaving the form open.
Open the sub and step through the code.
The program properly runs thru the for next loop four times.
Each time the loop reaches "ctla.ItemData(varItem)" holding the mouse
cursor
over the code shows "ctla.ItemData(varItem) = null"
If varItem is examined it shows 0, 1, 2, 3 which is correct for the 4 items
selected.

If ctla.ItemData(varItem) is replaced with ctla.ItemData(2), the code fails
to
run.

What is happening to ItemData?

Chuck
Aug 13 '07 #12

P: n/a
On Sat, 11 Aug 2007 12:55:03 -0400, Chuck <li*****@schoollink.netwrote:
I have a solution. It's awkward, but it woks!
>Please help me correct the statements in sub "BoundData"

The following sub is in a module. Everything runs with no error messages,
however,data is not reaching the text box. Data is being appended to
WorkingTable but not filtered by value in text box. If I input data into the
text box, it gets deleted by the sub routine, indicating that
ctla.ItemData(varItm) is null.

Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control, ctlb As Control, ctlc As Control 'List boxes, Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim <list of fields snipped As Fields
Dim MainTable, WorkingTable As TableDefs

DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;"

Set frm = Form3
Set ctla = Forms!Form3!ctla
For Each varItem In ctla.ItemsSelected
If (varItem = "0") Then Forms!Form3.Text1 = "A" ' Text1 is name of
text box
If (varItem = "1") Then Forms!Form3.Text1 = "B"
If (varItem = "2") Then Forms!Form3.Text1 = "C"
If (varItem = "3") Then Forms!Form3.Text1 = "D"
>
DoCmd.RunMacro "AppendData" ' SQL 256 characters
Next varItm

End Sub
Now, if there is a way to make the 'varItem' be inside "" in the line:
Forms!Form3.Text1 = ctla.ItemData(varItem) , I'd be home free.
Otherwise, the list box has so many choices, I'll have to change all the 'if'
statements to 'Select Case' statements.

Chuck
Aug 13 '07 #13

P: n/a


Dim i as Integer

For i = 0 To Forms!Form1!ctla.ListCount - 1
If Forms!Form1!ctla.Selected(i) = True Then
Forms!Form3!Text1 = Forms!Form1!ctla.ItemData(i)
End If
Next i
"Chuck" <li*****@schoollink.netwrote in message
news:qr********************************@4ax.com...
On Sat, 11 Aug 2007 12:55:03 -0400, Chuck <li*****@schoollink.netwrote:
I have a solution. It's awkward, but it woks!
>Please help me correct the statements in sub "BoundData"

The following sub is in a module. Everything runs with no error messages,
however,data is not reaching the text box. Data is being appended to
WorkingTable but not filtered by value in text box. If I input data into
the
text box, it gets deleted by the sub routine, indicating that
ctla.ItemData(varItm) is null.

Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim ctla As Control, ctlb As Control, ctlc As Control 'List boxes,
Multi
Select = Simple
Dim varItm As Variant
Dim ItemData As Variant
Dim <list of fields snipped As Fields
Dim MainTable, WorkingTable As TableDefs

DoCmd.RunSQL "Delete WorkingTable.<field1FROM WorkingTable;"

Set frm = Form3
Set ctla = Forms!Form3!ctla
For Each varItem In ctla.ItemsSelected
If (varItem = "0") Then Forms!Form3.Text1 = "A" ' Text1 is name
of
text box
If (varItem = "1") Then Forms!Form3.Text1 = "B"
If (varItem = "2") Then Forms!Form3.Text1 = "C"
If (varItem = "3") Then Forms!Form3.Text1 = "D"
>
DoCmd.RunMacro "AppendData" ' SQL 256 characters
Next varItm

End Sub
Now, if there is a way to make the 'varItem' be inside "" in the line:
Forms!Form3.Text1 = ctla.ItemData(varItem) , I'd be home free.
Otherwise, the list box has so many choices, I'll have to change all the
'if'
statements to 'Select Case' statements.

Chuck
Aug 13 '07 #14

P: n/a
On Mon, 13 Aug 2007 08:56:32 -0400, "Len Robichaud"
<le***********@rqwproserv.comwrote:
>

Dim i as Integer

For i = 0 To Forms!Form1!ctla.ListCount - 1
If Forms!Form1!ctla.Selected(i) = True Then
Forms!Form3!Text1 = Forms!Form1!ctla.ItemData(i)
End If
Next i

Big <snip>
Apologies to anyone who has not been following this thread.

Len,

Thanks for being persistent trying to help me.

Unfortunately the code didn't work. Text1 still comes up null.
I made a query of the table that supplies the list data and added a calculated
field: ListName: """" <listData""""
So now the list box has two columns, one the plain data and the other the plain
data wrapped in "". I changed the code so that the data in "" was the saved
field. Text1 was no longer null and was displaying the selected row with the
"" around the data.

However, the criteria for Field1 of the append query!,
[Forms]![Form1]![Text1], was still null!!

I'm going to let this problem rest for a while then maybe with a fresh mind I
can find whatever silly errors I'm making.

Thanks again for your help

Chuck
--
Aug 14 '07 #15

P: n/a
On Sat, 11 Aug 2007 12:55:03 -0400, Chuck <li*****@schoollink.netwrote:

Got It! It all came from Access help.

Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim Field1 As Control, ctla As Control
Dim < big snip of field list As Fields
Dim MainTable, WorkingTable As TableDefs
Dim i As Integer

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete WorkingTable.Field1 FROM WorkingTable;"
'Clears WorkingTable

Set frm = Forms!Form3
Set ctla = frm.ctla

For i = 0 To ctla.ListCount - 1 'zero based
If Forms!Form3.ctla.Selected(i) Then
Forms!Form3.Text26 = Forms!Form3.ctla.Column(0, i)
Forms!Form3.Text35 = i 'Text box to show last item selected in list
box
DoCmd.RunMacro "AppendData" 'Appends data to WorkingTable
' Report based on WorkingTable. All sorting and grouping done in report
End If
Next i
DoCmd.SetWarnings True
End Sub

Chuck
--
Aug 15 '07 #16

P: n/a
On Tue, 14 Aug 2007 23:10:48 -0400, Chuck <li*****@schoollink.netwrote:
>On Sat, 11 Aug 2007 12:55:03 -0400, Chuck <li*****@schoollink.netwrote:
The reason that using the "ItemData" method did not work was because:
Microsoft Access uses zero-based numbers to refer to columns in the Column
property. That is, the first column is referenced by using the expression
Column(0); the second column is referenced by using the expression Column(1);
and so on.

H O W E V E R, the BoundColumn property uses 1-B A S E D numbers to refer to
the columns. This means that if the BoundColumn property is set to 1, you could
access the value stored in that column by using the expression Column(0).

That is BoundColumn 1 is the same as Column(0)

Chuck
--

>Got It! It all came from Access help.

Sub BoundData()
Dim frm As Form
Dim Form3 As Form
Dim Field1 As Control, ctla As Control
Dim < big snip of field list As Fields
Dim MainTable, WorkingTable As TableDefs
Dim i As Integer

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete WorkingTable.Field1 FROM WorkingTable;"
'Clears WorkingTable

Set frm = Forms!Form3
Set ctla = frm.ctla

For i = 0 To ctla.ListCount - 1 'zero based
If Forms!Form3.ctla.Selected(i) Then
Forms!Form3.Text26 = Forms!Form3.ctla.Column(0, i)
Forms!Form3.Text35 = i 'Text box to show last item selected in list
box
DoCmd.RunMacro "AppendData" 'Appends data to WorkingTable
' Report based on WorkingTable. All sorting and grouping done in report
End If
Next i
DoCmd.SetWarnings True
End Sub

Chuck
Aug 15 '07 #17

This discussion thread is closed

Replies have been disabled for this discussion.