Copying data automatically from a form 2

[JLSigman]

OK, let me see if I can explain this well...

I have a database that is used to keep track of all the Air Quality permits here. They would like a history table, so that when someone checks out a file, they have a list of everyone who's ever checked out that particular file. I'm thinking to make one History table, and having the data inserted into it when the check out form is used.

But, I can't seem to make it put the data into the table. I would like this to be as easy and painless (and hidden, preferably) as possible, since neither person who uses this database is very computer literate. If you have more questions or need more clarifiation, let me know and I'll try to explain better.

Thanks,

Jennifer Sigman

 

[jebry]

Hi!

You can try something like this in the Before Update event procedure of the form:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblHistory", dbOpenDynaset)

With rst
.AddNew
!Employee = txtEmployee
!Date = Date()
!File = txtFile
Etc. (I don't know all of the data you want to keep)
.Update
End With

Set rst = Nothing


This will automatically add a new record to the table tblHistory. Of course you will need to use whatever name you have for your own tables, fields and text boxes.

hth
Jeff Bridgham

 

[lakers8175]

1.Create the history table with the fields from the form that you want to see history on.

2.Create an Append query that will append all the fields from the form to the History table.

3.Add the OpenQuery (New Append Query) to the end of the procedure or macro that is getting the data for their original search.

4. As far as finding out who is running it, you can create a field in the table for the user name. In the user name field, you can either put the Network ID name, or the access security log in name, if you using Access security.

 

[JLSigman]

Thanks, both of y'all, for the quick help. I really appreciate it.

 

[JLSigman]

*sigh* Me again.

Lakers, I've tried that suggestion, and the database tries to append/change 0 records, and Access 2000 locks up. Am I missing something obvious?

Thanks,

-jenn

 

[JLSigman]

Jeff,

When I tried your code, I got this error: "User-defined type not defined" on the first line (Dim rst As DAO.Recordset).

I'm not a Visual Basic programmer (yet), so I don't know what this means. Thanks,

-jenn

 

[lakers8175]

Jenn, can you past your code in here. It sounds like your criteria is wrong or maybe you aren't referencing the fields from the form correctly.

Sean

 

[jebry]

Hi!

If you are using A2k, then you need to go to the VB window, click on the Tools menu and choose References. Find the reference to DAO 3.6 library and check it. Actually, if you are using A97, you will probably need to to the same thing, but it happens more often in A2k because DAO isn't the default object library, ADO is.

hth
Jeff Bridgham

 

[JLSigman]

Jeff, I owe ya one. ;-) THANKS! That made it work perfectly.

-jenn

 

[JLSigman]

Is there a difference in the way Access 2000 works between Windows 98 and Windows 2000 Professional? The form for checking in works fine on my machine (the Win2kPro), but won't pull up the records on the Windows 98 machine.

Just fishing for hints... Thanks,

-jenn