Re: Using query values for field name in table

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" <Rocky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9908F555-0EDF-4671-BFE5-D8EC7CB3F25F@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> 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

  • Re: Database set up help
    ... let's see...I choose the y/n data type because I am using ... User opens up form and enters Employee Information in the fields ... 2 of the 38 options in my main menu are BPCS Applications ... I set up a database with this so far: ...
    (microsoft.public.access.gettingstarted)
  • Re: runtime error with Mask edit control
    ... DateOfBirth property is a string. ... If no value is entered into the Property, the database stores a null. ... > its data type (one would assume the same as the property, ...
    (microsoft.public.vb.controls)
  • Re: Type Mismatch Error Message
    ... >> Using Text as SQL data type. ... I created a database on a SQL server manually and matched the column names ... I thought you "didn't use the database wizard?" ... >> Data type in form is No Constraints. ...
    (microsoft.public.frontpage.programming)
  • Re: Search for columns in tables that are a specific data type?
    ... It may be more info than you want, but you can use the Database Documenter ... was the AutoNumber data type; it did get migrated as an Integer, ... I know how to do this in SQL but can't seem to find ...
    (microsoft.public.access.queries)
  • RE: function doesnt return value - SOMETIMES!
    ... It seems the calling stack has some issues. ... If not, I suggest that you create a new database, and import the ... |> thing I can see is that you are declaring 3 of your numerical values as ... |> when it does work correctly that would be because this data type is ...
    (microsoft.public.access.modulesdaovba)