[AccessD] Help with Code

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
>
> >>> andy at minstersystems.co.uk 2/7/2005 1:30:16 AM >>>
>
> 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
>
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Borge Hansen
> > Sent: 07 February 2005 01:47
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Help with Code
> >
> >
> > Terri,
> > This is what I would try to do:
> >
> > 1. Create the crosstab you need withouth the memo field and
> > save as Query 2. Create another query based on 1. and with
> > the memo field added
> >
> > Regards,
> > Borge
> >
> > ----- Original Message -----
> > From: "Terri Jarus" <jarus at amerinet-gpo.com>
> > To: <accessd at databaseadvisors.com>
> > Sent: Monday, February 07, 2005 6:41 AM
> > Subject: [AccessD] Help with Code
> >
> >
> > > SECOND ATTEMPT (without attached sample file)
> > > I apologize for not knowing how to do this on my own and
appreciate
>
> > > any help I can get.  I have a table of data - one field
> > named "Name",
> > > a second field named "Text" and a third field named
"ContractID".
> > >
> > > The TEXT field is a memo field.  What I really wanted to do was
to
>
> > > create a crosstab that has the Contract ID as a row, the Name as
a
>
> > > Column Heading and the TEXT appearing under the appropriate
name.
> > > However, crosstab won't work with a memo field.
> > >
> > > The data under NAME is confined to 23 distinctive items.
> > For example,
> > > one would be ACCESS CRITERIA, one would be CONTRACT
> > BENEFITS, etc.  So
> > > I thought to create a new table with each of the NAME items
> > as a field
> > > heading. Then writing a row for each Contract ID (another field
> > > heading) and the assigned TEXT to the appropriate NAME.  Hope
that
>
> > > makes sense. I just don't know how to write the code to do this.
> > >
> > > Small sample data file can be sent on request.  As is
> > always the case,
> > > I need to get this going ASAP.  Any help or pointing to
something
> > > similar would be greatly appreciated.
> > >
> > > You guys rock!
> > >
> > > Terri Jarus
> > > Vice President, Contract Services
> > > jarus at amerinet-gpo.com
> > > 314-542-1902
> > >
> > >
> >
> ----------------------------------------------------------------------
> > > ----
> > -
> > > 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
> > >
> >
> > --
> > 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
>
>
>
---------------------------------------------------------------------------
> 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