RE: Using query values for field name in table

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

"'69 Camaro" wrote:

> > 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: Using query values for field name in table
    ... tblProcedures will be created in the current database. ... Dim dbsGeneralThoracic As Database ... Dim fOpenedDB As Boolean ... > "Proc" is a caption given to acalculated field in the query ...
    (microsoft.public.access.modulesdaovba)
  • RE: Using query values for field name in table
    ... "Rocky" wrote: ... >> tblProcedures will be created in the current database. ... >> Dim dbsGeneralThoracic As Database ... >> Dim fOpenedDB As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • RE: Using query values for field name in table
    ... "Rocky" wrote: ... >> tblProcedures will be created in the current database. ... >> Dim dbsGeneralThoracic As Database ... >> Dim fOpenedDB As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)