Copy Record 2

[jbento]

All,
I have a book check-out database. I have about 6 fields in this database. I want to be able to copy a record to an archive table, once the check-in date field is filled in. I also want the record that was copied from to automatically wipe out the check-in date field and the field that has the name of the person who checked the book out (studentNameField). Can someone please help me out with this. Thanks once again!!!

Jerome

 

[gol4]

Not knowing the field names It is difficult for me to assist. However I can tell you, you want to create an append query with its selection criteria set to the field on your form that identifys the book and then run an update query using the same criteria. You should be able to create these queries and then use a macro. If this is not enough then list your tables names and the names of the fields in your tables and then we can do the code for you

 

[jbento]

Sorry about that.

Here are the fields that I need copied to the archive table once the "DateIn" field is filled in:

Id
StudentName
IDNumber
Title
DateOut
DateIn
Author_Illustrator
Reading_Level
Category
Condition
LibrarianInitials

Once that is copied, I want the following fields in the main table to automatically erase data in fields:

StudentName
DateOut
DateIn

I hope that helps. Let me know if you need more. Thanks for your assistance.

Jerome

 

[gol4]

this code assumes a lot and is not debugged but you need to do something like this.


Private Sub datein_AfterUpdate()
Dim strsql As String

If Me.datein <> &quot;&quot; Then
strsql = &quot;INSERT INTO archivetbl SELECT mainTBL.* FROM mainTBL where ID = &quot; & Me.ID 'assumes ID is number field
DoCmd.RunSQL strsql
srtsql = &quot;UPDATE mainTBL SET mainTBL.studentname = '', mainTBL.dateout= '',mainTBl.datein = '' where ID = &quot; & Me.ID
DoCmd.RunSQL strsql
End If

End Sub

/////////////////////
me.id is what I assume is the name of the field on your form that keeps track of the bookid
mainTBl is what I refer to as main table you want to provide the proper name, the same for archivetbl. If archivetbl does not already exist you need to create it. just copy main table and paste the structure only

 

[dakota81]

Another way to handle this would to open a recordset object and add the new record that way, use the addNew command, then copy the data directly from the form fields, something like

-----
Dim db As Database, rstArchive As Recordset

Set db = CurrentDb
Set rstArchive = db.OpenRecordset(&quot;ArchiceTable&quot;, dbOpenDynaset)

rstArchive.addNew
rstArchive![Title] = [Title]
rstArchive![Author] = [Author]
rstArchive![Catagory] = [Catagory]
...
rstArchive.Update

[StudentName] = Null
[DateOut] = Null
[DateIn] = Null
-----

It's a bit more to write the copying of each field, but you can better control the data copy. I'm also assuming you're using bound fields to the main table, with the same name as the table field they control. This approach will accomplish the same task, just you can better control and customize the underlying steps that are done automatically by macros.

 

[jbento]

I thank you both for sending the code to help me out, but I'm trying the one that &quot;gol4&quot; sent me, and I'm having problems.

I had to put parenthesis around one part, which is as follows, because I was getting &quot;expected end of statement&quot; error. Let me know if the parenthesis is ok:

mainTBL where (ID) = &quot; & Me.ID

I am also getting an &quot;expected end of statement&quot; error for the following, and I don't know how to resolve it:

strsql = &quot;UPDATE tblClassroombooklist SET tblClassroombooklist.StudentName = &quot;, tblClassroombooklist.DateOut = &quot; tblClassroombooklist.DateIn = &quot;, where ID = &quot; & Me.ID

It highlights the comma after the .StudentName entry, for the error. Can you help me with that error? Thanks again.

Jerome

 

[THoey]

Jerome, I think you need to look into your design more than how do I make this work. It looks like you have one table that handles student information and book information and whether or not the book is checked out and by who. This is not normalized and leads to redundant data and greater risk of problems and inconsistancies.

I suggest you look into three tables:

tblStudent
Id
StudentName

tblBook
IDNumber
Title
Author_Illustrator
Reading_Level
Category
Condition

tblCheckout
Id
IDNumber
DateOut
DateIn
LibrarianInitials

With these three tables, you don't have to worry about an archive, as anything with a valid check in date can be filtered out of your query to not show it.

As MichaelRed says: &quot;There is never time to do it right, but there is always time to do it over...&quot; Terry M. Hoey

Please read the following FAQ to learn how to help me help you...

faq183-874

 

[gol4]

Thanks for catching this Terry, I should have seen the design issue myself and pointed it out, too often I just look at the problem as stated and throw out a solution. I highly recommend you follow Terry's advice and normalize you database.
However I still feel inclined to fix my coding error's

I think the problem is I was trying to send an empty string to the field '' and I should have just set them to null.
In order to alleviate any problems or any potential type errors replace the empty strings with null so it will read

dim strsql as string

If Me.datein <> &quot;&quot; Then
strsql = &quot;INSERT INTO archivetbl SELECT mainTBL.* FROM mainTBL where ID = &quot; & Me.ID 'assumes ID is number field
DoCmd.RunSQL strsql
strsql = &quot;UPDATE mainTBL SET mainTBL.studentname = null, mainTBL.dateout= null,mainTBl.datein = null where ID = &quot; & Me.ID
DoCmd.RunSQL strsql
End If

Sorry,

 

[jbento]

gol4,
I am still having problems with a certain part of the code. The part that is giving me an error message is as follows:

mainTBL where ID = &quot; & Me.ID

I changed mainTBL to my table name. I even changed the &quot; to null, but I still get the error message &quot;Compile error: Expected: end of statement&quot;. Then it highlights &quot;ID&quot; as the problem area. I even put a End If statement after that part of the code, thinking that might solve it, but it didn't. Could you please help me with that error &quot;gol4.

I thought about doing the design stated by &quot;th3856&quot;, but I want to get this working first, and if I need to do the design at a later date, I will implement that way. I thank you both for your help and assistance.

Jerome

 

[jbento]

dakota81,
I used your code and it worked. I want to thank each and everyone of you who assisted me. You are all very wonderful individuals, and I THANK GOD for allowing me to interact with each of you. I have referred so many people to this site. Thanks again, and GOD bless you all.

Jerome