Help With Access Project Please!!

[lee_cufc]

Hi All,

I am developing a booking system for my final University project and have hit a stumbling block which I hope someone can help me with.

Basically I have designed a daily appointment sheet with timeslots on, the is called ‘DiaryViewForm’, I have a form which has a pop-up calendar on to allow the appointment date to be selected, this is called ‘DateSelectionForm’

I need my programme to open an instance of ‘DiaryViewForm’ based on the value (i.e. the date) selected by the user on the Calendar on the form ‘DateSelectionForm’

Any ideas about how I should go about this? If I can crack this problem I’m almost there with my project so any help is most appreciated!

p.s I am developing in MS Access 2007

Thanks
Lee

 

[pbaldy]

This type of thing?

http://www.baldyweb.com/wherecondition.htm

 

[lee_cufc]

Any ideas how this would be applied to my project?
Sorry im not the best/most proficient at VBA

Thanks so much

 

[lee_cufc]

I have now adopted a slightly different approach but have run into further problems and I wondered if anyone could help me out.

I have a form with a ComboBox on which allows a date to be selected from a calendar this value is then returned to the combo box.

I have a subform on here which has a form I designed with various coloumns on it which are time slots in a booking diary.

I need my program to open the correct record in the subform based on the date selected in the combobox on the main form, any ideas of the commands I use to sort this??

Any help would be awesome
Thanks
Lee

 

[pbaldy]

Try

Me.SubFormName.Form.RecordSource = "SELECT * FROM TableName WHERE DateFieldName = #" & Me.ComboName & "#"

 

[lee_cufc]

Paul,

This translates to the following in terms of my project:

Me.Appointments.Form.RecordSource = "SELECT * FROM Diary_View_Table WHERE DateOfAppointment = #" & Me.cboDate & "#"

I have tried running this and get the following error:

'The record source 'SELECT * FROM Diary_View_Table WHERE DateOfAppointment = #09/04/2009#' specified on this form or report does not exist

Im confused, sorry i really am a major beginner at this!

Lee

 

[pbaldy]

Can you post the db?

 

[lee_cufc]

Sure, the form im working on is called 'TEST'
any advice you have would be brilliant.

I should also say that as i said i need it to search for the record and display it in the subform but if that record for that date doesnt yet exist then i need it to create that record

Thank-you
Lee

 

[pbaldy]

Try this, as you have the name of the table wrong:

Me.Appointments.Form.RecordSource = "SELECT * FROM [Diary View Table] WHERE DateOfAppointment = #" & Me.cboDate & "#"

I got an error with your custom calendar, but this seemed to work:

Me.Appointments.Form.RecordSource = "SELECT * FROM [Diary View Table] WHERE DateOfAppointment = #" & Me.txtDateOfAppointment & "#"

 

[lee_cufc]

Thanks for the advice Paul, I have tried it but still without any luck, I have changed my form slightly again.

I have uploaded another version of the database, on it the form in question is called ‘NEW’ there is a textbox on there called ‘txtDateOfAppointment’ I currently have 6 records in the system (you can see these by using the built in access record controls at the bottom of the form) but what I need the program to do is to allow a date selection in ‘txtDateOfAppointment’ and then: a) search through the records and display the one matching that date if it exists, and b) if it doesn’t exist create this record.

I have tried all sorts of variations of the Docmd.findrecord commands etc but with no luck I cannot get the form to jump to a different record based on the value in ‘txtDateOfAppointment’

Any help at all would be so much appreciated, this if for a college project that I am running out of time on L

Thank-you
Lee