469,365 Members | 1,945 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,365 developers. It's quick & easy.

Pulling Data from Sub Form with an Append Query

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:
Expand|Select|Wrap|Line Numbers
  1. [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.
Feb 23 '07 #1
11 7365
Rabbit
12,516 Expert Mod 8TB
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
Expand|Select|Wrap|Line Numbers
  1. Private Button_Name_Click()
  2. Me.Undo
  3. End Sub
Feb 23 '07 #2
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:

Expand|Select|Wrap|Line Numbers
  1. 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?
Feb 24 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Feb 24 '07 #4
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?
Feb 24 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
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

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL
  2.  
  3.    strSQL = "DELETE * FROM Order WHERE OrderID = " & Me.OrderID
  4.    DoCmd.RunSQL strSQL
  5.  
Mary
Feb 26 '07 #6
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)>. ?
Mar 2 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL
  2.  
  3.    If Not IsNull (DLookup("OrderID","Order","[OrderID]=" & Me.OrderID)) Then
  4.     strSQL = "DELETE * FROM Order WHERE OrderID = " & Me.OrderID
  5.     DoCmd.RunSQL strSQL
  6.    End If
  7.  
  8.  
Mary
Mar 2 '07 #8
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
Mar 3 '07 #9
MMcCarthy
14,534 Expert Mod 8TB
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
Mar 3 '07 #10
I always feel so dumb when I hear these answers :-|
Mar 3 '07 #11
MMcCarthy
14,534 Expert Mod 8TB
I always feel so dumb when I hear these answers :-|
LOL - Don't worry about it. We've all been there.
Mar 3 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Paul | last post: by
7 posts views Thread by colleen1980 | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.