deleting part of the data

[Mike Green]

Hi All
I am looking for some ideas how to do this. I have a form based on a query
that shows customer and Plot. There is a relationship between the Customer
and Plot so that the plot is selected from the customer table. In the
Query view I can delete the Plot ID so tha the customer has no plot,
however, I need to be able to remove the Plot but keep the customer
hopefully with code. I have tried to just use a button and event procedure
to just set the plot ID to Null but this does not work! Am I going about
this the wrong way? Does any one have an example database that shows how to
link records and then unlink them?
Thanks in advance.

Mike

 

[Steve Schapel]

Mike,

I'm guessing that you have a Customers table and a Plots table? And I
would have assumed that there is a one-to-many relationship between
Customers and Plots, i.e. any given Customer can have more than one
Plot? But from your deswcription, it sounds like it is the other way
around, i.e there is a one-to-many relationship between Plots and
Customers, i.e. any given Plot can have more than one Customer, while a
Customer can only have one plot? So the Plot ID in the Customers table
identifies which Plot the Customer is associated with? How am I doing
so far? So, can you post back with the relevant fields of the relevant
tables, and also the SQL view of the Query? I would have thought that
you could set the value of the Plot ID to Null, so I am curious to the
meaning of "this does not work"... what happens? Plot ID field is not
cleared? Error message? Something else? Thanks.

 

[Mike Green]

Yes you are correct. I have tables for Customer and Plots and Yes there is
a one to many relationship, one customer can have may plots.
I am trying to delete the plot Id from the Customers table via a form that
is based on a query. The form displays all the relevant information about
the customer and the plot that they have. I need the ability to change the
plot that the customer has and to remove the plot from the customer but keep
the customer information for later use.
I was trying to take the simple approach and just add a button to the form
that deletes the plot ID. However I get an error message that either says
the data must be saved first or you cannot delete this record as it is part
of a relationship.

The SQl for the query is:-

SELECT Tbl_MainData.*, Tbl_Plot.SiteID, Tbl_Plot.SiteNo, Tbl_Plot.SiteType,
Tbl_Plot.CustomerName, [SiteID] & "- " & [SiteNo] AS PlotName
FROM Tbl_Plot RIGHT JOIN Tbl_MainData ON Tbl_Plot.Ref_ID =
Tbl_MainData.PlotID;

The event proceedure simply tries to save the secord and then set the plotID
to Null.

If I open view just the query I can delete the plot ID with no problems, it
just seems that when the form is running it all goes wrong. (I am VERY
rusty on Access I havn't used it properly like this for about 3 years so
please bere with me on some of the things I may be doing wrong)

Thanks for the quick reply

Mike

 

[Steve Schapel]

Mike,

Let's deal with first things first... If "one customer can have may
plots", then it is not correct for there to be a Plot ID field in the
Customer table. It would probably be necessary instead to have a
CustomerID field in the Plots table.

 

[Mike Green]

Steve
Thanks for the replys but I've now got myself into a complete pickle! I
must admit I have never had to deal with a DB that manages a 'fixed' number
of records that can be assigned and unasigned i.e Plots So I must admit I am
a little stumped let me try and explain:-
Plots now has a Customer ID field and will look up from the customers table.
But I need to see all the customers details and which plots that they rent,
however how do I now get the customers details first and then assign them a
plot (select list i suppose). At the moment I have to look for the plot
first and then the customer don't I? Do you know of an example I could look
at to try and figure this out?
Thanks

Mike

Mike,

Let's deal with first things first... If "one customer can have may
plots", then it is not correct for there to be a Plot ID field in the
Customer table. It would probably be necessary instead to have a
CustomerID field in the Plots table.

--
Steve Schapel, Microsoft Access MVP

Yes you are correct. I have tables for Customer and Plots and Yes there
is a one to many relationship, one customer can have may plots.
I am trying to delete the plot Id from the Customers table via a form
that is based on a query. The form displays all the relevant information
about the customer and the plot that they have. I need the ability to
change the plot that the customer has and to remove the plot from the
customer but keep the customer information for later use.
I was trying to take the simple approach and just add a button to the
form that deletes the plot ID. However I get an error message that
either says the data must be saved first or you cannot delete this record
as it is part of a relationship.

The SQl for the query is:-

SELECT Tbl_MainData.*, Tbl_Plot.SiteID, Tbl_Plot.SiteNo,
Tbl_Plot.SiteType, Tbl_Plot.CustomerName, [SiteID] & "- " & [SiteNo] AS
PlotName
FROM Tbl_Plot RIGHT JOIN Tbl_MainData ON Tbl_Plot.Ref_ID =
Tbl_MainData.PlotID;

The event proceedure simply tries to save the secord and then set the
plotID to Null.

If I open view just the query I can delete the plot ID with no problems,
it just seems that when the form is running it all goes wrong. (I am
VERY rusty on Access I havn't used it properly like this for about 3
years so please bere with me on some of the things I may be doing wrong)

 

[Steve Schapel]

Mike,

There are a number of considerations and approaches here.

To enter the Customer for a given Plot, you would use a combobox on the
Plots form, with the Row Source of the combobox set to the Customers
table. This combobox can be multi-columns, in other words you can set
it up so you can see all the customer-related information you need from
the user-operational point of view.

If you mean you wnat to be able to see the Plots that each customer
already has, at the time when you are assigning the Customer to a Plot,
then for the Row Source of the combobox you could use a Query in which
you concatenate the PlotIDs of all that Customer's existing Plots. Hope
that makes sense. If this is the caswe, let me know if you need help
with the concatenation, as it will involve writing a User-Defined Function.

And then, I guess you will have a Customer form. On the Customer form,
a common scenario here would be to have a continuous view subform, based
on the Plots table, which shows the plot details of all plots assigned
to the currently displayed customer.

So, I know this is not a complete answer, but then I am not really clear
on your complete requirements. So just get back with whatever aspect of
the above you need further help with.