Add a simple date picker to an existing form

[83dons]

I have created a very basic form that lists all the fields and you can also scroll through the entries in the table. This was done simply by highlighting the table, right clicking and going to Create > Forms > Form.

What I require though is to copy this form but add in the feature that when you open it a popup box appears first asking you to pick a date of birth to search on. Then for only rows with that date of birth to be returned from the source table. I am sure this is simple but I just can't find how to do it most easily!

Any help appreciated.

 

[arnelgp]

on design view of your form,
format the textbox as any
of the Date format and the
datepicker will automatically
show when the control is in
focus.

 

[arnelgp]

sorry, wrong mistake on my first post.
i haven't read anything except the title.

you need to create a pop-up form first.
add one unbound textbox and a commandbutton.
call the textbox txtDateOfBirth and format is
as any of date format (short date, etc.)

name the command button (cmdSearch).
set its caption to "Search".

on the Click Event of the cmdSearch button,
first verify if a record can be returned.

Private Sub cmdSearch_Click()
If DCount("*", "yourTable", "DateOfBirthField=#" & Format("0" & Me.txtDateOfBirth,"mm/dd/yyyy") & "#")<>0 Then
	' open the form that you have and add the criteria
	DoCmd.OpenForm FormName:="theFormName", WhereCondition:="DateOfBirthField=#" & Format(Me.txtDateOfBirth,"mm/dd/yyyy") & "#"
	' close this pop-form
	DoCmd.Close acForm, Me.Name
Else
	Msgbox "No date of birth of this date found on the table"
End If

 

[83dons]

Hi I am almost there but at the moment every search seems to provide the no results warning message. So the query is not picking the rows as expected from the original table. Any ideas where I might be going wrong?

Private Sub cmdSearch_Click()
If DCount("*", "WICS - IDENTITY", "'DATE OF BIRTH'=#" & Format("0" & Me.txtDateOfBirth, "dd/mm/yyyy") & "#") <> 0 Then
    ' open the form that you have and add the criteria
    DoCmd.OpenForm FormName:="001_All-Clients", WhereCondition:="'DATE OF BIRTH'=#" & Format(Me.txtDateOfBirth, "dd/mm/yyyy") & "#"
    ' close this pop-form
    DoCmd.Close acForm, Me.Name
Else
    MsgBox "No date of birth of this date found on the table"
End If
End Sub

 

[moke123]

Why not put a combobox in the header of the form and set its rowsource to something like "Select Distinct DateOfBirth from YourTableName"

This will list all the distinct dates of birth and will only show DOB's that are in the database. Use the not in list event if there are no matching dates.

You should also consider removing the spaces and special characters from your table and field names.

 

[83dons]

I don't want to go in and edit the column names in the original database although I am aware spaces can be tricky. Any idea how to get the query working as expected? I had to put ' around the DATE OF BIRTH field or else it was throwing up other error messages.

 

[Minty]

You need square brackets [ ] around [Date of Birth] not ' ' .

 

[arnelgp]

firstly you need to put brackets
on your tablename and field name because
they contain spaces and - sign.

second, use US date format (mm/dd/yyyy)

If DCount("*","[WICS - IDENTITY]", "[DATE OF BIRTH]=#" & Format("0" & Me.txtDateOfBirth, "mm/dd/yyyy") & "#") <> 0 Then
	DoCmd.OpenForm FormName:="001_All-Clients", WhereCondition:="[DATE OF BIRTH]=#" & Format(Me.txtDateOfBirth, "mm/dd/yyyy") & "#"
	DoCmd.Close acForm, Me.Name
Else
	Msgbox "No date of birth of this date found on the table"
End If

 

[Acropolis]

In an OnOpen event for the form, add a bit of code that captures the date you want to look at from an input box, add some checks to make sure that a date has been entered, i would then check to see if there are any records with that from using a count, if there aren't give the user option to close form or try and other date.

if there are records for that date, then assign the date to a TempVar and open the form. In the form query, on the date field just add as a criteria the name of the TempVar you are using.

 

[83dons]

Thanks for your help. Yes the square brackets was the answer around the field names in particular.

Also changing the date format to american mm/dd/yyyy also helped as I initially has swap round the day and month in the query text box to get it to work (despite the source database using dd/mm/yyyy and the form itself which seemed odd to me).

 

[83dons]

The only thing now is that when i paste in a UK date format to the text box txtDateOfBirth then when I click the calendar icon it doesnt take you to the correct DOB it is still stuck on todays date. If paste it in then press return the calendar icon vanishes and when I click back in the text box it reappears. It then when pressed shows the correct date in calendar format. ANy way of tidiying this up using code or to just remove the calendar option (icon) entirely and just have the user copy and paste the date in to the text box and press the control button?

 

[Minty]

Have a read here about dates in Access http://allenbrowne.com/ser-36.html

Your initial format command is adding a leading 0 to the date for some reason ;

If DCount("*","[WICS - IDENTITY]", "[DATE OF BIRTH]=#" & Format([COLOR="Red"]"0" &[/COLOR] Me.txtDateOfBirth, "mm/dd/yyyy") & "#") <> 0 Then

I would remove that if it's still in there?

 

[83dons]

sorry, wrong mistake on my first post.
i haven't read anything except the title.

you need to create a pop-up form first.
add one unbound textbox and a commandbutton.
call the textbox txtDateOfBirth and format is
as any of date format (short date, etc.)

name the command button (cmdSearch).
set its caption to "Search".

on the Click Event of the cmdSearch button,
first verify if a record can be returned.

Private Sub cmdSearch_Click()
If DCount("*", "yourTable", "DateOfBirthField=#" & Format("0" & Me.txtDateOfBirth,"mm/dd/yyyy") & "#")<>0 Then
	' open the form that you have and add the criteria
	DoCmd.OpenForm FormName:="theFormName", WhereCondition:="DateOfBirthField=#" & Format(Me.txtDateOfBirth,"mm/dd/yyyy") & "#"
	' close this pop-form
	DoCmd.Close acForm, Me.Name
Else
	Msgbox "No date of birth of this date found on the table"
End If

Hi is there a reason why you are adding the initial zero to the string as the previous comment asked?

 

[Minty]

Arne (who posted the code) is off line at the moment - but have you simply tried removing it?

However I thought that the original code was one you have posted up, and Arne had just copied it.

 

[83dons]

No its Arne's code.

I have tried removing it and it works with or without the zero seemingly.

 

[isladogs]

If you want to consider an alternative, I have a very simple but effective date picker form. You can download it here:

https://www.access-programmers.co.uk/forums/showpost.php?p=1533910&postcount=2

 

[arnelgp]

Hello it will work without 0. But if txtxDateofbirth is blank you will have a runtime error there. So you see 0 there is intentional.

 

[83dons]

Hello it will work without 0. But if txtxDateofbirth is blank you will have a runtime error there. So you see 0 there is intentional.

I have now tried it with and without and I get runtime error either way so I don't think it makes much difference if it is there or not?