Replies

Markus,

when you’re using the return value of a function, then you have to write the function with parantheses, even if there are no arguments to pass:

Me.RecordSource = StrUniversity & FncRating()

If there is no error message, I wonder if VBA thinks, FncRating is a new variable (which would be Null, thus leaving University as the only criteria). So be sure you have the ‘Option Explicit’ statement in the module header.

And Francois is right: StrRating seems to begin with ‘And’ – it should be ‘ And’, with a space as first character.

Hope this helps

Hi Mark, Harry,

setting the recordsource in Report_Open does work. Moreover, this is the only place you can do it.

Hi Veronicay,

> table as it should be. My problem is that if I close
> the form and return to it, if there is a P in the PPCFlag
> field the subform is not populated with existing records
> from the PPGroupTitleISBN title as it should be —
> although it still works fine for “non-P” titles. Can
> anyone tell this Access newbie what I am missing??

From your code I suppose you’re using the Enter event of the subform, at least that’s what I’m reading from these lines, where frmselTitleMulti seems to be the name of the subform:

> Private Sub frmselTitleMulti_Enter()
> If Me.PPCFlag = “P” Then
> strSQL = “SELECT ISBN, GroupTitle FROM PPGroupTitle
> ORDER BY GroupTitle”
> Me.frmselTitleMulti.Form.RecordSource = “PPGroupLinkISBN”

Please correct me if this is ot so. However, this is the wrong place. You need to change the record source in 2 other events:
AfterUpdate and Current of the main form.

The AfterUpdate takes care of new main form records and PPCFlag field changes on old ones, the Current event fires for each record when you’re navigating the main form, so the subform will show the right data according to the current PPCFlag.

The Enter event fires only if you klick in the subform, so it’s useless when you’re just navigating or doing changes on the main form. The subform is showing the right data for non-P flags because GroupLinkISBN is the hardcoded record source in the first place.

Hi B2R,

I did a bit of search at the MS site. They have a downloadable converter pack for Word 95 users somewhere in the Office Resource Kit toolbox. When installed, you can open Word 2000 docs without problems. I did work myself with such a converter for Word 97 docs and it worked good. Of course, if the Word 2000 users put some fancy tables and animated text in the docs, they’ll be lost, but with RTF docs you are loosing even more features, including the useful ones.

I don’t know if styles are going to help, because I suppose they don’t get saved in the RTF format. If you would send Word docs, you could indeed use your own styles.

Here some links from the MS site to start with:

Sharing Documents with Word 95 and Word 6.0 Users:
http://msdn.microsoft.com/library/default….html/68t6_5.asp

About Microsoft Office Converter Pack:
http://msdn.microsoft.com/library/default….html/95t3_5.asp

(just 2cents)

Hi,

'Define 2 arrays to hold all possible choices
'(define index starting with 1
'to correspond to option values)
Dim astrMonth(1 To 12) As String
Dim astrOffice(1 To 10) As String
 
'Fill in month names,
'index is the ordinal no. of the month
astrMonth(1)="Jan"
[fill in Feb, Mar, ...]
astrMonth(12)="Dec"
 
'Fill in office names
astrOffice(1)="London"
[fill in Liverpool, Manchester...]
astrOffice(10)="Paris"
 
'Take the chosen month and concatenate
'with chosen office
'If no office chosen, take only the month
If office="" Then
   strCriteria=astrMonth(month)
Else
   strCriteria=astrMonth(month) & astrOffice(office)
'You may want to put a blank between the two strings
End If

However, Mark’s solution with combo boxes would be better, specially if there is a chance the office locations change some time in the future.

dave,

DMax and sorting are both doing the same thing: comparing the values with each other to find out which one is greater. I should have written ‘compare’ instead of ‘sort’

