how to set a field for all records in a record set to a certain value? 1

[FacilitiesCAD]

First Thank you everyone for the great posts here. I have learned alot about access from here. You have saved me unmeasurable time in organizing data.

I am trying to maintain a database of all the autocad drawings on a specific public drive.
The code bellow allows me to add a new record if it isn't found in the database. This is a great step forward for me. I am now tring to close up some loose ends.

I would like a way set a variable "status1" to "lost" for every record in a table called "table_files1". By marking everything "lost" I would like to then mark them "Found" if it shows up as part of my test for duplicates. I would mark new records as "New" as seen in the line !Status1 = "New" bellow.
This will then seperate my data into 3 catagories.
"Lost" where the file has been moved or deleted since I last updated the database and I will need to investigate further.
"New" where its a new entry to the database that needs more information added to it.
or "Found" where the data was already there and needs no more editing.

I don't know how to set a variable for every record to the same value. I expect it is some kind of loop just not sure of the syntax.
Once I use the Dcount test to find a duplicate and stop re-entry of data how do I change the records original variable to a new value. I expect it is something like dlookup but I'm just not sure.

Any solutions or hints are aprecialted.

Tim

Code follows bellow:


Xi = DCount("Saved_Date1", "Table_files1", "Path1 = '" & Path2 & "' and File1 = '" & File2 & "'")
If Xi > 0 Then
' insert here way to show "found" records
Else
With rstDWG
.AddNew
!Path1 = Path2
!Saved_Date1 = Saved_Date2
!Saved_Time1 = Saved_Time2
!File_Size1 = File_Size2
!File1 = File2
'insert here way to show "new" records
!Status1 = "New"
.Update
.Bookmark = .LastModified
End With
End If 'Xi if

 

[scriverb]

You perform a very similiar operation using the rs.Edit and rs.Update commands on either side of your field assignments.

The looping arrangement would be had through a Do loop. See the following example:

dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("tblname",dyopendynaset)
rs.movefirst
Do
   rs.edit
   rs!field1 = [i]value to assign[/i]
   rs.Update
   rs.MoveNext
Loop until rs.EOF
rs.close
db.close

This example opens a recordset then moves through the recordset one record at a time and updates one field with a preset value( variable or string or number etc ).



Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[FacilitiesCAD]

Thanks Bob,
This I can follow.

for my second problem can I dlookup() the ID which is the KEY variable and then move to that recordset. If so how?

Thanks again,

Tim

 

[scriverb]

When you say key variable are you talking about the field name to be updated?


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[FacilitiesCAD]

Sorry Im not sure.
When I make a table and try to close it. I am told there is no primary key defined. It then makes a column labeled "ID" and no 2 ID's are the same in a table(due to autonumbering).
Basicaly I want to change
rs!field1 = "found"
for the record that has ("Path1 = '" & Path2 & "' and File1 = '" & File2 & "'") in table "Table_files1"

where Path2 and File2 are strings and
Path1 and File1 are columns of my record.


DCount("Saved_Date1", "Table_files1", "Path1 = '" & Path2 & "' and File1 = '" & File2 & "'")

 

[FacilitiesCAD]

Thank you Bob,

I have a working solution to the problems I've listed here.

I use:

dim criteria as string
rs.movefirst
criteria = "Path1 = '" & Path2 & "' and File1 = '" & File2 & "'"
rs.FindFirst (criteria)
rs.edit
rs!field1 = "found"
rs.update

by the way what does the .update do for me?

Thanks,
Tim

 

[scriverb]

The rs.Update perform sthe SAVE to the recordset of the changes you made during the .EDIT process. You see you move to the record to edit, you designate the process you want to perform(rs.Edit), you assign new values to fields in the recordset, and then you perform the SAVE or rs.Update process.

Glad that this all works out for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]