Find record

A

Amduke

Hello,

can someone help me out. I'm trying to generate the VBA code for next problem:
With the Update event i try to generate a number out of a date and folowup
number. The dates and numbers are stored in a seperate table.
When starting the event (after update) i would like to look into the table
to find out if there are already records in it with the same date. If so, i
would like to know the highest number.
The problem i encounter is to find the right code to find a match. Here some
code i already wrote:

strSql = "select * from tbl_CertificateNr"
Set rstRecord = CurrentDb.OpenRecordset(strSql)
'"Go to last record"
If Not rstRecord.EOF Then
Me.Bookmark = rstRecord.Bookmark
Else
'"No records in this file"
End If

If rstRecord.EOF = True Then
'"Geen record gevonden"
Else
"Here is where the problem starts" ???????????????????????????

DoCmd.FindRecord M_date

If rstRecord!Date = M_date Then
'Already record available for this request date
M_lstNr = rstRecord!nr
M_date = rstRecord!Date
M_eof = False
Else
'No records available for this request date
End If
End If
'now, we can go:
 
O

Ofer

Try this, if I'm not wrong with the names

M_lstNr = nz(Dmax("nr","tbl_CertificateNr","[Date] = #" & M_date & "#" ),0)

If there are no records it will return 0. if there are records it will
return the max nr.
Also, you better change the name of the field, date, it a resurved name in
access, it will be OK as long that you put it in square brackets
 
A

Amduke

Ofer,

thank you, it is as simple as it can get.
Much apreciated.

Ofer said:
Try this, if I'm not wrong with the names

M_lstNr = nz(Dmax("nr","tbl_CertificateNr","[Date] = #" & M_date & "#" ),0)

If there are no records it will return 0. if there are records it will
return the max nr.
Also, you better change the name of the field, date, it a resurved name in
access, it will be OK as long that you put it in square brackets
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



Amduke said:
Hello,

can someone help me out. I'm trying to generate the VBA code for next problem:
With the Update event i try to generate a number out of a date and folowup
number. The dates and numbers are stored in a seperate table.
When starting the event (after update) i would like to look into the table
to find out if there are already records in it with the same date. If so, i
would like to know the highest number.
The problem i encounter is to find the right code to find a match. Here some
code i already wrote:

strSql = "select * from tbl_CertificateNr"
Set rstRecord = CurrentDb.OpenRecordset(strSql)
'"Go to last record"
If Not rstRecord.EOF Then
Me.Bookmark = rstRecord.Bookmark
Else
'"No records in this file"
End If

If rstRecord.EOF = True Then
'"Geen record gevonden"
Else
"Here is where the problem starts" ???????????????????????????

DoCmd.FindRecord M_date

If rstRecord!Date = M_date Then
'Already record available for this request date
M_lstNr = rstRecord!nr
M_date = rstRecord!Date
M_eof = False
Else
'No records available for this request date
End If
End If
'now, we can go:
Click to expand...
Click to expand...
 
Top