Forms and Records Help Please

[lee_cufc]

Can anyone help me? I have an access 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 :-(

Thank-you
Lee

 

[BruceM]

Is this an assignment in an Access class, or are you trying to use Access
for another reason? If the first, I have to wonder why there are not
resources available to help with this sort of thing.

In any case, you need to clarify just what you are trying to do. For
instance, what if there is more than one record with the selected date?

How are you attempting to apply the code for DoCmd.FindRecord?

 

[lee_cufc]

Bruce,

Sorry that i was not clearer i have a very basic knowledge of all of this and
so i often get easily confused!

The date of appointment field is unique and so there should only ever be one
'record' with this date, i need to select a date in a text box with a date
picker and then for it to search through and display the record that matches
this date, if there is no match i need it to create this record based on this
date.

I hope this helps? maybe i havent grasped what u were trying to work out
though, i am happy to send a copy of my DB if you or anyone could spare a
little time to help me out, i would be most appreciative, my email address is
(e-mail address removed) if anyone can help out,

Thanks
Lee

Is this an assignment in an Access class, or are you trying to use Access
for another reason? If the first, I have to wonder why there are not
resources available to help with this sort of thing.

In any case, you need to clarify just what you are trying to do. For
instance, what if there is more than one record with the selected date?

How are you attempting to apply the code for DoCmd.FindRecord?

Can anyone help me? I have an access database, on it the form in question
is

[quoted text clipped - 19 lines]

Thank-you
Lee

 

[BruceM]

Try using the combo box wizard to get started. Open the toolbox, make sure
the magic wand icon is highlighted, click the combo box icon, and click the
form. Follow the prompts to have the combo box look up a record based on
what you select. Select table or query as the row source, and use the
AppointmentDate field from the current table as the row source for the combo
box. Switch to form view. Select a date from the combo box. This should
take you to the record with that date. It will not do anything if there is
no record with the date you selected, but one thing at a time. Post back
with the results of this experiment.

Bruce,

Sorry that i was not clearer i have a very basic knowledge of all of this
and
so i often get easily confused!

The date of appointment field is unique and so there should only ever be
one
'record' with this date, i need to select a date in a text box with a date
picker and then for it to search through and display the record that
matches
this date, if there is no match i need it to create this record based on
this
date.

I hope this helps? maybe i havent grasped what u were trying to work out
though, i am happy to send a copy of my DB if you or anyone could spare a
little time to help me out, i would be most appreciative, my email address
is
(e-mail address removed) if anyone can help out,

Thanks
Lee

Is this an assignment in an Access class, or are you trying to use Access
for another reason? If the first, I have to wonder why there are not
resources available to help with this sort of thing.

In any case, you need to clarify just what you are trying to do. For
instance, what if there is more than one record with the selected date?

How are you attempting to apply the code for DoCmd.FindRecord?

Can anyone help me? I have an access database, on it the form in
question
is

[quoted text clipped - 19 lines]

Thank-you
Lee

 

[lee_cufc via AccessMonster.com]

Thank-you Bruce, i cant believe i did not think to use a combo box with a
wizard, i'd forgoten it was so simple to get it to display the record when
set up this way.

This is now progress however still creates a problem.
I have taken a screen shot of what it looks like:

IMG]http://i42.tinypic.com/20iuelg.jpg[/IMG]

As you can see in the combo box it displays the 6 records that i created as
tests, however they are in a list format, is there a way to get a date picker
calendar to appear when accessing the combo box instead of the list? this
then needs to still take me to the coresponding record based on the selection.


Then there is the problem of creating the record if it doesnt exist, but like
you say one step at a time!

Thanks so much
Lee

Try using the combo box wizard to get started. Open the toolbox, make sure
the magic wand icon is highlighted, click the combo box icon, and click the
form. Follow the prompts to have the combo box look up a record based on
what you select. Select table or query as the row source, and use the
AppointmentDate field from the current table as the row source for the combo
box. Switch to form view. Select a date from the combo box. This should
take you to the record with that date. It will not do anything if there is
no record with the date you selected, but one thing at a time. Post back
with the results of this experiment.

Bruce,

[quoted text clipped - 35 lines]

 

[BruceM]

I have not used calendar controls, but they are available for free. One
possibility is here:
http://allenbrowne.com/ser-51.html
There are links to other options at the bottom of that page. I know that
Allen Browne, who offers the option in the link, says it works only with
text boxes as it is configured. I don't know if it can be made to work in a
combo box, but I very much doubt you can have a combo box with a calendar
rather than a list. You may be able to have the calendar appear with the
"taken" dates highlighted, but as I said I have no experience with this
utility. For more specific help you would do best to start a new thread
specifically about the calendar control.

Regarding starting a new record, this code will do that:
Me.Recordset.AddNew

However, I don't know where you would put this code, as I do not know how
your database is structured.

Thank-you Bruce, i cant believe i did not think to use a combo box with a
wizard, i'd forgoten it was so simple to get it to display the record when
set up this way.

This is now progress however still creates a problem.
I have taken a screen shot of what it looks like:

IMG]http://i42.tinypic.com/20iuelg.jpg[/IMG]

As you can see in the combo box it displays the 6 records that i created
as
tests, however they are in a list format, is there a way to get a date
picker
calendar to appear when accessing the combo box instead of the list? this
then needs to still take me to the coresponding record based on the
selection.


Then there is the problem of creating the record if it doesnt exist, but
like
you say one step at a time!

Thanks so much
Lee

Try using the combo box wizard to get started. Open the toolbox, make
sure
the magic wand icon is highlighted, click the combo box icon, and click
the
form. Follow the prompts to have the combo box look up a record based on
what you select. Select table or query as the row source, and use the
AppointmentDate field from the current table as the row source for the
combo
box. Switch to form view. Select a date from the combo box. This should
take you to the record with that date. It will not do anything if there
is
no record with the date you selected, but one thing at a time. Post back
with the results of this experiment.

Bruce,

[quoted text clipped - 35 lines]

Thank-you
Lee