Finish the code.........

[Guest_imported]

hi,

I want to be able to search a database using multiple fields, however it will only let me search with on field.

I want the search button to find these fields:

Make
Model
Reg
price
location
Seller type: Trade/private
Automatic/manual

Here is the code for "make" field:

Private Sub testing_Click()
On Error GoTo Err_testing_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Entry"

stLinkCriteria = "[Make]=" & "'" & Me![Make] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_testing_Click:
Exit Sub

Err_testing_Click:
MsgBox Err.Description
Resume Exit_testing_Click

End Sub



So could anyone tell me how to add the other field to this code so the command button will search all the field, instead of 1.

Thanx

 

[JimHorton]

fifa,
stLinkCriteria = "[Make]=" & "'" & Me![Make] & "' AND MODEL = '" & me!Model & "' AND Reg = '" & me!Reg & "' AND...etc

Be sure to omit the single quotes if the field is numeric, ie:
" AND Reg = " & me!Reg & " AND...etc

--Jim

 

[Guest_imported]

thanx alot m8, that really works!

However, you have to put something in the field otherwise it wont work. I want to be able to leave it blank. Do you know how to do this?

thanx

 

[JimHorton]

fifa,
For this you need more logic...
strSq = "" 'init
If Not isnull(me!make) then
strSq = "[Make]=" & "'" & Me![Make] & "'"
End IF

If Not isnull(me!Model) then
If strSq = "" then 'if this is first crit
strSq = "MODEL = '" & me!Model & "'"
Else
strSq = strSQ & " AND MODEL= '" & me!Model & "'"
End IF
End IF

If Not isnull(me!Reg) then
If strSq = "" then 'if this is first crit
strSq = " Reg = " & me!Reg
Else
strSq = strSQ & " AND Reg = " & me!Reg
End IF
End IF
If strSq = "" Then
msgbox "Enter some Criteria"
Else
stLinkCriteria = strSq
DoCmd.OpenForm stDocName, , , stLinkCriteria
ENd if

--Jim

 

[Guest_imported]

thanx again,

3 more slight problems...

1st: Everytime i do a search, it adds another record. How do i stop this?

2nd: When I set the codes you gave to me I have to enter something in each of the fields. I would like to have the option of leaving one of the fields blank and still be caperble of completing the search.

3rd: The code for searching for the Reg will not work I have only set a letter for this field i.e A,T,S when I try to search it comes up with a box with enter value.

 

[GingerR]

1) are your text boxes on your search form unbound? they should be. sounds like you have the search form bound to your table? Look in the properties of your FORM. Is there something in the RECORD SOURCE? if so, there shouldn't be.

2) i did a whole test and this worked ok for me. maybe if #1) above is true like i said, it is messing you up.

3) you have to fix the criteria for REG. Jim did it like REG was a number, but you say it is text. therefore you need single quotes:

replace strSq = strSQ & " AND Reg = " & me!Reg
with strSq = strSq & " AND Reg = '" & Me!Reg & "'"

 

[Guest_imported]

hi, thanx for the speedy reply.

for 1.) you said take away the record source. I did this, however, the fields in the search come up with #Name?. I can't delete this from the fields.

thanx ;-)

 

[GingerR]

because your fields are looking for the data in the table you just deleted from being your recordsource.

look at the properties for each of the text boxes on your form. take away the control source for each text box. they should be UNBOUND text boxes, meaning they are just pretend spaces for you to type into, they are not tied to a table or query, which is what you want.

 

[Guest_imported]

thanx yet again,

another question to ask you,

i have got a drop down menu as one of my fields, its called manual/automatic. When i follow your guide for the vba code, it doesn't seem to work. It says "enter some critera"

this is the code i have done:

If Not IsNull(Me!Manual / Automatic) Then
If strSq = "" Then 'if this is first crit
strSq = "MANUAL/AUTOMATIC = '" & Me!Manual / Automatic & "'"
Else
strSq = strSq & " AND MANUAL/AUTOMATIC= '" & Me!Manual / Automatic & "'"
End If

 

