Search Button Help in Form (1 Viewer)

annaisakiwi

New member
Local time
Today, 23:54
Joined
Aug 12, 2009
Messages
6
Hi all

I've tried to replicate code from another database, and feel like I'm going in circles. In the attached - using Form1 - I'd like the user to be able to select different options and select Search.

It's been many moons since I've used Access - and I'd appreciate anyone having a look at it and suggesting how I can make this all work.

THanks Anna
 

Attachments

  • Hoists.zip
    71.8 KB · Views: 50

Ranman256

Well-known member
Local time
Today, 06:54
Joined
Apr 9, 2015
Messages
4,337
You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

Code:
if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

if IsNull(cboState) then
  me.filterOn = false
else
       'remove 1st And
    sWhere= mid(sWhere,4)

     'just use the filter

  me.filter = sWhere
  me.filterOn = true
endif
 

bob fitz

AWF VIP
Local time
Today, 10:54
Joined
May 23, 2011
Messages
4,717
Take a look at the form called "SearchForm" in the attached file. I have used the code supplied by Ranman256 to apply a filter to the sub form after a selection is made in each of the combo boxes.
 

Attachments

  • HoistsBob01.zip
    120.3 KB · Views: 45

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:54
Joined
May 7, 2009
Messages
19,169
Also with yiur own code
 

Attachments

  • Hoists.zip
    66.1 KB · Views: 40

AccessBlaster

Registered User.
Local time
Today, 03:54
Joined
May 22, 2010
Messages
5,823
Before you get to far into your project you may want to step back and consider your choices for field names. Type is reserved for access so is Description and year. There are other issues with your field names, like spaces and special characters. There are repeating field names also.

These field names will be headaches down the road.

 

Attachments

  • InkedHoistTables_LI.jpg
    InkedHoistTables_LI.jpg
    42.2 KB · Views: 144

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:54
Joined
May 7, 2009
Messages
19,169
yes, moved those "old hours" to a separate table (1 table, history table).
add 2 extra columns for month and year.
 

annaisakiwi

New member
Local time
Today, 23:54
Joined
Aug 12, 2009
Messages
6
Thanks everyone! I'm working through the changes / suggestions now
 

Users who are viewing this thread

Top Bottom