By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 855 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

Pulling Data from Sub Form with an Append Query

P: 55
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
Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,365
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

P: 39
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
Expert Mod 10K+
P: 14,534
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

P: 39
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
Expert Mod 10K+
P: 14,534
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

P: 39
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
Expert Mod 10K+
P: 14,534
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

P: 39
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
Expert Mod 10K+
P: 14,534
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

P: 39
I always feel so dumb when I hear these answers :-|
Mar 3 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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.