Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-18-2011, 06:10 AM   #1
timf79
Newly Registered User
 
Join Date: Dec 2010
Location: TEXAS
Posts: 23
Thanks: 0
Thanked 6 Times in 5 Posts
timf79 is on a distinguished road
Validation of field based on entry in previous recordset

Hello,
I am trying to find the most efficen way to validate the entry in a field based on the previous recordset.

The idea is to log time and activity.

The form as a header form with the date and a subform in datasheet view.
I want to make sure that the subform taes time (for the specific date) only in ascending values.
Since SQL does not consider a recordset order I assured that the udnerlying table has a autonumber field (this will give me the order of recordsets).

Further I want to make sure that the activity entered is lmited based in the previous activity.

For example:

5:45 getting up
5:55 brushing teeth

BUT NOT:
5:45 brushing teeth
5:55 getting up

My though is to have a VBA code that runs on the "before update" property of the time field and compare the the time of the previous recordset if the date is the same.
Though I am concerend about the timet his mgiht take?

For the activity I can only think of a similiar code, but then VBA hardcoding what can come after what.

Any better ideas on this topic?

timf79 is offline   Reply With Quote
Old 08-18-2011, 09:23 AM   #2
timf79
Newly Registered User
 
Join Date: Dec 2010
Location: TEXAS
Posts: 23
Thanks: 0
Thanked 6 Times in 5 Posts
timf79 is on a distinguished road
Re: Validation of field based on entry in previous recordset

OK,
I solved a part of the riddle myself.

My idea was that data is entered in sequential time.
The problem is what happens when you miss a line.

Therefore I created VBA code that does the ranking, thereore you can enter times in arbitraily order:

Quote:
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim CurrentOEEHD As Integer
CurrentOEEHD = [ID]
strSQL = "SELECT * from TABLE Where CRITERIA =" & FilterOnForm & " Order by Time;"
Set rs = New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

With rs
.MoveFirst
x = 1
Do Until .EOF
![Ranking] = x
.MoveNext
x = x + 1
Loop
End With
rs.Close
Set rs = Nothing
From here on out I can do the cehck from one recordset to another easily.
timf79 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hiding text box based on previous combo box data entry HKJ Forms 9 08-06-2010 04:19 AM
Conditional field entry validation based on VBA Barkholt Modules & VBA 2 05-25-2010 04:44 AM
Default Value from previous entry into field jmriddic General 19 04-07-2008 12:07 PM
How can I configure a table to auto data entry into fields based on a previous field scott-atkinson Tables 3 08-31-2006 07:06 AM
Valildation based on previous cell entry cassowary43 Forms 9 05-13-2006 06:29 PM




All times are GMT -8. The time now is 03:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World