use access form textbox with date to run query

[ronwmon]

I am trying to use data in a textbox in a form to run a query.

I have a simple database with one table, one form and one query. The table has two fields, name and date. The form has only one textbox. The query is attached to the table. I want to enter a date in the form and have it find the date in the table and display the date and name in the table.

Ron--

 

[benderulz]

You would use [Forms]![FormName]![TextBoxName] as the criteria in the date field of your query.

 

[TheAceMan1]

How are ya ronwmon . . .

[blue] ... The form has only one textbox ... [purple]find the date in the table and display the date and name in the table.[/purple][/blue]

I sure hope you don't mean you want to view the data in the table and not the form. One of the main reasons we use forms in access to view data is that [red]users are not allowed[/red] to manipulated data directly in tables. If you can view your data in the form copy/paste the following to the [blue]After Update[/blue] event of your textbox and give it a shot ... [blue]you![/blue] substitute proper names in [purple]purple[/purple]:

[blue]   Dim rst As DAO.Recordset, Cri As String
   
   Set rst = Me.RecordsetClone

   If IsDate(Me.[purple][b]YourTextboxName[/b][/purple]) Then
      If rst.RecordCount > 0 Then
         Cri = "[BirthDay] = #" & Me.[purple][b]YourTextboxName[/b][/purple] & "#"
         rst.FindFirst Cri
         
         If Not rst.NoMatch Then 'Date Found!
            Me.Recordset.Bookmark = rst.Bookmark
         Else
            MsgBox "'" & Me.[purple][b]YourTextboxName[/b][/purple] & "' Doesn't Exist In The Table!", _
                   vbInformation + vbOKOnly, _
                   "Date Not Found! ..."
         End If
      Else
         MsgBox "No Records to Search!", _
                vbInformation + vbOKOnly, _
                "No Rewcords from Query"
      End If
   Else
      MsgBox "You Must Enter a Valid Date!", _
             vbInformation + vbOKOnly, _
             "Improper Date! . . ."
   End If

   Set rst = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[TheAceMan1]

To All . . .

The criteria line I used in my code on [blue]25 Sep 13 14:55[/blue] is what I used for testing. According to your needs one the following lines should be substituted:

[blue][green]Origional (don't use)[/green]
Cri = "[BirthDay] = #" & Me.YourTextboxName & "#"

[green]'If Textbox is [b]Text[/b] type use[/green]
Cri = "[YourTextboxName] = '" & Me.YourTextboxName & "'"

[green]'If Textbox is [b]Numeric[/b] type use[/green]
Cri = "[YourTextboxName] = " & Me.YourTextboxName

[green]'If Textbox is [b]Date[/b] type use[/green]
Cri = "[YourTextboxName] = #" & Me.YourTextboxName & "#"[/blue]

Sorry about the mess! ...

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!