LookUp Form

  • Thread starter Thread starter Melinda
  • Start date Start date
M

Melinda

I'm fairly new to Access, and I have a form with 4 unbound
text boxes that I want to use to pass data to a parameter
query. After the user enteres the data they are searching
for, what event do I use to return the data subset? I
also want to use a subform within this unbound form to
return the recordset created by the users entry. Help.

Example:

Main Form:

Search by Unit ID#: _________
Search by VIN#: __________
Search by License Plate: _________
Search By Dept ID: ____________

Subform:

Unit ID: 12345 VIN# 123456 License Plate: TX1234 etc.

Thanks.

Melinda
 
-----Original Message-----
I'm fairly new to Access, and I have a form with 4 unbound
text boxes that I want to use to pass data to a parameter
query. After the user enteres the data they are searching
for, what event do I use to return the data subset? I
also want to use a subform within this unbound form to
return the recordset created by the users entry. Help.

Example:

Main Form:

Search by Unit ID#: _________
Search by VIN#: __________
Search by License Plate: _________
Search By Dept ID: ____________

Subform:

Unit ID: 12345 VIN# 123456 License Plate: TX1234 etc.

Thanks.

Melinda

.
Click to expand...
Hi Melinda,
add buttons - Search and Reset
code examples are aircode...

caption: Search
name: cmdSearch
event:
private sub cmdSearch_OnClick()
dim strSQL as string
dim strWhere as string
const BASE_SQL as string = "table or query for subform"

if not isnull(txtUnitID) then
strwhere="((UnitID)=" & txtUnitID & ")"
end if

if not isnull(txtVIN) then
if len(strwhere)>0 then
strwhere=strwhere & " and "
end if
strwhere=strwhere & "((VIN)=" & txtVIN & ")"
end if

if not isnull(txtPlate) then
if len(strwhere)>0 then
strwhere=strwhere & " and "
end if
' note use of single quote for text string
strwhere=strwhere & "((Plate)='" & txtPlate & "')"
end if

if not isnull(txtDeptID) then
if len(strwhere)>0 then
strwhere=strwhere & " and "
end if
strwhere=strwhere & "((DeptID)=" & txtDeptID& ")"
end if

if len(strwhere)>0 then
strsql="select " & base_sql & ".* from " & base_sql _
& " where (" & strwhere & ");"
me.subformcontrolname.form.recordsource=strsql
else
msgbox "user freindly... 'pick something!'"
end if
end sub

caption: Reset
name: cmdReset
event:
private sub cmdReset_OnClick()
dim ctl as control
for each ctl in controls
with ctl
if .controltype=actextbox then
..value=vbnullstring
end if
end with
doevents
next ctl
end sub

Luck
Jonathan
 
Back
Top