asked on

Disable or Enable Primary Key , depending upon ADD or EDIT

Hello Experts,
i am using the same form for Editing and Addition of new record (say Purchase Entry).
I dont want to allow user to edit PO_No (Purchase Order No) if the same is used in Sales Table (tblSale)

On Load event of the form, i am using below code :

Private Sub Form_Load()

'If Me.AllowEdits = True Then
'If Me.DataEntry = False Then
If Me.NewRecord = False Then ' to check if its in Edit Mode

Dim poid As String
Dim stlinkCriteria As String
MsgBox "Form is in edit mode"
poid = Me.PO_No.Value

stlinkCriteria = "[PO_NO]=" & "'" & poid & "'"

If DCount("PO_NO", "tblsale", stlinkCriteria) > 0 Then
MsgBox "Dcount of total sales records :" & DCount("PO_NO", "tblsale", stlinkCriteria)
Me.txtPO_no.Enabled = False
MsgBox "This PO No can not be edited as this is used in sale Master", vbOKOnly, "ERROR"
Else
Me.txtPO_no.Enabled = True
MsgBox stlinkCriteria, vbOKOnly
End If
End If

End Sub

Till now its working fine, but when the form is used for new record, textbox PO_NO is automatically disabled. :(

Could you please help ?
Thanks in advance.
Kamlesh Jain

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kamlesh Jain

ASKER

hello. Thanks for your prompt response.

My problem is that i am not gettng the field "PO No " enabled while i am in NEw Record Mode / Data Entry Mode.

This field needs to be enabled / disabled depending upon the selection of record by Dcount and i am getting the same done and its working fine.

Kindly help.

Gustav Brock

You have:

If Me.NewRecord = False Then 
    ' Stuff ...
Else
    Me.txtPO_no.Enabled = True
End If

Open in new window

So, if the code is run, the control will be enabled. And, if put in the OnCurrent event, it will.

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kamlesh Jain

ASKER

Thanks.. i just modified a little bit and got the desired results :
If Me.NewRecord = True Then ' to check if its in Edit Mode

Me.PO_No.Enabled = True

Else
Dim poid As String
Dim stlinkCriteria As String
'MsgBox "Form is in edit mode"
poid = Me.PO_No.Value

stlinkCriteria = "[PO_NO]=" & "'" & poid & "'"

If DCount("PO_NO", "tblsale", stlinkCriteria) > 0 Then
MsgBox " You have already booked " & DCount("PO_NO", "tblsale", stlinkCriteria) & " Sale against this PO & PO Number can not be edited ...", vbCritical, "NOTE"
Me.PO_No.Enabled = False
Else
Me.PO_No.Enabled = True
'MsgBox "PO no shall be enabled", vbOKOnly
End If
End If

Kamlesh Jain

ASKER

Thanks. Closing the question.