Transfer of data

  • Thread starter Thread starter Dart QIS
  • Start date Start date
D

Dart QIS

Hi guys,

I am in the process of updating 3,500 rows of data. My two
fields are UserID (the second field) and a field called
DataEntry (the 50th field). The user of the database has
hired another person to do the data entry for this field
so all of the fields are going to be UserID = DataEntry. I
have set up my module based on the Help files, but I have
no idea how I am going to transfer my data.

here is what I have so far in a module. I am a beginner at
Access 97 VBA:

Function UpdateTable()
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("ProdTestingCopy")

MsgBox "There is " & rst.RecordCount & " records in this
table."

Set dbs = Nothing
Set rst = Nothing

End Function
 
Instead of updating the table through code, create a form whose data source
is the table in question. Create bound controls (text or combo boxes) for the
fields needing entry on the form. Then, in the Form_BeforeUpdate, make
UserID=DataEntry.

Your post was not clear about how many of the fields need to be updated by
the data entry user, but perhaps the above will give you a start.
 
Why not just use an update query?

UPDATE ProdTestingCopy
SET DataEntry = UserID
WHERE DataEntry Is Null

If you have to do that in VBA code then the lines should be something like
(UNTESTED AIR CODE follows):

Function UpdateTable()
Dim strSQl as String, Dbs as DAO.Database

StrSQL = "UPDATE ProdTestingCopy SET DataEntry = UserID WHERE DataEntry Is Null"

DBS.Execute StrSQL, dbFailOnError

End Function
 
Back
Top