Code Not Working Properly..urgent Pls.

Hi All,

I have Header form and subform subform in it with following fields

Subform fields are :

1. MQE_No : (Text field as it will be like MG-120, MG230), Duplicate OK)
2. RPO_No : (Number field – Double, Duplicate OK ) The are project no.
3. ForemanNo (Number field – Long Int – Duplicate OK)

Header form fields are ;

1.ForemanNo – Long Int – No Duplicate
2.Other fields…

The subform field have ForemanNo One-To-many relationship with Header form.

Following project MG-411 need to enter (assign) to foreman no. 641

Actual data to store in subform

MG-411 (MQE_NO)
5421654 (RPO_NO)
641 (FOREMANNO)

I started to enter data into subform selecting the ForemanNo at header so records to enter in specific foremans account. Everything is fine here.

What I need is:

I would like to select / add another Foreman into the header and start entering same above data for another Foreman. Bcz sometimes we need to do like this in case previous Foreman may go on to a week vacation. We can not stop project for a week. So need to assign project to other foreman.

When selected the Project (MG-411) thru a combo to assign it with other foreman, it should give a short msg to user that this project has already been assigned to “XYZ” forman. Need to assign again “ (Y/N box )

If Y then data entered otherwise cancelled.

To achieve this, I did the following but only 50% success. (Used Cmbo CboRPO2 select MQE_NO). Tried to satisfy both condition but no use.

Private Sub CboRPO_AfterUpdate()
If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO = " & ENO) Then
MsgBox "RPO ALREADY ASSIGNED TO SOMEONE / FOREMAN", vbOKCancel, "WARNING!!!"
Me.Undo
Exit Sub
Else

Dim Msg, STYLE, TITLE, HELP, CTXT, Response, MYSTRING
If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO <> " & ENO) Then
Msg = "RPO ALREADY EXIST WITH OTHERFOREMAN, ASSIGN AGAIN TO ANOTHER ?"
STYLE = vbYesNo + vbInformation + vbDefaultButton2
TITLE = "!! ATTENTION !!"
HELP = "TEST FILE"
CTXT = 1000
Response = MsgBox(Msg, STYLE, TITLE, HELP, CTXT)

If Response = vbYes Then

Me.MQE_NO = Me.CboRPO.Column(0)
Me.RPO_No = Me.CboRPO.Column(1)
Me.WORKSHEET_NO = Me.CboRPO.Column(2)
Me.WORKORDER_NO = Me.CboRPO.Column(3)
Me.WORK_DESC = Me.CboRPO.Column(4)
Me.PL = Me.CboRPO.Column(5)
Me.PipeLineKM = Me.CboRPO.Column(6)
Me.DiaMeter = Me.CboRPO.Column(7)
Me.PipeLength = Me.CboRPO.Column(8)
Me.PipeLineArea = Me.CboRPO.Column(9)
Me.P = Me.CboRPO.Column(10)
Me.RPO_AMOUNT = Me.CboRPO.Column(12)
Me.INV_AMOUNT = Me.CboRPO.Column(13)
Me.Status = "WIP"
Me.StatusID = 2
Me.CboStatus.SetFocus
Else
Me.Undo
Exit Sub
End If

Else
Me.MQE_NO = Me.CboRPO.Column(0)
Me.RPO_No = Me.CboRPO.Column(1)
Me.WORKSHEET_NO = Me.CboRPO.Column(2)
Me.WORKORDER_NO = Me.CboRPO.Column(3)
Me.WORK_DESC = Me.CboRPO.Column(4)
Me.PL = Me.CboRPO.Column(5)
Me.PipeLineKM = Me.CboRPO.Column(6)
Me.DiaMeter = Me.CboRPO.Column(7)
Me.PipeLength = Me.CboRPO.Column(8)
Me.PipeLineArea = Me.CboRPO.Column(9)
Me.P = Me.CboRPO.Column(10)
Me.RPO_AMOUNT = Me.CboRPO.Column(12)
Me.INV_AMOUNT = Me.CboRPO.Column(13)
Me.Status = "WIP"
Me.StatusID = 2
Me.CboStatus.SetFocus
End If
End If
End Sub


I tried with following code also

'Dim cdn As String
'cdn = "[MQE_NO] = '" & Nz(CboRPO, "") & "'"
'cdn = cdn & " And ENO = " & ENO

'If DCount("*", "T_RPO_Footer", cdn) > 0 Then
'MsgBox "THIS RPO ALREADY ASSIGNED TO THIS FOREMAN", vbOKOnly, "WARNING!!!"
'Me.Undo
'Exit Sub
'Else
‘do something
‘End if

Can somebody help it please.

With kind regards,
Ashfaque