Conditional IF and RecordSet

[charly.csh]

Hi all Access friends,

I have a question regarding usage of recordset and also conditional If'

This is the first time I will use recordset (I am not an expert with this... my code is very wrong)

What I want is that the recordset' search into the field "precode" from my table "NC_Main_Table" if this "precode data" existed previously...

If the answer is "No" I want to create something like Consecutive_ID = 1, otherwise if this data exist in the table make something like.... Consecutive_ID = Consecutive_ID + 1

'****************************
Dim Clave As String
Dim Zero As String


Dim Year1 As String
Dim Mes As String
Dim Mes1 As String
Dim Dia As String
Dim Dia1 As String
Dim BU As String

Zero = "0"
Year1 = Year(Date) - 2000
Mes = Month(Date)
Dia = Day(Date)
BU = DLookup("[Code]", "[Line_Production]", "[LineProduction]= '" & Me.Line_Production & "'")

If Mes < 10 Then
Mes1 = Zero + Mes

Else

Mes1 = Mes

End If

If Dia < 10 Then
Dia1 = Zero + Dia

Else

Dia1 = Dia

End If

Me.PreCode = "NC-" & BU & "-" & Year1 & Mes1 & Dia1

'Now I want to create the consecutive number
'First time using RecordSet is highly probable wrong....

Dim db As Database
Dim rs As DAO.Recordset
Dim Consecutive_ID As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from NC_Main_Table Where PreCode = '" & Me.PreCode & "'", dbOpenDynaset)

If rs.EOF = False Then
rs.MoveLast
rs.MoveFirst

Do Until rs.EOF

'FROM HERE I DON'T KNOW HOW TO CONTINUE....

'******************************
I Highly appreciate your support!!!

[orange]

Lots of info on recordsets here by Steve Bishop.

[Gicu]

While recordsets are vital to master to use Access at the fullest I need in your case would be an overkill.

I think all you would need is this:


'<<<<<<<<<<<<<<<<StartOfCode>>>>>>>>>>>>>>>>>>>>>> >>>>>>
Dim sDate As String
Dim BU As String
Dim sPrecode As String
Dim Consecutive_ID As Long




BU = DLookup("[Code]", "[Line_Production]", "[LineProduction]= '" & Me.Line_Production & "'")
sDate = Format(Date, "yymmdd")
sPrecode = "NC-" & BU & "-" & sDate


If DCount("*", "[NC_Main_Table ]", "[Precode]='" & sPrecode & "'") = 0 Then
Consecutive_ID = 1
Else
Consecutive_ID = Consecutive_ID + 1 'here you can use a dMax to get the max of the consecutive_id from wherever you have it
End If


''to use the recordset method uncomment the lines below
'Dim db As Database
'Dim rs As DAO.Recordset


'Set db = CurrentDb
'Set rs = db.OpenRecordset("Select * from NC_Main_Table Where PreCode = '" & Me.PreCode & "';", dbOpenDynaset)


'If rs.RecordCount = 0 Then 'the recordset is empty meaning no precode present
' Consecutive_ID = 1
'Else
' Consecutive_ID = Consecutive_ID + 1 'here you can use a dMax to get the max of the consecutive_id from wherever you have it
'End If
''clean up
'rs.Close
'Set rs = Nothing
'Set db = Nothing
'<<<<<<<<<<<<<<<<EndOfCode>>>>>>>>>>>>>>>>>>>>>>>> >>>>


For some reason the Code tags are broken for this post in the forum
Cheers,
Vlad

[charly.csh]

Wow!

Good to see it.
Question how do you use the dMax for this? I have never use before this function

[Gicu]

You use it just like dLookup - https://www.techonthenet.com/access/...omain/dmax.php

Cheers,
Vlad