Re: Using query values for field name in table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <Rocky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:22EE4655-8E00-43CF-9CC3-6988F8DC0009@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> 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

  • CurrentDb( ) Method
    ... My syntax as follows: ... Dim db As Database ... Dim rs As Recordset ... that is the why the help files show it also. ...
    (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)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... I created a database exactly as you said, the only change I made was to ... upload it into the 'databases' folder, and not a folder called 'App_Data', ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: mailmerge and sql
    ... that is essentially a database application with a document ... the recordset to the Word Template and use it as if I got ... >> using an ADO recordset as a datasource (if it could be ... >Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)