Find record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:
 
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
 
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...
 
Back
Top