Validation? 2

[mwarnimont]

OK, I've got a strange one here.

What I have are 24 items. The data to be stored is the item number and the date.

The problem is that each one can only be entered once EXCEPT item 7 and 12 which can be entered more than once.

Any idea how I can make sure this happens?

Thanks!

 

[SgtJarrow]

Give a little more info....make some specific examples and we can maybe help you out....SOunds like you are going to need some VBA coding with this one, so how much do you know???

Are there 24 fields???
24 Items???
What is the Table structure???
What do you need to accomplish???

Look at some of the other threads on how to post this info....The more details you can provide, the better we can help you.... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[LittleSmudge]

Fields are ItemNumber and TheDate

You will need to allow duplicates to ItemNumber and then put filtering code in the FORM that only allows one record per item number except where ItemNumber = 7 or 12.


'ope-that-'elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[mwarnimont]

Thanks LittleSmudge, we're on the same track I think. Problem is, I'm stuck on how to go about that filter. Can you help get me started on how to code that?

Thanks!

 

[LittleSmudge]

In the BEFORE_UPDATE event of the ItemNumber control :-


Private Sub txtItemNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtItemNumber_BeforeUpdate
If ( txtItemNumber <>7 AND txtItemNumber <>12 ) Then
If DCount(&quot;ItemNumber&quot;,&quot;tblItemDate&quot;,&quot;ItemNumber = &quot; & txtItemNumber) > 0 Then
MsgBox &quot;An entry for Item Number &quot; & txtItemNumber & &quot; already exists.&quot; & vblf _
& &quot;You cannot enter a second record for that Item Number.&quot;, , &quot;Caught You !&quot;
Cancel = True
txtItemNumber.Undo
End If
End If
Exit Sub

' Prevent CtrlBreak bypass of the catchnet.
Err_txtItemNumber_BeforeUpdate:
Cancel = True
txtItemNumber.Undo
End Sub




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[mwarnimont]

Thanks! Worked great

Michelle