Access Parameter Query - Public Function Input Help? 1

[Majic]

I have a query (linked to a single table). I am using a public function to get a value to use as a parameter query...

Public Function InputTY() as string
Dim ITY as string
ITY = inputbox ("enter tax year&quot
InputTY = ITY
End Function

In the criteria box of the correct field i have used the build to select the function, however, when i run the query I always get the input box appear twice...? The query works, but why does it appear twice?

Any assistance appreciated...

 

[ghubbell]

Hi Majic,
Please try this: in the criteria row under the correct field remove your function stuff and type:

[Please enter tax year:]

Exactly as you see it here. This should do exactly the same job without the fuss! Gord
ghubbell@total.net

 

[Majic]

Sorry, the parameter query i am actually using does valadation etc... i only used the above code as an example because it creates the same annoying problem... The real module code is below:


Option Compare Database

Public Function inputPayNo() As String

Dim EmpMaster As ADODB.recordset
Dim EmpMasterSQL As String
Dim inputpn As String
Dim sname As String
Dim connection As ADODB.connection

Set connection = New ADODB.connection
Set EmpMaster = New ADODB.recordset

connection.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source= " & CurrentProject.FullName
connection.Open

EmpMasterSQL = "select * from [EmpMasterFilter]"
EmpMaster.Open EmpMasterSQL, connection, adOpenKeyset, adLockOptimistic

Do While True
EmpMaster.MoveFirst
inputpn = InputBox("Enter Pay Number&quot
EmpMaster.Find "[Pay_No] = '" & inputpn & "'"

If inputpn = "" Then
Exit Do
End If

If EmpMaster.EOF Or EmpMaster.BOF Then
MsgBox ("ERROR! The Pay Number requested is either incorrect or restricted &quot & Chr$(10) & ("If you require access - Contact Sys Admin&quot & Chr$(10) & Chr$(10) & ("You requested Pay Number - &quot & inputpn

Else
'MsgBox ("You have requested the following Record: &quot & Chr$(10) & (" Pay No: " & EmpMaster.Fields("pay_no&quot & " Name: " & EmpMaster.Fields("surname&quot), vbQuestion + vbYesNo
inputPayNo = inputpn

Exit Do

End If

Loop

End Function

 

[ghubbell]

Majic, you have some fine code here. I suspect the problem lies with the query calling the function and the function starting the input box. It appears your capable of, so why don't you copy your query into this function (or a compilation of) and run everything from one place? I tried your little function and got a similar 2x issue. I do not use input boxes (usually opting for forms if data entry is required) and am equally puzzled...
Gord
ghubbell@total.net