delete current record in form

[Davo78]

I have a continuous form "Customers" based on table "Customers" when I click
the command button I have the following actions, open form "Input" and close
form "Customers". (This works fine)
When I click the command button in form "Input" I have the following
actions, close "Input" open "Customers" and delete the current record in
"Customers" table.
The objective is to update "Input" and remove that customer from being
displayed in the continuous form "Customers".
I have tried several methods including:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Is there a way of doing this simply

Thanks in advance
Davo78

 

[Steve Schapel]

Davo,

What's wrong with DoCmd.RunCommand acCmdDeleteRecord? Doesn't it work
for you?

 

[Davo78]

Thanks for the reply Steve,

I get a message "The record cannot be deleted or changed because the
table"Input" includes related records"

Davo

 

[Steve Schapel]

Davo,

That means that the Input table is on the "many" side of a one-to-many
relationship with the Customers table. And you have Referential
Integrity enforced in your setup in the Relationships window. Probably
the simplest approach here will be to go to Tools|Relationships, and
edit the relationship between these two tables, to enable Cascade
Deletes. That means that any time you delete a Customer record, and
related Input records will also be automatically deleted, which should
solve the problem.

 

[Davo78]

Steve
Thanks for the reply it solved the deletion issue but my goal is to keep the
updated info in the "Input" table or a related table.

i.e. Combine "Customer" data and other related data (materials used, cash
collected, etc) in "Input". Once this is done use a command button to close
"Input" and prevent that record from being displayed in the contiuous form.

Davo

 

[Steve Schapel]

Davo,

To be honest, I am not 100% clear what you mean. But I think this is
what you will need to consider... In the Input table, you apparently
have a field for a Customer reference. Maybe this is a CustomerID field
or some such. Which is good, as you want to be able to identify which
Customer each Input record is related to. Now, enforcing Referential
Integrity in the Relationship definition between these two tables, is
expressly for the purpose of not permitting a record to exist in the
Input table if a corresponding Customer does not exist in the Customers
table. That's what it's for. The corollory of this is that you are not
permitted to delete a Customer record if corresponding Input records
exist. Make sense? So, with Referential Integrity enforced, the only
way you can delete a Customer is to also delete corresponding Input
records as well. So, if in fact you want to allow records in the Input
table without a corresponding Customer, you can't have Referential
Integrity between the tables. That means you need to go to the
Relationshipos window, and edit the relationship to remove RI.

Hope that helps.

 

[Davo78]

Steve

Taking in consideration what you are saying I believe that I am on the wrong
track and deleting may not be correct, my objective is as follows.

I recieve my daily work (route) via the internet which I transpose in MS
Exel then transferspreadsheet into Access 2007, that table is "Customers",
which is displayed as a continuous form where I open table "Input" and add in
materials used etc, when I close "Input" and return to table "Customers" I do
not want the completed job displayed. Is there a way to do this.

 

[Steve Schapel]

Davo,

I am really sorry, but I am lost. So I can't really give a good answer
except to ask some more questions.

I understand that you are importing data from Excel. And this data is
going into your Customers table. Is this adding records to existing
records in the Customers table? Or is it replacing the existing records
in the Customers table with the imported ones?

And then there is Input. Is the data going into Input related to a
Customer? Can you explain the relationship between Customers and Input?
Am I right in assuming that there can be more than one record in the
Input table for any record in the Customers table? If so, am I right in
assuming that there is a field in the Input table (maybe it is
CustomerID or some such) that will indicate which Customer record the
Input data is related to?

And then, we have "jobs". What is Jobs? Do you mean the combination of
a Customer record, with the associated Input recordss, constitutes a
Job? If so, do you mean that after entering the Inputs, you want to
move to a new Customer so you can enter their details?

Maybe a good idea would be to give us a list of the fields in these
tables, with some sample data to illustrate what it is all about.

 

[Davo78]

Steve
To explain the basis of my DB. I work as an Installation Technition, Each
customer is an individual installation and may never be attended by me again.
I wish to relate other information such as Cash collected and materials used
to the relevant customer using the form "Input" (record source= Customers
table).
A continuous form "Daily Route" (record source= Customers table) displays
the current customers to attend each day, once I have completed a customers
installation and entered information into form "Input" a command button
returns me to form "Daily Route" at this point I would like to see only the
remaining customers and not the completed customers.
As I stated before I had been looking at this incorrectly and have found
that my structure was wrong where the table "input" was not required and that
I was trying to delete the source record from table "Customers". What I need
to do is to move the completed customer to another table or tag the record so
as not to display it in the table "Daily Route".
I would like to have included a zip file of my DB but unable to find away of
doing it.

The imported records are added to the customer table.
A Job is the individual installation for a customer

Hope that helps to explain
Thank you for your interest

Davo
(e-mail address removed)

 

[Steve Schapel]

Davo,

In terrms of the *data*, how do we know that a customer is a "remaining
customer". If you look at the table, are you able to identify something
that differentiates a "remaining customer" from the others? Maybe
according to whether a particular field does or does not have data
entered? Or maybe by the value in a date field? Or something else? If
so, then you can simply use a query as the Record Source of the Daily
Route form, and use a criteria in this query that will select out the
"remaining customers".

 

[Davo78]

Steve
Every installation is completed only when a completion code is issued by our
dispatch controller, this is added to the form "Input" as the last entry
using a sub form "CompletionCode" (the codes are preset and entered by a
combo drop-down) which only relates to the table "Customers", could a check
box be used, or is it possible to add a new field in table "Customers" and
have a script to add to some data to this new field, (my prefered option) if
so what code would you suggest.

Thanks again
Davo

 

[Steve Schapel]

Davo,

So. let me check I have this right...
There is data entered into a field named CompletionCode, in the
Customers table.
You only want the Daily Route form to show records that do not have a
CompletionCode entered.

If so...
Make a query, and in the Criteria of the CompletionCode field, put like
this:
Is Null

Then enter the name of this query into the Record Source property of the
Daily Route form.

Having said that, there seems to be a confusion somewhere along the
line. You have told me that the Input form *and* the Daily Route form
*and* the CompletionCode subform are *all* based on the Customers table.
This seems wrong.

 

[Davo78]

Steve

Have E-Mailed to mpvs

Davo

Davo,

So. let me check I have this right...
There is data entered into a field named CompletionCode, in the
Customers table.
You only want the Daily Route form to show records that do not have a
CompletionCode entered.

If so...
Make a query, and in the Criteria of the CompletionCode field, put like
this:
Is Null

Then enter the name of this query into the Record Source property of the
Daily Route form.

Having said that, there seems to be a confusion somewhere along the
line. You have told me that the Input form *and* the Daily Route form
*and* the CompletionCode subform are *all* based on the Customers table.
This seems wrong.

 

[Davo78]

Steve

Apologies for not getting back earlier.
I have used your suggestion of "Is Null" in a query and this works fine.

Thanks again for all your help, much appreciated.

Davo

 

[Steve Schapel]

Glad to hear that you have made progress, Davo. Best wishes.