Using query values for field name in table

[Rocky]

I have a long list of numbers and names which I imported from a .pdf file.
In the .pdf file they were a single string. After stripping off unprintable
characters and separating the numbers and names with a query, I want to use
each name for a field name in a separate table. My problem is getting the
code to find the field in the query (a calculated value) and store the value
to a variable which I can then use as a field name. Following is the code:
Sub CreateTableDefX()

Dim dbsGeneralThoracic As Database
Dim tdfNew As TableDef
Dim prpLoop As Property
Dim fldName As Field

Dim recno As Long
Set dbsGeneralThoracic = CurrentDb

Set dbsGeneralThoracic = OpenDatabase("I:\Database\Thoracic
database\General Thoracic.mdb")
DoCmd.OpenQuery "qryProcSpecImport"

' Create a new TableDef object.
Set tdfNew = dbsGeneralThoracic.CreateTableDef("tblProcedures")
recno = 1

Do While recno < 125
DoCmd.GoToRecord acDataQuery, "qryProcSpecImport", acNext, recno

Set fldName = Proc

On the last line above is where I get the error message, "Compile error:
type mismatch"

Help!

 

['69 Camaro]

Hi, Rocky.

Proc has not been defined as a DAO.Field object type and, unless it's a
global variable or module-level variable and you've assigned it a value
outside of this procedure, it hasn't been initialized before being assigned
to the "fldName" Field, which will present another problem after you fix
Proc's object type.

If you haven't set Option Explicit in your modules (which appears to be the
case), then Proc is a Variant.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Rocky]

Hi, 69 Camaro,
Tx.
"Proc" is a caption given to acalculated field in the query
"qryProcSpecImport".
That is the crux of my problem, what are the properties of a field in a
query? Is it even appropriate to call it a "field"? Whereas a field is a
field in a table. And knowing that, how does one extract its value? I
realize I'm not talking in computerese, so I'm sorry if the concepts I'm
discussing are confusing.
And then, should fldName be defined as a string variable which I can then
use to insert as a fieldname, or are there specific qualities of a variable
defined as a "field" which I need to maintain?
Rocky

 

['69 Camaro]

Is it even appropriate to call it a "field"? Whereas a field is a

field in a table. And knowing that, how does one extract its value?

Please see the following example for the answers to these questions.
qryProcSpecImport is expected to be a query in the current database, and
tblProcedures will be created in the current database. The table will have
fields with Text data types, will be required, and will not allow zero length
strings. You may alter this example to meet your needs, but as you can see,
the field name is assigned at creation, allong with the data type and size.
The fields properties can also be set in the following code:

Public Sub CreateTableDefX()

On Error GoTo ErrHandler

Dim dbsGeneralThoracic As Database
Dim recSet As DAO.Recordset
Dim tdfNew As TableDef
Dim fldName As DAO.Field
Dim recno As Long
Dim fOpenedDB As Boolean
Dim fOpenedRecSet As Boolean

Set dbsGeneralThoracic = CurrentDb
fOpenedDB = True

Set recSet = CurrentDb().OpenRecordset("qryProcSpecImport")
fOpenedRecSet = True

' Create a new TableDef object.
Set tdfNew = dbsGeneralThoracic.CreateTableDef("tblProcedures")

If (Not (recSet.BOF And recSet.EOF)) Then
recSet.MoveLast
recSet.MoveFirst

For recno = 1 To recSet.RecordCount
Set fldName = tdfNew.CreateField(recSet.Fields("Proc").Value,
dbText, 25)
tdfNew.Fields.Append fldName
fldName.Properties("Required").Value = True
fldName.Properties("AllowZeroLength").Value = False

If (Not (recSet.EOF)) Then
recSet.MoveNext
End If
Next recno
End If

dbsGeneralThoracic.TableDefs.Append tdfNew

CleanUp:

Set fldName = Nothing
Set tdfNew = Nothing

If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If

Set recSet = Nothing

If (fOpenedDB) Then
dbsGeneralThoracic.Close
fOpenedDB = False
End If

Set dbsGeneralThoracic = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in CreateTableDefX( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Rocky]

Camaro,
Thank you. I probably won't get a chance to try out the code you sent until
tomorrow, but thanks for helping me through the wasteland. I'm reading your
code while simultaneously reading a textbook on coding and applying both to
the project I'm working on.
Rocky

 

[Rocky]

Camaro,
The code worked, I had to change slightly the syntax referring to the "Proc"
field, as you can see in the following lines. Call me greedy, but I also
want to try to define the fields in the new tblProcedures table as having a
Value List of "Yes" or "No". I modified the code you sent to include the
lines as seen below. However, that resulted in an "Error#3270 Property not
f ound" error message. Can you help again with the syntax
Tx
Rocky

 

[Rocky]

Camaro,
Here's the code that I forgot to include: (sorry)
For recno = 1 To recSet.RecordCount
Set fldName = tdfNew.CreateField((recSet.Fields!Proc.Value),
dbText, 50)

tdfNew.Fields.Append fldName
fldName.Properties("Required").Value = True
fldName.Properties("AllowZeroLength").Value = False
fldName.Properties("RowSourceType") = "ValueList"
fldName.Properties("RowSource").Value = "'Yes';'No'"
fldName.Properties("BoundColumn").Value = 1
fldName.Properties("ColumnCount").Value = 1
fldName.Properties("ColumnWidths").Value = "1'"
fldName.Properties("ColumnHeads").Value = No
fldName.Properties("ListRows").Value = 8
fldName.Properties("ListWidth").Value = "1'"
fldName.Properties("LimitToList").Value = No
fldName.Properties("DisplayControl") = "ComboBox"

If (Not (recSet.EOF)) Then

 

['69 Camaro]

Hi, Rocky.

The code worked, I had to change slightly the syntax referring to the
"Proc"
field

You didn't need to change the syntax on the Fields collection to get it to
work, but your syntax is just fine. They'll both execute at the same speed,
so there's no benefit of one over the other, other than one may be easier to
remember how to type and therefore is quicker to write code for.

I also
want to try to define the fields in the new tblProcedures table as having
a
Value List of "Yes" or "No".

No can do. Those properties you're trying to assign values to are for combo
box objects, not table field objects. I suspect you want to put bad joo-joo
in your database by creating lookup fields. Please see the following Web
page for why this is not recommended:

http://www.mvps.org/access/lookupfields.htm

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Rocky]

Carmaro,
What then is the best way to control input of "Yes/No" fields. The database
I'm working on will be harvested to a larger database. At that point in
time, many of the controlled input will need to be put into numeric
categorical format. I don't remember what it was exactly about the "Yes/No"
field property that I saw, but it seemed that it would be easier to format a
report of harvest data which had text "yes or no" translated to 0's or 1's
for the harvest. (e.g. let "yes" = 1, etc. or something like that).
Suggestions?
Tx
Rocky

 

['69 Camaro]

Hi, Rocky.

What then is the best way to control input of "Yes/No" fields.

When creating the new field, use a Boolean data type, not a Text data type.
For example:

Set fldName = tdfNew.CreateField(recSet.Fields("Proc").Value, dbBoolean)

The numerical values will be 0 and -1 (TRUE = -1 in VB, not 1 as in other
programming languages).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Rocky]

Camaro,
Another question about lookup fields. In this database I have constructed
other lookup fields for the purpose of definining input by users and avoiding
responses which are not completely within bounds of acceptable responses.
What is the preferred alternative to lookup fields? Is it better to define
acceptable responses by use of default texts? And in that case is there a
way to create a "drop down" list for the user to choose from which is not a
lookup field.
Again, tx
Rocky