Acess 7.0 - Dumb questions
Jane West
I've lost my original posting, but here I am again with code snippets to
go along with my three dumb questions:
1. Why won't On Error work as advertised? For instance, here's some
code I deliberately made fail, trying to get it to go to my error
handling routine. It doesn't. Instead of my error message, as soon as
Access hits the MoveLast statement, it flashes up its on 3021 runtime
error.
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
Dim rstMeasures As Recordset
Set mDb = CurrentDb
Set mTDf = mDb.TableDefs(1)
Set rstMeasures = mDb.OpenRecordset( _
"SELECT Measurement, MeasureAbbrev from tblMeasureDescriptions")
rstMeasures.MoveLast
rstMeasures.MoveNext
. . . . .
rstMeasures.Close
Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox ("Unexpected end of file!"), vbInformation
Resume Exit_Form_Open
End Sub
I can also watch this code using debug and the code never arrives at the
paragraph labeled Err_Form_Open.
I have also tried putting the On Error immediately before the offending
statement (including On Error Resume, which ought to let the code just
keep on as if the error hadn't happened). NOTHING I try works as
documented! The thing that irritates me the most is that I've tried
copying code directly from the on-line help that demonstrates how all
this is supposed to work, and even that fails! That's why I suspect the
problem is not in my code, but somewhere else.
2. How do I re-position the cursor at the field I choose, instead of
having it go directly to the next field? For instance, I have a field
called Desc, followed in the tab order by a field called Amount. After
doing an edit on Desc, I see that the user hasn't entered anything in the
description field, so I send a warning message, after which I want to
cursor to remain on Me!Desc, so I use
mFrm!Desc.SetFocus
That's obviously not the way to do it, because the cursor drops down to
the Amount field anyway. Maybe I'm not understanding how to use SetFocus
properly, or maybe I need to do something else entirely. Beats me!
3. This one's a real bummer. I want to fill my list box based on data
already entered. In this case, the user gets to select a Group field (or
omit it) and part or all of the description, and then I open a recordset
with the records meeting those criteria. Then I string them into a long
field, delimited by semi-colons, that I move to the RowSource of the list
box (well, combo box, actually). The only problem is that my data for
the description field contains commas, and Access thinks those are
delimiters, too. Right now, to get around this, I'm inspecting the data
first and replacing the commas with periods, then changing them back
before I actually save the data, which is a real nuisance (plus, it's not
100% safe--what if my data actually did contain periods? I could use a
less likely character, but then my list would look REALLY weird). Here's
the code as it stands now, including my clunky workaround:
Do Until rst.EOF
Descript = rst!Desc
ptrStart = InStr(1, Descript, ",")
Do Until ptrStart = 0
Mid(Descript, ptrStart, 1) = ". "
ptrStart = InStr(1, Descript, ",")
Loop
strFillBox = strFillBox + Descript + ";" & _
UCase$(rst!Group) & ";"
rstFoods.MoveNext
Loop
If my data is (description followed by group):
Acme, Inc m
Bigwigs, Ltd M
it comes out looking like this unless I change to commas to periods
first:
Acme Inc
M Bigwigs
Ltd M
whereas it should look like this:
Acme, Inc M
Bigwigs, Ltd M
Can anyone tell me a way to convince Access that the semi-colon, and ONLY
the semi-colon, is the delimiter?
Any and all help greatly appreciated.
-
JANE WEST DAP...@prodigy.com
Jane West
Answer to one of my three dumb questions: If the data I'm trying to
string into a combo box contains commas, and Access treats them like a
delimiter (even though I'm using the semi-colon as a delimiter), the
solution is pretty simple. Put the data in the field containing the
commas in quotation marks, and then it resolves like a champ. I had this
brainwave shortly after posting this note (Me!Brain.Functioning is true,
but only sporadically). SO obvious once I thought of it.
Thanks to those of you who pointed out the error of my ways in trying to
position the cursor where I wanted. Forget XetFocus, on with GoToControl.
You'll have to forgive the Cobol mainframer who hasn't used the words
"Go To" in years--my fingers still feel guilty just typing the letters,
but with VBA, I must overcome my prior training.
My On Error problem--the weirdest of the lot--remains. I knew there were
simple answers to the other two questions, but this still makes no sense
to me. I keep writing more code, but none of it has proper error
handling because I can't make On Error trap anything. I hope someone has
experienced something like this. It's not a coding problem, it's
something else. I just can't imagine what.
-
JANE WEST DAP...@prodigy.com