Recordset??

[Guest]

I have 2 tables as follow:

dbo_MM_1040_Tracking Table
ClientID ApptDate Appt Time PostcardSent Tax Year
22 01/05/2006 2:00PM 0 2005
201 02/07/2006 4:00PM 0 2005
512 04/01/2006 10:00AM 0 2005
512 04/03/2005 11:00AM 1 2004


Client Table
Client ID Client Name Address Manager
22 John Smith A Street Tina
201 MaryJones B Avenue Sandy
512 Sally Smith C Blvd Tina

I also use 2 forms frm:Main Menu and frm:BeginEnd

We send reminder postcards to our clients. The Postcard gets printed and on
closing that report it prints the labels for the postcards. On close of the
labels, the user is asked if they want to indicate that postcards were sent.

For my report close event I used to use

DoCmd.RunSQL "update dbo_MM_1040_Tracking set PostCardSent = 1 where
[TaxYear]= Forms![frm:Main Menu]![Current Tax Year]and [ApptDate] Between
Forms![frm:BeginEnd]![BeginDate] and Forms![frm:BeginEnd]![EndDate]

This has worked very well. Now we would like to do batch processing of the
postcards by manager. I have a form frm:manager with a drop down for the
manager name.

I can do the postcards and labels and it all works. I can't get the code
correct to update the PostCardSent field. I want to do my update of one
table based on data (manager) in another table. Do I need to create a
recordset that combines the 2 tables I have never done a recordset before
and I have no clue what to do to get this to work.

Any ideas??

 

[David Lloyd]

I am uncertain of all of your specifics, however, you can do an INNER JOIN
in the UPDATE SQL statement to allow you to update all the PostCardSent
fields for a particular manager. Assuming that the Manager field you are
interested in is in the Client Table, your SQL statement might look
something like the following

"update dbo_MM_1040_Tracking INNER JOIN [Client Table] ON
dbo_MM_1040_Tracking.ClientID=[ClientTable].ClientID set PostCardSent = 1
where [TaxYear]= Forms![frm:Main Menu]![Current Tax Year]and [ApptDate]
Between
Forms![frm:BeginEnd]![BeginDate] and Forms![frm:BeginEnd]![EndDate] AND
[Manager]=Forms![frm:Manager]!MyManagerDropDownName

This assumes also that frm:Manager is open at the time.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have 2 tables as follow:

dbo_MM_1040_Tracking Table
ClientID ApptDate Appt Time PostcardSent Tax Year
22 01/05/2006 2:00PM 0
2005
201 02/07/2006 4:00PM 0
2005
512 04/01/2006 10:00AM 0 2005
512 04/03/2005 11:00AM 1 2004


Client Table
Client ID Client Name Address Manager
22 John Smith A Street Tina
201 MaryJones B Avenue Sandy
512 Sally Smith C Blvd Tina

I also use 2 forms frm:Main Menu and frm:BeginEnd

We send reminder postcards to our clients. The Postcard gets printed and on
closing that report it prints the labels for the postcards. On close of the
labels, the user is asked if they want to indicate that postcards were sent.

For my report close event I used to use

DoCmd.RunSQL "update dbo_MM_1040_Tracking set PostCardSent = 1 where
[TaxYear]= Forms![frm:Main Menu]![Current Tax Year]and [ApptDate] Between
Forms![frm:BeginEnd]![BeginDate] and Forms![frm:BeginEnd]![EndDate]

This has worked very well. Now we would like to do batch processing of the
postcards by manager. I have a form frm:manager with a drop down for the
manager name.

I can do the postcards and labels and it all works. I can't get the code
correct to update the PostCardSent field. I want to do my update of one
table based on data (manager) in another table. Do I need to create a
recordset that combines the 2 tables I have never done a recordset before
and I have no clue what to do to get this to work.

Any ideas??

 

[Guest]

I tried your suggestion and it worked GREAT! Thanks for the help!

I am uncertain of all of your specifics, however, you can do an INNER JOIN
in the UPDATE SQL statement to allow you to update all the PostCardSent
fields for a particular manager. Assuming that the Manager field you are
interested in is in the Client Table, your SQL statement might look
something like the following

"update dbo_MM_1040_Tracking INNER JOIN [Client Table] ON
dbo_MM_1040_Tracking.ClientID=[ClientTable].ClientID set PostCardSent = 1
where [TaxYear]= Forms![frm:Main Menu]![Current Tax Year]and [ApptDate]
Between
Forms![frm:BeginEnd]![BeginDate] and Forms![frm:BeginEnd]![EndDate] AND
[Manager]=Forms![frm:Manager]!MyManagerDropDownName

This assumes also that frm:Manager is open at the time.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have 2 tables as follow:

dbo_MM_1040_Tracking Table
ClientID ApptDate Appt Time PostcardSent Tax Year
22 01/05/2006 2:00PM 0
2005
201 02/07/2006 4:00PM 0
2005
512 04/01/2006 10:00AM 0 2005
512 04/03/2005 11:00AM 1 2004


Client Table
Client ID Client Name Address Manager
22 John Smith A Street Tina
201 MaryJones B Avenue Sandy
512 Sally Smith C Blvd Tina

I also use 2 forms frm:Main Menu and frm:BeginEnd

We send reminder postcards to our clients. The Postcard gets printed and on
closing that report it prints the labels for the postcards. On close of the
labels, the user is asked if they want to indicate that postcards were sent.

For my report close event I used to use

DoCmd.RunSQL "update dbo_MM_1040_Tracking set PostCardSent = 1 where
[TaxYear]= Forms![frm:Main Menu]![Current Tax Year]and [ApptDate] Between
Forms![frm:BeginEnd]![BeginDate] and Forms![frm:BeginEnd]![EndDate]

This has worked very well. Now we would like to do batch processing of the
postcards by manager. I have a form frm:manager with a drop down for the
manager name.

I can do the postcards and labels and it all works. I can't get the code
correct to update the PostCardSent field. I want to do my update of one
table based on data (manager) in another table. Do I need to create a
recordset that combines the 2 tables I have never done a recordset before
and I have no clue what to do to get this to work.

Any ideas??