Custom Property in User Defined Function? 4

[logmonkey]

Hi all I've created a generic search function to make creating custom searches in my Access 2010 application easier.

Here is the function name and its arguments. I won't bore you with the rest.

Public Function SrchFrm(strPrompt As String, _
                     strForm As String, _
                       strField As String, _
                       intDataType AS integer)

I call function and set the intDataType argument as 1 for int or 2 for string, 3 for date so I can specify a where clause based on the data type.

CALL SrchFrm("Hey enter some data, Monkey!","frmPeople","ID",1)

It works, but the problem is; in a couple months when my boss asks for a new search, I'll have to go back and find out which number I selected for a specific data type.
I'd like to be able enter the calling statement in the VB editor and for intellisense to display a selection of properties for intDataType such as acInteger,acString,acDate, so I can select them from the list. Is it a custom property?
Any references or help to get me started? I'm not expecting a full explanation in a post.
Not knowing the terminology makes searching for it a pain.

Thanks

 

[PHV]

I'd use an Enum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[logmonkey]

Thanks PHV. That did it. Hey you helped me out using less than 5 words.
Pretty cool.

For those of you searching for this. the answer was after doing a little reading, I created a new module and added the following code.

Public Enum vbDataType
vbInt = 2
vbString = 1
vbDate = 3
End Enum

 

[HughLerwill]

It depends a little on how strField gets assigned but if you replaced it with a Variant you may be able to use VBA's VarType Function to determine the Type of its contents.

 

[logmonkey]

Hugh,
Awesome You detected the root of the problem with my function, dynamically detecting the data type, which is why I chose to add the intDatatype argument to specify the input data, then used a series of if statements.

I will look at VarType to see if I can eliminate the need to specify the data type.


One follow-on question. A large part of my coding, once I solved the problem and created the logic, was error-handling related to the InputBox() method, (ugggh) so much, that I hesitate to use it in the future unless I have to.

Any alternatives? The only alternative I could think of was to call an unbound form and then use search by form....

Thanks again for the input.