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" <schapel[ at ]mvps.org.ns> wrote in message
news:uVZHn4L5GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Mike Green wrote:
>> 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
|