Check data on new record entry

[Maclain]

Hi all,

We have a table, JobRegister that people enter information on using a form, FrmOrderEntry

I would like to run a check when a serial number is entered in the field SerialNo to see if the same serial number exists where CompletionDate is null.

this would stop duplicate open orders being put on the system, or new orders being entered where the previous order hasn't been completed.

TIA

 

[Minty]

On the after update event on the serial number control (Air Code & assumes Serial number is text)

Declare iCurrent as integer

iCurrent = DCount("UniqueJobID","JobRegister", "[SerNo] ='" & me.txtNewSerialNumber & "' AND [CompletionDate] is Null"

IF iCurrent >0 Then
msgbox "Already exits"
do more stuff here

Else
Save / update the record here code

 

[Maclain]

Hi there,

thanks for the response, I've tried the code..

Private Sub Serial_No_AfterUpdate()
Declare iCurrent as integer

iCurrent = DCount("[PKFLD]", "[Job Register and Report Log]", "[Serial No]") = "" & Me.txtNewSerialNumber & "' AND [Date Done] is Null"

If iCurrent > 0 Then
MsgBox "There is already an open order for this item."

Else
DoCmd.RunCommand acCmdSaveRecord
End Sub

I get a compile error. which highlights

Private Sub Serial_No_AfterUpdate()

and in VB editor

 Declare iCurrent as integer

is showing red.

Apologies, I'm not incredibly clever at scripting

Edit: Not sure if it makes any difference but the form in a split form / datasheet view. most edits are made in the data sheet bit, does this matter?

 

[Minty]

Apologies that should be Dim iCurrent as Integer not declare - I've been doing a lot of SQL server coding and got confused

I don't use split forms but as long as the controls are correctly referenced I'm sure it should work.

As an aside: I would also get into the habit of not using spaces in your field names. It will save you a lot of pain later on.
If you search for Naming conventions on here there is an excellent FAQ that will greatly assist you as you move forwards.

It's a lot easier to start now than try and correct it months later when all the references in the code need to be changed.

 

[Maclain]

Thanks for that,

I now get Compile error: Method or data member not found

on me.txtnewserialnumber

Is this looking for a text box called newserialnumber?

Edit: I know about the very poor naming. Unfortunately the system was substantial before I got hold of it. Needless to say all the recent tables forms and queries are named better

 

[Minty]

Nope- it's looking for a txt box called txtnewserialnumber , replace it with the control name that has your new serial number in it. (Which looks like you have called [Serial No] ?)

 

[Maclain]

Thanks,

I now have:

Private Sub Serial_No_AfterUpdate()
Dim iCurrent As Integer

iCurrent = DCount("[PKFLD]", "[Job Register and Report Log]", "[Serial No]") = "" & [Serial No] & "AND [Date Done] is Null"

If iCurrent > 0 Then
MsgBox "There is already an open order for this item."
End If
End Sub

However, when I enter a duplicate line, I don't get a warning..

 

[Minty]

This Line isn't quite right - remove the red bracket and replace with the green one;

iCurrent = DCount("[PKFLD]", "[Job Register and Report Log]", "[Serial No]"[COLOR="Red"])[/COLOR] = "" & [Serial No] & "AND [Date Done] is Null"[COLOR="Lime"])[/COLOR]

 

[Maclain]

excellent,

final code:

Private Sub Serial_No_AfterUpdate()
Dim iCurrent As Integer

iCurrent = DCount("[PKFLD]", "[Job Register and Report Log]", "[Serial No] = '" & [Serial No] & "' AND [Date Done] is Null")

If iCurrent > 0 Then
MsgBox "There is already an open order for this item."
End If

End Sub

TYVM!

 

[namliam]

Those field/table names /headache!

 

[Maclain]

Actually those are some of the nicer ones.

 

[namliam]

/major headache

Hope you didnt make them up

 

[Minty]

In fairness I inherited one called

[Stock code / Part Number]

Yup with a forward slash in it...
Unfortunately it's used all over a large database, so changing it would be a massive pain, nearly as massive as trying to code around it...

 

[Maclain]

This has developed a little, we would like to expand this feature a little more.

NB: I know the table, form and field names are terrible, please bare with me.

we have a form, JobRegisterBookingIn where the code above runs to check to see if the serial number already exists where the date completed is blank; invariably a duplicate order entry.

We are now trying to have it open a form, JobRegister filtered to the matching records for the serial number entered the form JobRegisterBookingIn.

We have started the process by when there is is duplicate detected on order entry, a form (frmduplicate) pops up with the warning message, and a button to either ignore, or view matching entries. - this bit is fine.

we are struggling with opening the form, jobregister filtered by matching entries from the form jobregisterbookingin.

we have this so far..

Private Sub Command3_Click()
DoCmd.OpenForm "Job Register and Report Log"

Dim BookinSerial As Variant
Dim RegisterSerial As String

BookinSerial = Forms![Job Register and Report Log BOOKING IN].[serial no]
RegisterSerial = Forms![Job Register and Report Log]![serial no]
DoCmd.ApplyFilter , RegisterSerial = BookinSerial
End sub

I should point out, and i'm sure it's reasonably obvious, I do not fully understand the use of variant and string.

Help?

 

[namliam]

In the openform method you can apply the filter there already
docmd.OpenForm "Yourform",,,"[serial no] = " & me![Serial no]

or if serial no is a tekst field

docmd.OpenForm "Yourform",,,"[serial no] = """ & me![Serial no] &""""

 

[Minty]

I would use Me.filter in preference (double check the syntax as I'm not in a position to check it atm)

Me.Filter = "[Serial No] = '" & Forms![Job Register and Report Log BOOKING IN].[serial no]&"'"
Me.FilterOn = True

FYI Variants are a store for "Anything", Strings only store Strings. Variants take up loads more memory so unless you have to use a string or integer depending on the type of value being used.

 

[Maclain]

Thanks for the response,

We are now not getting an error, but the form opens and a filter is applied, but not to anything worthy (how do we check where and what has been filtered?)

I feel I need to point out the the new line is being booked in for jobregisterbookingin, the code is running on a command button on form frmduplicateorder and we are trying to open the form jobregister filtered by the serial number entered on the first form..

This is a bit heavy for a Friday afternoon!

Also, the initial line being entered will not have been saved AFAIK as not all the required fields will have been entered.

 

[Minty]

I can't easily check the code at the moment, but although it may be more work, I think you would be better served by checking the serial number before starting to add a new record.

Put the new serial number in an unbound control on the booking in form, then check it for duplication. If it's not a duplicate copy it to the bound Serial number control on your booking in screen. If it is a duplicate you should be able to get the job number of the duplicate when you do your checking open the Job Register form on that job record using the job number rather than trying to filter it. (open args may be a good method)

 

[Maclain]

HI Minty,

I understand that would be a better way, but duplicates are reasonably infrequent, and sometimes are required. Although they appear often enough to warrant some thought.

It wouldn't justify checking the serial numbers for duplicates on every line item, hence letting access do the checks post entry.

hope this makes sense!

 

[Minty]

Okay - perfect sense - just a thought as I've worked in service industry for 20 + years so have a handle on where you are at

Using this
iCurrent = DCount("[PKFLD]", "[Job Register and Report Log]", "[Serial No] = '" & [Serial No] & "' AND [Date Done] is Null")

But copy and change it to a DLookup will give you the first PKFLD that is duplicated. You could present this both in your message box and use it to open the form on the correct record, without needing to do clever serial number filter bits...