Form causes Illegal Operation

[Taff]

Hi All,

I have a form with a text box and a list box on.

When i type in text to the list box it refines my search. I have the following on the OnChange Event on my text box.

Private Sub txtsurname_Change()
Dim txtSearchString As Variant
Dim strSQL As String

txtSearchString = Me![txtsurname].Text

strSQL = "SELECT DISTINCTROW [Learner Dataset].Learn_id, [Learner Dataset].provi_id, [Learner Dataset].lsurname, [Learner Dataset].Lforenam, [Learner Dataset].Nat_insu FROM [Learner Dataset] "
strSQL = strSQL & "WHERE (([Learner Dataset].lsurname) Like '" & txtSearchString & "*') "
strSQL = strSQL & "ORDER BY [Learner Dataset].lsurname, [Learner Dataset].Lforenam"

Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!txtsurname.SetFocus
End Sub

The problem is, if i click on my list box anywhere and press a key, it causes an illegal operation.


Any Ideas.

Thanks

Anthony

 

[Taff]

Sorted it now,

On the OnKeyPress Event of the Listbox, i have made it to SetFocus back to my text box.

Anthony

 

[Mile-O]

The problem is, if i click on my list box anywhere and press a key, it causes an illegal operation.

What: backstreet abortion? Something Kevorkian? What's the error message and number? Does it show you on which line the error occurs?

I wouldn't have thought you needed to use the .Text property of the textbox.

prov_id becomes [prov_id] - always include field names that use 'dodgy' (i.e. unrecommended) characters - the same goes for the others.

SQL strings should be given a little semi-colon to end them off (ie. ("ORDER BY [Learner Dataset].lsurname, [Learner Dataset].Lforenam;"

Are you sure it's forenam and not forename?

Careful here: Like '" & txtSearchString & "*') " - as you are searching on a surname there is the possibility that someone's name may contain an apostrophe which would break your SQL statement. For such eventualities you 'double up' your apostrophes; so for each ' replace it with two " - Like """ & txtSearchString & "*"") "

Ditch the late binding and use early binding in this instance: i.e. Me. instead of Me!

Me.lstResults.RowSource = strSQL
Me.txtsurname.SetFocus

As you are replacing the RowSource the listbox is requeried anyway so this line is redundant:

Me.lstResults.Requery

You are also wasting memory space by dimensioning the variable txtSearchString (with a prefix usually expected of textboxes ) when it is quite clearly a string variable; disambiguate.


Finally, going along a normalisation route, why do you not have a specific learner's table?

 

[Taff]

Hi Mile-O-Phile,

Made some adjustments as you pointed out.

The error i was having is:-

MSACCESS caused an invalid page fault in
module MSACCESS.EXE at 0167:3030df85.
Registers:
EAX=00000204 CS=0167 EIP=3030df85 EFLGS=00010246
EBX=0062f1c4 SS=016f ESP=0062efb4 EBP=0062f17c
ECX=00000000 DS=016f ESI=0062f1b4 FS=3c6f
EDX=00000000 ES=016f EDI=077df064 GS=0000
Bytes at CS:EIP:
88 48 05 8b 4d f4 e8 4e 8d db ff 85 c0 75 06 83
Stack dump:
0062f1b4 077df01c 0062f248 08803a74 004010d4 00400808 00000004 077df040 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

Finally, going along a normalisation route, why do you not have a specific learner's table?

Not sure what you mean?

 

[Mile-O]

Finally, going along a normalisation route, why do you not have a specific learner's table?

Okay, I don't know what, with respect to your database, a "Learner" is but it seems to me that if you are trying to populate a table with the SELECT DISTINCTROW case then the field(s) in question may have many identical values. You do, however, have a field, called Nat_insu which is obviously short for National Insurance - this should be a unique number so you shouldn't have any duplicates in your table thus negating the need for the DISTINCTROW statement.

I think I prattled of the normalisation statement for no reason... but, as for your actual error problem I don't know. Have you tried comapcting and repairing? Tried importing all your objects into a blank database?

 

[Taff]

Hi Mile-O-Phile,

Unfortunately not everyone knows their National Insurance Number and because the database tracks students from the Age of 14, they will not have one. Therefore the field is filled with Nine's thus making it not unique.

Tried Compacting and Repairing and it seems to continue causing the illegal operation. The only way i've found of preventing it is setting the focus back to the Text box when a key is pressed while on the listbox.

Also Learner means Student / Candidate. Its the new word for them in sunny old Wales.

Regards

Anthony

 

[Mile-O]

I wasn't making comment on your error as I don't know what that is.
I was just saying that you need not use the DISTINCTROW keyword in the SQL.

 

[Taff]

Got Ya

Anthony