Can someone point me in the right direction (I'm not even sure I should be asking under the macros section)? I am creating a form where one can search for, add and delete inventory.

1. When an item is searched for, there are 5 fields that can be filled in, to make the search more specific, or left blank to make it more general. When the user hits search, I want the results to display in a subform below. How do I go about this? Do I want to write some sort of case of conditional structure that filters? Can someone point me to an example of a database like this and/or code that achieves this?

2. When the user finds an item in the inventory I want them to be able to click on the correct item in the subform at the bottom, and have that item display back in the main form at the top where it can then be editted or deleted. Any clues on this one?

This is what I think:

I could write a hundred if statements for each possibility of combinations of NULL versus NOT NULL data, but is there a way to write dynamic code? So I do something like declare a string and for each TRUE condition of NOT NULL controls, append the string with the appropriate command?

I.E.

DIM Scriteria AS String

Scriteria = ""

IF [Forms]![Form1]![Item1] IS NOT NULL THEN

IF Scriteria = "" THEN

Scriteria = """ + "Item1 = [Forms]![Form1]![Item1]" + """

ELSE

Scriteria = "AND """" + "Item1 = [Forms]![Form1]![Item1]" + """

ENDIF

ENDIF

'...Repeat for each control on form

DoCmd.ApplyFilter ,Scriteria

I'm floundering here and hoping someone will answer this thread.

I am going ahead with what I posted in my last message as an attempt to filter by multiple conditional criteria. I tried it, but I get an error message when I apply it (via a click on a button in the main form) that says "Object Required".

Can anyone give me some direction?

In troubleshooting I wondered if the Object Required didn't refer to the restrictions I placed on the table that data had to be entered into certain fields in order for records to be added. I commented out the parts that did anything but a simple filter, and that seemed to do the trick with the object error:


Sub FilterRecs()

Dim sCriteria As String

sCriteria = ""

'If [Forms]![Hardware Groups]![Make] Is Not Null Then

' If sCriteria = "" Then

sCriteria = """Make = [Forms]![Hardware Groups]![Make]"""

' Else

' sCriteria = sCriteria & " AND " & """Make = [Forms]![Hardware Groups]![Make]"""

' End If

'End If

'If [Forms]![Hardware Groups]![Model] Is Not Null Then

' If sCriteria = "" Then

' sCriteria = """Model = [Forms]![Hardware Groups]![Model]"""

' Else

' sCriteria = sCriteria & " AND " & """Model = [Forms]![Hardware Groups]![Model]"""

' End If

'End If

' If [Forms]![Hardware Groups]![RAM] Is Not Null Then

' If sCriteria = "" Then

' sCriteria = """RAM = [Forms]![Hardware Groups]![RAM]"""

' Else

' sCriteria = sCriteria & " AND " & """RAM = [Forms]![Hardware Groups]![RAM]"""

' End If

'End If

'If [Forms]![Hardware Groups]![RAMtype] Is Not Null Then

' If sCriteria = "" Then

' sCriteria = """RAM type = [Forms]![Hardware Groups]![RAMtype]"""

' Else

' sCriteria = sCriteria & " AND " & """RAM type = [Forms]![Hardware Groups]![RAMtype]"""

' End If

'End If

'If [Forms]![Hardware Groups]![IType] Is Not Null Then

' If sCriteria = "" Then

' sCriteria = """Type = [Forms]![Hardware Groups]![Itype]"""

' Else

' sCriteria = sCriteria & " AND " & """Type = [Forms]![Hardware Groups]![Itype]"""

' End If

'End If

'If sCriteria = "" Then

' Exit Sub

'Else

DoCmd.ApplyFilter , sCriteria

'End If


End Sub

However, now it updates the table rather than actually applying a filter. Why?