A simple(?) Appointments db... 1

[sagamw]

I am over my head here, trying to build a simple appointment db.
I downloaded some examples (with Calendars) from the net and I realize that is a very difficult task.
(I read the "Create your own calendar" faq702-838)
I decided that you don't really need a calendar (Outlook style) if you just need to note appointments, so I came up with another design:

I read faq702-681 & faq702-4246 about dual listboxes and filter the one based on the other BUT I can't really get it...

The key points is:
(1) To have a single date in the 1st listbox even if there are more appointments in that day. (dates with no appointments of course shouldn't appear)

(2) When you select the date the 2nd listbox to show only the appointments (time) of the date selected in the 1st listbox.

(3) When you select the time, the Record with the appointment to appear.

Any help, or even better a ready example will be appreciated!

 

[sagamw]

Well!
I am ALMOST there!
If I got a more specific problem I will seek for help...

 

[sagamw]

Grr!
I am getting some weird results...

What I have is:
- A table like the one in the image in my previous post.
- 2 list boxes
- the 1st has 3 columns (a) the date (b) a function for the day of the week (because I can't change the format of the date to my 1st column) (c) a function for counting the number of appointments in that day (it worked at start but now I am getting some zeros even when there are appointments)

- the 2nd has 2 columns (a) the time of the day (b) the name/appointment

My problems so far
- I can't get the "criteria" in the query design to work as expected.
- Without a reason the dates keep changing(??!) by them selfs. After I while I have ...the same date for all appointments (that's weird)
- I can't get a record/appointment to show then I choose the time

...anyway, if anyone can spare a few minutes to make a simple database that works like the image above (1st listbox to filter the 2nd, and the 2nd to select/show the record) it will make my very happy...

P.S. I use MS Access 2007

 

[dhookom]

It would help if you provided more information about your controls such as Names, Control Sources, Row Sources, Column Counts, Bound Columns, Column Widths ...

I would make sure all of the controls on the form were unbound (no control source). Create a subform to display a single scheduled event. Use the Link Master/Child to bound column of the time list box to the ID field of the subform.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I should have also mentioned Date and Time are not good field names since they are function names. I don't like ID either since it doesn't do anything to describe its contents. If I created this table, my names would be:
[tt]
tblAppointments
====================
appAppID autonumber primary key
appDate date of the appointment
appStartTime time of the appointment (you may want end time in the future)
appAppointment person meeting
appStatus I almost always add a status field
appNotes I almost always add notes/comments
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, just to know, why 2 fields (appDate, appStartTime) for a single value (DateTime of the appointment) ?

 

[dhookom]

I feel someone self described as "over my head" would have a more difficult time parsing out the date and time values to display as desired in list boxes. I thought it would be better to leave the split date and time to focus on their questions.

Duane
Hook'D on Access
MS Access MVP

 

[sagamw]

Thank you for your answer.
In the meantime I managed to build my db as described in my 1st post!
( I don't need any "status" or "end time" fields for my purpose)

It works fine (although I found some SERIOUS BUGS in ms Access, fortunately with their workarounds) for example: I added a dcount function to show the number of appointments in a specific date and the (idiotic) came up with a weird bug. If the date is like 28/10/2009 everything worked FINE. If the date is of one of the first 12 days of the month ...IT REVERSE IT! i.e. 8/12/2009 (december) stands for 12/8/2009 !!!!!
I was keep getting 0 (none) for appointments for all all the days (1-12) of EVERY month, so I figured out! My solution was to transform the date to another column of my query with an iif day<13 condition.

I am almost done but I have some problems to refresh my listboxes. (.Requery doesn't suffice)

I'll be back with more infos...

 

[PHV]

JetSQL consider ambiguous date to be in the mm/dd/yyyy format.
To avoid this american behaviour, use a non ambiguous date format like the ANSI one: yyyy-mm-dd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I'm not sure why you even need to use DCount(). I would expect the Row Source of your first list box to be something like:

SELECT [Date], Count(*) as NumOf
FROM tblAppointments
GROUP BY [Date]
ORDER BY [Date];

Duane
Hook'D on Access
MS Access MVP

 

[sagamw]

Thanks for your suggestions but because my db is working fine I don't want to mess with that part again.

My problem now is that my 2 list boxes DON'T UPDATE when I make some specific changes i.e. (1) Delete a single record/appointment (2) Running a Delete query to delete "past records/appointments" (before today that is)

They update instantaneously when I create a new appointment, though.

I tried .requery but doesn't help. i.e. for the button of my delete query

Private sub ??????_Click()

DoCmd.Openquery "mydelquery"
   Me.LBdate.Requery
   Me.LBhour.Requery

End sub

(where LBdate and LBhour is my 2 listboxes)

Any help for a WORKING UPDATE subroutine will be appriciated!\

Also I get some annoying prompts ("Do you want to delete etc."). I tried some "DoCmd.SetWarnings False" etc. but I still get them. Later I found out that Access 2007 has a setting for this but I had some (weird) problems and I gave up this approach.

 

[dhookom]

This should work as long as you have set the option when openning to allow code:

Private sub ??????_Click()
   DoCmd.SetWarnings False
   DoCmd.Openquery "mydelquery"
   DoCmd.SetWarnings True
   Me.LBdate.Requery
   Me.LBhour.Requery
   DoCmd.SetWarnings True
End sub

Of course you still haven't provided the row sources or any other properties.

Duane
Hook'D on Access
MS Access MVP

 

[sagamw]

Thanks dhookom! 1st time I tried it , it didn't work but I gave it another try and ...it works, (so I give you a star for ...making me try again!)

I did the same with a DoCmd.RunCommand acCmdDeleteRecord
in another button for deleting the current record.

My trouble are not over though, because I have the same problem (the 2 Listboxes not refreshing) when I update (ie change the date or the time or the person) a field of an existing record.

I tried...

Private sub ??????????_AfterUpdate()
   Me.LBdate.Requery
   Me.LBhour.Requery
End sub

(where ?????????? is the date of the appointment)

...but doesn't work

You asked me about my rowsources:
I hope the foreign letters doesn't confuse you.
The 1st list box (Dates) has 3 columnes date,name of the day, and how many appointments in that day

SELECT DISTINCT [???????? ???????].??????????, [???????? ???????].RVD, [???????? ???????].RVcount
FROM [???????? ???????]
ORDER BY [???????? ???????].??????????;


Where
[???????? ???????] is the query derived from the table of my appointments
?????????? is the date
RVD is a parameter I defined for the name of the day, because (ANOTHER BUG?) the function Weekdayname DOESN'T WORK in my local access 2007
RVcount is the Dcount result (with the bug I circumvented)

The 2nd list box has 2 columns the time and the person, and I wrote this code for filling it with the appointments of that day and for select and show the 1st record.

Private Sub LBdate_AfterUpdate()
    Me.LBhour.RowSource = "SELECT [???????? ???????].???????????, [???????? ???????].?????????, [???????? ???????].??????????? FROM [???????? ???????] WHERE [???????? ???????].??????????2 = #" & _
    Me.LBdate & "# ORDER BY ?????????"
    Me.LBhour = Me.LBhour.ItemData(0)
   Dim rs2 As DAO.Recordset
        Set rs2 = Me.RecordsetClone
        rs2.FindFirst ("??????????? = " & Me.LBhour)
    If Not rs2.EOF Then Me.Bookmark = rs2.Bookmark
    
       
End Sub

Where 
??????????2 is a parameter derived from ?????????? (the date of the appointment) to cope withe reverse date bug I mention earlier
????????? is the time
??????????? is the person

So. I have this form with 2 list boxes (LBdate and LBhour) and 3 fields for the user to input data ?????????? (date) ????????? (Time) ??????????? (person) and some buttons.

The .requery (Refresh my listboxes) now works for adding and deleting (1 or many) records BUT DOESN'T WORK when the user changes any from the 3 fields...

Any help will be appreciated!

 

[sagamw]

Well. I think I did it!
With a combination of requery, refresh, and bookmark.

But I am looking forward for any suggestions.
I am not a programmer so I'm sure my code/approach it's not "optimized"

 

[dhookom]

I would replace your query with the DCount with my totals query for the dates and counts in the date list box.

I would have also used a subform with Link Master/Child which would get rid of the recordset code.

Since your local uses different date formats, you might want to read Allen Browne's Access Tips which includes information on international dates, bugs, and a lot more.

Duane
Hook'D on Access
MS Access MVP