[AccessD] Help with Code

Andy Lacey andy at minstersystems.co.uk
Mon Feb 7 09:30:12 CST 2005


Happy to help Terri. And if your last question was in any way semi-serious
you could always make a donation to dba. Donations are what keep the list
alive.

--
Andy Lacey
http://www.minstersystems.co.uk



--------- Original Message --------
From: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
To: accessd at databaseadvisors.com <accessd at databaseadvisors.com>
Subject: RE: [AccessD] Help with Code
Date: 07/02/05 15:49

>
> That was it!!!!  That was what I needed - you are so awesome!  Where do
> I send the check....
>
> Terri Jarus
> Vice President, Contract Services
> jarus at amerinet-gpo.com
> 314-542-1902
>
> >>> andy at minstersystems.co.uk 2/7/2005 8:20:40 AM >>>
>
> Terri
> Yes, if you have alphanumeric contract id's then cahnge the FindFirst
> line
> to
>
> rstOut.FindFirst "ContractId='" & !ContractId &
"'"
>
> --
> Andy Lacey
> http://www.minstersystems.co.uk
>
>
>
> --------- Original Message --------
> From: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> To: accessd at databaseadvisors.com <accessd at databaseadvisors.com>
> Subject: RE: [AccessD] Help with Code
> Date: 07/02/05 14:53
>
> >
> > You knew it wouldn't go well straight out.  It runs through the
> first
> > time just fine and adds the ContractId to the new table - when it
> loops
> > through it errors out (Run time 3070)- &quot;The Microsoft Jet
> Database
> > Engine does not recognize 'AS90165' as a valid field name or
> > expression.&quot;
> >
> > AS90165 is the ContractId - the next line in the In table is the
> same
> > ContractId with a new memo field.  Any thoughts on this problem?  Is
> it
> > a string issue? Do I need quotes somewhere?
> >
> > Thanks for taking the time to work this out with me
> >
> > Terri Jarus
> > Vice President, Contract Services
> > jarus at amerinet-gpo.com
> > 314-542-1902
> >
> > &gt;&gt;&gt; andy at minstersystems.co.uk 2/7/2005 6:44:08
AM
> &gt;&gt;&gt;
> >
> > Here's a function to update the table Terri. It assumes that you've
> > already
> > created the output table with the appropriate field names. You also
> > need to
> > add error handling.
> >
> > =====================================
> > Function Terri()
> >
> > Dim db As Database
> > Dim rstIn As Recordset
> > Dim rstOut As Recordset
> >
> > Set db = CurrentDb
> > Set rstIn = db.OpenRecordset(&quot;tblIn&quot;, dbOpenTable,
> dbReadOnly)
> > Set rstOut = db.OpenRecordset(&quot;tblOut&quot;,
dbOpenDynaset)
> > With rstIn
> > Do While Not .EOF
> > rstOut.FindFirst &quot;ContractId=&quot; &amp;
!ContractId
> > If rstOut.NoMatch Then
> > rstOut.AddNew
> > rstOut!ContractId = !ContractId
> > Else
> > rstOut.Edit
> > End If
> > rstOut(!Name) = !Text
> > rstOut.Update
> > ..MoveNext
> > Loop
> > End With
> >
> >
> > rstIn.Close: Set rstIn = Nothing
> > rstOut.Close: Set rstOut = Nothing
> > Set db = Nothing
> >
> >
> > End Function
> > ==========================
> >
> >
> > --
> > Andy Lacey
> > http://www.minstersystems.co.uk
> >
> >
> >
> > --------- Original Message --------
> > From: Access Developers discussion and problem solving
> > &lt;accessd at databaseadvisors.com&gt;
> > To: accessd at databaseadvisors.com
> &lt;accessd at databaseadvisors.com&gt;
> > Subject: RE: [AccessD] Help with Code
> > Date: 07/02/05 12:32
> >
> > &gt;
> > &gt; Not sure I fully understand.  I've set up the crosstab query
> which
> > now
> > &gt; has the proper field headings - but how do I get the memo
> fields
> > &gt; associated with the correct fields/headings?
> > &gt;
> > &gt; Terri Jarus
> > &gt; Vice President, Contract Services
> > &gt; jarus at amerinet-gpo.com
> > &gt; 314-542-1902
> > &gt;
> > &gt; &amp;gt;&amp;gt;&amp;gt;
andy at minstersystems.co.uk 2/7/2005
> 1:30:16
> AM
> > &amp;gt;&amp;gt;&amp;gt;
> > &gt;
> > &gt; Terri
> > &gt; Borge's answer sounds right to me, but if that doesn't work
for
> you
> > I
> > &gt; could
> > &gt; write you some DAO code if you want. It'll be difficult
though
> for
> > me
> > &gt; to fit
> > &gt; it in so please only ask if Borge's approach doesn't suit.
> > &gt;
> > &gt; -- Andy Lacey
> > &gt; http://www.minstersystems.co.uk
> > &gt;
> > &gt; &amp;gt; -----Original Message-----
> > &gt; &amp;gt; From: accessd-bounces at databaseadvisors.com
> > &gt; &amp;gt; [mailto:accessd-bounces at databaseadvisors.com]
On Behalf
> Of
> > &gt; &amp;gt; Borge Hansen
> > &gt; &amp;gt; Sent: 07 February 2005 01:47
> > &gt; &amp;gt; To: Access Developers discussion and problem
solving
> > &gt; &amp;gt; Subject: Re: [AccessD] Help with Code
> > &gt; &amp;gt;
> > &gt; &amp;gt;
> > &gt; &amp;gt; Terri,
> > &gt; &amp;gt; This is what I would try to do:
> > &gt; &amp;gt;
> > &gt; &amp;gt; 1. Create the crosstab you need withouth the
memo field
> and
> > &gt; &amp;gt; save as Query 2. Create another query based on
1. and
> with
> > &gt; &amp;gt; the memo field added
> > &gt; &amp;gt;
> > &gt; &amp;gt; Regards,
> > &gt; &amp;gt; Borge
> > &gt; &amp;gt;
> > &gt; &amp;gt; ----- Original Message -----
> > &gt; &amp;gt; From: &amp;quot;Terri Jarus&amp;quot;
> &amp;lt;jarus at amerinet-gpo.com&amp;gt;
> > &gt; &amp;gt; To:
&amp;lt;accessd at databaseadvisors.com&amp;gt;
> > &gt; &amp;gt; Sent: Monday, February 07, 2005 6:41 AM
> > &gt; &amp;gt; Subject: [AccessD] Help with Code
> > &gt; &amp;gt;
> > &gt; &amp;gt;
> > &gt; &amp;gt; &amp;gt; SECOND ATTEMPT (without attached
sample file)
> > &gt; &amp;gt; &amp;gt; I apologize for not knowing how to
do this on
> my
> own and
> > appreciate
> > &gt;
> > &gt; &amp;gt; &amp;gt; any help I can get.  I have a
table of data -
> one
> field
> > &gt; &amp;gt; named &amp;quot;Name&amp;quot;,
> > &gt; &amp;gt; &amp;gt; a second field named
&amp;quot;Text&amp;quot;
> and a
> third field
> > named
> > &amp;quot;ContractID&amp;quot;.
> > &gt; &amp;gt; &amp;gt;
> > &gt; &amp;gt; &amp;gt; The TEXT field is a memo field. 
What I
> really
> wanted to do
> > was
> > to
> > &gt;
> > &gt; &amp;gt; &amp;gt; create a crosstab that has the
Contract ID as
> a
> row, the
> > Name as
> > a
> > &gt;
> > &gt; &amp;gt; &amp;gt; Column Heading and the TEXT
appearing under
> the
> > appropriate
> > name.
> > &gt; &amp;gt; &amp;gt; However, crosstab won't work with
a memo
> field.
> > &gt; &amp;gt; &amp;gt;
> > &gt; &amp;gt; &amp;gt; The data under NAME is confined to
23
> distinctive
> items.
> > &gt; &amp;gt; For example,
> > &gt; &amp;gt; &amp;gt; one would be ACCESS CRITERIA, one
would be
> CONTRACT
> > &gt; &amp;gt; BENEFITS, etc.  So
> > &gt; &amp;gt; &amp;gt; I thought to create a new table
with each of
> the
> NAME
> > items
> > &gt; &amp;gt; as a field
> > &gt; &amp;gt; &amp;gt; heading. Then writing a row for
each Contract
> ID
> (another
> > field
> > &gt; &amp;gt; &amp;gt; heading) and the assigned TEXT to
the
> appropriate
> NAME.
> > Hope
> > that
> > &gt;
> > &gt; &amp;gt; &amp;gt; makes sense. I just don't know how
to write
> the
> code to do
> > this.
> > &gt; &amp;gt; &amp;gt;
> > &gt; &amp;gt; &amp;gt; Small sample data file can be sent
on request.
>  As
> is
> > &gt; &amp;gt; always the case,
> > &gt; &amp;gt; &amp;gt; I need to get this going ASAP. 
Any help or
> pointing to
> > something
> > &gt; &amp;gt; &amp;gt; similar would be greatly
appreciated.
> > &gt; &amp;gt; &amp;gt;
> > &gt; &amp;gt; &amp;gt; You guys rock!
> > &gt; &amp;gt; &amp;gt;
> > &gt; &amp;gt; &amp;gt; Terri Jarus
> > &gt; &amp;gt; &amp;gt; Vice President, Contract Services
> > &gt; &amp;gt; &amp;gt; jarus at amerinet-gpo.com
> > &gt; &amp;gt; &amp;gt; 314-542-1902
> > &gt; &amp;gt; &amp;gt;
> > &gt; &amp;gt; &amp;gt;
> > &gt; &amp;gt;
> > &gt;
> >
> ----------------------------------------------------------------------
> > &gt; &amp;gt; &amp;gt; ----
> > &gt; &amp;gt; -
> > &gt; &amp;gt; &amp;gt; This email and any files
transmitted with it
> are
> > confidential
> > and
> > &gt; &amp;gt; &amp;gt; intended solely for the use of the
individuals
> or
> entities
> > to
> > whom
> > &gt;
> > &gt; &amp;gt; &amp;gt; they are addressed. If you have
received this
> email
> in
> > error
> > please
> > &gt;
> > &gt; &amp;gt; &amp;gt; return it to the sender, and erase
any copies
> thereof.
> > &gt; &amp;gt; Copyright 2005
> > &gt; &amp;gt; &amp;gt; Amerinet 1nc.
> > &gt; &amp;gt; &amp;gt; --
> > &gt; &amp;gt; &amp;gt; AccessD mailing list
> > &gt; &amp;gt; &amp;gt; AccessD at databaseadvisors.com
> > &gt; &amp;gt; &amp;gt;
> http://databaseadvisors.com/mailman/listinfo/accessd
> > &gt; &amp;gt; &amp;gt; Website:
http://www.databaseadvisors.com
> > &gt; &amp;gt; &amp;gt;
> > &gt; &amp;gt;
> > &gt; &amp;gt; --
> > &gt; &amp;gt; AccessD mailing list
> > &gt; &amp;gt; AccessD at databaseadvisors.com
> > &gt; &amp;gt;
http://databaseadvisors.com/mailman/listinfo/accessd
> > &gt; &amp;gt; Website: http://www.databaseadvisors.com
> > &gt; &amp;gt;
> > &gt; &amp;gt;
> > &gt;
> > &gt; --
> > &gt; AccessD mailing list
> > &gt; AccessD at databaseadvisors.com
> > &gt; http://databaseadvisors.com/mailman/listinfo/accessd
> > &gt; Website: http://www.databaseadvisors.com
> > &gt;
> > &gt;
> > &gt;
> >
>
---------------------------------------------------------------------------
> > &gt; This email and any files transmitted with it are
confidential
> and
> > &gt; intended solely for the use of the individuals or entities
to
> whom
> > they
> > &gt; are addressed. If you have received this email in error
please
> > return
> > &gt; it to the sender, and erase any copies thereof.
> > &gt; Copyright 2005 Amerinet 1nc.
> > &gt; --
> > &gt; AccessD mailing list
> > &gt; AccessD at databaseadvisors.com
> > &gt; http://databaseadvisors.com/mailman/listinfo/accessd
> > &gt; Website: http://www.databaseadvisors.com
> > &gt;
> > &gt;
> > &gt;
> > &gt;
> > &gt;
> > &gt;
> >
> > ________________________________________________
> > Message sent using UebiMiau 2.7.2
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
> >
> >
> >
>
> ________________________________________________
> Message sent using UebiMiau 2.7.2
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
>
>

________________________________________________
Message sent using UebiMiau 2.7.2




More information about the AccessD mailing list