Trouble defining a field

[The_Kurgan]

Hi all!

I am attempting to create a table based on a selection that the user chooses. Available to the user is the list of fields from another Access table. The new table will basically contain that chosen field and the associated type (Text, Date, or Double).

Here is my code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim myTable As DAO.TableDef
Dim myField As DAO.Field
Dim myProp As DAO.Property

'variables
fld = Me.List7.Value
Op = Me.List9.Value
FldAndOp = fld & " " & Op

'Get field type of field chosen by user (found in My_Data table)
Set rs = db.OpenRecordset("My_Data")
My_My_Data_Tbl = TypeName(rs.Fields(fld).Value)

'Create table with field name + operator and give the field the proper field type
With myTable
    If My_My_Data_Tbl = "Double" Then
        .Fields.Append .CreateField(FldAndOp, dbDouble)
    ElseIf My_My_Data_Tbl = "Date" Then
        .Fields.Append .CreateField(FldAndOp, dbDate)
    ElseIf My_My_Data_Tbl = "String" Then
        .Fields.Append .CreateField(FldAndOp, dbText)
    End If
End With
db.TableDefs.Append myTable   '!!!!!!!!!!!!!!!!!!!!!!!! THIS LINE GIVES ME THE ERROR !!!!!!!!!!!!!!!!!!!!!!!!
Set myField = myTable.Fields(FldAndOp)

The error I'm getting is "No field defined--cannot append TableDef or Index." The strange thing is that it seems to be happening on one date field. All other fields seem to work (including other date fields). I've double-checked the DataType in the table itself and it's the same as other date fields.

I hope this makes sense. I'd love to see what y'all come up with. TIA!

 

[Micron]

This might not be of much help since I have limited experience with creating tables this way.
You're supposed to create the field first, then append it to the fields collection of the table def, then add the table def to the tables collection. You seem to be trying to append then create.
.Fields.Append .CreateField(FldAndOp, dbDouble) as opposed to
.CreateField(FldAndOp, dbDouble)
.Fields.Append
However, since you said it works most of the time, then have you stepped through this code to ensure at least one If statement is true within your With block? If there is a failure just one time, your code is trying to append a table def when no field was created. Are you certain that there is always a recordset returned after every user choice?
Maybe add another Else line with debug.print "My_My_Data_Tbl= " & My_My_Data_Tbl. If it's a different type, null or empty, you'll get a printout.

 

[xenou]

I'd be willing to test (try the same thing) but I'd want to know what the values are for the variables.

 

[The_Kurgan]

Micron: Yes, I stepped through it and everything appears fine. However, a null was being returned as the field type for that one particular field.
xenou: That is very kind of you. Thank you.

Thank you both for your replies! I stayed up last night and figured a way around it by just creating a new table with those field names in one column and the data type in the other. I'll just have to remember to add fields/types if new fields are brought into the original. It's not ideal, but it works.