As for formatting a real number field with leading zeros, this is no problem, the Format function works (see uder defined number formats in the Help). I also have in a database an AutoKey field and a normal integer field formatted with leading zeros in the table: the AutoKey with ‘00000’ and the number with ’00’ (these are magazine issue numbers per year), and they all show up everywhere with leading zeros, including datasheet view of the table itself. So the concatenating is also no problem.

In my opinion, this way around is easier, more flexible and reliable then the “make a text behave like a number” way.

To find out exactly what causes the error message needs careful testing I think, maybe it’s something one wouldn’t even imagine (not so uncommon with Microsoft ). Maybe I’ll take some time and play around with it.

The default ’01’ (or 1) is good, you could use this anyway because it saves you the trouble (and programming) for a new record in the group.

Hi Charlotte,

what I meant is, setting the canshrink property to true should be enough. The Access help describes exactly the behaviour you wrote about – cangrow and canshrink are sort of twins, working symmetrically. But we all know the help isn’t 100% So I tested this in A2000 (I had the last “naked” report without labels, lines etc. in the detail section in a A95 database, converted in A97 and then modified). I set up a simple report of the address label kind:

firstname lastname
address
zip code
city

No labels, no lines, nothing else, all controls have “normal” height, and I set the canshrink property for all controls except the first/lastname to true. And it does work. I got no empty lines in the report. The only thing is, if there is some vertical space between the controls, they will be preserved and get added. If say the address line is skipped while empty, there would be a larger space between the names line and the zip code. But this is definitely not a blank line in the full height of the skipped control, it is exactly twice the vertical space between the controls.

And this is the same, whether its a single column or a multiple column report, or whether the detail section’s canshrink property is set to true or false. Only, if the latter, there will be a larger space between one address record as a whole and the other if there are empty controls in the first, which should be if printing address labels.

If one had to set the control height to 0 in order to skip blank lines, then the canshrink property would make no sense. The 0 height +cangrow property would do the job.

This whole thing concerns only bound controls having a table/query field as a source. If the control source is something else, then it’s an other story, but then one has to do some formatting per VBA anyway.

And BTW, I am very sorry for the confusion. With “labels” I meant address labels, not the label fields in the report. I’ll try to be more careful with my translations from german

Hi dave,

—————————
Run Time error ‘-2147352567 (800200009)
The field is too small to accept the amount of data you

which made absolutely no sense to me. All I was trying to do was convert any empty space to “0”. The field size is 2, as it was earlier. I have posted the code below.
—————————

Well… at first glance, it doesn’t make sense indeed. I don’t know, but I just took a look in Access Help for the + operator. There is something like this (I have to translate from german):

“If you use the + operator, you cannot always define if it’s going to be an addition or a concatenation.”
And there is a list of various combinations of the operands.

Could it just be, that DMax brings up some 2 character text (because your field is in reality this and not a number) which then gets _concatenated_ with 1? Which would return a 3 character text, like 011 (for ’01’+1) or 101 (for ’10’+1)?

I agree this sounds a bit fantastic and it has nothing to do with Nz, but trying to make a text work like a number can be fantastic.

Hi dave,

————————-
I should mention that if I manually place in the table, an 01, it works through 10, but if I use 10, it seems to advance to 11,12, and so on;
————————

I think the real problem is the field being a text and not a number. In that case, the sorting – and thus DMax – will be different, it’s good ol’ ASCII sorting and not the normal numeric. I’ll try to illustrate this:

Say you have in the table the values (inserted no matter how):
1
2
10

Normally, you’d expect DMax brings up 10, but this won’t happen I suppose. As sorting/comparing for text is done character by character, the biggest value in this case will be 2, because it’s greater than the first character in ’10’! The sorting would be OK if indeed the content would always have those leading zeros.

I’ll say you should change the datatype of the field, otherwise the DMax won’t work correctly no matter what you’re doing. Or you’ll have to do such way too much work just to make such simple things like incrementing work.
Then you can format the content to show 2 (or more) digits everywhere on the UI, i.e. controls on forms, reports etc., even in the table and all kind of DMax, incrementing etc. will work correctly.

