Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 24th, 2007, 05:46 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default Combo to Search for Record w/Calendar Object

Hiya Folks!

I have a form and subform. The form only has a "date" field in this format dd-mm-yyyy. The subform has sales details for the date in the main form.

I created a combo box in the main form that searches for the record based on the value I select. In this case, the value is a "date" value in this format: dd-mm-yyyy.

Another thing I have done is that when the user clicks on the combo box so he can search for the record with that date, the Calendar control appears so that the user can select the date that he wants from there.

The problem I am experiencing is that when the user selects the date that he wants, and the value from the Calendar object is transfered to the combo, I recieve an error message saying that the value type is not correct. I cant seem to figure out what is going on... the value type is set for the combo is "Short Date". And the field that contains the value in the table is also set as "Short Date".

Can anyone help? Thanks!!!

 
Old April 24th, 2007, 06:52 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Instead of using the calendar control, set the recourd source for the combo box to be the date field in your actual sales records. That way users can only select dates for which there is data. Be sure to set the query to Unique Values = Yes.

You shouldn't have to use the date picker here.

Did that help?

mmcdonal
 
Old April 24th, 2007, 07:31 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Hi Mmcdonal!

Thank you for your reply! I considered doing your recommendation with the combo... the only problem is when I have so many dates to pick from... for example, lets say I have 6 months of data. The user will have to search through a list 180 dates to find the one that he wants.

Thats the reason I thought of using the Calendar control to make it easier to pick the date that he wants. Hmm ... i just thought of something... I intend to have a switchboard... maybe when he clicks on the button to open the form, is there a way to ask the user which date he prefers to view in the form? This may be easier.

Thank you!
 
Old April 24th, 2007, 07:41 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You could also use a Year combo, Month combo, and day combo together. Generally, though, since combos are autocomplete, the user just has to start typing in and the combo will finish the work for them. HTH

mmcdonal
 
Old April 26th, 2007, 02:36 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Hi! Thanks for your replies!

I think I will use the autocomplete feature of the combo to fill this out like you suggested. I forgot about that.

Do you know if there is any way to prevent the combo from opening when the user clickes on it? I would prefer to force the user to input the date that he wants and press enter to search for the record.

Thanks!!!

 
Old April 26th, 2007, 10:05 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Yes, do this:

Dim dtDate As Date
etc...

If IsNull(Me.DateCombo) Or Me.DateCombo = "" Then
   MsgBox "Please select a date.", vbCritical
   End Sub
Else
   dtDate = Me.DateCombo
End If

etc...

Did that help?

mmcdonal
 
Old April 26th, 2007, 10:11 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

I am really sorry, but my Access knowledge is somewhat limited... where would I put this instruction? In the "AfterUpdate" of the combo? Thanks!

 
Old May 3rd, 2007, 06:01 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Hi Again! Sorry to bother you... but where would I put this instruction exactly? Sorry for my limited Access knowledge.

Thanks!

 
Old May 3rd, 2007, 07:09 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

On the button that is launching the form or report based on the parameters selected in the combo box. Don't open a query with it. Base a report or form on the query, and open that. Your users should not work with queries or tables.

mmcdonal
 
Old May 14th, 2007, 06:57 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

I am verrrrrrry sorry for the delay in replying again! I have been so busy!

I had a chance to try what you recommended... but I wasnt sure what you meant by "etc" in the code that you provided above. Am I supposed to type something there? (sorry but once again I am not entirely Access literate).

Thanks!!!!!






Similar Threads
Thread Thread Starter Forum Replies Last Post
Pull up or Create Record on Calendar Click Maverick10gk Pro VB Databases 10 April 26th, 2007 02:23 PM
I Challenge U! Combo Search with Calendar Object Odeh Naber Access VBA 1 April 24th, 2007 06:54 AM
Getting date from Calendar 9 into a new record johnkeeb Access VBA 4 November 8th, 2004 11:24 PM
Find Record Use Combo Box martinaccess Access 2 October 17th, 2004 07:42 AM
Search combo acko ASP.NET 1.x and 2.0 Application Design 4 December 18th, 2003 05:09 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.