Re: Setting field properties in code

I tried to delete an index that I had just created. When I run the code to
delete it, I get an error '3265' Item not found in this collection. I have
typed in the correct name for the index, which in this test instance is
MyNewIndex. The table is tblFaculty. The code is being run temporarily
from an on click event on a button. The exact line of code I used was.

DBEngine(0)(0).TableDefs("tblFaculty").Indexes.Delete "MyNewIndex"

I have viewed the indexes for this field and it is indeed showing as
MyNewIndex. There is only one table in this test database, therefore no
relationships to get in the way.

What have I done wrong now? Sorry.

I also found a reference to DROP Index in the Help file. I created a SQL
query using
DROP INDEX MyNewIndex ON tblFaculty;
and it worked. Is there a downside to that method?

dixie

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:431f930f$0$14493$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> The name of the index does not matter.
>
> By default, Access names the primary key index PrimaryKey (without the
> space), but you can call it anything you like. By default, it names an
> index on a field with the same name as the field if that is available, but
> if not it uses a GUID as the name. Any valid name will do, but naturally a
> descriptive one is preferred as it is self-documenting.
>
> You should also be aware that Access automatically creates a hidden index
> on the foreign key field when you create a relation with referential
> integrity enforced. That means you don't want to manually index your
> foreign key fields when designing your database, and also that the number
> of indexes you can see programmatically is probabably greater than the
> number shown in the Indexes box in table design view.
>
> You can delete an index programmatically by using the Delete method on the
> Indexes collection of the TableDef:
> dbEngine(0)(0).TableDefs("MyTable").Indexes.Delete "MyIndex"
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dixie" <dixie@xxxxxxxxxxx> wrote in message
> news:431f5f01@xxxxxxxxxxxxxxxxxxxxxxxxx
>> Sorry, I didn't understand the first time and got involved with the other
>> parts. I have just been experimenting with the code for creating indexes
>> and now realise that it is the .unique = true/false that sets the No
>> Duplicates and Duplicates OK parts.
>>
>> Just a question for my understanding. Does it matter what you call the
>> index? Like in the line:
>> Set ind = tdf.CreateIndex("MyNewIndex") From my experimentation it
>> doesn't seem to matter. I used the exact same name as the field and it
>> seemed to work OK, then I did it again and used "MyNewIndex" and again it
>> seemed to work. When you create an index in design view, what is the
>> index naming convention that Access uses?
>>
>> I tried to modify the code to use Delete instead of Append to delete an
>> index, but it didn't work. How would I modify this code to delete an
>> existing index?.
>>
>> Set ind = tdf.CreateIndex("fldAditional")
>> ind.Fields.Append ind.CreateField("fldAdditional")
>> tdf.Indexes.Append ind
>>
>> Allen thanks heaps for the help. I really appreciate it. This is an
>> area that I have never been into before and found it a little daunting.
>> But to update an application that has lots of new fields on existing
>> tables and changed field properties for others, it is essential.
>>
>> dixie
>>
>> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
>> news:431efbf1$0$14453$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>> Late in that code, there was an example of how to create an index on the
>>> *table* for the field(s) you want to index.
>>>
>>> (There was also an example of setting the DisplayControl to checkbox.)
>>>
>>> "Dixie" <dixie@xxxxxxxxxxx> wrote in message
>>> news:431eccf1@xxxxxxxxxxxxxxxxxxxxxxxxx
>>>> Great, I have just worked out how to programmatically display the
>>>> Yes/No field as a checkbox. Lots of googling and fiddling with things
>>>> that were close has finally rewarded with a postive result. I guess
>>>> that means I am hopefully down to the indexing as per my previous post?
>>>> Any help?
>>>>
>>>> dixie
>>>>
>>>> "Dixie" <dixie@xxxxxxxxxxx> wrote in message
>>>> news:431eb849@xxxxxxxxxxxxxxxxxxxxxxxxx
>>>>> Thanks Allen, that was all it needed - to use 'Call' in front of it.
>>>>> That part is now working and I have extended that bit of code to
>>>>> include InputMask and Description as well as Format.
>>>>>
>>>>> Now, to finish this off, I am not too sure what you meant by "To make
>>>>> a yes/no field display as a check box, create and set the
>>>>> DisplayControl property." I have done some looking at the help file
>>>>> for the DisplayControl property, but it does not give any example
>>>>> code, just instructions on how to do it manually in design view. I
>>>>> will need to be able to set this to TextBox for Yes/No fields - I have
>>>>> noticed it is not the default value. Could you possibly give me the
>>>>> context for the code required to do this.
>>>>>
>>>>> The other thing is setting an index - I have again read your post and
>>>>> still can't quite make it work. I want to be able to set the index
>>>>> property to Yes (Duplicates OK) and Yes (No Duplicates). Can you
>>>>> possibly give me an example of code to do it, again using the Faculty
>>>>> Table and say a Field ID that could be set to Indexed (Yes No
>>>>> Duplicates) or Indesed (Yes Duplicates OK)
>>>>>
>>>>> I know I am asking for a lot, but the pressure of time is getting to
>>>>> me. I normally do a lot of internet 'googling' to try to find help for
>>>>> doing things, but that has let me down a bit lately and I have spent
>>>>> many hours fiddling with variations of what might work.
>>>>>
>>>>> BTW, I have already read your excellent article on dealing with non
>>>>> american date formats and it helped me out on a previous problem.
>>>>>
>>>>> Thanks for your perseverence.
>>>>> dixie
>>>>>
>>>>> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
>>>>> news:431e99d1$0$14492$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>>>>> Try adding the word Call, i.e.:
>>>>>> Call SetPropertyDAO(...
>>>>>>
>>>>>> If it won't compile, and you are using Access 2000 or 2002, then
>>>>>> choose References on the Tools menu (from the code window), and check
>>>>>> the box beside:
>>>>>> Microsoft DAO 3.6 Library
>>>>>> More on references:
>>>>>> http://allenbrowne.com/ser-38.html
>>>>>>
>>>>>> (BTW, the Format property is a Text type, regardless of the type of
>>>>>> field.)
>>>>>>
>>>>>> "Dixie" <dixie@xxxxxxxxxxx> wrote in message
>>>>>> news:431e7074@xxxxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>I am having the same problem with that that I was having before I
>>>>>>>gave up trying to get it right. When I put that into a button module
>>>>>>>behind a form (where I am testing it from), it comes up with an error
>>>>>>>as soon as I put it there and fix up the _ for the broken line.
>>>>>>>Microsoft Visual Basic Compile error: Expected: =
>>>>>>> There is no point in looking at the help file for this error as it
>>>>>>> is very generic.
>>>>>>>
>>>>>>> I tried changing the dbText to dbDate and it the same thing
>>>>>>> happened.
>>>>>>>
>>>>>>> Just to make sure I haven't misspelled anything here is the line
>>>>>>> removed out of the form module.
>>>>>>>
>>>>>>> SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty").Fields("StartDate"),"Format",
>>>>>>> dbText, "dd/mm/yyyy")
>>>>>>>
>>>>>>> Is there something obviously wrong?
>>>>>>>
>>>>>>> dixie
>>>>>>>
>>>>>>> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
>>>>>>> news:431e6d53$0$14487$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>> The idea is:
>>>>>>>> SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty").Fields("StartDate"),_
>>>>>>>> "Format", dbText, "dd/mm/yyyy")
>>>>>>>>
>>>>>>>> Or you might use "General Date" in place of "dd/mm/yyyy", which
>>>>>>>> will use the format that the user has defined in the Windows
>>>>>>>> Control Panel, under Regional Options.
>>>>>>>>
>>>>>>>> BTW, if you are having difficulties with the Australian date format
>>>>>>>> in Access, see:
>>>>>>>> International Date Formats in Access
>>>>>>>> at:
>>>>>>>> http://allenbrowne.com/ser-36.html
>>>>>>>> It deals with the 3 cases that often trip people up.
>>>>>>>>
>>>>>>>> "Dixie" <dixie@xxxxxxxxxxx> wrote in message
>>>>>>>> news:431e5f64@xxxxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>>> OK, I've copied the code into a module and had a play with it.
>>>>>>>>>
>>>>>>>>> Now just say I want to create a format property of d/m/yyyy for a
>>>>>>>>> date/time field called StartDate, that is in a table called
>>>>>>>>> Faculty, what is the syntax I need for the event I am going to do
>>>>>>>>> this from. I know that is what I have to do, but can't work out
>>>>>>>>> the arguments and how they come together.
>>>>>>>>>
>>>>>>>>> dixie
>>>>>>>>>
>>>>>>>>> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
>>>>>>>>> news:431e4f7d$0$14481$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>>>> If the Field in the TableDef does not have the property, you need
>>>>>>>>>> to CreateProperty(). The example below shows how to create the
>>>>>>>>>> property if it does not exist, and set it.
>>>>>>>>>>
>>>>>>>>>> To make a yes/no field display as s check box, create and set the
>>>>>>>>>> DisplayControl property.
>>>>>>>>>>
>>>>>>>>>> To create an index on a field, CreateIndex on the table.
>>>>>>>>>>
>>>>>>>>>> Note that if you are working on an attached table, you will need
>>>>>>>>>> to OpenDatabase and work directly on the back end.
>>>>>>>>>>
>>>>>>>>>> The example below illustrates how to set what you might consider
>>>>>>>>>> standard properties:
>>>>>>>>>> - setting the table's SubDatasheetName to [None].
>>>>>>>>>> - setting AllowZeroLength to No for all text fields, memos, and
>>>>>>>>>> hyperlinks.
>>>>>>>>>> - removing that darn zero as Default Value for numeric fields.
>>>>>>>>>> - setting the Format property for Currency types (and
>>>>>>>>>> illustrating how to set the Default Value if you wish.)
>>>>>>>>>> - setting Yes/No fields to display as a check box.
>>>>>>>>>> - setting a Caption with spaces on fields that have a mixed-case
>>>>>>>>>> name (e.g. OrderDate.)
>>>>>>>>>> - setting a Description of each field
>>>>>>>>>>
>>>>>>>>>> Finally, the last example shows how to create a primary key
>>>>>>>>>> index, a single-field index, and a multi-field index.
>>>>>>>>>>
>>>>>>>>>> Paste the code into a module, and see how it works.
>>>>>>>>>> ----------------------code starts-----------------
>>>>>>>>>> Sub StandardProperties(strTableName As String)
>>>>>>>>>> 'Purpose: Properties you always want set by default:
>>>>>>>>>> ' TableDef: Subdatasheets off.
>>>>>>>>>> ' Numeric fields: Remove Default Value.
>>>>>>>>>> ' Currency fields: Format as currency.
>>>>>>>>>> ' Yes/No fields: Display as check box. Default to
>>>>>>>>>> No.
>>>>>>>>>> ' Text/memo/hyperlink: AllowZeroLength off,
>>>>>>>>>> ' UnicodeCompression on.
>>>>>>>>>> ' All fields: Add a caption if mixed case.
>>>>>>>>>> 'Argument: Name of the table.
>>>>>>>>>> 'Note: Requires: SetPropertyDAO()
>>>>>>>>>> Dim db As DAO.Database 'Current database.
>>>>>>>>>> Dim tdf As DAO.TableDef 'Table nominated in argument.
>>>>>>>>>> Dim fld As DAO.Field 'Each field.
>>>>>>>>>> Dim strCaption As String 'Field caption.
>>>>>>>>>> Dim strErrMsg As String 'Responses and error messages.
>>>>>>>>>>
>>>>>>>>>> 'Initalize.
>>>>>>>>>> Set db = CurrentDb()
>>>>>>>>>> Set tdf = db.TableDefs(strTableName)
>>>>>>>>>>
>>>>>>>>>> 'Set the table's SubdatasheetName.
>>>>>>>>>> Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]",
>>>>>>>>>> _
>>>>>>>>>> strErrMsg)
>>>>>>>>>>
>>>>>>>>>> For Each fld In tdf.Fields
>>>>>>>>>> 'Handle the defaults for the different field types.
>>>>>>>>>> Select Case fld.Type
>>>>>>>>>> Case dbText, dbMemo 'Includes hyperlinks.
>>>>>>>>>> fld.AllowZeroLength = False
>>>>>>>>>> Call SetPropertyDAO(fld, "UnicodeCompression",
>>>>>>>>>> dbBoolean, _
>>>>>>>>>> True, strErrMsg)
>>>>>>>>>> Case dbCurrency
>>>>>>>>>> fld.DefaultValue = 0
>>>>>>>>>> Call SetPropertyDAO(fld, "Format", dbText, "Currency",
>>>>>>>>>> _
>>>>>>>>>> strErrMsg)
>>>>>>>>>> Case dbLong, dbInteger, dbByte, dbDouble, dbSingle,
>>>>>>>>>> dbDecimal
>>>>>>>>>> fld.DefaultValue = vbNullString
>>>>>>>>>> Case dbBoolean
>>>>>>>>>> Call SetPropertyDAO(fld, "DisplayControl", dbInteger,
>>>>>>>>>> _
>>>>>>>>>> CInt(acCheckBox))
>>>>>>>>>> End Select
>>>>>>>>>>
>>>>>>>>>> 'Set a caption if needed.
>>>>>>>>>> strCaption = ConvertMixedCase(fld.Name)
>>>>>>>>>> If strCaption <> fld.Name Then
>>>>>>>>>> Call SetPropertyDAO(fld, "Caption", dbText,
>>>>>>>>>> strCaption)
>>>>>>>>>> End If
>>>>>>>>>> Next
>>>>>>>>>>
>>>>>>>>>> 'Clean up.
>>>>>>>>>> Set fld = Nothing
>>>>>>>>>> Set tdf = Nothing
>>>>>>>>>> Set db = Nothing
>>>>>>>>>> If Len(strErrMsg) > 0 Then
>>>>>>>>>> Debug.Print strErrMsg
>>>>>>>>>> Else
>>>>>>>>>> Debug.Print "Properties set for table " & strTableName
>>>>>>>>>> End If
>>>>>>>>>> End Sub
>>>>>>>>>>
>>>>>>>>>> Function SetPropertyDAO(obj As Object, strPropertyName As String,
>>>>>>>>>> _
>>>>>>>>>> intType As Integer, varValue As Variant, Optional strErrMsg As
>>>>>>>>>> String) As Boolean
>>>>>>>>>> On Error GoTo ErrHandler
>>>>>>>>>> 'Purpose: Set a property for an object, creating if
>>>>>>>>>> necessary.
>>>>>>>>>> 'Arguments: obj = the object whose property should be set.
>>>>>>>>>> ' strPropertyName = the name of the property to set.
>>>>>>>>>> ' intType = the type of property (needed for
>>>>>>>>>> creating)
>>>>>>>>>> ' varValue = the value to set this property to.
>>>>>>>>>> ' strErrMsg = string to append any error message to.
>>>>>>>>>>
>>>>>>>>>> If HasProperty(obj, strPropertyName) Then
>>>>>>>>>> obj.Properties(strPropertyName) = varValue
>>>>>>>>>> Else
>>>>>>>>>> obj.Properties.Append obj.CreateProperty(strPropertyName,
>>>>>>>>>> intType, varValue)
>>>>>>>>>> End If
>>>>>>>>>> SetPropertyDAO = True
>>>>>>>>>>
>>>>>>>>>> ExitHandler:
>>>>>>>>>> Exit Function
>>>>>>>>>>
>>>>>>>>>> ErrHandler:
>>>>>>>>>> strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & "
>>>>>>>>>> not set to " & varValue & ". Error " & Err.Number & " - " &
>>>>>>>>>> Err.Description & vbCrLf
>>>>>>>>>> Resume ExitHandler
>>>>>>>>>> End Function
>>>>>>>>>>
>>>>>>>>>> Public Function HasProperty(obj As Object, strPropName As String)
>>>>>>>>>> As Boolean
>>>>>>>>>> 'Purpose: Return true if the object has the property.
>>>>>>>>>> Dim varDummy As Variant
>>>>>>>>>>
>>>>>>>>>> On Error Resume Next
>>>>>>>>>> varDummy = obj.Properties(strPropName)
>>>>>>>>>> HasProperty = (Err.Number = 0)
>>>>>>>>>> End Function
>>>>>>>>>>
>>>>>>>>>> Sub CreateIndexesDAO()
>>>>>>>>>> Dim db As DAO.Database
>>>>>>>>>> Dim tdf As DAO.TableDef
>>>>>>>>>> Dim ind As DAO.Index
>>>>>>>>>>
>>>>>>>>>> 'Initialize
>>>>>>>>>> Set db = CurrentDb()
>>>>>>>>>> Set tdf = db.TableDefs("tblDaoContractor")
>>>>>>>>>>
>>>>>>>>>> '1. Primary key index.
>>>>>>>>>> Set ind = tdf.CreateIndex("PrimaryKey")
>>>>>>>>>> With ind
>>>>>>>>>> .Fields.Append .CreateField("ContractorID")
>>>>>>>>>> .Unique = False
>>>>>>>>>> .Primary = True
>>>>>>>>>> End With
>>>>>>>>>> tdf.Indexes.Append ind
>>>>>>>>>>
>>>>>>>>>> '2. Single-field index.
>>>>>>>>>> Set ind = tdf.CreateIndex("Inactive")
>>>>>>>>>> ind.Fields.Append ind.CreateField("Inactive")
>>>>>>>>>> tdf.Indexes.Append ind
>>>>>>>>>>
>>>>>>>>>> '3. Multi-field index.
>>>>>>>>>> Set ind = tdf.CreateIndex("FullName")
>>>>>>>>>> With ind
>>>>>>>>>> .Fields.Append .CreateField("Surname")
>>>>>>>>>> .Fields.Append .CreateField("FirstName")
>>>>>>>>>> End With
>>>>>>>>>> tdf.Indexes.Append ind
>>>>>>>>>>
>>>>>>>>>> 'Refresh the display of this collection.
>>>>>>>>>> tdf.Indexes.Refresh
>>>>>>>>>>
>>>>>>>>>> 'Clean up
>>>>>>>>>> Set ind = Nothing
>>>>>>>>>> Set tdf = Nothing
>>>>>>>>>> Set db = Nothing
>>>>>>>>>> Debug.Print "tblDaoContractor indexes created."
>>>>>>>>>> End Sub
>>>>>>>>>> ----------------------code ends-----------------
>>>>>>>>>>
>>>>>>>>>> "Dixie" <dixie@xxxxxxxxxxx> wrote in message
>>>>>>>>>> news:431e4763$1@xxxxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>>>>> Can I set the Format property in a date/time field in code?
>>>>>>>>>>>
>>>>>>>>>>> Can I set the Input Mask in a date/time field in code?
>>>>>>>>>>>
>>>>>>>>>>> Can I set the Format of a Yes/No field to Checkbox in code?
>>>>>>>>>>>
>>>>>>>>>>> I am working on a remote update of tables and fields and can't
>>>>>>>>>>> find enough information on these things.
>>>>>>>>>>>
>>>>>>>>>>> Also, how do you index a field in code?
>
>


