deleting subform info but not main form

[dhunter]

I have a frm_New_SOW (Mainform) with a sbfrm_New_SOW. User selects a customer(from customer table) on the main form and fills out information on the subform for that customer (this gets put into tbl_ALL_SOW.) Forms are linked by a customer id. Customers can have multiple SOW's.

I would like to be able to close the form without the information being saved to the table. I do have a save & close but users want the option to not save & close.The fields are bound so I have tried running a delete query among other things. I don't want to delete the customer, only the sow for that customer.

Here are some things I have tried, if there some way to improve upon these ideas or a better way to accomplish what I want any help is appreciated!!

Me.sbfrm_New_SOW.Form.Recordset.Delete
'this deletes the record from tbl_SOW but it replaces the first customer in the tbl_customer with the deleted customer. so close!

'Dim strSQL As String
'Dim vMyID
'vMyID = Me.sbfrm_New_SOW.Form![SOW_NUM]
'strSQL = "DELETE * FROM tbl_ALL_SOW WHERE ((Me.txt_ALL_CUST_ID)=" & vMyID & ");"
'this says it can't find the field ALL_CUST_ID (i have double checked that is what it is named)

'Me.sbfrm_New_SOW.SetFocus
'With Me.sbfrm_New_SOW
'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
'End With
'this deletes the record from tbl_ALL_SOW and renames the customer in the cust table with the current deleted customer

'DoCmd.RunSQL "DELETE * FROM tbl_ALL_SOW WHERE ALL_CUST_ID = " & Forms![sbfrm_New_SOW]![SOW_Num] & " ;"
'this says it can't find the sbfrm, but i have checked everywhere, i don't see another name, this is what populates if you start typing me.

DoCmd.Close acForm, "frm_New_SOW"

Thanks!!!

 

[pbaldy]

Well, the error here is due to the "Me"; you just want the field name:

'strSQL = "DELETE * FROM tbl_ALL_SOW WHERE ((Me.txt_ALL_CUST_ID)=" & vMyID & ");"

should be

'strSQL = "DELETE * FROM tbl_ALL_SOW WHERE ALL_CUST_ID=" & vMyID

This one contains incorrect syntax in referring to the subform:

'DoCmd.RunSQL "DELETE * FROM tbl_ALL_SOW WHERE ALL_CUST_ID = " & Forms![sbfrm_New_SOW]![SOW_Num] & " ;"

http://www.mvps.org/access/forms/frm0031.htm

 

[dhunter]

Thanks for looking at it. Still not working. The first customer in the tbl_customers is being replaced with the name of the deleted customer. I need to find out why this is happening.

I used this:
DoCmd.RunSQL "DELETE * FROM tbl_ALL_SOW WHERE SOW_Num = " & Me!sbfrm_New_SOW.Form!SOW_Num & " ;"

Thanks btw that page really helped.

A user is selecting a customer from a combo box on the main form. I have no idea why the id number would change? I have a dlookup on a box on the main form to find the cust id of the customer choosen in the combo box. The subform is linked to this id. Why when the sow is deleted does the whole form get changed to the first id?

I don't know if I am explaining myself correctly so I will give an example:

cust id 130 is the very first cust in the table.
-customer.Cust Id = 130 customer = Nike
The last customer id is 186, Avaya. I choose Avaya in the combo box, then give them an SOW NUM on the subform and continue filling out the form. Then I want to close the form with out saving anything new for Avaya. With this code Avaya now becomes cust id 130, Nike goes away completely and no SOW information is saved.

Help!

 

[dhunter]

I even tried naming the specific fields and not deleting the Cust_Id and it still does it?!?

 

[pbaldy]

What's the full code now? Can you post the db? Running that delete should not affect the displayed record. The DoMenuItem are deprecated, I don't use them so don't know what those particular ones do. Part of why nobody uses them anymore is that you can't tell by looking what they're doing.

 

[dhunter]

I figured out why it was overwriting the first customer. I have the "Data Entry?" on the main form set to No, so that customers do not get duplicated. I could run a delete query to delete the duplicated customer but the new customer gets written to the table automatially so I would have to run a delete query on the save and close also. I think that would confuse the user. So I think its best I leave the Data Entry set to no and figure out a way around this mess.

Maybe I can do a dlookup to save the name of the first cust and then apply that name to the customer field before closing the form?

Thanks for your help!

 

[pbaldy]

Oh wait, it sounds like you're using a bound combo box for the "go to this record" function. That combo should not be bound to anything. The data entry property controls whether the form opens with existing records displayed or just opens blank for new records.

 

[dhunter]

Yes! You're a genius!! I for some reason had an invisible txt field bound to the customer table and I had some code that made whatever the user choose input into that txt field. After I got rid of that all my problems were solved!!! Thanks!!!!!!!!

 

[pbaldy]

LOL! A genius would have picked up on that right away. I needed this Dr Pepper to get my brain moving. Glad we got it sorted out.