Results 1 to 11 of 11
  1. #1
    Mister-B is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    31

    Use Calendar to find record

    Happy Easter to All,



    I have a simple DB with one record for every day of the year. I have a form in the DB called "Kontrollbuch" and a bound field on the form called "Datum". I also have an unbound field "cboSuchen" that's set up to be a drop down calender. I would like to be able to use the drop down calendar to find the record with the corresponding value in "Datum". There will only ever be one record with this value.

    I have trolled through lots of forums but can't find the right synthax to get this to work. What I need to do is find record on the Form "Kontrollbuch" where the Value in "Datum" is the same as the value in "cboSuchen".

    Who can help?

    Regards,
    Martin

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    IMHO it would be easier to create a combo box (using the wizard) to find a record.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    or you can post what you tried, thereby giving us something to work with. If it's a syntax issue, perhaps no need for us to start from scratch.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Mister-B is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    31
    Quote Originally Posted by Bob Fitz View Post
    IMHO it would be easier to create a combo box (using the wizard) to find a record.
    I have actuall tried this and it's working fine. The problem is that as the database fills, more and more entries will fill the combobox. I think it would be more elegant having a dropdown calendar that always shows the current month.

  5. #5
    Mister-B is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    31
    Quote Originally Posted by Micron View Post
    or you can post what you tried, thereby giving us something to work with. If it's a syntax issue, perhaps no need for us to start from scratch.
    I'm trying this:

    Private Sub cboSuchen2_Change()
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    With rs
    .FindFirst "[Datum] = #" & Format(Me![cboSuchen], "mm\/dd\/yy") & "#"
    If RecordsetClone.NoMatch Then
    MsgBox "No Match"
    Else
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    End With
    End Sub

    I've also tried lots of variations of that code that I found on various Forums. This code just does nothing to my form.

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Mister-B View Post
    I have actuall tried this and it's working fine. The problem is that as the database fills, more and more entries will fill the combobox.
    I don't see why this is a problem. Users are not obliged to drop the combo and pick an entry. The entry will auto-fill as user types.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Mister-B View Post
    I have actuall tried this and it's working fine. The problem is that as the database fills, more and more entries will fill the combobox.
    I don't see why this is a problem. Users are not obliged to drop the combo and pick an entry. The entry will auto-fill as user types.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    In the Change event of your text box try:
    Code:
        DoCmd.SearchForRecord , "", acFirst, "[dat] = #" & Format(Me.[YourTextBoxName], "mm\/dd\/yyyy") & "#"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Bob Fitz View Post
    In the Change event of your text box try:
    Code:
        DoCmd.SearchForRecord , "", acFirst, "[dat] = #" & Format(Me.[YourTextBoxName], "mm\/dd\/yyyy") & "#"
    After more testing, it would seem that the code should actually be:
    Code:
    DoCmd.SearchForRecord , "", acFirst, "[dat] = #" & Format(Me.[YourTextBoxName].Text, "mm\/dd\/yyyy") & "#"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    Mister-B is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    31

    Cool

    Quote Originally Posted by Bob Fitz View Post
    After more testing, it would seem that the code should actually be:
    Code:
    DoCmd.SearchForRecord , "", acFirst, "[dat] = #" & Format(Me.[YourTextBoxName].Text, "mm\/dd\/yyyy") & "#"
    Thank you so much! This works perfectly.

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Mister-B View Post
    Thank you so much! This works perfectly.
    Glad to Help
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-16-2018, 09:15 PM
  2. Replies: 4
    Last Post: 05-30-2016, 10:53 AM
  3. Replies: 1
    Last Post: 06-23-2014, 07:21 AM
  4. Replies: 1
    Last Post: 05-26-2014, 12:39 PM
  5. Replies: 4
    Last Post: 04-01-2014, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums