A search box that searches through a database and returns results using vba

[wedd]

I have search box that checks through my database and returns back to me a message box of dates that are available based on a table...but I also want in my code to display a date, time and location when it is not available, when the user enters a date, time and location to my existing code. I would like the search to begin from 01/01/2011 to 31/12/2099... Is this possible to do? My code is written below with a DateDiff function:


Private Sub Command9_Click()
Dim SearchAndDisplayABooking
SearchAndDisplayABooking = InputBox("Enter the date to check if it's available")
MsgBox "This booking is available on " & DateDiff("yyyy", SearchAndDisplayABooking, _
Date) & "#Date."



End Sub



Thanks for your contributions:friends:

[tcoombes]

Seems to me that you are not going to do this that with DateDiff. Perhaps you need to be clearer as to exactly what you are wanting to do.

My initial suggestion would be to create a recordset of your data and then go through that looking for what you are wanting.

You can use a > and < to find dates in between

If you can outline what you are doing I can show you how to do it


ted

[wedd]

Hi Ted,

I'm building a scheduling system where the user can search for a date or a range of dates, venues, locations, as well as enter start time and end time that may be available; and the message box will search through the record and return back a message that it is either available or unavailable at the location, venue, dates, start times and end times; also if there is a cancellation of a booking it will notify the user with a message box; and allow them to change the booking. I hope that is clear. Thanks for your help.

[orange]

You may find this video search tutorial helpful
http://www.datapigtechnologies.com/flashfiles/searchform.html

[tcoombes]

Seems to me that the best way is by creating a Recordset whereby the Record Source is filtered to only get the records you want.



Dim rst As New ADODB.Recordset
Dim db As ADODB.Connection
Dim Mysql as string

Set db = CurrentProject.Connection

Mysql = "Select * from YourTable where IntegerField = " & InIntegerField & " And Textfield1 = '" & InTextField & "'"
rst.Open Source:=Mysql, ActiveConnection:=db, CursorType:=adOpenStatic
If Not rst.BOF And Not rst.EOF Then
' If you get in here you have found matching records
' so you can loop through each record and display it to the user
Else
' If you get in here no records match your selection
End If

rst.close
set rst = nothing
set db = nothing

Note you can have as many parameters in your MYSQL statement as you need. Only thing you have to know is that a number parameter can be addressed by = " & NoParameter & " whereas a Text Parameter must have an additional single quote namely = '" & Textparameter & "'

[wedd]

tcoombes, should I add this type of code in a module?