Oh I understand now (I think)
The way I would do thing is a lot simpler than you seem to have arrived at.
Can you do this - create a new table (if you don't have one already). In
this new table should be the appointments. Make sure the new table has a
relationship with the Reps.
Create a new continous form from the new appointments table and make it a
subform of the Rep's form.
Link the Reps ID on both forms.
Next create another new form based on the appointment table (with the
appointment id on somewhere).
Make sure Record Selector is enabled on the subform and put this on click.
Private Sub Form_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "NewAppointmentFormName"
stLinkCriteria = "[AppointmentID]=" & Me![AppointmentID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
Now you will be able to select any Rep and then from the subform select and
then edit an appoint.
This said I may, again, have misunderstood what you're trying do
--
Wayne
Manchester, England.
Can i just give you some more detail to what im trying to do...
Ive made a flexgrid that shows appointments for staff.. as so...
Date: 11/04/07
Time Staff 1 Staff 2 Staff 3
0900 Customer1 Customer 2
0915 Customer1 Customer 2
0930 Customer3 Customer 2
This all works great and we can see who is supposed to be where...
I would like to now make it edit an appointment when I click on an
appointment...
I can determine the time and staff member easy enough but to call up the
correct record, I need to find out the ID of the Appointment, the easiest
way is for me to do a select query on the appointments that matches the
Date, the StaffMember and the start/end times.
I can do everything apart from the times. As you can see from the lits,
staff 1's appointment is from 09:00:00 - 09:30:00. When I click in the
flexgrid, the best i can return is the time at the position clicked, so for
example, 09:15:00.
So I need to determine which record has a starttime before that 09:15:00 and
an endtime that is after 09:15:00.
So basically i need to know how to correctly format the following
query/code...
Dim vRow As Long, vCol As Long, vDay As Long
Dim vDate As Date
Dim vText As String
Dim rst As Recordset
Dim q As String
Dim vTime as String
FlexGridClick flxDates, vRow, vCol, vText
'return Row & Col (or whatever else)
vDate = 0
'set vDate to 0
'if DAY mode
vDate = txtToday
'set todays date
vTime = "09:15:00" 'sample data, real time would come from a click
when ive coded it
If vDate <> 0 Then
q = "SELECT * FROM Appointments WHERE StaffMember = " &
currentstaffmember & " AND AppDate=#" & vDate & "# AND ((AppStartTime <=" &
vTime & ") AND (AppEndTime >=" & vTime & "))"
Set rst = CurrentDb.OpenRecordset(q)
Do Until rst.EOF
MsgBox ("'" & rst!AppStartTime & "'")
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End If
Wayne-I-M said:
If it was me doing this - my users don't like things too complecated - I
would put 3 unbound controls on a form. Date - Start time - End time.
Date would be a text box with an input mask
the other 2 would be combos with 1am;2am;etc, up 10pm;11pm, etc
I would use the QBF I gave you to get the date. Then use the other 2 to
get
the start and end time for the query.
To make it even simpler you may want to add 2 calculated columns to the
query - start time and end time for them like this
StartTime:Format([Table1]![DateTimeField],"h AM/PM")
EndTime:Format([Table1]![DateTimeField],"h AM/PM")
Then use the criteria row like this
Forms![FormName]![txtStartTime]
Forms![FormName]![txtEndTime]
To make it better you could put soething liek this on the form
If not date start end is null then
Open something
else
msgbox
you must put in the date and times
end if
Of course you need to put this in vba - no time at the mo - VERY busy
Good luck
--
Wayne
Manchester, England.
Craig Armitage said:
The Datatype in the database is Date/Time and if just run a standard
query
to get a time from the database its formatted as '09:00:00'
Wayne-I-M said:
Hi Criag
This is more difficult as it depends on how you have stored your
time/date
If you have a standard access time/date field you can format it as
Medimum
time like this
SELECT Format([TableName]![DateTimeField],"Medium Time") AS StartTime
FROM TableName;
SELECT Format([TableName]![DateTimeField],"Medium Time") AS EndTime
FROM TableName;
and then use a prompt or (better) use an unbound dropdown with times to
and
end thjen use this is this basis for the QBF.
As I said not sure how you have stored your times - if it's text then
the
above will not work but a very simple 1:20,2:45,3:12,4:15,5:35,etc
would
as a
prompt.
Let us know if your still having problems but you will need to give
information about the fields in the table (format) before anyone could
give
you an answer
--
Wayne
Manchester, England.
:
Hi Wayne,
Thanks thats sorted part of the problem, it now finds the date
correctly..
Im doing this in VBCode so I was wondering if you could help with the
other
part of the problem... The times..
I assumed from the previous example you gave that the following would
work
but sadly it doesnt..
SELECT * FROM Appointments WHERE AppDate=#17/04/07# AND ((AppStartTime
<=
#09:00:00#) AND (AppEndTime => #10:00:00#))
The idea is to return records that return records that fall over a
selected
time.
Hope you can see what im doing wrong!
Just re-read your post - if you want to use the original change it
to
this
SELECT Appointments.AppDate
FROM Appointments
WHERE (((Appointments.AppDate)=#4/17/2007#));
--
Wayne
Manchester, England.
:
Hi,
I have a time-slot grid that I want to be able to click on and have
access
find out if there are any records based around that slot..
so basically,
I have a varDate and a varTime and I need to use a query to query
against
my
databases StartTime, EndTime, and AppDate variables..
in psuedocode i need to find the result of
WHERE varDate = AppDate AND (StartTime <= varTime AND EndTime >=
varTime)
Ive never done a search using dates before. I was wondering if the
formatting of the dates is important?
I did do a basic query that seem to fail when just searching for
dates..
the query was
SELECT * FROM Appointments WHERE AppDate=17/04/07
I know I have records with dates on that date but it returned no
results!
Please Help
Was this post helpful to you?
Why should I rate a post?
