Update records

[bhcob1]

Hey Guys,

I have a db, with a table tblCSOC, there are 4 fields that are releveatn to my question:
tblCSOC
[CSOC Number] - primary key
[Issue] - primary key
[Latest Issue] - checkbox
[ID] - autonumber
The autonumber key was created so i could uniuqly identify each record.

I want a button which i click that will go thru the table, and for each record, find out if that is the latest issue for that CSOC number, and if so set [Latest Issue] to true.

I have been messing around with the Max function in a query, and also using record sets.

I am using Access 2000. Here is my code, any suggestions or ideas on how i could go about this...super.

Go easy on me, my debut to the post world

Sub Command118_Click()

'Use a nonreadonly lock type to be able to delete records.
Set rsMyTable1 = New ADODB.Recordset
rsMyTable1.ActiveConnection = CurrentProject.Connection
rsMyTable1.Open "qryCSOCIssueUpdate", , adOpenKeyset, adLockOptimistic, _
adCmdTable

Set rsMyTable2 = New ADODB.Recordset
rsMyTable2.ActiveConnection = CurrentProject.Connection
rsMyTable2.Open "tblCSOC", , adOpenKeyset, adLockOptimistic, _
adCmdTable

'Loop through recordset.
Do Until rsMyTable1.EOF
rsMyTable2.MoveFirst
Do Until rsMyTable2.EOF
If (rsMyTable1.Fields("FirstOfID") = rsMyTable2.Fields("ID")) Then
rsMyTable2.Fields("Latest Issue") = True
Else: rsMyTable2.Fields("Latest Issue") = False
End If
rsMyTable2.MoveNext
Loop

rsMyTable1.MoveNext
Loop

End Sub

[nico5038]

Normally we don't record a latest issue this way.
It would imply that after every change the procedure would need to run to make sure that the data is consistent.
When you have the [Issue] as a sequence number, the highest [Issue] will determine the latest issue and can be found like:

select a.[CSOC Number], a.[Issue] from tblCSOC a where [Issue]=(select MAX(b.[Issue]) from tblCSOC b where a.[CSOC Number]=b.[CSOC Number]);

Getting the idea ?

Nic;o)

[Rabbit]

Nico is right. This would require the code to run everytime a new record is added.

It'd be better to just have code run whenever you need the lastest Issue.

DMax("Issue", "tblCSOC", "CSOC = '" & CSOC & "'")
 

This will give you the latest Issue for a chosen CSOC Number, assuming that Issue increments. Use a query if you need it for each CSOC Number.

On a different note. It seems as if you can have multiple records with the same CSOC Number in which case it can't be a primary key. And the same may be true for the Issue.

[NeoPa]

They are both workable ways of determining the value required.
FYI Rabbit. Nico's way is the more efficient as this form can be optimised by the SQL engine and doesn't have the overhead of a function call from within the SQL. If it is required from within ordinary VBA code then the DMax() function is fine.

[bhcob1]

Thanks for that Nico, your code will do me just fine and save me pulling my hair out.

Cheers

[nico5038]

Glad I could help and success with your application !

Nic;o)