Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set Caption for Field using Code (97)

    A client wants to change a comment field from a text data type to memo data type so they are not restricted to the 255 characters. The databases (multiple databases-one per site) are located off-site from me with the table in the back-end. I've constructed code which adds a new comment field-memo data type, copies the existing comments to the new field and deletes the old field.

    However, I'm stumped on how to:

    a) Rename the new comment (memo data type) field to the same field name as the old field
    [img]/forums/images/smilies/cool.gif[/img] Set the caption for the new comment field to "Caption"

    Any help pointing me in the right direction would be greatly appreciated.

    Also, what potential pitfalls could be awaiting me changing the field type from text to memo? This field currently is ONLY viewed via a form and does NOT appear in any reports. The contents of the field are changed in one of two ways: manual entry via the form -OR- data from a spreadsheet is appended to the field via a query.

    TIA

    TexasTJ

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Set Caption for Field using Code (97)

    Probably do it in 2 steps:
    1. Copy Text field (Textfield) to Memo (called Memo1fld) field
    2. Delete text field (Textfield)
    3. Copy Memo1fld to Memo field (Textfield)
    4. Delete Memo1fld field

    To set the caption do:
    Me.Textfield.Caption = "Caption"

    Change the above field names to your field names, they are just a guide.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Caption for Field using Code (97)

    You can change the data type of the existing text field to memo using SQL:

    CurrentDb.Execute "ALTER TABLE [NameOfYourTable] ALTER COLUMN [NameOfYourField] LONGTEXT"

    Memo fields reputedly are more prone to corruption than text fields, but I have had very few problems with them.

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Caption for Field using Code (97)

    I'm using Access 97 and keep getting an error message "Syntax error in ALTER TABLE statement". The error does not have any number associated with it. The statement I'm trying to run is:

    ALTER TABLE tblpatient ALTER COLUMN strcomment_patinfo longtext;

    When using Access 2003 this statement works, but the client has Access 97. I spent an hour or so this weekend just trying to get the Alter Table statement to work in 97. Lack of success was why I was using the longer route of:

    1) Adding new Comment field with a Memo data type
    2) Copying the data from the existing Comment field to the new Comment field
    3) Deleting the old field
    4) Renaming the new field to the old field's name
    5) Setting the caption for th new (renamed) field to "Comment"

    I have everything but item 5) working. Thus far I haven't found a way to set the caption for the table field via code.

    Any ideas on how to get the ALTER TABLE statement working (in '97) or how to change a table's field's caption via code???

    TIA

    Texas TJ

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Caption for Field using Code (97)

    Apparently ALTER COLUMN wasn't implemented yet in Access 97 SQL. You can use DAO to add a caption to a field.

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("NameOfTable")
    Set fld = tdf.Fields("NameOfField')
    fld.Properties.Append fld.CreateProperty("Caption", dbText, "Comment")
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Caption for Field using Code (97)

    Thanks! That bit of code - altered slightly to look at the backend database - completed the journey. Hurray!!!

    Onward to the next issue . . .

    Thanks again - ever so much.

    TexasTJ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •