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

Making a Form save hidden boxes when Append Query runs?

100+
P: 283
Hello,

First just want to say im going to try and explain this the best I can and hope i dont confuse people :)


What I have is a form to delete a record. Now what I have set the form up to do is pull up the record and then press a button to delete the record. At the same time when the record is deleted I have an Append Query run that makes a copy of the record being deleted. Now I also have a hidden box on the form that captures the users ID so i know who is deleting a record. My problem is this box is not working.

What im having trouble is getting the form to save the one box along with the information that is being appended. I have been trying a lot of save form command combinations but so far no luck. Im just looking for a VB code that will run the append query and at the same time save the hidden box information to the same table at the same time. Also i have a column set up to save the hidden box info to.

Appreciate the help on this!

here is an example of what i have so far...
Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2.     Dim stDocDel As String
  3.  
  4.     stDocName = "AppendDelRecord"
  5.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  6.     RunCommand acCmdSaveRecord("Text12", "DeletedRecord", "UserDeleted=" & Me.Text12)
  7.  
Jun 29 '10 #1

✓ answered by jimatqsi

I don't know what RunCommand acCmdSaveRecord does, but it seems to me it is your query that should be saving the user ID, not this other command. Your query should have a reference to Forms!formname!Text12 in order to make the append query save the user ID when it saves everything else. But then the query is linked directly to the form and cannot be run except when that form is open. It would be better to do something like this:

Expand|Select|Wrap|Line Numbers
  1. dim dbs as dao.Database
  2. dim rsDeletions as dao.Recordset
  3.  
  4. set dbs = Codedb
  5. set rsDeletions = dbs.Openrecordset("name of table",dbopenDynaset,dbseechanges)
  6. rsDeletions.addnew
  7. rsdeletions!Field1 = me!DataForField1
  8. rsDeletions!Field2 = me!DataForField2
  9.  ... etc for all the fields in the table
  10. rsDeletions!UserID = me!txt12  
  11. rsDeletions.update
  12. rsdeletions.close
  13. set rsdeletions = nothing
  14. set dbs = nothing
  15.  
  16. exit sub  ' also should add error handling
  17.  
And it would be good to choose better names for your data objects. txt12 is not at all descriptive of the content of that object.

Jim




Jim

Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,240
I don't know what RunCommand acCmdSaveRecord does, but it seems to me it is your query that should be saving the user ID, not this other command. Your query should have a reference to Forms!formname!Text12 in order to make the append query save the user ID when it saves everything else. But then the query is linked directly to the form and cannot be run except when that form is open. It would be better to do something like this:

Expand|Select|Wrap|Line Numbers
  1. dim dbs as dao.Database
  2. dim rsDeletions as dao.Recordset
  3.  
  4. set dbs = Codedb
  5. set rsDeletions = dbs.Openrecordset("name of table",dbopenDynaset,dbseechanges)
  6. rsDeletions.addnew
  7. rsdeletions!Field1 = me!DataForField1
  8. rsDeletions!Field2 = me!DataForField2
  9.  ... etc for all the fields in the table
  10. rsDeletions!UserID = me!txt12  
  11. rsDeletions.update
  12. rsdeletions.close
  13. set rsdeletions = nothing
  14. set dbs = nothing
  15.  
  16. exit sub  ' also should add error handling
  17.  
And it would be good to choose better names for your data objects. txt12 is not at all descriptive of the content of that object.

Jim




Jim
Jun 29 '10 #2

NeoPa
Expert Mod 15k+
P: 31,768
That's a good explanation of what you're doing Slenish, but it's a little short on the detail of the query you're trying to run. I guess it may be a parameter query, but I suspect you'd be better off creating some SQL from scratch, or alternatively, using Jim's idea of a Recordset in your VBA code.

If you want to proceed with a predefined QueryDef, post the SQL you have for it in here and we'll see what we can see for you.
Jun 29 '10 #3

100+
P: 283
Hi Jim,

Really appreciate your help with this. What you said to try worked great!! A little longer process in a way, but now i dont need the Query i had made to save the information. Thanks a lot!!

Also i am re-naming the boxes :D i just had not gotten to that yet. When im testing stuff i just leave things as no names till I get the bugs worked out.


NeoPa,
Great to hear from you again :D Been a while. The Query I had made was something called an Append Query which is in the drop down query menu.(I read how to make this on the Microsofts Site)

Basically what it did was save a copy of the record before it was deleted. I made a form that i had an Append Query linked to. So what you would do is there was a drop down box you would select a record, then using the cascading box trick it would fill in all the different boxes with the information, then when you would hit the delete record button the Append Query would then make a copy of the information to another table and then prompt you with a msg box to make sure you wanted to delete the record. It worked great just couldnt get the hidden information that was only on the form to save, but by using what Jim suggested it all works great now!

Really apprecaite the help from you both :D
Jun 29 '10 #4

NeoPa
Expert Mod 15k+
P: 31,768
No worries Slenish. It sounds like you have a suitable solution already :)

I've set the Best Answer for you ;)
Jun 30 '10 #5

100+
P: 283
Whoops!

Thanks NeoPa,
Sorry i forgot to set that as the best answer! In the excitment of getting that to work i forgot, hehe.

Thanks again!
Jul 5 '10 #6

NeoPa
Expert Mod 15k+
P: 31,768
slenish: Whoops!
No big deal. It's done now and took me very little time as I was in here anyway :)
Jul 5 '10 #7

Post your reply

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