Adding A Single Record When Criteria Matches

[DMT Dave]

Hi Guy's, this should be a simple answer that for some reason I can't think why this doesn't work properly!!!!

I may have 12 records that is DelNo 1

This event is After Update on DelNo, so only on the 1st instance it needs to add to tblJobSheets2 1 x record

Name-Town-PostCode 12 Items

Once you change the next DelNo (2 of 12), im trying to stop it adding because the record now exists

It is adding 12 records instead so i believe the If Not Is NUll is not doing it's job

I haven't put full code on as the result is correct just adding however many records that are for the selected DelNo instead of 1 record only

Any advise is appreciated, its probably so simple and jut me having a logic lapse

If Not IsNull(DLookup("DelNo", "tblJobSheets2", "[Vehicle] = '" & strVehicle & "' And [DelDate] = #" & dtDelDate & "# And [DelNo] = " & intDelNo)) Then    DoCmd.CancelEvent
    Else
    With rs2
    .AddNew
    !IDNumber = iJS
    !DelDate = dtDelDate
    !Driver = strDriver
    !Vehicle = strVehicle
    !DelNo = intDelNo
    !DelTo = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !Items = strAddDel
    !EntCode = strEntCode
    !ExtCode = strExtCode
    .Update
    .Close
    End With
    
    End If

[Welshgasman]

Walk through your code

[DMT Dave]

Hi WGM, yes i am going to do that because I think the item count is zero so thats then next step as you have suggested

I just can't get my head around why i can't do the simplest tasks ever which is: Count a set of records with criteria's

[Gicu]

To count you use DCount with "*":

If DCount("*", "tblJobSheets2", "[Vehicle] = '" & strVehicle & "' And [DelDate] = #" & dtDelDate & "# And 	[DelNo] = " & intDelNo)=0 Then    
    With rs2
    .AddNew
    !IDNumber = iJS
    !DelDate = dtDelDate
    !Driver = strDriver
    !Vehicle = strVehicle
    !DelNo = intDelNo
    !DelTo = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !Items = strAddDel
    !EntCode = strEntCode
    !ExtCode = strExtCode
    .Update
    .Close
    End With
End If

Cheers,

[Welshgasman]

Hi WGM, yes i am going to do that because I think the item count is zero so thats then next step as you have suggested

I just can't get my head around why i can't do the simplest tasks ever which is: Count a set of records with criteria's

I think I have said this before.
If you have more than one criteria, put it all into a string variable. Than you debug.print it and inspect it.
Whilst that does not appear to issue here, always worth doing, as you walk through your code.

[DMT Dave]

Thank you guy's i can't come back to this at the moment, soon as i can get back to it, will totally take on what you have suggested and run the debug.print to see results

Thanks Again

[Welshgasman]

Really sounds like you should be using DCount() ?

[DMT Dave]

Yes just picked up on that, i totally forgot about the wildcard rather than counting specific field, count anything within criteria's

[Welshgasman]

Yes just picked up on that, i totally forgot about the wildcard rather than counting specific field, count anything within criteria's

Could just as easy used a fieldname?

[DMT Dave]

Yeh, would typically use postcode as all postcodes in contacts are unique then fine tune to names, vehicles, drivers

[isladogs]

Whilst postcodes are themselves unique, it is quite likely that you will have two or more contacts with the same postcode

[Welshgasman]

We have 14 houses in our cul de sac.
Amazon must deliver to pretty much all of them. :-)

[DMT Dave]

Thanks guys, we never deliver to any postcodes that close, the competition un the market is too great but your right if we were door to door then postcodes may not be in certain circumstances as unique as a primary key ID NUmber

[isladogs]

Never...yet! It will happen at some point!