Please check my vb code

[Groundrush]

Please can anyone tell me where I’m going wrong
I don’t have much experience in VBA so please be gentle.

I have table called “Concept Orders”which stores all our orders for jobs to be carried out and
I have a form in which users type in details of the work.

A combo box is used to select a “JobNo”, (which is limited to list) from the Concept Orders Table

Those records are then stored in a table called “Sub-Contractor Orders”.

The JobNo is indexed to (duplicates ok.) which is fine because we may on occasion use the same job for more than one Sub-Contractor.

The problem is, that users are duplicating orders without knowing about it.

I am trying to notify them with a message warning them that they duplicating an order,
They then have a choice to continue or first check

This is the code that I am using on the “JobNo” control of the form.

Private Sub JobNo_AfterUpdate()
If DCount("[JobNo]", "Sub-Contractor Orders", "[JobNo]=Forms![frmSub-Contractor Orders]![JobNo]") > 0 Then
MsgBox "This Job No has already been issued.", 48, ""
End If
End Sub


This code allows me to enter the first record, after that the message is triggered every time a new record is created.

Where am I going wrong?

I know this will probably be an easy one for you guys

Any help will be much appreciated.

thanks

 

[Mile-O]

Private Sub JobNo_AfterUpdate() 
If DCount("[JobNo]", "Sub-Contractor Orders", "[JobNo] = " &  Forms![frmSub-Contractor Orders]![JobNo]) > 0 Then 
MsgBox "This Job No has already been issued.", 48, "" 
End If 
End Sub

That should do it for you.

The only problem was that you were counting all the [JobNo]'s in the table that were literally equal to Forms![frmSub-Contractor Orders]![JobNo] and not the value itself.

 

[Groundrush]

Run-time error '3075'

Thanks for your help

I have just tried the code again, and after entering one of the job no's (0222:1000) from the Combo box, I get this message:

Runtime error '3075':
Syntax error (missing operator) in query expression'[JobNo]=0222:1000'.

tried to debug, but cant see what the problem is.

 

[WayneRyan]

GroundRush,

Mile-O-Phile's example was right, but since your [JobNo] field
is text then you must add the single-quotes:

Private Sub JobNo_AfterUpdate()
If DCount("[JobNo]", "Sub-Contractor Orders", "[JobNo] = '" & Forms![frmSub-Contractor Orders]![JobNo] & "'") > 0 Then
MsgBox "This Job No has already been issued.", 48, ""
End If
End Sub

Wayne

 

[Groundrush]

Thanks for that Wayne.

But I,m sorry to say

I'm back to the original problem

it lets me enter 1 record, then after that on every record entered the message is activated, regardles of whether it's a duplicate or not.

 

[Drevlin]

If you are checking to see if your value is in the table after you've updated the value then it will always find it there (the one you just input).

Either change your code to say > 1 or place the code into the BeforeUpdate.

 

[WayneRyan]

GroundRush,

Try making that a BeforeUpdate event.

Wayne

 

[Groundrush]

IF I change it to >1, all it does it let me create 3 records before triggering the Message instead of 2. regardless if it's a duplicate or not.

I also change it to a BeforeUpdate event

Sorry to be such a pain, but I,m going to jump out my window if I cant get this sorted out.

well ok, I live on the ground floor, but it will still hurt if I jump head first.


I have taken the liberty to attach a copy of it. smaller version of course,

 

[WayneRyan]

GroundRush,

Put it back to > 0 and
change [JobNo] to [Job No].

Those spaces and special symbols will get you every time.

Wayne

 

[Groundrush]

Thank you

Thanks Wayne.

very grateful