Alert message based on record selection

[Ron Weaver]

Hi all
My database has an order form which books equipment for meetings. The main
form has (among others), text boxes for Start Date and End Date. The subform
has a combo box to select the Equipment. When I select a piece of equipment
in the combo box, I would like a msg box or a pop up to alert me if that
piece of equipment is already booked for that date period. This information
is stored in the Orders table. The msg box or pop up should show the Customer
Name, Dates, and Invoice number. I would appreciate it if someone will give
me an idea of how to do this, or guide me to an example procedure.
Thanks
Ron

 

[Dale Fye]

Ron,

Does each piece of equipment have a specific ID value? Is the user
selecting a specific item (a particular wrench) , or a type of item (wrench,
crescent, 1 1/2")?

If the items they are selecting are specific items, and those items are
tracked in your Orders table table, then you would need to put some code in
the combo boxes AfterUpdate event to do the check. Maybe something like:

Private Sub cbo_Equipment_AfterUpdate

Dim strSQL as string
Dim rs as DAO.Recordset 'make sure you have the DAO reference set
Dim strMsg as string

strSQL = "SELECT O.Invoice, O.CustomerName, O.StartDate, O.EndDate "
& "FROM tbl_Orders as O "
& "WHERE O.EndDate >= #" & me.txt_StartDate & "# " _
& " AND O.StartDate <= # & me.txt_EndDate & "#"
Set rs = currentdb.openrecordset strsql

if rs.eof then
'no problem
Else
strMsg = "That item is already reserved by:" & vbcrlf
While not rs.eof
strMsg = strMsg & vbcrlf & rs("CustomerName") & " from " _
& rs("StartDate") & " thru " & rs("EndDate") _
& " on invoice:" & rs("Invoice")
rs.movenext
Wend
msgbox strMsg
me.cbo_Equipment = Null
End if

rs.close
set rs = nothing

End sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Ron Weaver]

Thanks Damen
I will work on this and get back to the group.

Ooops! should be X= DLookup, not DCount, also <= and >=

Damon

in the afterupdate event of the combobox, you could use dlookup on your
order table.
(not tested aircode)
X= DCount("OrderID", "tblOrders", "StartDate > " &
Me.Parent.[txtStartDate] & " And EndDate < " & Me.Parent.[txtEndDate] &
"EquipmentID = " & YourCombobox.Column(0))

'use dlookup to fill variables with Custname, Dates, and Inv#
Custname= dlookup("CustomerName", "tblOrders", "OrderID= " & X)
Dates= dlookup.....etc.
Inv#= dlookup...etc
'then display msgbox.
msgbox "This piece of equipment is booked by " & Custname & " on " & Dates
& "with Invoice Number " & Inv#, vbinfo

me.yourcombobox.setfocus

Damon

Hi all
My database has an order form which books equipment for meetings. The
main
form has (among others), text boxes for Start Date and End Date. The
subform
has a combo box to select the Equipment. When I select a piece of
equipment
in the combo box, I would like a msg box or a pop up to alert me if that
piece of equipment is already booked for that date period. This
information
is stored in the Orders table. The msg box or pop up should show the
Customer
Name, Dates, and Invoice number. I would appreciate it if someone will
give
me an idea of how to do this, or guide me to an example procedure.
Thanks
Ron

 

[Ron Weaver]

Thanks Dale
Yes, each pc of equipment has a specific ID value. I will work on this and
get back to the group.

 

[Ron Weaver]

Dale,
I finally got this code to work. It is great, but I have one problem: The
Customer Name. My Orders table has CustomerID (from the Customer table), but
not the name. How can we modify the following code to show the name instead
of the ID:

Private Sub cboProducts_AfterUpdate()

Dim strSQL As String
Dim rs As DAO.Recordset 'make sure you have the DAO reference set
Dim strMsg As String

