Does anyone know how to set up a procedure that will check the field in a subform for duplication of Code, and allow the entry if that code has and ending date. For example the program I am designing have BCodes which are benefits codes. The subform is linked to the main form is by SSN. On the subform the user doesn't want the BCode to be duplicated unless the Ending Date is on the previous BCode, then the BCode can be duplicated. If anyone have a suggestion, please let me know I would greatly appreciate it.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Duplication on a Subform
- Thread starter Shirley
- Start date
- Thread starter
- #2
I'm not sure I understand the question.<br><br>Could you email an example .mdb?<br><br>Please make sure there is no important "secret" data included in the database (like real SSN's). <p>Jim Conrad<br><a href=mailto:JimConrad@Consultant.com>JimConrad@Consultant.com</a><br><a href= > </a><br>
- Thread starter
- #4
Jim<br><br>I have tried twice to sent you the sample but for some reason I can not send it to you, but let me explain more. <br><br>Key Log Out Table<br>Fields Name<br><br>TransID<br>SiteNumber<br>KeyNumber<br>EmployeeID<br>IssuedDate<br>ReturnedDate<br><br>This table is linked to Site Table, Key Table and Employee Table<br><br>On this table there is a site number and a key number assigned to each key, I would like to let the user know if that particular key have already been assigned to someone else because it doesn't have a returned date. But if there is a return date then the user may issue that key out again. The problem is that I'm not a very good VB coding person, but I'm good at developing database. I can not make those three fields the primary key because the return date is sometimes null. I have tried to make the other two fields primary, but then it will not allow me to ever use the site number and key number again. I've even tried to set up a unique index, but I did not know how the coding for that would go. So if you have any ideas, please let me
- Thread starter
- #5
It sounds like you want to check to see if there is a transaction with a particular KeyNumber where the ReturnedDate is null? And if there is one to display a message to the user?<br><br>If your form contains a control called KeyNumber...<br><br><FONT FACE=monospace><br>Sub KeyNumber_BeforeUpdate(Cancel as Integer)<br><br> Dim KeyNotReturnedCount as Long<br><br> KeyNotReturnedCount = DCount("*","Key Log Out Table","KeyNumber =" & Me!KeyNumber & " AND ReturnedDate Is Null"
<br><br> If KeyNotReturnedCount = 0 Then<br> ' allow process to continue<br> Else<br> ' stop process<br> MsgBox "Key still not returned"<br> Cancel = True<br> End If<br> <br>End Sub<br></font> <p>Jim Conrad<br><a href=mailto:JimConrad@Consultant.com>JimConrad@Consultant.com</a><br><a href= > </a><br>
<br><br> If KeyNotReturnedCount = 0 Then<br> ' allow process to continue<br> Else<br> ' stop process<br> MsgBox "Key still not returned"<br> Cancel = True<br> End If<br> <br>End Sub<br></font> <p>Jim Conrad<br><a href=mailto:JimConrad@Consultant.com>JimConrad@Consultant.com</a><br><a href= > </a><br>- Thread starter
- #7
- Thread starter
- #8
Jim<br><br>I have tried and tried and it is not working. Maybe because each site number have 40 keynumbers from 1-40. The SiteNumber and KeyNumber needs to be checked , and then the ReturnDate need to be Null. The process is enter sitenumber then keynumber then do the check. Wouldn't the check need to be AfterUpdate and If cancel = True then delete record.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question