Update query with the hic ups

[MattBegg]

I have an update query which calls up a function for a field.
The function uses an input box to take a value with which to enter into the field.

The problem is that the function seems to run 3 times

the sql for the the query is:

UPDATE Base_Patient SET Base_Patient.pa_visit = updatevisit()
WHERE (((Base_Patient.Pa_id)=[Forms]![OL_Main]![ChForm].[Form]![pa_id]));

and the function code is :-

Function updatevisit()
updatevisit = InputBox("Please enter a visit number&quot
End Function

The idea is that the visit number entered will be updated in the table base_patient based on the row selected in a list box on a subform.

Hope you follow this Regards

Matt

matt@begg-uk.co.uk

 

[PaulF]

Because the code for the InputBox gets run for each Update. I just answered a question about using a variable in more than one query, and it's answer would fit here. Create a Public variable to store the results of the InputBox, then store the results of the variable to the Public Function (Public so you can call it from more than one form), and you'll only call the InputBox once, but can retrieve the answer multiple times.

Public intVisitNo as Integer

intVisitNo = InputBox("Please enter a visit number&quot

Public Function updatevisit()
updatevisit = intVisitNo
End Function

PaulF