Making a Form save hidden boxes when Append Query runs?

[slenish]

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...

Dim stDocName As String
    Dim stDocDel As String
 
    stDocName = "AppendDelRecord"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    RunCommand acCmdSaveRecord("Text12", "DeletedRecord", "UserDeleted=" & Me.Text12)
 

[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:

dim dbs as dao.Database
dim rsDeletions as dao.Recordset
 
set dbs = Codedb
set rsDeletions = dbs.Openrecordset("name of table",dbopenDynaset,dbseechanges)
rsDeletions.addnew
rsdeletions!Field1 = me!DataForField1
rsDeletions!Field2 = me!DataForField2
 ... etc for all the fields in the table
rsDeletions!UserID = me!txt12  
rsDeletions.update
rsdeletions.close
set rsdeletions = nothing
set dbs = nothing
 
exit sub  ' also should add error handling
 

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

[NeoPa]

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.

[slenish]

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

[NeoPa]

No worries Slenish. It sounds like you have a suitable solution already :)

I've set the Best Answer for you ;)

[slenish]

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!

[NeoPa]

slenish: Whoops!

No big deal. It's done now and took me very little time as I was in here anyway :)