Help Please

  • Thread starter Thread starter SJJ via AccessMonster.com
  • Start date Start date
S

SJJ via AccessMonster.com

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.
 
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.
Click to expand...

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.
 
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.
 
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
 
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.
Click to expand...

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
 
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
 
could u be able to help me with the VBA code update please john W
Click to expand...

Well, certainly not with the information you have provided. Bear in
mind I have no idea how your table is structured, what you want to
calculate, how you want to calculate it, or what you want to do with
the value which you have calculated.

If you'll provide some background or context I'm certainly willing to
try to help. An indication of what you are *currently* doing would be
a good first step.

John W. Vinson[MVP]
 
only the me.time is working but me.date (the date field) is not working. i
cant find it on the dropdown when u type "me." i think may be because i put
the code on the date field!!
any suggestion.

thanks
 
three different forms, say frm1, frm, and frm.
each table contain date (short date) and time (e.g morning, afternoon,
evening)

i want some sort of solution which checks the date and time so there will no
duplicate date and time.
if 04/02/2006 Evening is assigned to the frm1 then i shouldn't be able to
put this date and time on the frm2 or frm3. this is my problem. i have no
idea.
 
Both Date and Time are reserved words, and you shouldn't use them for your
own purposes.

Try renaming them.
 
three different forms, say frm1, frm, and frm.
Click to expand...

Frm and frm eh? What are these form's recordsources? (and their real
names perhaps)...
each table contain date (short date) and time (e.g morning, afternoon,
evening)
Click to expand...

Fieldnames and datatypes?
i want some sort of solution which checks the date and time so there will no
duplicate date and time.
if 04/02/2006 Evening is assigned to the frm1 then i shouldn't be able to
put this date and time on the frm2 or frm3. this is my problem. i have no
idea.
Click to expand...

It appears that your table structure is incorrectly normalized, and
that this fact is at the base of your problems. Are the three forms
based on three separate tables with the same kind of data?

You can certainly do this with some VBA code - looking up the value of
the date and time fields using DLookup in the three tables - but it's
going to be slow and inefficient, and I really hate to support an
evidently badly flawed database design.

That said... you could use each Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not _
IsNull(DLookUp("[IDfield]", "[Table1]", "[Date] = #" & Me![Date] _
& "# AND [Time] = '" & Me![Time]) _
Or Not _
IsNull(DLookUp("[IDfield]", "[Table2]", "[Date] = #" & Me![Date]) _
& "# AND [Time] = '" & Me![Time]) _
Or Not _
IsNull(DLookUp("[IDfield]", "[Table3]", "[Date] = #" & Me![Date]) _
& "# AND [Time] = '" & Me![Time]) Then
MsgBox "This date/time slot is taken"
Cancel = True
End If
End Sub

But I'd really suggest fixing your table structure (and, maybe, as has
been repeatedly suggested, sharing it with us so we can help).

John W. Vinson[MVP]
 
sorry about that, ok this is what exactly i have

frmrarebooking, frmregularbooking and frmindividualbooking

frmrarebooking : recordsource is tblrarebooking
frmregularbooking : recordsource is tblregularbooking
frmindividualbooking: recordsource is tblindividualbooking

tblrarebooking : date (date/time, shortdate) date is the primary key
: time(number, because i have an option group
on my form)
example: morning = 1, afternoon = 2 and so
on

tblregularbooking : date (date/time, shortdate) date is the primary key
: time (text)

tblindividualbooking : date (date/time field type) date is the primary key
: time (number, coz i have option
group)

i hope this explain more about the structures.

thank you
 
sorry about that, ok this is what exactly i have

frmrarebooking, frmregularbooking and frmindividualbooking

frmrarebooking : recordsource is tblrarebooking
frmregularbooking : recordsource is tblregularbooking
frmindividualbooking: recordsource is tblindividualbooking
Click to expand...

I would REALLY suggest having *one* table - Booking - with an
additional field BookingType, with values "rare", "regular",
"individual".
tblrarebooking : date (date/time, shortdate) date is the primary key
: time(number, because i have an option group
on my form) example: morning = 1, afternoon = 2 and so
on
Click to expand...

If [Date] is the primary key then you CANNOT have two records for the
same date (i.e. one in the morning, one in the afternoon) - the
primary key must be unique.
tblregularbooking : date (date/time, shortdate) date is the primary key
: time (text)
Click to expand...
tblindividualbooking : date (date/time field type) date is the primary key
: time (number, coz i have option
group)
Click to expand...

Why is the time a number in two tables, and text in the third!?
i hope this explain more about the structures.
Click to expand...

I would suggest instead a single table design:

Bookings
BookingDate Date/Time <Primary Key>
BookingTime Number <Primary Key>
BookingType Text
<other fields as needed>

You can create a two-field Primary Key by ctrl-clicking the two (or
three, or ten) fields and clicking the Key icon. Doing so will prevent
you from entering the same pair of values, but allow either one to be
duplicated (i.e. so you could have a morning and an afternoon booking
on 3/8/06, or morning bookings on 3/8 and 3/9, but you could not have
two records both with 3/8 and morning selected).

If you want three forms, you can certainly still do so - just base the
Regular form on a Query selecting Regular as the BookingType, and
having that field default to Regular.

John W. Vinson[MVP]
 
the trouble is that i have certain condition for the regular book

i am given few events:
event 1 has to me on mondays and wednesdays morning (every week)
event2 has to be on tuesdays only (everyweek)
event3 has to be 2nd fridays of every months and so on.

i can't do them on the same table and i tried it but is not working at all?

do u have ideas

thanks a lot
 
how can i modify the code that u provided to me, putting my table names and
values
i tried it but keep getting some sort of error message.

If Not _
IsNull(DLookUp("[IDfield]", "[Table1]", "[Date] = #" & Me![Date] _
& "# AND [Time] = '" & Me![Time]) _
Or Not _
IsNull(DLookUp("[IDfield]", "[Table2]", "[Date] = #" & Me![Date]) _
& "# AND [Time] = '" & Me![Time]) _
Or Not _
IsNull(DLookUp("[IDfield]", "[Table3]", "[Date] = #" & Me![Date]) _
& "# AND [Time] = '" & Me![Time]) Then
MsgBox "This date/time slot is taken"
Cancel = True
End If
 
Times need to be delimited with # as well.

However, you may want to reconsider your design. The Date datatype in Access
is intended to be a timestamp: to hold a Date AND Time. If you have occasion
to require only one or the other, you can use the DateValue or TimeValue
functions to extract the appropriate part.

As well (apologies if you've already heard this, but I haven't seen the rest
of the thread), you really shouldn't name table fields or form controls or
VBA variables Date or Time: those are reserved words, and you can get
yourself in trouble using them for your own objects.
 
Back
Top