Update recordset from filtered form

[Guest]

I have a table called Renewal in my database. I am trying to load a form
called fmRenewal which will load with filtered data from a query. I would
like to read each record on this form and then compare 2 text box fields,
(1st is Me!renewalDate, 2nd is Me!meternumber) with 2 fields in the renewal
table, which have the same name as the text boxes on the form.

If the 2 fields on the form match any record in the table then do not write
this record to the table renewal. If the 2 fields differ to any record in
the table then write the record. I know how to read all the records on the
form. It’s the comparing of the records to another recordset which I cannot
implement.

Thanks

Ronnie

 

[Jeff Boyce]

Ronnie

Another approach would be to let the table decide ... <g>.

If you add an Index(no duplicates) to the table, covering the two fields,
when you try to write something that already exists, the table rejects it.

 

[Guest]

Jeff

Thanks for the reply. I read your comment on creating an index for the
table, which is a sensible solution. I have however given you misleading
information so I cannot use an index as suggested. The information I have
given you which is incorrect is that the renewal table has a renewal date but
not a meter number. See table below.

Renewal Table

Customerid Number
RenewalStatus Text
RenewalDate Date

I have another 2 tables in my database which are below

Telesales

TeleSalesID AutoNumber
CustomerID Number
EmployeeID Number
DateSigned Date
Telecomms Yes/No
Notes Memo

Telesales Details

TelesalesID Number
ServicesID Number
Eac Number
RenewalDate Date
Meternumber Text

What I am trying to do is to pull all records from Telesales Details table
where renewal date is 21 days away, and then write only the customerID, and
renewal date and renewal status to the renewal table. This information can
only be written to the renewal table if this information is not already
contained in the renewal table. I thought I could do this with a filtered
form where data comes from the telesales details table but I now know this is
inadequate. This looks like some complex VB coding but I need some guidance
as to where to start then I could build on any information provided.

Thanks

Ronnie

 

[Jeff Boyce]

Could you post the SQL statement you are using now to try to append the rows
to your "Renewal" table?

Is there a chance you don't need a "Renewal table" at all, and could make do
with a dynamic query?