For tlbroadbent 1

[ksigvart]

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).
Syntax
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.
Remarks
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&quot

Set rst = qryLn.OpenRecordset()
Set tblR = rst.CreateTableDef("tblCmbOrdHeadLine&quot '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

'TRY NUMBER ONE FROM SOMEONE ELSE'S QUESTUION ON TEC-TIPS
'Set idx = tblR.CreateIndex("idxOrderLineID&quot
'idx.Primary = True
'idx.Required = True
'idx.Unique = True

'TRY NUMBER TWO FROM SOMEONE ELSE'S QUESTUION ON TEC-TIPS
'Create Index "idxOrderLineID" On "tblCmbOrdHeadLine (OrderLineID)"

'Add a field to the index Not sure if this step is necessary
Set fld = idx.CreateField("OrderLineID&quot
idx.Fields.Append fld

'Add the index to the TableDef
tblR.Indexes.Append idx

Set qry = dbs.QueryDefs("qrysbtinvoice&quot
qry.PARAMETERS![invno] = inv
Set rst = qry.OpenRecordset()

DoCmd.Echo False, "Send Data To SBT"

If rst.RecordCount > 0 Then
rst.MoveFirst
strFileName = lookup("filename", "tblfilelocation", "fileid", "SBTOUT&quot
fHandle = stdFileOpen(strFileName, "Append&quot

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
Else
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), ""
Else
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&quot
qryUpd.PARAMETERS![invno] = inv
qryUpd.Execute 'incriments through records to change them

DoCmd.Echo False, "Update Invoice"
Set qryInvUpd = dbs.QueryDefs("qryInvoiceToDoneSBT&quot
qryInvUpd.PARAMETERS![invno] = inv
qryInvUpd.Execute 'incriments through records to change them

MsgBox "Finished Processing SBT Output File"
End If 'file opened
dbs.Close
Else
MsgBox "No Reccords To Send"
End If

DoCmd.Echo True
DoCmd.Hourglass False

End Sub

 

[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
Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot