pardon code below. vba novice. looking upon clicking form button, inform customers must make library selection if item have chosen checked out. determine checked out if "check in date" recent date in "1transaction" table null. note every check in , check out, new record created in 1transaction table , every record (whether check in or check out) have check out date info. logic, take recent date lease (book) number , if there no return date still checked out. code below meant make references , return message box in vba stuck. understand logically require know vba syntax off. thanks.
private sub check_out() if [1transactions].[asset].value = me.lease_num , dmax([tables]![1transactions].[check out date]) , [tables]![1transactions].[check in date] = null msgbox "the requested documents checked out" end if docmd.openform "check in" end sub note:
- 1transactions = table holding check in/out data
- me.lease_num = value pulled combo box user fills out provide "lease number" (book code) interested in checking out.
okay - instead of trying modify every line of code, think it's better use parameter query simple check if item out now
first create query using transactions table - modify sql below , save "qdflease"
parameters [what lease num] text ( 255 ); select top 1 [1transactions].asset, [1transactions].[check out date], [1transactions].[check in date] 1transactions ([1transactions].asset = [what lease num]) , ([1transactions].[check in date] null) order [1transactions].[check out date] desc; modify code in sub to:
dim qdf dao.querydef dim rs dao.recordset dim strleasenum string strleasenum = nz(me.lease_num,"") set qdflease = currentdb.querydefs("qdflease") qdflease.parameters("what lease num") = strleasenum set rs = qdflease.openrecordset(dbopendynaset, dbreadonly) if rs.eof ' item checked in else ' item checked out end if rs.close set rs = nothing 