RE: Using query values for field name in table

> 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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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!
.

Relevant Pages

  • Re: DAO takes too much time to link tables
    ... I am trying to link some tables to a back-end database. ... 'Link the tables contained at the given query. ... Dim rst As DAO.Recordset ... Dim success As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: HELP WITH AUTO EXE PROGRAMMING/CODE
    ... Create a separate database for those times when you won't have your database ... create a Query that returns only the Records about which you ... Dim strsubject As String ...
    (comp.databases.ms-access)
  • Re: mailmerge and sql
    ... means that you will not be able to see them in a database you open using the ... I believe you may have to use DAO instead of ADO to ... then creates a View containing a UNION query. ... Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)
  • RE: Using query values for field name in table
    ... "'69 Camaro" wrote: ... > tblProcedures will be created in the current database. ... > Dim dbsGeneralThoracic As Database ... > Dim fOpenedDB As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: Using a module in a query
    ... > query builder: ... > in modules in your database are visible using the ... > them you will see the custom functions they contain. ... >>Dim WholeWeeks As Variant ...
    (microsoft.public.access.queries)
Loading