.

Relevant Pages

  • Re: War on Short yellow lights.
    ... I bitched to the police and city council for years about ... The way to control speeding is to design the road for the speed ... feel a bit safer (or even less safe for that matter), ... rightful owner should I desire to do so. ...
    (sci.electronics.design)
  • Re: War on Short yellow lights.
    ... I bitched to the police and city council for years about ... The way to control speeding is to design the road for the speed ... feel a bit safer (or even less safe for that matter), ... rightful owner should I desire to do so. ...
    (sci.electronics.design)
  • Re: Is New Orleans finished ?
    ... I expect solid answers regarding this matter from the folks running the plants. ... "Trust the scientists who know, yada, yada, yada". ... I was merely pointing out fields where a rather lax approach towards design validation isn't working. ... Are you saying that there are never any recalls and all medical devices have been engineered after looking at "all aspects"??? ...
    (sci.electronics.design)
  • Re: The so-called professionals
    ... I'd been doing graphic design and production since cutting and pasting was ... I'm under the apparent delusion that no matter what ... know what you need (clever pre-press guy) unless you spell it out. ... In most work flows and for most jobs the actual color called out in your native Quark doc does not really matter - as the pressman can put the correct ink on the press. ...
    (comp.publish.prepress)
  • Re: Update problem
    ... There are a couple of ways to avoid the orphan records in the subform: ... Select the foreign key field (i.e. the one that links to the main form's ... In the lower pane of table design, set its Required property to Yes. ... Private Sub Form_BeforeInsert ...
    (microsoft.public.access.modulesdaovba)