UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Entry Retriction Compare With Another Field, Access 2013    
 
   
komo
post Jun 16 2017, 12:49 PM
Post#1



Posts: 15
Joined: 28-July 16



Is it possible to restrict data entry to a field with information from another field in subformform or a query?
Subform Route:
sfrmResourceAllocation
Control field: Resource
Row Source: SELECT tblItem.DisplayName, tblItem.ItemID, [qryAllocation].[Available]-[Used] AS Remaining FROM tblItem LEFT JOIN qryAllocation ON tblItem.ItemID = qryAllocation.Resource
Goal: Compare Data to Resource with the Remaining Amount ([Resource].Column(2))

qryRoute:
qryAllocation
Remaining Field (column 3 I believe)


Private Sub Items_AfterUpdate()
If [Forms]![frmResourceEdit].[Form]![sfrmResourceAllocation].[Form]!Data <= (what do I put in here to compare the data?)
Then
Response = MsgBox("Careful! The chosen Resource has" & vbCrLf & "Remaining Available Slots: " & _
[Forms]![frmResourceEdit].[Form]![sfrmResourceAllocation].[Form]![Resource] & _
vbCrLf & vbCrLf & _
"Do you want to allocate this Resource anyway?", vbYesNo)
If Response = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.RunCommand acCmdSave
End If
End Sub
This post has been edited by komo: Jun 16 2017, 12:50 PM
Go to the top of the page
 
theDBguy
post Jun 16 2017, 01:59 PM
Post#2


Access Wiki and Forums Moderator
Posts: 69,949
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Not sure I understand your question, but I suspect it is possible to do what you're asking. However, it is hard to offer any advice without any knowledge of your database setup. Can you give us more details? Thanks.

--------------------
Go to the top of the page
 
komo
post Jun 16 2017, 03:57 PM
Post#3



Posts: 15
Joined: 28-July 16



sure! I hope this image will clarify!


Attached File(s)
Attached File  frmlook.jpg ( 106.5K )Number of downloads: 3
 
Go to the top of the page
 
theDBguy
post Jun 16 2017, 04:15 PM
Post#4


Access Wiki and Forums Moderator
Posts: 69,949
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thank you for the additional information. First of all, to validate input data, it is recommended to use the BeforeUpdate event rather than the AfterUpdate event.

So, to check if the value entered does not exceed available quantity, you can simply compare the input value against the quantity column from the combobox. For example:

If Me.TextboxName > Val(Me.ComboboxName.Column(2)) Then
'sorry, this is not allowed.
End If

Hope it helps...

--------------------
Go to the top of the page
 
komo
post Jun 17 2017, 08:51 AM
Post#5



Posts: 15
Joined: 28-July 16



Thank you very much theDBGuy! I always appreciate your help!
Is there a way that I can reset the value to 0 to ensure that the user inputs the proper value again?
Right now, if user enters .5 and it's invalid...even when they close message box, the field still shows .5

This is the code I have:
Private Sub Data BeforeUpdate (Cancel As Interger)
If Me.Data > Val (Me.Resource.Column(2)) Then
MsgBox "Sorry, you're allocating more than what's available.",vbCritical
End If
End Sub
This post has been edited by komo: Jun 17 2017, 08:51 AM
Go to the top of the page
 
theDBguy
post Jun 17 2017, 09:29 AM
Post#6


Access Wiki and Forums Moderator
Posts: 69,949
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You can add the following two lines:

Cancel=True
Me.Data.Undo

Hope it helps...

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2017 - 12:11 AM