Pulling Data from Sub Form with an Append Query

[kabradley]

Hello Everyone,
So, thanks to nico's help I was finally able to 'finish' our companies access database. For the past week or so though,I have been designing forms that contain a subform and an option group so that whenever a certain button on the option group is pressed the correct subform source object property is changed to display the correct form. For instance, if they click "add new investment" the subform's source object is now changed to newInvestment. All of that is working flawlessly. Originally I had each form set up as a bound form to the appropriate table. What I was finding though was that if a user entered just one piece of data it created a new record in that table. A terrible thing! Or, at least it is terrible for us because it is not at all forgiving of input mistakes aka, a person relizes half-way through entering information they don't need that info entered etc. I have a submit button and a cancel button on the main form. The submit button 'resets' each object after it is depressed that purely for asthetic purposes. After I learned of the problems a bound form could create I made them all unbound and tried to write an INSERT INTO SQL statement that was would run when the submit button was pressed. Although, from what I have read on the internet and the examples I have seen, everything was layed out fine, but it would never run. I then tried to make an append query in access and see if I could pull the actual data from the subform. I planned to just OpenQuery the append query when the submit button was pushed. Upon creating the query, it could never "see" that anything was choosen in the subform. The layout I used for the criteria was:

[Forms]![Investment Manager]![subfrmVendor]![LPName]

After running the query nothing was returned. Odd I thought because I had used the exact same code before for a parameter except it was grabbing it from a traditional form instead of a subform.

I guess my real question / real problem is how to go about making the subform update the table that it is related to. Should I infact be using an insert into statement / append query, or is there another way to go about things. Any light on this matter would be greatly appreciated.

[Rabbit]

You can keep the bound forms but have an Undo button for those times when they realize they don't need that info. The code is simple too, in the On Click event of the button, just have

Private Button_Name_Click()
Me.Undo
End Sub

[bdockery]

This looks cool and I tried it out. I've noticed that it doesn't undo the subform, though.

I tried the following code also:

Forms!MainForm!SubformObject.Undo

but it didn't work.


It seems like by the time the focus goes to the subform it has actually posted the records to the main form. This might just be specific to my form, because my subform is linked to the parent form. The subform is product line-items associated with the parent order form.Think thats it?

[MMcCarthy]

This looks cool and I tried it out. I've noticed that it doesn't undo the subform, though.

I tried the following code also:

Forms!MainForm!SubformObject.Undo

but it didn't work.


It seems like by the time the focus goes to the subform it has actually posted the records to the main form. This might just be specific to my form, because my subform is linked to the parent form. The subform is product line-items associated with the parent order form.Think thats it?

Click on the relationship between these tables in the relationships window.
Make sure the referential integrity and cascade update/delete options are ticked. Now if the main form record is undone the subform record should delete on cascade.

Mary

[bdockery]

Thanks for the tip :)


I gave it a shot, but it didn't work.


I did a little expirimenting and found that at the exact moment the first field is changed in the "OrderDetails" subform, the main form's "Order" record is created and saved in the table. Therefor the undo doesn't work because it's already been saved.

Is there a similiar command that would delete the record in the parent table, or a way to make it so no records are saved until both the parent and child forms are filled out completely and the save button is pushed?

[MMcCarthy]

Thanks for the tip :)


I gave it a shot, but it didn't work.


I did a little expirimenting and found that at the exact moment the first field is changed in the "OrderDetails" subform, the main form's "Order" record is created and saved in the table. Therefor the undo doesn't work because it's already been saved.

Is there a similiar command that would delete the record in the parent table, or a way to make it so no records are saved until both the parent and child forms are filled out completely and the save button is pushed?

You could set up a query to delete current record

Dim strSQL
 
   strSQL = "DELETE * FROM Order WHERE OrderID = " & Me.OrderID
   DoCmd.RunSQL strSQL
 

Mary

[bdockery]

I didn't realize you had updated this!! Sorry I didn't reply.


Anyway - it works great! This also teaches me a lot, since I never realized about the docmd.RunSQL - very powerful!


Now I just need it to only try and delete if there is actually a record created, this way it doesn't pop up an error.

Is there a way to use an if statement and say

"Only do the following code if there is a record in <table> that contains <me.orderID (foreign key)>. ?

[MMcCarthy]

Try this ...

Dim strSQL
 
   If Not IsNull (DLookup("OrderID","Order","[OrderID]=" & Me.OrderID)) Then
    strSQL = "DELETE * FROM Order WHERE OrderID = " & Me.OrderID
    DoCmd.RunSQL strSQL
   End If
 
 

Mary

[bdockery]

Sweet. Works. Thanks McCarthy


So why is it if NOT isnull() rather than: if isnull() ??


It seems like the if statement should process if the dlookup evaluates to null


/boggled

[MMcCarthy]

Sweet. Works. Thanks McCarthy


So why is it if NOT isnull() rather than: if isnull() ??


It seems like the if statement should process if the dlookup evaluates to null


/boggled

No, the opposite is true.

If there is no record matching the current OrderID (Dlookup is null) then delete can't happen it only proceeds when the Dlookup is not null.

Mary

[bdockery]

I always feel so dumb when I hear these answers :-|

[MMcCarthy]

I always feel so dumb when I hear these answers :-|

LOL - Don't worry about it. We've all been there.