[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

When are Access records actually deleted from table?

i added record navigation and operation buttons to my form, through the button wizard.
I had 5 test records in my database table.
I used the delete record button to delete the 5 test records.
Doing a test with the record navigation buttons, it appears that they are deleted since when i use the previous record and next record buttons, nothing shows up.
when i go to the actual table, however, i still see the records.
Also, i have a query on my form that queries records by salesman name.
This query still returns records, when in fact it shouldn't (since i deleted all records).
Again, when i look at at the table, the records are there.  The record navigation buttons just dont see them once i hit delete.
Can anyone explain this and tell me what i need to do differently with the delete record button?
0
GTC-KTX
Asked:
GTC-KTX
  • 9
  • 8
  • 2
  • +2
1 Solution
 
silemoneCommented:
When you drop a table.  Different from Mssql, if you used Truncate, it would delete and remove data from  db...but Access doesn't have Truncate ability and therefore any 'Deleted' info is not truly deleted from my understanding...
0
 
silemoneCommented:
one thing however....Delete shouldn't allow records to show in the DB table...they are there, but they should not show.  Is your query correct?  on button click, write a query that uses the Delete command...your database object will still retain all info from what I understand as i wrote in my earlier post, but it won't show in a table or in queries...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What code do you have for your Delete button?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
peter57rCommented:
I thought we'd already worked out what was happening with your deletes.
0
 
GTC-KTXAuthor Commented:
Hi Guys,
Yes peter, this is esentially what you and i had worked on last week.  I thought that my last thread solved the problem since i believe i had tested it by making sure all phone records were deleted, then verified that i was able to delete the the opportunity table records.  That is when i accepted the solution.  From your post, what i had understood the problem to be is that you can't delete a record if there are any related records in other tables.  What i'm seeing now is that all related tables are blank.  So i'm trying to delete these test records from the main opportunity table that has related tables which are blank.  So i guess i either didn't solve it last time, or misunderstood your answer.  Either way, i'm stuck on this again.

LSM,
my code for the delete button is the embedded macro that the wizard makes.
0
 
GTC-KTXAuthor Commented:
By the way, below is the recordsource for the form since this is what you looked at last time peter.
SELECT keystoneopportunities.BDR_Generated, keystoneopportunities.ID, keystoneopportunities.Opportunity_Title, keystoneopportunities.Customer, keystoneopportunities.Salesman, keystoneopportunities.Commodity, keystoneopportunities.Rating, keystoneopportunities.How_Found, keystoneopportunities.Probability, keystoneopportunities.businessdescription, keystoneopportunities.Comments, keystoneopportunities.Customer_Acct_no, keystoneopportunities.Rate_Class, keystoneopportunities.Demand_KW, keystoneopportunities.Yearly_Usage_KWh, keystoneopportunities.Current_Supplier, keystoneopportunities.Current_Contract_expires_on, keystoneopportunities.Current_Broker, keystoneopportunities.Supplier1_name, keystoneopportunities.Supplier1_term1_duration_months, keystoneopportunities.Supplier1_price_term1_centsperKWh, keystoneopportunities.Our_markup_for_Supplier1_price_term1_mils, keystoneopportunities.Is_our_markup_included_in_Supplier1_price_term1, keystoneopportunities.Supplier1_Price_Term1_expires_on, keystoneopportunities.Supplier1_term2_duration_months, keystoneopportunities.Supplier1_price_term2_centsperKWh, keystoneopportunities.Our_markup_for_Supplier1_price_term2_mils, keystoneopportunities.Is_our_markup_included_in_Supplier1_price_term2, keystoneopportunities.Supplier1_Price_Term2_expires_on, keystoneopportunities.Supplier1_term3_duration_months, keystoneopportunities.Supplier1_price_term3_centsperKWh, keystoneopportunities.Our_markup_for_Supplier1_price_term3_mils, keystoneopportunities.Is_our_markup_included_in_Supplier1_price_term3, keystoneopportunities.Supplier1_Price_Term3_expires_on, keystoneopportunities.Supplier2_name, keystoneopportunities.Supplier2_term1_duration_months, keystoneopportunities.Supplier2_price_term1_centsperKWh, keystoneopportunities.Our_markup_for_Supplier2_price_term1_mils, keystoneopportunities.Is_our_markup_included_in_Supplier2_price_term1, keystoneopportunities.Supplier2_Price_Term1_expires_on, keystoneopportunities.Supplier2_term2_duration_months, keystoneopportunities.Supplier2_price_term2_centsperKWh, keystoneopportunities.Our_markup_for_Supplier2_price_term2_mils, keystoneopportunities.Is_our_markup_included_in_Supplier2_price_term2, keystoneopportunities.Supplier2_Price_Term2_expires_on, keystoneopportunities.Supplier2_term3_duration_months, keystoneopportunities.Supplier2_price_term3_centsperKWh, keystoneopportunities.Our_markup_for_Supplier2_price_term3_mils, keystoneopportunities.Is_our_markup_included_in_Supplier2_price_term3, keystoneopportunities.Supplier2_Price_Term3_expires_on, keystoneopportunities.Supplier3_name, keystoneopportunities.Supplier3_term1_duration_months, keystoneopportunities.Supplier3_price_term1_centsperKWh, keystoneopportunities.Our_markup_for_Supplier3_price_term1_mils, keystoneopportunities.Is_our_markup_included_in_Supplier3_price_term1, keystoneopportunities.Supplier3_Price_Term1_expires_on, keystoneopportunities.Supplier3_term2_duration_months, keystoneopportunities.Supplier3_price_term2_centsperKWh, keystoneopportunities.Our_markup_for_Supplier3_price_term2_mils, keystoneopportunities.Is_our_markup_included_in_Supplier3_price_term2, keystoneopportunities.Supplier3_Price_Term2_expires_on, keystoneopportunities.Supplier3_term3_duration_months, keystoneopportunities.Supplier3_price_term3_centsperKWh, keystoneopportunities.Our_markup_for_Supplier3_price_term3_mils, keystoneopportunities.Is_our_markup_included_in_Supplier3_price_term3, keystoneopportunities.Supplier3_Price_Term3_expires_on, keystoneopportunities.Action_Rqd, keystoneopportunities.Open_Date, keystoneopportunities.Distribution_co, keystoneopportunities.PTC, keystoneopportunities.State, keystoneopportunities.SIC_Code, keystoneopportunities.Gas_LDC, keystoneopportunities.[Gas Supplier], keystoneopportunities.Gas_Acct_no, keystoneopportunities.Supplier1_sentLOA, keystoneopportunities.Supplier1_sentDATA, keystoneopportunities.Supplier1_receivedPRICE, keystoneopportunities.Cust_Signed_LOA, keystoneopportunities.Req_Cust_Data, keystoneopportunities.Rcvd_Data, keystoneopportunities.Supplier2_sentLOA, keystoneopportunities.Supplier2_sentDATA, keystoneopportunities.Supplier2_receivedPRICE, keystoneopportunities.Supplier3_sentLOA, keystoneopportunities.Supplier3_sentDATA, keystoneopportunities.Supplier3_recievedDATA, keystoneopportunities.Current_Contract_expires_on, keystoneopportunities.Gas_Rt_Schedule, keystoneopportunities.Gas_Yearly_Usage_MCF, keystoneopportunities.supplier_chosen, keystoneopportunities.term_chosen, keystoneopportunities.we_signed_customer, keystoneopportunities.est_start_electric, keystoneopportunities.est_end_electric, keystoneopportunities.sales1, keystoneopportunities.sales2, keystoneopportunities.sales3, keystoneopportunities.sales4, keystoneopportunities.percent_to_sales1, keystoneopportunities.percent_to_sales2, keystoneopportunities.percent_to_sales3, keystoneopportunities.percent_to_sales4, keystoneopportunities.percent_to_BDR, keystoneopportunities.sales5, keystoneopportunities.percent_to_sales5, keystoneopportunities.MeterRead_Date, keystoneopportunities.Gas_MeterRead_Date, keystoneopportunities.Est_Mthly_rev, keystoneopportunities.Est_Com_tot, keystoneopportunities.Est_Com_S1, keystoneopportunities.Est_Com_S2, keystoneopportunities.Est_Com_S3, keystoneopportunities.Est_Com_S4, keystoneopportunities.Est_Com_S5, Phone_LOG.ID AS ID_Phone_LOG, Phone_LOG.opportunityID, Phone_LOG.CallDate, Phone_LOG.Call_Direction, Phone_LOG.ConvSummary, keystoneopportunities.DATEmodified, keystoneopportunities.TIMEmodified
FROM keystoneopportunities LEFT JOIN Phone_LOG ON keystoneopportunities.ID = Phone_LOG.opportunityID;

Open in new window

0
 
peter57rCommented:
If you want to delete a keystoneopportunities record, and whenever you do that you ALWAYS want to  automatically delete ALL matching Phone_LOG entries for that record then you can set it up via the relationship.

In the relationship screen you create a relationship between the two tables, and set the Enforce Referential Integrity option.
You can then set the Cascade Deletes option.
This means you do not need to include both tables in your form, you only need the keystoneopportunities table.
If you delete one of those records then you will automatically delete any matching phone-log records.
0
 
GTC-KTXAuthor Commented:
Peter,
was out of town for a few days, so i apologize for not getting back to this very quickly.
I have in fact verified that all of the relationships between tables have referential integrity enforced.  I also have went to each of these 3 tables and deleted all records directly from the table:
KeystoneOpportunities
PhoneLOG
MeetingLOG

There are also 2 other tables for which referential integrity is enforced, but on these i did not delete the records that are in the table:
employees
customers

The cascading delete is set for the phonelog, meetinglog, and keystoneopportunities
however it is not set for employees and customers.

so anyway, starting from scratch with no records in keystoneopportunities, phonelog, or meetinglog i added an opportunity record.  within that record i added several phonelog records and several meetinglog records.  For this test opportunity record, i also picked a salesman and customer (which both have relationships back to their tables mentioned above).

i then use navigation buttons to delete this test opportunity record.  i get a box that pops up telling me i am about to delete 1 record and asks me to confirm i want to do this.  I select yes.  I go to the keystoneopportunity table, hit refresh all and the record is still there.  

It seems like everything is set right as far as the referential integrity that you mentioned.  Could it be something to do with the employee and customer database?  again, i got the message from access saying that it is deleting one record, so dont understand why its still there.  Let me know what you think.  Still struggling with this.

0
 
thenelsonCommented:
>i added record navigation and operation buttons to my form, through the button wizard

To isolate if the problem is with the coding of your delete record button or elsewhere, try deleting the record with the delete record button on the Form View toolbar. Also try deleting the record while selected in the table itself.
0
 
GTC-KTXAuthor Commented:
thenelson,
Thanks for the response.
If i go to that record from the form, the delete button in the records section of the toolbar is greyed out.
I am able to delete directly from the table by highlighting the record and then either hitting the delete button, or by hitting the delete button in the record section of the toolbar.  After i do this, if i go to the form and use the forward and backward record navigation buttons, when i get to where the record was that i deleted, there is a #Deleted written in every part of the form.

The coding for the delete button on the form is directly from the wizard.
0
 
thenelsonCommented:
>there is a #Deleted written in every part of the form.

Records are not actually deleted in Access until the database it compacted. It Instead a flag in set indicating the record is deleted. If the form has a simple record source property, the table does not have a relationship defined with another table and the form is opened or refreshed after the delete, the record will not be displayed or show up in the record count. However if the form's record source references more than one table or the table has a relationship with other tables with maintain referential integrity on and cascade delete off,the table may be forced to display the record with "#Deleted" in the fields bound to that table.

>the delete button in the records section of the toolbar is greyed out.

This means that the form cannot delete the record usually either because of the form's record source or there is a relationship defined between tables. Therefore a delete record button created by the button wizard will also not delete the record.

To determine which way to go to delete the records, post the form's record source and describe if the table is related to others and how.

0
 
GTC-KTXAuthor Commented:
Thanks thenelson,
Attached is a file that shows my table relationships.
All relationships show have referential integrity set except for the DocAttachments table.
MeetingLog and Phone_LOG tables have both cascading update and delete checked.
Cusomers and Employees tables have only cascading update checked (delete is un-checked).
There record source of the form is shown a couple of posts above (04/20/09 01:25 PM, ID: 24187233)
tableRelationships.png
0
 
thenelsonCommented:
So to confirm what you said: the relationship between keystoneopportunities and Phone_LOG has cascading update and delete checked.

Try temporally changing the record source for the form to just keystoneopportunities. See if the delete button works. If it does, try changing it to:
SELECT keystoneopportunities.*, Phone_LOG.* FROM keystoneopportunities LEFT JOIN Phone_LOG ON keystoneopportunities.ID = Phone_LOG.opportunityID;

(keystoneopportunities.*, Phone_LOG.* means use all fields in those tables - a lot more readable than a long list of fields.)

I doubt that this SQL will allow the deletion but if it does, just use it.


Lets try these steps to delete the record.

Put the original query in the form's record source.

Is there a record in Phone_LOG related to the record in keystoneopportunities?  

Close the form.

Delete the record in keystoneopportunities.

Is the related record in Phone_LOG deleted? If no, then the cascade delete is not working. Probably because the relationship is one to many. (Don't delete it yet)

Open the form. If the record shows with "#Deleted" in the fields, close the form, delete the related record in Phone_LOG. Re open the form. If the record is now gone in the form, then you know you can delete the records in both tables so it will not show up in the form. You can do this by coding two delete queries (one for each table) followed by a form request command behind the delete button.

I'm half guessing here on the details of the code:

Private Sub CommanButton_Click()
db.Execute "DELETE keystoneopportunities.* FROM keystoneopportunities Where keystoneopportunities.ID = " & Name_Of_keystoneopportunities_ID_Control_in_Form"
db.Execute "DELETE Phone_LOG.* FROM Phone_LOG Where keystoneopportunities.ID = " & Name_Of_Phone_LOG_ID_Control_in_Form"
Me. Requery
End Sub

Note: For the above code to work, you will need to have text boxes for the two ID fields in the form (they can be hidden).

BTW: I never use cascade update or delete and rarely use maintain referential integrity. I prefer to handle that myself and KNOW what is going on.
0
 
GTC-KTXAuthor Commented:
thenelson,
Trying to work through your solution here.
There first thing i tried to do, which is what  i think you mean, is leave everything after "select" the same in the record source, but after "from" change it to just "keystoneopportunities", deleting "left join" and everthing after.

When i do that, the record seems to delete normally.  I view the table and see it there.  I hit the delete record button, and then when i go to the keystoneopportunities table the record is still there untill i hit the "refresh all" button.  After i do that, the record disappears from the table and #deleted is written in all the table fields where the record was.

when i changed to the code you suggested (below) the delete didn't work:
SELECT keystoneopportunities.*, Phone_LOG.* FROM keystoneopportunities LEFT JOIN Phone_LOG ON keystoneopportunities.ID = Phone_LOG.opportunityID;

>Is the related record in Phone_LOG deleted? If no, then the cascade delete is not working. Probably because the relationship is one to many. (Don't delete it yet)

This DOES work when i follow that procedure of closing adding a phone record which is related to an opportunity record.  Closing the form, then deleting the opportunity record.  When i do that, the phone record is also deleted (says #delete in all fields)

One thing that is confusing once i started looking at the record source code.
There is also a meeting log table as you can see in my post that showed the relationships.
why dont i see any reference to this when i look at the record source for the form?  I see the reference to the phone log table, but not the meeting log table.  Not sure why.  I did not write this code myself, it was generated by access when i added the fields onto my form.

I didn't do your last step yet.  Was getting a little confused.  Based on what i've done so far, what are you thinking?


0
 
thenelsonCommented:
>There first thing i tried to do, which is what  i think you mean, is leave everything after "select" the same in the record source, but after "from" change it to just toneopportunities", deleting "left join" and everthing after.

When i do that, the record seems to delete normally.  I view the table and see it there.  I hit the delete record button, and then when i go to the keystoneopportunities table the record is still there untill i hit the "refresh all" button.  After i do that, the record disappears from the table and #deleted is written in all the table fields where the record was.<

I meant for you to select "keystoneopportunities" in the drop down of the record source property but what you did is essentially the same thing. But since you could delete the record with Phone_LOG removed from the record source, this shows that the inner join (LEFT JOIN) in the query is making the query not updateable so you cannot delete through the form. Try changing  the form's dataset type to "Dynaset (Inconsistant Updates) - If that works, you're done. See my canned discussion on not updateable queries ant the end of this comment.


>when i changed to the code you suggested (below) the delete didn't work:
SELECT keystoneopportunities.*, Phone_LOG.* FROM keystoneopportunities LEFT JOIN Phone_LOG ON keystoneopportunities.ID = Phone_LOG.opportunityID;<

I didn't think it would work as it is essentially the same query as yours- just shorter.


>>Is the related record in Phone_LOG deleted? If no, then the cascade delete is not working. Probably because the relationship is one to many. (Don't delete it yet)

This DOES work when i follow that procedure of closing adding a phone record which is related to an opportunity record.  Closing the form, then deleting the opportunity record.  When i do that, the phone record is also deleted (says #delete in all fields)<

>When i do that, the phone record is also deleted

That is showing the cascade delete is working.

>(says #delete in all fields)

That will happen until the open table (or form) is refreshed.


>One thing that is confusing once i started looking at the record source code.
There is also a meeting log table as you can see in my post that showed the relationships.
why dont i see any reference to this when i look at the record source for the form?  I see the reference to the phone log table, but not the meeting log table.  Not sure why.  I did not write this code myself, it was generated by access when i added the fields onto my form.<

I assume your form is not using the phone log table. If you need to access the data of the phone log table from your form, You would add it to the form's record source property.

>I didn't do your last step yet.  Was getting a little confused.  Based on what i've done so far, what are you thinking?<

Since the underlying query in the form's record source is not allowing the record to be deleted by the form, the code I suggested deletes the record directly from the tables. "The Me.ReQuery" gets rid of the #delete in all fields. Try setting the form's dataset type to "Dynaset (Inconsistant Updates). If that doesn't work, try the code.


One thought, do you need the Phone_LOG table for this form?  When You removed Phone_LOG from the record source, did any of the control's in the form display "#Name?".  If no, then you are probably not using the Phone_LOG table. Try removing it (select keystoneopportunities from the drop down) and see if you get any errors using the form.


You might want to take the online course on Access forms:
http://office.microsoft.com/training/training.aspx?AssetID=RC010162551033


You can change the form's dataset type to "Dynaset (Inconsistant Updates) to make the form updatable in some cases.  If that doesn't work, you need to change the query or use Update or Insert queries to update the data.  Type "When can I update data from a query?" in MSAccess help answer wizard for more information on when queries (datasets) are updatable or check this link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304474#XSLTH4225121123120121120120

0
 
GTC-KTXAuthor Commented:
thenelson,
sorry for the delayed response.  Was out of town on business for the past few days.

I already had the form's dataset type set to "dynaset", so that didn't work.

>I assume your form is not using the phone log table. If you need to access the data of the phone log table from your form, You would add it to the form's record source property

Still confused on this, because there are 2 subforms on this main form.  One is "Phone_LOG" and the other is "MeetingLOG".  Both have identical relationships to the keystoneopportunities table (see my earlier post that had the tableRelationships.png post).
I do a test by adding a record to keystoneopportunities, then add a test phone log record, then a test meeting log record.
I go to the keystoneopportunities table and delete the test record there.  I then go to the Phone_Log and MeetingLog tables and both show #deleted.  so this seems to indicate that the MeetingLog and Phone_Log are properly related to the keystoneopportunities table.
There is no reference to MeetingLog in the recordsource SQL.  It should be there, shouldn't it?


>One thought, do you need the Phone_LOG table for this form?  When You removed Phone_LOG from the record source, did any of the control's in the form display "#Name?".  If no, then you are probably not using the Phone_LOG table. Try removing it (select keystoneopportunities from the drop down) and see if you get any errors using the form.

I am NOT getting "#Name?" in any of the fields on the Phone_Log or MeetingLog subforms.
But how could i not be using the table?  When i add records from the subform, they show up in the table, when i delete a keystone opportunities record, the related phone_log and MeetingLog records are deleted.  Doesn't this indicate that i'm using it and the relationships are working?

attached is the recordsource sql code.  Just posting it again incase it somehow changed since i posted it earlier.  This is the latest.


SELECT keystoneopportunities.BDR_Generated, keystoneopportunities.ID, keystoneopportunities.Opportunity_Title, keystoneopportunities.Customer, keystoneopportunities.Salesman, keystoneopportunities.Commodity, keystoneopportunities.Rating, keystoneopportunities.How_Found, keystoneopportunities.Probability, keystoneopportunities.businessdescription, keystoneopportunities.Comments, keystoneopportunities.Customer_Acct_no, keystoneopportunities.Rate_Class, keystoneopportunities.Demand_KW, keystoneopportunities.Yearly_Usage_KWh, keystoneopportunities.Current_Supplier, keystoneopportunities.Current_Contract_expires_on, keystoneopportunities.Current_Broker, keystoneopportunities.Supplier1_name, keystoneopportunities.Supplier1_term1_duration_months, keystoneopportunities.Supplier1_price_term1_centsperKWh, keystoneopportunities.Our_markup_for_Supplier1_price_term1_mils, keystoneopportunities.Is_our_markup_included_in_Supplier1_price_term1, keystoneopportunities.Supplier1_Price_Term1_expires_on, keystoneopportunities.Supplier1_term2_duration_months, keystoneopportunities.Supplier1_price_term2_centsperKWh, keystoneopportunities.Our_markup_for_Supplier1_price_term2_mils, keystoneopportunities.Is_our_markup_included_in_Supplier1_price_term2, keystoneopportunities.Supplier1_Price_Term2_expires_on, keystoneopportunities.Supplier1_term3_duration_months, keystoneopportunities.Supplier1_price_term3_centsperKWh, keystoneopportunities.Our_markup_for_Supplier1_price_term3_mils, keystoneopportunities.Is_our_markup_included_in_Supplier1_price_term3, keystoneopportunities.Supplier1_Price_Term3_expires_on, keystoneopportunities.Supplier2_name, keystoneopportunities.Supplier2_term1_duration_months, keystoneopportunities.Supplier2_price_term1_centsperKWh, keystoneopportunities.Our_markup_for_Supplier2_price_term1_mils, keystoneopportunities.Is_our_markup_included_in_Supplier2_price_term1, keystoneopportunities.Supplier2_Price_Term1_expires_on, keystoneopportunities.Supplier2_term2_duration_months, keystoneopportunities.Supplier2_price_term2_centsperKWh, keystoneopportunities.Our_markup_for_Supplier2_price_term2_mils, keystoneopportunities.Is_our_markup_included_in_Supplier2_price_term2, keystoneopportunities.Supplier2_Price_Term2_expires_on, keystoneopportunities.Supplier2_term3_duration_months, keystoneopportunities.Supplier2_price_term3_centsperKWh, keystoneopportunities.Our_markup_for_Supplier2_price_term3_mils, keystoneopportunities.Is_our_markup_included_in_Supplier2_price_term3, keystoneopportunities.Supplier2_Price_Term3_expires_on, keystoneopportunities.Supplier3_name, keystoneopportunities.Supplier3_term1_duration_months, keystoneopportunities.Supplier3_price_term1_centsperKWh, keystoneopportunities.Our_markup_for_Supplier3_price_term1_mils, keystoneopportunities.Is_our_markup_included_in_Supplier3_price_term1, keystoneopportunities.Supplier3_Price_Term1_expires_on, keystoneopportunities.Supplier3_term2_duration_months, keystoneopportunities.Supplier3_price_term2_centsperKWh, keystoneopportunities.Our_markup_for_Supplier3_price_term2_mils, keystoneopportunities.Is_our_markup_included_in_Supplier3_price_term2, keystoneopportunities.Supplier3_Price_Term2_expires_on, keystoneopportunities.Supplier3_term3_duration_months, keystoneopportunities.Supplier3_price_term3_centsperKWh, keystoneopportunities.Our_markup_for_Supplier3_price_term3_mils, keystoneopportunities.Is_our_markup_included_in_Supplier3_price_term3, keystoneopportunities.Supplier3_Price_Term3_expires_on, keystoneopportunities.Action_Rqd, keystoneopportunities.Open_Date, keystoneopportunities.Distribution_co, keystoneopportunities.PTC, keystoneopportunities.State, keystoneopportunities.SIC_Code, keystoneopportunities.Gas_LDC, keystoneopportunities.[Gas Supplier], keystoneopportunities.Gas_Acct_no, keystoneopportunities.Supplier1_sentLOA, keystoneopportunities.Supplier1_sentDATA, keystoneopportunities.Supplier1_receivedPRICE, keystoneopportunities.Cust_Signed_LOA, keystoneopportunities.Req_Cust_Data, keystoneopportunities.Rcvd_Data, keystoneopportunities.Supplier2_sentLOA, keystoneopportunities.Supplier2_sentDATA, keystoneopportunities.Supplier2_receivedPRICE, keystoneopportunities.Supplier3_sentLOA, keystoneopportunities.Supplier3_sentDATA, keystoneopportunities.Supplier3_recievedDATA, keystoneopportunities.Current_Contract_expires_on, keystoneopportunities.Gas_Rt_Schedule, keystoneopportunities.Gas_Yearly_Usage_MCF, keystoneopportunities.supplier_chosen, keystoneopportunities.term_chosen, keystoneopportunities.we_signed_customer, keystoneopportunities.est_start_electric, keystoneopportunities.est_end_electric, keystoneopportunities.sales1, keystoneopportunities.sales2, keystoneopportunities.sales3, keystoneopportunities.sales4, keystoneopportunities.percent_to_sales1, keystoneopportunities.percent_to_sales2, keystoneopportunities.percent_to_sales3, keystoneopportunities.percent_to_sales4, keystoneopportunities.percent_to_BDR, keystoneopportunities.sales5, keystoneopportunities.percent_to_sales5, keystoneopportunities.MeterRead_Date, keystoneopportunities.Gas_MeterRead_Date, keystoneopportunities.Est_Mthly_rev, keystoneopportunities.Est_Com_tot, keystoneopportunities.Est_Com_S1, keystoneopportunities.Est_Com_S2, keystoneopportunities.Est_Com_S3, keystoneopportunities.Est_Com_S4, keystoneopportunities.Est_Com_S5, Phone_LOG.ID AS ID_Phone_LOG, Phone_LOG.opportunityID, Phone_LOG.CallDate, Phone_LOG.Call_Direction, Phone_LOG.ConvSummary, keystoneopportunities.DATEmodified, keystoneopportunities.TIMEmodified
FROM keystoneopportunities LEFT JOIN Phone_LOG ON keystoneopportunities.ID = Phone_LOG.opportunityID;

Open in new window

0
 
thenelsonCommented:
Just a quick response before I review your complete message:
>I already had the form's dataset type set to "dynaset", so that didn't work.

"Dynaset (Inconsistant Updates)" is a different setting than "Dynaset". "Dynaset" is the default setting for forms. You need to manually change recordset type to "Dynaset (Inconsistant Updates)" to get it. The three types of recordset types for forms are:
Dynaset                                          - default
Dynaset (Inconsistant Updates)     - allows updating of some non updateable record sources
Snapshot                                        - faster read only
0
 
thenelsonCommented:
>Still confused on this, because there are 2 subforms on this main form.

Subforms have their own record source property so their record source references do not need to and should not be included in the parent (main) form record source.

Try clicking on the dropdown of the parent form's record source and select keystoneopportunities. If everything works right) your done.
0
 
GTC-KTXAuthor Commented:
Ok.....You're a Genius.  I don't understand why that works, but it works.
ALL that i did was what you suggested:

>Try clicking on the dropdown of the parent form's record source and select keystoneopportunities. If everything works right) your done.

Can you explain to me what that did?

Does it get rid of that long SQL recordsource that was previously in there (the one that i had previously posted)?

When will i actually have a SQL statement in the recordsource as opposed to simply selecting "keystoneopportunities"?

When i get the #deleted in the tables until i hit the refresh button, you had said in an earlier post that this was normal, and they wont go away until the DB is compacted.  So am i correct to assume that there is nothing more i need to do in regard to this?  don't need to add anything to the button click event or anything?

I had misunderstood your earlier post and didn't realize that Dynaset and Dynaset (inconsistent updates) were 2 completely different things.  Based on the fact that it appears to be working, should  i still change to Dynaset (inconsistent updates)?
0
 
thenelsonCommented:
>Does it get rid of that long SQL recordsource that was previously in there (the one that i had previously posted)?<
Yes

>When will i actually have a SQL statement in the recordsource as opposed to simply selecting "keystoneopportunities"?<
When you have a form (cr report) that needs to pull in data from many different tables. Many times combo boxes or subforms can take care of this but sometimes you want the martin form to do it. For example, in a medical billing database, I have a form to select claims based on several different criteria pulled from 8 tables. The user can select to see all the claims of patients who were seen by Doctor Smith, between June 1, 2007 and now, who have United Insurance and have a positive balance. As the user makes his selections, the selected claims are displayed in a subform. This form then creates a query that pulls data from 23 tables that becomes the record source for various reports . (Medical billing is extremely complex).

>When i get the #deleted in the tables until i hit the refresh button, you had said in an earlier post that this was normal, and they wont go away until the DB is compacted.  So am i correct to assume that there is nothing more i need to do in regard to this?  don't need to add anything to the button click event or anything?<

Usually the deleted record will just disappear but sometimes Access can't do that and then #deleted will be displayed. Then the #deleted will go away and the deleted record will disappear when you reopen or requery the table, form or report or in single record view, when you move to another record.

Even though the deleted record will disappear, the deleted record is truly not deleted until the database is compacted. This is very common computer behavior. For example, when you delete a file, it is truly not deleted until it is overwritten.

>Based on the fact that it appears to be working, should  i still change to Dynaset (inconsistent updates)?<
No. Save that for sometime when you have a form based on a query that is not updateable. You can change the form's dataset type to "Dynaset (Inconsistant Updates) to make the form updatable in some cases.  If that doesn't work, you need to change the query or use Update or Insert queries to update the data.  Type "When can I update data from a query?" in MSAccess help answer wizard for more information on when queries (datasets) are updatable or check this link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304474#XSLTH4225121123120121120120
 
0
 
GTC-KTXAuthor Commented:
Thanks thenelson,
Exceptional Help on this.  Some of the most thorough and helpful answers i've ever gotten from this site.  Thank you!
0
 
thenelsonCommented:
Glad to help and thank you very much for the points with "A" grade!

Happy Computing!

Nelson
www.thenelson.name
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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