[GingerR]

you need to put square brackets [] around your field name. It cannot understand what you are wanting, it is not recognizing that as a control name because of the way you have it written, so it is asking you for the value it can't find/understand.

i suggest never putting punctuation or spaces in field or table names. a better idea for visual separation of words is using capital letters and non-caps, i.e.

ManualAutomatic

you will run into lots of problems down the road believe me.

 

[Guest_imported]

thanx again m8,

I would now like to add an helpfile to the database to help users with the database. How would i create a helpfile?

thanx

 

[GingerR]

go to HELP + CONTENTS AND INDEX and search on HELP FILE

you have to have some kind of compiler to do that.

what i do is write html pages (dont need to be on a special server or anything) and on a button on the main screen it says HELP with question mark on the button and it just opens the html pages. you could do the same with .doc or .pdf or whatever.

g

 

[Guest_imported]

thanx,

Can you please check this code for me?, Reg, location and manualautomatic arnt working...

Private Sub searchcars_Click()
On Error GoTo Err_searchcars_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Entry"
stLinkCriteria = "[Make]=" & "'" & Me![Make] & "' AND MODEL = '" & Me!Model & "' AND REG = '" & Me!Reg & "' AND AUTOMATICMANUAL = '" & Me!AutomaticManual & "' AND LOCATION = '" & Me!location & "'"
strSq = "" 'init
If Not IsNull(Me!Make) Then
strSq = "[Make]=" & "'" & Me![Make] & "'"
End If

If Not IsNull(Me!Model) Then
If strSq = "" Then 'if this is first crit
strSq = "MODEL = '" & Me!Model & "'"
Else
strSq = strSq & " AND MODEL= '" & Me!Model & "'"
End If
End If

If Not IsNull(Me!Reg) Then
If strSq = "" Then 'if this is first crit
strSq = " Reg = " & Me!Reg
Else
strSq = strSq & " AND Reg = '" & Me!Reg & "'"
End If

If Not IsNull(Me!AutomaticManual) Then
If strSq = "" Then 'if this is first crit
strSq = "AUTOMATICMANUAL = '" & Me!AutomaticManual & "'"
Else
strSq = strSq & " AND AUTOMATICMANUAL= '" & Me!AutomaticManual & "'"
End If

If Not IsNull(Me!location) Then
If strSq = "" Then 'if this is first crit
strSq = "LOCATION = '" & Me!location & "'"
Else
strSq = strSq & " AND LOCATION= '" & Me!location & "'"
End If
End If

End If
End If
If strSq = "" Then
MsgBox "Enter some Criteria"
Else
stLinkCriteria = strSq
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If


DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_searchcars_Click:
Exit Sub

Err_searchcars_Click:
MsgBox Err.Description
Resume Exit_searchcars_Click

End Sub

 

[GingerR]

1) omit the first big string where you set stLinkCriteria at the beginning. It is unncecessary since what you are doing in the rest of the code is building that string properly.

2) you have the End If statements in the wrong places:

If Not IsNull(Me!Reg) Then
If strSq = "" Then 'if this is first crit
strSq = " Reg = " & Me!Reg
Else
strSq = strSq & " AND Reg = '" & Me!Reg & "'"
End If
End If(add this here)
If Not IsNull(Me!AutomaticManual) Then
If strSq = "" Then 'if this is first crit
strSq = "AUTOMATICMANUAL = '" & Me!AutomaticManual & "'"
Else
strSq = strSq & " AND AUTOMATICMANUAL= '" & Me!AutomaticManual & "'"
End If
End If(add this here)

If Not IsNull(Me!location) Then
If strSq = "" Then 'if this is first crit
strSq = "LOCATION = '" & Me!location & "'"
Else
strSq = strSq & " AND LOCATION= '" & Me!location & "'"
End If
End If

End If OMIT THIS
End If OMIT THIS