Passing listbox values to Excel Spreadsheet

G

Guest

Hi,
I am using a hidden listbox to pass a resulting value from a SELECT query to
a cell in an Excel spreadsheet when the user opens it.

For one spreadsheet it works perfectly. The values are displayed in the
cell. The code I am using is shown below.

strSQLMYR643 = "SELECT SUM([MYRNonProject].[" &
Forms!frmSelectCC!cmbCostCentre.Value & "]) AS NonPrjMYR FROM [MYRNonProject]
WHERE([MYRNonProject].[GLCode]='F6430000' OR
[MYRNonProject].[GLCode]='E6430000')"

Me!lstMYR643.RowSourceType = "Table/Query"
Me.lstMYR643.RowSource = strSQLMYR643

objActiveWksh.cells(3, 20) = Me.lstMYR643.ItemData(0)

The same code in the other form (a different query of course) displays
values in the listbox (When listbox is set to visible), but does not pass
the value to the cell in the Excel Spreadsheet.

strSQLMYRNonPrj = "SELECT [MYRNonProject].[" &
Forms!frmSelectCC!cmbCostCentre.Value & "] FROM [MYRNonProject]
WHERE([MYRNonProject].[GLCode]='" & Forms!frmSelectGLC!txtGLCode.Value & "')"

Me!lstFCMYRNonPrj.RowSourceType = "Table/Query"
Me.lstFCMYRNonPrj.RowSource = strSQLMYRNonPrj

objActiveWksh.cells(3, 10) = Me.lstFCMYRNonPrj.ItemData(0)

Is there anything I need to know about this. Why doesn't the same code work
on different accasions?
It's driving me nuts. Please help me.

Toyfixer
 
G

Guest

Hi Alex.

Thanks for the suggestion. I tried it by passing the value to a text box in
an intermediate form. It worked fine.

However, there was a difference between two forms. The first one which
worked did not have a combo box before the event and the second one had.

So I included the select query in the AfterUpdate event of the combo box. It
passed the value to the worksheet, with that change.

Therefore, my problem is solved, however I would like to know what was the
reason behind this if you happen to know. (or send me a reference to read)

Thank you again.
Toyfixer.

Alex Dybenko said:
try to set a breakpoint and check the value of

Me.lstFCMYRNonPrj.ItemData(0)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

ToyFixer said:
Hi,
I am using a hidden listbox to pass a resulting value from a SELECT query
to
a cell in an Excel spreadsheet when the user opens it.

For one spreadsheet it works perfectly. The values are displayed in the
cell. The code I am using is shown below.

strSQLMYR643 = "SELECT SUM([MYRNonProject].[" &
Forms!frmSelectCC!cmbCostCentre.Value & "]) AS NonPrjMYR FROM
[MYRNonProject]
WHERE([MYRNonProject].[GLCode]='F6430000' OR
[MYRNonProject].[GLCode]='E6430000')"

Me!lstMYR643.RowSourceType = "Table/Query"
Me.lstMYR643.RowSource = strSQLMYR643

objActiveWksh.cells(3, 20) = Me.lstMYR643.ItemData(0)

The same code in the other form (a different query of course) displays
values in the listbox (When listbox is set to visible), but does not pass
the value to the cell in the Excel Spreadsheet.

strSQLMYRNonPrj = "SELECT [MYRNonProject].[" &
Forms!frmSelectCC!cmbCostCentre.Value & "] FROM [MYRNonProject]
WHERE([MYRNonProject].[GLCode]='" & Forms!frmSelectGLC!txtGLCode.Value &
"')"

Me!lstFCMYRNonPrj.RowSourceType = "Table/Query"
Me.lstFCMYRNonPrj.RowSource = strSQLMYRNonPrj

objActiveWksh.cells(3, 10) = Me.lstFCMYRNonPrj.ItemData(0)

Is there anything I need to know about this. Why doesn't the same code
work
on different accasions?
It's driving me nuts. Please help me.

Toyfixer
Click to expand...
Click to expand...
 
Top