Help Please

i have 3 different forms each forms contain almost the same fields . in each
form there are date field and time field. how can i like these dates from all
of the 3 forms so nobody can assign the same date and the same time.

i.e. date:18/02/2006 time:Afternoon

if this date&time is assigned in one form, then it shouldnt be able to assign
to the other two forms.
but i should be able to assign same date with different times or same
timewith different dates.

is there any way to do this please.

thanks a lot.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200602/1

On Sat, 18 Feb 2006 11:48:20 GMT, "SJJ via AccessMonster.com"
u16634@uwe wrote:

i have 3 different forms each forms contain almost the same fields . in each
form there are date field and time field. how can i like these dates from all
of the 3 forms so nobody can assign the same date and the same time.

i.e. date:18/02/2006 time:Afternoon

if this date&time is assigned in one form, then it shouldnt be able to assign
to the other two forms.
but i should be able to assign same date with different times or same
timewith different dates.

is there any way to do this please.

thanks a lot.

The first thing to realize is that your Forms don't contain ANY fields
or any data. Data is stored in Tables, and only in Tables; forms are
just tools to manage that data.

What you can do in the Table definition is to select the Indexes icon
on the toolbar (looks like lightning hitting a datasheet); create a
new unique two field index on the datefield and timefield. To do so,
put a distinctive index name (UniqueTime say) in the left column, the
datefield name next to it in the right column, and the timefield in
the next row of the right column:

UniqueTime Datefield
Timefield

Select the "Unique" checkbox and save the table. You now will be
protected from entering duplicates, but either field alone can be
duplicated (i.e. you can have 18/02/2006 morning and afternoon in two
records).

John W. Vinson[MVP]

thank you for the response

i have three different tables and each table contail date and time fields. if
i assign one date and time then i shouldn't be able to assign the same data
on the other two tables. is it possible to do that.

--
Message posted via http://www.accessmonster.com

Here is one way that works with mdb as opposed to adp which uses
different sql logic.

In most of my applications now I have a hidden form and I have fields
on it that I need for common queries etc. (On a main menu form in the
on open event (docmd.openform "hidden",,,,, achidden - something
like that)

1) on that hidden form have search date and search time field
2) Create a query for each of the tables with those fields as criteria.

2) then on each of the forms, in the afterupdate event of both date and
time
for example in the table3 form:
if both fields are not null then
load the hidden form fields with data from that form
ckcnt1 = dcount("[ID]" (or what ever), "Querytable1")
ckcnt2 = dcount("[ID]" (or what ever), "Querytable2")
if (ckcnt1 + ckcnt2) 0 then
msg- there is a problem etc.
cancel
endif
endif

You can break those down too and branch around if you want to be more
specific about which table has a conflict with the new time.

Also will need to do it on the before insert and cancel the insert if
they have ignored the message.

Similar logic will need to be on each of the forms but with with calls
to different queries.

Just an idea........

Ron

thnak you Ron

but i dont understand fully. could u be able to explain in more detail if
possible.

thanks in advance.

--
Message posted via http://www.accessmonster.com

1) Create a form named MyHiddenForm
2) on that form place 2 unbound txt fields named HKsearchdate (format
- short date) and HKSearchtime (format medium time - or whatever you
have for the time in your tables)
3) In the OnLoad event of your main menu or whatever is the first form
that you open up place this:
DoCmd.OpenForm "MyHiddenKey", acNormal, , , acFormEdit,
acHidden
4) create three queries:
a) Table 1 query with the criteria of the key date =
Forms![MyHiddenKey]![HKSearchdate] and key time =
Forms![MyHiddenKey]![HKSearchtime] and have it show a field that will
ALWAYS have data in it (for instance the ID or even the date field that
you are matching against.
b) Table 2 query same format as above and same fields.
c) table 3 query same format as above and same fields.

5) In the form for the editing table 1 in the afterupdate event for
both the date and time field add the code AND in the before insert
event for the form (which is the point at which the record would be
added):
If NOT isnull(me.thisdate) and NOT isnull(me.thistime)
then
tbl2cnt = dCount ("[ID]", "Table 2 query")
tbl3cnt = dCount ("[ID]", "Table 3 query")
if tbl2cnt 0 then
msgbox "This time is already in use in Table 2.
Select another time."
cancel = true ' I am not sure that this is
formated correctly - check for it in "Help"
elseif tbl3cnt 0 then
msgbox "This time is already in use in Table 3.
Select another time."
cancel = true
endif

6) In the form for table 2 you would do exactly the same thing except
that instead of checking in table 2 and 3 you would be using the query
for table 1 and 3.

7) In the form for table 3 you would do exactly the same thing except
that instead of checking in table 2 and 3 you would be using the query
for table 1 and 2.

Hope this helps-

Ron

On Fri, 24 Feb 2006 12:44:03 GMT, "SJJ via AccessMonster.com"
u16634@uwe wrote:

thank you for the response

i have three different tables and each table contail date and time fields. if
i assign one date and time then i shouldn't be able to assign the same data
on the other two tables. is it possible to do that.

Not at all easily. Ron's solution is one way; VBA code on a Form's
BeforeUpdate event is another.

The best suggestion would be to normalize your table structures,
though. If a particular date/time combination should be unique, then
there should be ONE table with this combination as the primary key.


John W. Vinson[MVP]

unfortunatly the solution u gave me is not working.

there is no such code "me.thisdate".

it is not alowing me to chance times

any ideas

thanks a lot

--
Message posted via http://www.accessmonster.com

could u be able to help me with the VBA code update please john W

--
Message posted via http://www.accessmonster.com

the me.thisdate and me.thistime should be the names of the datefield
and the time field that you have on the individual forms.

Whatever the name of the field is on whose afterupdate you are putting
the code.

Both the date and time have to have values in them in order to make the
queries work and you cannot make the test until both items have values
in them. It has to be in the event on both of the fields because you
cannot guarantee which one will be filled in first.

Ron