BTW, is 99 the biggest possible value for that field?

Hi,

————-
Then take your Address2 and make it as close to zero height as you can get it, but set its cangrow and canshrink properties to true.
————-

I have a question to this, because it was repeatedly stated here. Is there any specific reason to make the field height so small by setting the canshrink property to true? I think, if the property is set, the field height will shrink automatically to 0 if there is no data in it – that’s what the property is for. Or am I missing something?

I always make the fields with normal height and it doesn’t seem to me that there is something wrong when there is no data in them. I would find it cumbersome to have such thin fields, because then it’s difficult to “read” the report in design mode and specially with labels or other limited formats I need to see the whole space needed by the data when layouting them.

Hi Mark,

thank you for replying. Yes, it would be so simple – only it didn’t work. I don’t know why, if I pressed ctrl+P, the printer dialogue came up all right, but clicking the custom command bar button just didn’t do anything. No dialogue, no error, nothing. After trying around, I turned back to VBA and ended up with this code (this was a hint in ADH in the discussion about command bars):

Public Function PrintDialogue()

On Error GoTo ErrPrintDialogue


   DoCmd.RunCommand acCmdPrint


ExitPrintDialogue:
On Error Resume Next
   Exit Function


ErrPrintDialogue:
   Select Case Err.Number
      Case ERR_CANCEL_COMMAND	'Constant for command cancelled error 2501
	'User klicked Cancel, do nothing
      Case Else
         MsgBox CStr(Err.Number) & ": " & Err.Description, vbCritical+vbOKOnly, "Error in PrintDialogue"
   End Select
   Resume ExitPrintDialogue

End Function

Put that in a macro (executeCode), assigned the macro to the button and it works. I also have buttons for 1, 2, 4 pages preview.

Of course, *that* doesn’t stop someone from taking a screenshot, and pulling out the text via OCR without even printing& scanning… :-)))

But seriously, just one more offtopic question: Does such a PDF really trap Ctrl+C if I select something just painting with the mouse on the screen? Because the shortcut works even if there isn’t a copy command even in sight.

Hi everybody,

the point is, “painting” with the mouse and Ctrl+C is always working, even in PDF, even if switching to text +copy command are somehow disabled. I’ve seen until now exactly one software trapping this shortcut (FineReader, unregistered trial).

I tried in 2 DB’s on my PC both ways, embedding and linking, and found the difference not so big (both A97, backend, local). The DB’s had about 30 resp. 70 MB with embedded images, with linking some 10 MB less and both ways growing fast and working very slow, even locally. So I threw away the OLE fields from the tables and use instead a hyperlink field to hold the path to the image file. So the image still can be opened for editing with a click.

On the form I have an image control, whose picture property gets updated in Form_Current and txtPath_AfterUpdate and a Search Image command button calling the GetOpenFileName API. Now, the DB’s have about 8 resp. 3 MB and performance is much better. After this, using image fields on a network sounds like nightmare for me.

However, I would like to know, what difference image format and associated software make? I tried these DB’s with BMP 256 colors, JPG and WMF and didn’t notice much difference. The associated program is Paintshop Pro and noticed that having it open speeded up the DB’s considerably. What’s happening actually, when an OLE image field gets filled/displayed? I also noticed, the DB’s got bloated very much, sort of from 7 to 20 MB after linking the very first image. Was Access somehow incorporating something like the “image engine” of Paintshop??

Mark,

you can find the values for those constants easily in the Access object catalogue under Constants. Just click each constant you want and you’ll see the value in the definition below. Then you can set up a table which you can keep for further use too.

I did something like that for OpenForm command in the switchboard. I put supplemental columns with DataMode and WindowMode in SwitchboardItems.

As for the benefit of intrinsic constants, IMHO it is mainly the more easy writing and reading of the code. One remembers acDataErrContinue and what it’s for, but not the value of it – remember WordBasic in OLE automation with those awful number worms?