Hello all,
So, with the help of some other threads I have successfully created a few search forms. I will focus on just one here even though I will do this for a few different searches.
Right now I have a form that has some different unbound text boxes that the user can fill in to search records...ReservationIDSearchText to search a ReservationID from a specifies query as well as the salesperson, arrival date range, return date range, etc. The code creates and opens a dynamic query which all works very nicely. However, I am looking to do something more advanced.
Here is what I have so far:
So here are the things I would like to change but do not know how:
1. If I change the text box properties on the search form, so that the ReservationIDSearchText is a number and the date fields are formatted a certain way the above code returns errors. I do not want people to enter letters stupidly for ReservationID or the dates in the range, so is there another way to validate this that will not cause errors?
2. Here is what I call the hard part: Instead of opening a dynamic query, I would like the button click to open some kind of popup with the same datasheet type view but that has the sole purpose of selecting the record the user has searched from the results and then opening it in the regular form for editing the reservations which already exists...say frmReservations. The datasheet style popup would not allow users to edit anything at all, just a list in essence, to select from the search results and open the record in the normal editing form. I hope I am explaining this clearly.
So, an example, I enter a date range for a certain client(s) arrival and the list popups with the search results in a datasheet form(maybe a dozen records or whatever), ReservationID,ArrivalDate,ReturnDate,EmployeeID, etc. (these fields are whatever exists in the source query). Then the user can select which Reservation they are looking for in this list, the list closes, and the record pops up in its edit form that we use to enter reservations to begin with.
I think this is a good start, let me know what i can clear up, and thanks!
So, with the help of some other threads I have successfully created a few search forms. I will focus on just one here even though I will do this for a few different searches.
Right now I have a form that has some different unbound text boxes that the user can fill in to search records...ReservationIDSearchText to search a ReservationID from a specifies query as well as the salesperson, arrival date range, return date range, etc. The code creates and opens a dynamic query which all works very nicely. However, I am looking to do something more advanced.
Here is what I have so far:
Code:
Option Compare Database
Private Sub cmdReservations_Search_Click()
Dim db As Database
Dim QD As QueryDef
Dim where As Variant
Set db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query does
' not exist.
On Error Resume Next
db.QueryDefs.Delete ("dynqryReservations_SearchResults")
On Error GoTo 0
where = Null
where = where & " AND [ReservationID]= " + Me![ReservationIDSearchText]
where = where & " AND [EmployeeID]= " + Me![EmployeeIDSearchText]
where = where & " AND [ReservationStatusID]= " + Me![ReservationStatusIDSearchText]
' The following evaluates the date search information.
If Not IsNull(Me![ArrivalEndDateSearchText]) Then
where = where & " AND [ArrivalDate] between #" + _
Me![ArrivalStartDateSearchText] + "# AND #" & Me![ArrivalEndDateSearchText] & "#"
Else
where = where & " AND [ArrivalDate] >= #" + Me![ArrivalStartDateSearchText] _
+ " #"
End If
If Not IsNull(Me![ReturnEndDateSearchText]) Then
where = where & " AND [ReturnDate] between #" + _
Me![ReturnStartDateSearchText] + "# AND #" & Me![ReturnEndDateSearchText] & "#"
Else
where = where & " AND [ReturnDate] >= #" + Me![ReturnStartDateSearchText] _
+ " #"
End If
' Remove the following MsgBox line if you do not want to display the
' SQL statement.
'MsgBox "Select * from qryReservations_Search " & (" where " + Mid(where, 6) & ";") 'No need for now
Set QD = db.CreateQueryDef("dynqryReservations_SearchResults", _
"Select * from qryReservations_Search " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "dynqryReservations_SearchResults"
End SubSo here are the things I would like to change but do not know how:
1. If I change the text box properties on the search form, so that the ReservationIDSearchText is a number and the date fields are formatted a certain way the above code returns errors. I do not want people to enter letters stupidly for ReservationID or the dates in the range, so is there another way to validate this that will not cause errors?
2. Here is what I call the hard part: Instead of opening a dynamic query, I would like the button click to open some kind of popup with the same datasheet type view but that has the sole purpose of selecting the record the user has searched from the results and then opening it in the regular form for editing the reservations which already exists...say frmReservations. The datasheet style popup would not allow users to edit anything at all, just a list in essence, to select from the search results and open the record in the normal editing form. I hope I am explaining this clearly.
So, an example, I enter a date range for a certain client(s) arrival and the list popups with the search results in a datasheet form(maybe a dozen records or whatever), ReservationID,ArrivalDate,ReturnDate,EmployeeID, etc. (these fields are whatever exists in the source query). Then the user can select which Reservation they are looking for in this list, the list closes, and the record pops up in its edit form that we use to enter reservations to begin with.
I think this is a good start, let me know what i can clear up, and thanks!
