Add a simple date picker to an existing form (1 Viewer)

8

83dons

Registered User.
Local time
Today, 03:00
Joined
Jan 11, 2010
Messages
19
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:00
Joined
May 7, 2009
Messages
19,233
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:00
Joined
May 7, 2009
Messages
19,233
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.

Code: 
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
 
Last edited:
8

83dons

Registered User.
Local time
Today, 03:00
Joined
Jan 11, 2010
Messages
19
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?

Code: 
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

moke123

AWF VIP
Local time
Today, 06:00
Joined
Jan 11, 2013
Messages
3,912
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.
 
8

83dons

Registered User.
Local time
Today, 03:00
Joined
Jan 11, 2010
Messages
19
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

Minty

AWF VIP
Local time
Today, 11:00
Joined
Jul 26, 2013
Messages
10,368
You need square brackets [ ] around [Date of Birth] not ' ' .
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:00
Joined
May 7, 2009
Messages
19,233
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)
Code: 
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
 
A

Acropolis

Registered User.
Local time
Today, 11:00
Joined
Feb 18, 2013
Messages
182
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.
 
8

83dons

Registered User.
Local time
Today, 03:00
Joined
Jan 11, 2010
Messages
19
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).
 
8

83dons

Registered User.
Local time
Today, 03:00
Joined
Jan 11, 2010
Messages
19
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

Minty

AWF VIP
Local time
Today, 11:00
Joined
Jul 26, 2013
Messages
10,368
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 ;

Code: 
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?
 
8

83dons

Registered User.
Local time
Today, 03:00
Joined
Jan 11, 2010
Messages
19
arnelgp said:
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.

Code: 
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
Click to expand...

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

Minty

AWF VIP
Local time
Today, 11:00
Joined
Jul 26, 2013
Messages
10,368
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.
 
8

83dons

Registered User.
Local time
Today, 03:00
Joined
Jan 11, 2010
Messages
19
No its Arne's code.

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:00
Joined
May 7, 2009
Messages
19,233
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.
 
8

83dons

Registered User.
Local time
Today, 03:00
Joined
Jan 11, 2010
Messages
19
arnelgp said:
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.
Click to expand...

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?
 
You must log in or register to reply here.

Similar threads

M
Solved Date Picker value depending on another control
Replies
7
Views
709
mafhobb
M
H
convert age to date of birth in three fields: year, month, and day.
Replies
3
Views
482
Pat Hartman
P
R
Keep the Date picker open on a form
Replies
2
Views
388
MajP
MajP
M
Call Excel-Style color picker from Access
Replies
6
Views
534
Marshall Brooks
M
M
Popup notification for due date..
Replies
14
Views
797
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom