Access crashes on exiting form with list box

[Guest]

This is a repost that Elwin couldn't help me with. Any suggestions out there

I'm getting an application error on closing a form in Access2000 (Win2000 Pro, SQL Server 2000 back end, VB.net on same computer). It's happened before with list boxes on this form, and as soon as I remove the list boxes and code the error goes away. The form and list box work great while I'm using them but then it crashes Access on closing the form

Error
Application Erro
The instruction at "0x30088859" referenced memory at "0x09e2068c". The memory could not be "read"

I tried repairing Office and it did nothing. I tried putting the controls and code into a new form and it worked for a little bit and then it happened again. It seems to be caused by my setting the RowSource property for the ListBox from VBA. I need to set the row source property from code, and this ends up generating an error. Is the form corrupt? I've seen that this is a 'known' bug...

If I take out the following code
me.desclist.rowsource = "EXEC proc....... filter1, filter2
me.desclist.requer

Then I don't get the error

If I set the RowSource property to
proc_fltinfo 116,
then the listbox works, but I can't filter it with the inputparameters

I'd like a way to set the RowSource to:
Exec proc_fltinfo Forms![frm1]![filter1], Forms![frm1]![filter2

but it won't let me use the forms declaration in the RowSource like it would if this were native Access. I found article "Microsoft Knowledge Base Article - 201962" that shows something similar happening in Access 97 when you change the RowSourceType property in code. Interestingly, I haven't changed this property. I've got it set to View/StoredProcedure and I keep it there. I also have tried with using a ValueList RowSourceType populated from a getstring of the desired recordset instead of a direct command. Still not getting it...

 

[Gary Walter]

Hi Jordan,

I don't mean to waste your time, trying to understand...

In your code, you do something like

filter1 = Forms![frm1]![filter1]
filter2 = Forms![frm1]![filter2]
<some code to varify valid filters>
<some code to varify filters will return some records>
me.desclist.rowsource = "EXEC proc_fltinfo " & filter1 & ", " & filter2
me.desclist.requery

and when you close form, you get error.

But, if in code you did something like

me.desclist.rowsource = "EXEC proc_fltinfo 116, 1"
me.desclist.requery

when you close form you get no error?

In form unload event, can you change rowsource
to string that does not cause error?

Apologies if not understanding correctly.

Gary Walter

This is a repost that Elwin couldn't help me with. Any suggestions out there?

I'm getting an application error on closing a form in Access2000 (Win2000 Pro, SQL

Server 2000 back end, VB.net on same computer). It's happened before with list boxes
on this form, and as soon as I remove the list boxes and code the error goes away.
The form and list box work great while I'm using them but then it crashes Access on
closing the form.

Error:
Application Error
The instruction at "0x30088859" referenced memory at "0x09e2068c". The memory could not be "read".

I tried repairing Office and it did nothing. I tried putting the controls and code

into a new form and it worked for a little bit and then it happened again. It seems
to be caused by my setting the RowSource property for the ListBox from VBA. I need
to set the row source property from code, and this ends up generating an error. Is
the form corrupt? I've seen that this is a 'known' bug...

If I take out the following code:
me.desclist.rowsource = "EXEC proc....... filter1, filter2"
me.desclist.requery

Then I don't get the error.

If I set the RowSource property to:
proc_fltinfo 116, 1
then the listbox works, but I can't filter it with the inputparameters.

I'd like a way to set the RowSource to:
Exec proc_fltinfo Forms![frm1]![filter1], Forms![frm1]![filter2]

but it won't let me use the forms declaration in the RowSource like it would if

this were native Access. I found article "Microsoft Knowledge Base Article - 201962"
that shows something similar happening in Access 97 when you change the RowSourceType
property in code. Interestingly, I haven't changed this property. I've got it set
to View/StoredProcedure and I keep it there. I also have tried with using a
ValueList RowSourceType populated from a getstring of the desired recordset instead
of a direct command. Still not getting it...

 

[Gary Walter]

Hi Jordan,

Implicit in my reply was that your
"yadayadayada" code

me.desclist.rowsource = "EXEC proc....... filter1, filter2"

looked like you were not *concatenating* the
parameter values, i.e., properly building a string
for the row source.

On pgs 454-455 of Mary Chipman's
"Microsoft Access Developer's Guide
to SQL Server":

****quote*****
For [listbox] row sources, use VBA code to
concatenate parameter values:

Me.MyCombo.RowSource =
"Exec MyStoredProc " & MyNumericParamValue

or:

Me.MyCombo.RowSource =
"Exec MyStoredProc '" & MyStringOrDateParamValue & "'"

***unquote***

No, it's not that I've set the RowSource to proc_fltinfo 116, 1 from code, but

rather in the listbox properties on the form itself and then saved the form. The
problem is that I can't seem to set the properties FROM CODE.

 

[Gary Walter]

Hi Jordan,

This won't cure a possibly corrupt FE,
but I don't know if you had seen Michel's
simple technique from queries ng.

****** quote ************
Use Pubs. Make a stored procedure ByRoyalty with one parameter @percentage
like the following:

----------------------
CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
-----------------------

Next, with a new form, add a text control, rename it percentage. Then add a
combo box, type ByRoyalty in its row source. Nothing else. Done it? OK.
Open the form in view mode. Enter 100 in the Text control, then, click on
the combo box to get its list. Tadam... here are the authors with a royalty
of 100%.

Back in Text box percentage, the name of the parameter, without its @, and
change 100 to 40, go back to the combo box, drop its list, and am I wrong?
yes. We (intentionally) forget to add a Me.ComboxName.Requery in the after
update event of the text box (personally, I prefer making it in the GotFocus
of the combo box). Add the code:

-------------------
Private Sub Percentage_AfterUpdate()
Me.Combo2.RowSource = Me.Combo2.RowSource
End Sub
-------------------

and make the test again, should work like a charm. So what is the trick:
use a control with the name of the parameter of the stored proc. Nothing
more complex, no ugly Form!FormName!ControlName syntax.

******** end quote ***********

Yeah, I've got that book... and I did try multiple concatenation methods. It

happens if I run it EXACTLY like it's put in Chipman/Baron, or with a
string-generated semicolon ValueList, or anything for that matter. It simply won't
let me set the RowSource from code. I'm thinking I may have a corrupt front end...
even though it does this on a new database. I repaired my installation of Office,
and I'm getting ready to wipe it out altogether. I have to verify that this happens
on another machine with a fresh install of Office. As it is, I've given up on this
option for now and I'm using a few work-arounds. That's not ideal, but I'll have to
live with it for now.

The sad part is that this wasn't anywhere near the most difficult apsect of the

application I was working on. I've been able to do this before without ANY problems.
I'd had a big breakthrough and was very happy with what I'd generated and then got
hung up on this 'finishing touch'. That's life.