confuse

[nur annie]

hi...

i have a function that check the value entered to the text box with value in a query call CALLRECORD. The field that i need to check in the query is a calculated field call MONTH that the value derived from field INSEPCTION DATE in the table Inspection Records - use in CALLREPORT QUERY. In my form call Search, i would like to enter the value for PRODUCT CODE & MONTH call the function to produce the output in the CALLREPORT_subform.

Well at the moment i have problem with the code seems that it doesn't running well. i don't know what is wrong but i have the idea that the problem maybe come from the field MONTH or my declarations...

can somebody help me... i really appreciate this

my code:

Private Sub cmdSearch_Click()
On Error GoTo errr
Me.CALLREPORT_subform3.Form.RecordSource = "SELECT * FROM CALLREPORT " & BuildFilter
Me.CALLREPORT_subform3.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"

varWhere = Null

If Me.txtProductCode > "" Then
varWhere = varWhere & "[PRODUCT CODE] like " & Me.txtProductCode & " AND "
End If

If Me.txtMonth > "" Then
varWhere = varWhere & "[MONTH] like " & Me.txtMonth & " AND "
End If

If IsNull(varWhere) Then
varWhere ""
Else
varWhere = " Where " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

 

[pr2-eugin]

Okay.. I am not sure.. I might be wrong.. but some changes might help..
1. Why have you declared the function return type as variant? Change it to String.

Private Function BuildFilter() As String
    Dim varWhere As String

This is because Left() returns a String..

2. Why you have this line of code..

If Me.txtProductCode > "" Then

If you want to check for empty string replace it with..

If Len(Me.txtProductCode & "") >0 Then
'Also
If Len(Me.txtMonth & "") >0 Then

3. Why do you have to check for this?

If Right(varWhere, 5) = " AND " Then

As the string is going to end with AND anyway, if the length is greater than 0 or if it is not null.. this is just to avoid unnecessary computation..

Do you get any error while running the code?

 

[nur annie]

actually i don't even know what i'm doing... i just copied this code from a source... i don't remember anything with programming since i left this field quite long, but now i need to succeed this project for my own merit. glad u help... anyway i'll try this and respond to u immediately

thanks..
annie

 

[nur annie]

sorry it didn't work...anyway thanks for the help...
is there any other way...

annie

 

[pr2-eugin]

Annie, what do you mean by,

sorry it didn't work...

Do you receive any error?

 

[nur annie]

Annie, what do you mean by,

Do you receive any error?

Hi Paul,

i altered my code to the one u gave me... there is no error msg appear, only that my form stay froze.. is there any other way... i am sooo lost i don't know what to do... i don't understand this vba programming technique, futhermore i left programming almost 10 years.. i can't remember anything about programming. at frist i just want to make a simple database for my own work... but seems simple is not enough...

thank a lot..
annie

 

[pr2-eugin]

If you do not mind.. could you please attach a simplified version of your DB, so that I could look into? As I am unable to recreate your problem.

 

[nur annie]

Paul,

pls refer to the attachment. thank you

annie

 

[nur annie]

Hi Paul,
. i happened to come across this msg when trying to open the Search Form...

"This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired."


any idea? i haven't alter the code

thanks
annie

 

[pr2-eugin]

Hello Annie, The file you attached has no tables.. So am not really able to do anything.. If you feel data is sensitive, copy the data structure and fill it with "make believe" data.. so we can work on it..

 

[pr2-eugin]

That error is because you have used cmdClear_Click which should actually preceed with "Call" so the code would be..

Call cmdClear_Click

 

[nur annie]

Hi Paul,
It is not sensitive at all, i can't upload the table, i've already deleted 70% of the data and zip the file... i'll try again...

thanks
annie