• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

Access2010 Form Delete Issue

I have a continuous form that uses the following SQL statement for it's record source.

strSQL = "SELECT Customer.Customer, RehabJobs.Address, Rehabjobs.CompletedDate, RehabJobs.RehabJobID, RehabJobs.CustomerType, Customer.CustAbrv, RehabJobs.Zip," _
        & " RehabJobs.JobName, RehabJobs.Lockbox, RehabJobs.RequestDate, RehabJobs.DueDate, RehabJobs.PMID, RehabJobs.JobDesc, RehabJobs.Amount," _
        & " RehabJobs.JobStatus, RehabJobs.ApprovalDate, RehabJobs.ScheduledDate, RehabJobs.ETCDate, RehabJobs.NewETCDate, RehabJobs.Notes, RehabJobs.NewETCReason, RehabJobs.CustomerID" _
        & " FROM Customer RIGHT JOIN RehabJobs ON Customer.CustomerID = RehabJobs.CustomerID" _
        & " Where ((Rehabjobs.closed) <> -1) AND ((Rehabjobs.JobStatus)<>11)" _
        & " ORDER BY Customer.Customer, RehabJobs.JobName, RehabJobs.RehabJobID;"

And when i use the forms record selector to delete a record. The form deletes both the customer record from the customer table and the rehabjob record from the rehabjobs table.

I just want to delete the record from the rehabjobs table.

Why is this.
Normally i control all deletions with VBA, but i was trying to speed things up a little with the development of the DB and thought i would take advantage of the built in functions.

Thanks Guys..

Jason
0
jb702
Asked:
jb702
  • 2
1 Solution
 
rshqCommented:
Hi
  Maybe you use a relation between Rehabjobs  and Customer tables.
  In this relation "Enforce Referential Integrity" and "Cascade Delete Related Records"    probably checked .
0
 
jb702Author Commented:
at first i didn't have any relationship at all. after this happened i went in and created a one to many, but am not able to use the enforce referential integrity because i already have lots of data.....and the message comes up and states i must have equal amount fields.

If i use a sQL statement that doesn't right join the customer table (the only reason i'm doing this is to populate the customer field with the customer name instead of the customerID) but embed a dlookup for the customer name i wouldn't  have to include the customer table in the sql syntax. Would this slow down performance.
0
 
Dale FyeCommented:
"Would this slow down performance. "

Generally, yes.  Domain functions (DLOOKUP, DMAX, DMIN) are notoriously slow.

My default form template (the one that every form in my application starts out looking like) has Allow Deletes = No.  If I want to allow my users to delete a record, I create a Delete button and put that, along with other buttons in the forms footer.  In that command buttons Click event, I either build the DELETE SQL statement on the fly, or execute a stored delete by passing the PK value associated with the record I want to delete.
0
 
jb702Author Commented:
Thanks...i figured as much..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now