DCount - can't figure out how to set Criteria for certain records

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

Guest

I have what I believe is a simple problem but have not figured it out.

I have a main form and subform. I need to restrict the number of records on
the subform to a maximum of 3. The relationship between the Main Form and
Subform is through a field called DMIID. I set an Add Record button on the
subform and need to add programing to count the number of sub-records, prior
to making the new record. If the count = 3, then a msg box to pop saying
"ALREADY AT MAX".
My initial try was:

Dim MELref, partspo, prevduedate, ExtensionCount, DMIIDnumber As String

'get previous data from current form


MELref = MELreferenceNUmber
partspo = PartsPOnumber
prevduedate = NextDueDate
DMIIDnumber = DMIID

ExtensionCount = DCount("[DMIID]", "MXExtRequest - DMI Extension data
tbl", "[DMIID] = DMIIDnumber")

I get a message error that says "you cancelled the previous operation".

What I need is, if the DMIID is "5877877", I count how many sub-records have
a DMIID of "5877877". If less than 3 then make a new record. If the count = 3
then msg box.

gavin hill
 
Use the BeforeInsert event of the subform

if me.recordset.recordcount >= 3 then
msgbox "You may not have more than 3 records" _
,,"Cannot Add record"
CANCEL = true
me.undo
end if

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Awsome Crystal,
Thank you for your time.

God Bless
--
GmH


strive4peace said:
Use the BeforeInsert event of the subform

if me.recordset.recordcount >= 3 then
msgbox "You may not have more than 3 records" _
,,"Cannot Add record"
CANCEL = true
me.undo
end if

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com


I have what I believe is a simple problem but have not figured it out.

I have a main form and subform. I need to restrict the number of records on
the subform to a maximum of 3. The relationship between the Main Form and
Subform is through a field called DMIID. I set an Add Record button on the
subform and need to add programing to count the number of sub-records, prior
to making the new record. If the count = 3, then a msg box to pop saying
"ALREADY AT MAX".
My initial try was:

Dim MELref, partspo, prevduedate, ExtensionCount, DMIIDnumber As String

'get previous data from current form


MELref = MELreferenceNUmber
partspo = PartsPOnumber
prevduedate = NextDueDate
DMIIDnumber = DMIID

ExtensionCount = DCount("[DMIID]", "MXExtRequest - DMI Extension data
tbl", "[DMIID] = DMIIDnumber")

I get a message error that says "you cancelled the previous operation".

What I need is, if the DMIID is "5877877", I count how many sub-records have
a DMIID of "5877877". If less than 3 then make a new record. If the count = 3
then msg box.

gavin hill
Click to expand...
Click to expand...
 
you're welcome ;) happy to help

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Back
Top