[AccessD] Help with Code

Andy Lacey andy at minstersystems.co.uk
Mon Feb 7 08:20:40 CST 2005


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)- "The Microsoft Jet Database
> Engine does not recognize 'AS90165' as a valid field name or
> expression."
>
> 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
>
> >>> andy at minstersystems.co.uk 2/7/2005 6:44:08 AM >>>
>
> 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("tblIn", dbOpenTable, dbReadOnly)
> Set rstOut = db.OpenRecordset("tblOut", dbOpenDynaset)
> With rstIn
> Do While Not .EOF
> rstOut.FindFirst "ContractId=" & !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
> <accessd at databaseadvisors.com>
> To: accessd at databaseadvisors.com <accessd at databaseadvisors.com>
> Subject: RE: [AccessD] Help with Code
> Date: 07/02/05 12:32
>
> >
> > Not sure I fully understand.  I've set up the crosstab query which
> now
> > has the proper field headings - but how do I get the memo fields
> > associated with the correct fields/headings?
> >
> > 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 1:30:16
AM
> &gt;&gt;&gt;
> >
> > Terri
> > Borge's answer sounds right to me, but if that doesn't work for you
> I
> > could
> > write you some DAO code if you want. It'll be difficult though for
> me
> > to fit
> > it in so please only ask if Borge's approach doesn't suit.
> >
> > -- Andy Lacey
> > http://www.minstersystems.co.uk
> >
> > &gt; -----Original Message-----
> > &gt; From: accessd-bounces at databaseadvisors.com
> > &gt; [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > &gt; Borge Hansen
> > &gt; Sent: 07 February 2005 01:47
> > &gt; To: Access Developers discussion and problem solving
> > &gt; Subject: Re: [AccessD] Help with Code
> > &gt;
> > &gt;
> > &gt; Terri,
> > &gt; This is what I would try to do:
> > &gt;
> > &gt; 1. Create the crosstab you need withouth the memo field and
> > &gt; save as Query 2. Create another query based on 1. and with
> > &gt; the memo field added
> > &gt;
> > &gt; Regards,
> > &gt; Borge
> > &gt;
> > &gt; ----- Original Message -----
> > &gt; From: &quot;Terri Jarus&quot;
&lt;jarus at amerinet-gpo.com&gt;
> > &gt; To: &lt;accessd at databaseadvisors.com&gt;
> > &gt; Sent: Monday, February 07, 2005 6:41 AM
> > &gt; Subject: [AccessD] Help with Code
> > &gt;
> > &gt;
> > &gt; &gt; SECOND ATTEMPT (without attached sample file)
> > &gt; &gt; I apologize for not knowing how to do this on my
own and
> appreciate
> >
> > &gt; &gt; any help I can get.  I have a table of data - one
field
> > &gt; named &quot;Name&quot;,
> > &gt; &gt; a second field named &quot;Text&quot; and a
third field
> named
> &quot;ContractID&quot;.
> > &gt; &gt;
> > &gt; &gt; The TEXT field is a memo field.  What I really
wanted to do
> was
> to
> >
> > &gt; &gt; create a crosstab that has the Contract ID as a
row, the
> Name as
> a
> >
> > &gt; &gt; Column Heading and the TEXT appearing under the
> appropriate
> name.
> > &gt; &gt; However, crosstab won't work with a memo field.
> > &gt; &gt;
> > &gt; &gt; The data under NAME is confined to 23 distinctive
items.
> > &gt; For example,
> > &gt; &gt; one would be ACCESS CRITERIA, one would be CONTRACT
> > &gt; BENEFITS, etc.  So
> > &gt; &gt; I thought to create a new table with each of the
NAME
> items
> > &gt; as a field
> > &gt; &gt; heading. Then writing a row for each Contract ID
(another
> field
> > &gt; &gt; heading) and the assigned TEXT to the appropriate
NAME.
> Hope
> that
> >
> > &gt; &gt; makes sense. I just don't know how to write the
code to do
> this.
> > &gt; &gt;
> > &gt; &gt; Small sample data file can be sent on request.  As
is
> > &gt; always the case,
> > &gt; &gt; I need to get this going ASAP.  Any help or
pointing to
> something
> > &gt; &gt; similar would be greatly appreciated.
> > &gt; &gt;
> > &gt; &gt; You guys rock!
> > &gt; &gt;
> > &gt; &gt; Terri Jarus
> > &gt; &gt; Vice President, Contract Services
> > &gt; &gt; jarus at amerinet-gpo.com
> > &gt; &gt; 314-542-1902
> > &gt; &gt;
> > &gt; &gt;
> > &gt;
> >
> ----------------------------------------------------------------------
> > &gt; &gt; ----
> > &gt; -
> > &gt; &gt; This email and any files transmitted with it are
> confidential
> and
> > &gt; &gt; intended solely for the use of the individuals or
entities
> to
> whom
> >
> > &gt; &gt; they are addressed. If you have received this email
in
> error
> please
> >
> > &gt; &gt; return it to the sender, and erase any copies
thereof.
> > &gt; Copyright 2005
> > &gt; &gt; Amerinet 1nc.
> > &gt; &gt; --
> > &gt; &gt; AccessD mailing list
> > &gt; &gt; AccessD at databaseadvisors.com
> > &gt; &gt;
http://databaseadvisors.com/mailman/listinfo/accessd
> > &gt; &gt; Website: http://www.databaseadvisors.com
> > &gt; &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;
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
> >
>
---------------------------------------------------------------------------
> > This email and any files transmitted with it are confidential and
> > intended solely for the use of the individuals or entities to whom
> they
> > are addressed. If you have received this email in error please
> return
> > it to the sender, and erase any copies thereof.
> > Copyright 2005 Amerinet 1nc.
> > --
> > 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