Help Needed PLEASE

[AcessHelp89]

Hey Guys
I created a form for scheduling appointments on access 2013 using a youtube tutorial. I got most of it work apart from the combo box that functions in a way that once the time slot has booked with a client it should no longer be shown in the list.
Eg:-2/2/2014 10:00AM is already booked with a client
the combo box should no longer display the 10:00 AM timeslot but can be shown on other days


Below is the VBA code that i typed out.
Could anyone help me create the code to make the combo box work in that manner.
It is part of a project i am doing and is due in very soon. can any of you guys help me out it would be much apreciated guys

Option Compare Database


Private Sub cboTime_Enter()
Dim i As Date, n As Integer, oRS As DAO.Recordset, sSQL As String
Dim dLowerbreak As Date, dUpperBreak As Date, dDuration As Date
Dim dLowerPrecision As Date, dUpperPrecision As Date
cboTime.RowSourceType = "Value List"
cboTime.RowSource = ""
If IsNull(Start) Then Exit Sub Else i = Start
If Me.NewRecord = True Then
DoCmd.RunCommand acCmdSaveRecord
End If
sSQL = "SELECT DoctorsID, AppointDate, AppointTime"
sSQL = sSQL & " FROM qrySubformAppoints"
sSQL = sSQL & " WHERE DoctorsID= " & Me.ID & _
" AND AppointDate=#" & Me.txtAppointDate & "#"
Set oRS = CurrentDb.OpenRecordset(sSQL)

dDuration = TimeValue("00:30")
dLowerbreak = Break - TimeValue("00:25") 'Break is a field
dUpperBreak = Break + TimeValue("00:25")

If oRS.RecordCount = 0 Then
Do
If i <= dLowerbreak Or i >= dUpperBreak Then
cboTime.AddItem i
End If
i = i + dDuration
Loop Until i >= txtEnd
Else
Do
If i <= dLowerbreak Or i >= dUpperBreak Then
dLowerPrecision = i - TimeValue("00:00:05")
dUpperPrecision = i + TimeValue("00:00:05")
oRS.FindFirst "[AppointTime] Between #" & dLowerPrecision & "# And #" & dUpperPrecision & "#"
If oRS.NoMatch Then cboTime.AddItem i
End If
i = i + dDuration
Loop Until i >= txtEnd
End If
oRS.Close
End Sub


Private Sub cboTime_AfterUpdate()
subform.SetFocus
DoCmd.GoToControl "AppointTime"
DoCmd.GoToRecord , , acNewRec
subform.Form.Controls("AppointTime") = Me.cboTime
subform.Form.Controls("AppointDate") = Me.txtAppointDate
subform.Form.Controls("cboClient").SetFocus
subform.Form.Controls("cboClient").Dropdown
End Sub


Private Sub txtAppointDate_BeforeUpdate(Cancel As Integer)
If CDate(txtAppointDate) <= Date Then
MsgBox "No more new appointments on this date"
Cancel = True
End If
End Sub


I found out that the combo box does not work like it does in the video i.e the option does not dissaspear even though their has been a booking with that time slot with another client.
Can anyone help me figure out the coding that is required to make the combo box work like that or is their an error within the code that is preventing the combo box to work in such manner

Thank You

 

[AcessHelp89]

Hey Guys
Any chance of helping me out i am really struggling right now
Please help me out guys this project is due in very soon
Thanks

 

[AcessHelp89]

Hey Guys any of you guys planning on helping me out been waiting for some time to at least get a reply

 

[JHB]

Calm down - it's unpaid volunteers who work here, and not a paid Hotline.
You could also have helped people here to easier read your code by putting the code into Code Tags, (as it is now, it is a long unreadable chunk of text!).

This is Code Tags (#)

..
  If x ... Then
    do some code
  Else
    ..
  End If

Post a stripped version of your database, with some sample data in it, (zip it) and information about in which form you have the problem.

 

[AcessHelp89]

Here is the database
the part i am trying to figure out is trying to make the appointment time combo box on the main form to work in a way that the time slot is no longer shown if there is already an appointment booked with an animal

eg:2/2/2014 10:00AM is already booked with a client
the combo box should no longer display the 10:00 AM timeslot but can be shown on other days

 

[AcessHelp89]

Sorry but im kinda new to vba so i dont what you mean by putting the code into Code Tags

 

[AcessHelp89]

Hey guys have any you guys figured how i could make the combo box work in the way i described before.
I uploaded my database so that you could take a look hopefully making it easier to help me out.

 

[AcessHelp89]

Hey guys
I really need your help soon this needs to be handed in on Monday and i cant seem to figure out why the combo box does not work like the way it does in the video.

i.e when a client has been booked at 10 AM
The 10AM slot is still shown in the combo box.

In the video if a client is booked at 10AM that time slot is no longer available. It is hidden so that the user can not select that time slot again preventing any chance of error.

How would i go about making the combo box work like the way it does in the video

Please Reply Soon In Need Of Urgent Help

 

[AcessHelp89]

Hey Guys
Any chance of those who have viewed my database could help me figure out the problem i am facing it will much appreciated if you can post it in this thread so I can take a look at the changes you have made

 

[JHB]

eg:2/2/2014 10:00AM is already booked with a client
the combo box should no longer display the 10:00 AM timeslot but can be shown on other days

There isn't any appointment for the date and time you mention in your database.
Add one and you'll see it works okay, look at the second picture.
To post code in code tags click the "#" sign before you insert any code.

 

[AcessHelp89]

Hey JHB
thats a bit of weird because when i try to do that it doesnt seem to work for me.
Do you have any idea why or a way to find out what is causing it ?
Please help me
this is due in for tomorrow and in need of desperate help
Please JHB

 

[AcessHelp89]

As you can see the time slot is still visible unlike the combo box in your picture
Please Help Me Out Guys

 

[JHB]

Is it school homework, isn't it?

In your reply to me you said nothing about your misinformation regarding. 02/02/2014, a small excuse could have been in place.
Now you yelled and asked for help for 2 day - the time you could have spent different, for example. to check that the date you mentioned was entered, had you done that, you have found out that it works okay.

Now I will give you the last help because you have to do some work yourself. If swapping the month and day and you'll also seen that it works.
So when you register a date for example. on January 7, 2014 at. 10 o'clock, you'll see that on July 1, 2014 at. 10 o'clock, (the time is not available).
The problem you've is related to the below description about US-date format.

From MS-Access Help-file:
You must use English (United States) date formats in SQL statements (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.) in Visual Basic. However, you can use international date formats in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.).

 

[AcessHelp89]

JHB
You life saver thanks for that
sorry but the e.g. i gave was made up just to help you understand the problem i was facing
and you where able to solve my problem.
Just one quick question how would I change it so that the format is in the UK format.
This is the last problem I am facing it would be grateful if you could help me out but I understand if you don't as you have helped me out a lot already
Thank You So Much
JHB

 

[AcessHelp89]

Hey JHB
Just wandering if you could help me out
I did not quite understand how to change the format.
Could you help me guide me through the process of changing the format.
Please help me JHB
It's the final problem I am facing

 

[JHB]

Read this how you change dates to US-format.

http://www.google.dk/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&cad=rja&ved=0CFsQFjAD&url=http%3A%2F%2Fallenbrowne.com%2Fser-36.html&ei=yJrLUsvwKrLc4QSpl4GwCg&usg=AFQjCNH6LNBnUMuOM7ePHvzsi-cVzyX-Jg&bvm=bv.58187178,d.bGE