Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

For tlbroadbent

For tlbroadbent

For tlbroadbent

Thanks for the idea.  I am actually working in Access 2002. the line of code you mentioned does not seem to be accepted.  Maybe I have a syntax error and need quotes or something.  This time I have included what the help file says concerning creating an index and the section of code I am working on below that.  Thank you again for the help.
Here is what the help file says for CreateIndex, it is somewhat different than what you mentioned:
CreateIndex Method
Creates a new Index object (Microsoft Jet workspaces only).
Set index = tabledef.CreateIndex (name)
The CreateIndex method syntax has these parts.
Part Description
index An object variable that represents the index you want to create.
tabledef An object variable that represents the TableDef object you want to use to create the new Index object.
name Optional. A Variant (String subtype) that uniquely names the new Index object. See the Name property for details on valid Index names.
You can use the CreateIndex method to create a new Index object for a TableDef object. If you omit the optional name part when you use CreateIndex, you can use an appropriate assignment statement to set or reset the Name property before you append the new object to a collection. After you append the object, you may or may not be able to set its Name property, depending on the type of object that contains the Indexes collection. See the Name property topic for more details.
If name refers to an object that is already a member of the collection, a run-time error occurs when you use the Append method.
To remove an Index object from a collection, use the Delete method on the collection.

HERE Is the section of code I am working on:
Sub SendSBTInvoice(inv As Integer) 'based on Sub SendBWInvoice KS 4/01

    Dim dbs, db As Database
    Dim qry, qryUpd, qryInvUpd, qryLn As QueryDef
    Dim rst As Recordset
    Dim fHandle As Integer
    Dim strFileName As String
    Dim printfreight As String
    Dim idx As Index
    Dim tblDef, tblR As TableDef
    Dim idxOrderLineID As Index
    Dim fld As Field
    DoCmd.Hourglass True
    DoCmd.Echo False, "Open Databases"
    Set dbs = CurrentDb
    Set qryLn = dbs.QueryDefs("qryOrderHeadLine")
    Set rst = qryLn.OpenRecordset()
    Set tblR = rst.CreateTableDef("tblCmbOrdHeadLine")  'Followed example in printout from tec-tips webpage
                                'but it doesn't seem to accept qryLn in place of dbs so will try another option
    DoCmd.OpenTable "tblCmbOrdHeadLine"

    'Create an index and set its properties NEITER TRY NUMBER ONE NOR TWO SEEMED TO BE WORKING
    'Set idx = tblR.CreateIndex("idxOrderLineID")
    'idx.Primary = True
    'idx.Required = True
    'idx.Unique = True
    'Create Index "idxOrderLineID" On "tblCmbOrdHeadLine (OrderLineID)"
    'Add a field to the index  Not sure if this step is necessary
    Set fld = idx.CreateField("OrderLineID")
    idx.Fields.Append fld
    'Add the index to the TableDef
    tblR.Indexes.Append idx
    Set qry = dbs.QueryDefs("qrysbtinvoice")
    qry.PARAMETERS![invno] = inv
    Set rst = qry.OpenRecordset()
    DoCmd.Echo False, "Send Data To SBT"
    If rst.RecordCount > 0 Then
        strFileName = lookup("filename", "tblfilelocation", "fileid", "SBTOUT")
        fHandle = stdFileOpen(strFileName, "Append")
        If fHandle > 0 Then
            'If rst!EDIFreight = True And rst!sumoftraderfreight > 0 Then
            If rst!BusinessWorksID = "Finger" Then  ' 10/20 Do only for FingerHut
                printfreight = rst!sumoftraderfreight
                printfreight = rst!sumoffreight
            End If
            If rst!EDIFreight = True Then ' pass freight charges
              Write #fHandle, rst!SBTid, CInt(rst!InvoiceNo), "", CStr(rst!UPSShipDate), CDec(rst!sumofsellprice), CDec(rst!sumofhandcharge), CDec(printfreight)
            Else ' Don't pass freight charges
              If rst!BusinessWorksID = "12SPIEGEL" Then ' Pass handleing charge for Speigal 5/99
                 Write #fHandle, rst!SBTid, CInt(rst!InvoiceNo), CStr(rst!PurchaseOrderNum), CStr(rst!UPSShipDate), CDec(rst!sumofsellprice), CDec(rst!sumofhandcharge), ""
                 Write #fHandle, rst!SBTid, CInt(rst!InvoiceNo), CStr(rst!PurchaseOrderNum), CStr(rst!UPSShipDate), CDec(rst!sumofsellprice), "", ""
              End If
            End If
            Close fHandle    ' Close file.
            DoCmd.Echo True
            DoCmd.Echo False, "Update Orders"
            Set qryUpd = dbs.QueryDefs("qryStatToDone")
            qryUpd.PARAMETERS![invno] = inv
            qryUpd.Execute 'incriments through records to change them
            DoCmd.Echo False, "Update Invoice"
            Set qryInvUpd = dbs.QueryDefs("qryInvoiceToDoneSBT")
            qryInvUpd.PARAMETERS![invno] = inv
            qryInvUpd.Execute 'incriments through records to change them
            MsgBox "Finished Processing SBT Output File"
        End If  'file opened
        MsgBox "No Reccords To Send"
    End If
    DoCmd.Echo True
    DoCmd.Hourglass False

End Sub

RE: For tlbroadbent

The code I gave was to run a SQL data definition query. You found the reference to the CreateIndex method. These are two different ways to create queries with different syntax, obviously.

The following lines to your VBA module after the code that creates the table.

Dim sql As String, i As Integer
sql = "Create Index idxOrderLineID On tblCmbOrdHeadLine (OrderLineID)"
DoCmd.RunSQL sql

Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close