How to autosize fields

[Kasperczak]

I am working on a glossary of terms and am trying to create a report that will contain a list looking something like this:

term, subject
    This is a long description for the term
    See also: term1, term2

I know how to set the font format to italics, but am having difficulty with line 1 because the fields are of fixed length. I want the length to be automatically determined according to the length of the actual content of the database field so that there is no gap between the text and comma and the subject field. I would also like to add a comma or possibly brackets around the subject text e.g.:

term (subject)
    This is a long description for the term
    See also: term1, term2

but can't find how to do this. Its been a while now since I used MS Access for anything an it has changed significantly. I did find properties called Can Grow and Can Shrink and both are set to yes, but both fields still appear of fixed length.

In the meantime I am working on trying to figure out how to make the 'See also' line appear only when the field in question is not empty. If anyone can help me with the above it would be appreciated.

[ranman256]

autofit col. width:

txtBox.ColumnWidth = -2

[Bob Fitz]

In the meantime I am working on trying to figure out how to make the 'See also' line appear only when the field in question is not empty. If anyone can help me with the above it would be appreciated.

You could try code in the On Format event of the relevant section to set the visible property of your textbox, dependent on the value of another control.

Alternatively, you use Conditional Formatting of a textbox to set the font colour to white but of course this won't work if printed on coloured paper

[Kasperczak]

You could try code in the On Format event of the relevant section to set the visible property of your textbox, dependent on the value of another control.

Alternatively, you use Conditional Formatting of a textbox to set the font colour to white but of course this won't work if printed on coloured paper

Bob, thanks for that. I did try the following in code (the Sub being generated automatically after selecting 'Build Event' in the Details header in Design view):

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.Alt.Value = "" Then
        Me.Also.Visible = False
        Me.Lab_Also.Visible = False
    Else
        Me.Also.Visible = True
        Me.Lab_Also.Visible = True
    End If
End Sub

Unfortunately it is getting totally ignored and both label and field still appear in all listed records. Still trying to figure it out. The project lives on a OnerDrive share so not sure whether its a code execution permissions (trust) issue perhaps?

[Kasperczak]

autofit col. width:

txtBox.ColumnWidth = -2

Thanks. Will give that a try.

[Bob Fitz]

Perhaps you need to test for a null value rather than a zero length string. Perhaps:
If Nz(Me.Alt,0)=0 Then Or perhaps even If Len(Me.Alt)=0 Then

The project lives on a OnerDrive share so not sure whether its a code execution permissions (trust) issue perhaps?

I'm not sure what you mean by this exactly but I don't you should have multiple users using a db on OneDrive (if that's what you are doing). I believe I've read that it is a recipe for corruption.

[orange]

Can you provide some sample data?

[Kasperczak]

autofit col. width:

txtBox.ColumnWidth = -2

I am wondering whether that applies to Excel? Apparently a value of -2 for field width is invalid in Access.

[Kasperczak]

Perhaps you need to test for a null value rather than a zero length string. Perhaps:
If Nz(Me.Alt,0)=0 Then Or perhaps even If Len(Me.Alt)=0 Then

I'm not sure what you mean by this exactly but I don't you should have multiple users using a db on OneDrive (if that's what you are doing). I believe I've read that it is a recipe for corruption.

No, its not intended for multiple users. Our organisation seems to have most of its data on OneDrive and my user 'Documents' folder is also on a OneDrive share. I don't know whether that has any bearing on the problem, but I do recall something about wherever you run the DB from needs to be a trusted location? Could be mixing it up with something else or ancient versions, but just thought to mention is as a possible factor.

[Kasperczak]

OK, here's a simple example:

Table fields:

1. auto-index
2. Term
3. Subject
4. Description

Sample data:

NAS, Networks, A network attached storage device
RAN, Telecoms, Radio Access Network
VM, Software, Virtual Machine

I am looking to get something like:

NAS (Networks)
    A network attached storage device

RAN (Telecoms)
    Radio Access Network
VM (Software)
    Virtual Machine

The descriptions will actually have much more detail, but this is the general idea.

[Bob Fitz]

I am wondering whether that applies to Excel? Apparently a value of -2 for field width is invalid in Access.

I seem to remember using it it the past but that was on textbox on a form shown in DataSheet view.

[Gicu]

I think you're overcomplicating things. Have just one textbox on line one and set its Control Source property to =[Term] & " (" & [Subject] & ")"

Cheers,

[CJ_London]

the -2 refers to columnwidth for datasheets

Table fields:

1. auto-index
2. Term
3. Subject
4. Description

Sample data:

NAS, Networks, A network attached storage device

so what applies to what?
Term=NAS
Subject=Networks
Description=A network attached storage device

so are you displaying as 3 fields in controls set out just so, or do you want to build these three fields into 1 field which says

NAS, (Networks)
    A network attached storage device

The other thing we need to know is if this is being displayed on a single form, a continuous form or a datasheet and if it is 3 fields which field it is you want to widen/adjust. The Term field so the subject is closer? or the description field to ensure it displays all the data? or what?

[Gicu]

I want the length to be automatically determined according to the length of the actual content of the database field so that there is no gap between the text and comma and the subject field. I would also like to add a comma or possibly brackets around the subject text e.g.:

I think replacing the two textboxes for Term and Subject with one using a concatenation of the two fields and the brackets would be what the OP wants.

Cheers,

[Kasperczak]

I did a little experimenting with above function like so:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Debug.Print (Me.Alt.Value)
    If Me.Alt.Value = "" Then
        Debug.Print ("Blank")
        Me.Also.Visible = False
        Me.Lab_Also.Visible = False
    Else
        Debug.Print ("NOT BLank")        
        Me.Also.Visible = True
        Me.Lab_Also.Visible = True
    End If
End Sub

It debug.print'ed the value of Me.Alt.Value, but what then happened is a bit weird. It printed 'Null' for some values and the actual value for others as expected but always printed "NOT Blank" for every iteration.

I changed:

If Me.Alt.Value = ""

To:

If Me.Alt.Value = Null

but got the same thing. What gives?
If the value is Null then should it not have printed 'Blank' for the empty Null values?