Is it possible to create a button that searches records that displays a message box t

[wedd]

I'm currently working on a project where on the form
I would like to search and check the availability by using a check button to search through the records of all the conference rooms that are availble and those rooms that are unavaible. By clicking the button the search will display the message "not avaible" or "yes, available" based on the user entering the time, date, venue on the form. Is this possible to do using vba? If so, how can this be done?

Thanks for your contributions:friends:

[Imdabaum]

If your tables are all set up to store the availability of your venues, then it may be as simple as querying and checking for a recordset with a count >0.

I do like to create function anytime something is going to be used frequently and I would suggest creating a custom form as the search form. On the form you could select the time/date you are wanting to find a venue for, the name of the venue, and any other criteria that you may want to search for. Then have button that executes the function.


Private Sub button1_Click()
If IsAvailable(Me.txtName [,Me.txtTime][, Me.txtDate]) Then
Msgbox("Not Available")
Else
MsgBox("Venue is Available")
End Sub


This is somewhat pseudo code so you will have to modify it to fit your needs.

Public Function IsAvailable(roomName as String, Optional dt as Date, Optional t as Time) as Boolean
Dim sSQL as String
Dim rs as DAO.Recordset

sSQL = "SELECT * From ScheduledVenues WHERE VENUEname = '" & roomName & "' AND scheduleDate = #" & _
dt & "# AND scheduleTIME =" & t & ";"
'Your sSQL would actually be customized according to which controls were
'populated on the form. Do that by checking if the control is Null or "", add
'criteria where the controls are not Null or "".
Set rs = Currentdb.openrecordset(sSQL)
If Not (rs.EOF and rs.BOF) Then
rs.MoveLast
rs.MoveFirst
End if

If rs.RecordCount >0 Then
'The venue is scheduled for that time and date
IsAvailable = False
Else
IsAvailable = True
End if
End Function

[wedd]

Thanks!

[hansup]

Public Function IsAvailable(roomName as String, Optional dt as Date, Optional t as Time) as BooleanThat throws a compile error: "User-defined type not defined". There is not a separate Time data type in VBA.

[Imdabaum]

That throws a compile error: "User-defined type not defined". There is not a separate Time data type in VBA.

Right. Sorry too much pseudo.

[hansup]

There is another problem with IsAvailable(). Declaring the Optional parameter dt as Date means that, if the user doesn't supply a value, dt will be #1899/12/30 00:00:00#

If you make dt Variant instead, your code can test whether a value was supplied with IsMissing().

If Not IsMissing(dt) Then
'include dt expression in WHERE clause
End If

[Imdabaum]

Ah. Thanks for pointing that out. I forgot about that.

[wedd]

I would also like to add a separate timetable on the form too, so that if a schedule is cancelled I can search for other possible dates when the rooms of the different centres will be available. Any suggestions? In terms of the button doing a simple query search for available times, room, date and centre as you pointed in your code on a button. How can I also implement hansup's suggesting into the code he suggested? So, when the user clicks the button a message box will display either available or not available? Following up on Hansup's suggestion that the user will have to select a date and time of a venue so it doesn't default to a null value. Thanks for your suggestions.

[wedd]

Opps! I entered your code and clicked on the button. The good thing it didn't bring up any errors. I'll test it and let you know. Thanks, again!

[wedd]

I tested the button with the above code but the message box isn't displayed. Any reasons why this may be happening?