strSQL = "SELECT O.OrderID, O.CustomerID, O.StartDate, O.EndDate " _
& "FROM Orders as O " _
& "WHERE O.EndDate >= #" & Parent.txtStartDate & "# " _
& " AND O.StartDate <= #" & Parent.txtEndDate & "# "
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.EOF Then
'no problem
Else
strMsg = "That item is already reserved by:" & vbCrLf
While Not rs.EOF
strMsg = strMsg & vbCrLf & rs("CustomerID") & " from " _
& rs("StartDate") & " thru " & rs("EndDate") _
& " on invoice:" & rs("OrderID")
rs.MoveNext
Wend
MsgBox strMsg
Me.cboProducts = Null
End If

rs.Close
Set rs = Nothing

End Sub

Thanks

 

[Dale Fye]

Ron,

The query will look something like:

strSQL = "SELECT O.OrderID, C.CustomerName, O.StartDate, O.EndDate " _
& "FROM Orders as O " _
& "INNER JOIN Customers as C " _
& "ON O.CustomerID = C.CustomerID " _
& "WHERE O.EndDate >= #" & Parent.txtStartDate & "# " _
& " AND O.StartDate <= #" & Parent.txtEndDate & "# "

Good luck.

 

[Ron Weaver]

Thanks Dale
That works. There is one more question: The message pops up every time for
the current booking. Is there any way to show the message only when there are
other bookings?
Thanks

 

[Dale Fye]

Since I'm not sure what your form looks like and what the data source is for
it, I'm not sure I'll get this correct. You need to another parameter to the
query to ignore the current record. If the current record on the form has an
OrderID, then the following might work. Another appoach may be to test for
O.CustomerID not equal to the current customer ID.

strSQL = "SELECT O.OrderID, C.CustomerName, O.StartDate, O.EndDate " _
& "FROM Orders as O " _
& "INNER JOIN Customers as C " _
& "ON O.CustomerID = C.CustomerID " _
& "WHERE O.EndDate >= #" & Parent.txtStartDate & "# " _
& " AND O.StartDate <= #" & Parent.txtEndDate & "# " _
& " AND O.OrderID <> " & me.txtOrderID

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Ron Weaver]

Dale,
That worked, however I have just discovered that instead of showing
equipment booked on the same date, the msg box is showing all orders booked
on the same date regardless of equipment selected. The equipment in the combo
box, cboProducts , is listed under source "ProductID" in the Products table.
I appreciate your help in this.

 

[Dale Fye]

I'm surprised I didn't pick up on that earlier.

Just add another parameter to the WHERE clause of the SQL string to test
that the Order.ProductID equals the value of the product ID control.
Something like:

& " AND O.ProductID = " & me.cboProduct

Dale

 

[Ron Weaver]

Dale,
That did't work because 'ProductID' isn't in the "Orders" table. It is in
the "Products" table. The "Products" table is joined to the "Orders" table
through a junction table named "Order Details". I hope this helps. Here is my
code to date:

Private Sub cboProducts_AfterUpdate()

Dim strSQL As String
Dim rs As DAO.Recordset 'make sure you have the DAO reference set
Dim strMsg As String

strSQL = "SELECT O.OrderID, C.FirstName, C.LastName, O.StartDate, O.EndDate
" _
& "FROM Orders as O " _
& "INNER JOIN Customer as C " _
& "ON O.CustomerID = C.CustomerID " _
& "WHERE O.EndDate >= #" & Parent.txtStartDate & "# " _
& " AND O.StartDate <= #" & Parent.txtEndDate & "# " _
& " And O.OrderID <> " & Parent.OrderID _

Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.EOF Then
'no problem
Else
strMsg = "That item is already reserved by:" & vbCrLf
While Not rs.EOF
strMsg = strMsg & vbCrLf & rs("FirstName") & " " & rs("LastName") _
& " from " _
& rs("StartDate") & " thru " & rs("EndDate") _
& " on invoice:" & rs("OrderID")
rs.MoveNext
Wend
MsgBox strMsg

End If

rs.Close
Set rs = Nothing


End Sub

 

[Ron Weaver]

Dale,
I finally got this to work by creating it in the query builder and copying
the SQL over.
Thanks for your help.