Passing parameter from a form to a query 1

[Spookarino]

Well folks here's the deal... On a form I want to enter a start & finish date (plus some other bits n pieces... This value would then be saved, then a query would be selected and these dates passed to it. Also a report may be required using the same dates... Would global variables be used?!?

 

[Craig0201]

Nope.....

Put the fields as criteria in the query....

Craig

 

[nagyf]

If you have the value in the field "Y" of a main form "X" the answer is simple:
You can simply add the conditon into you WHERE clause.

customer=Forms("X&quot!Y

In this case you cannot test the query without the form.
If you want to test the query without the form or the parameter of the query goes from a subform then use my attached function:

customer=var_form_field("X","Y&quot

Regards,
Ferenc Nagy, Institue of Isotope and Surface Chemistry


' ************************************************************
' (C) Ferenc Nagy, Institue of Isotope and Surface Chemistry
' Budapest, Hungary 2000.
' Qualification of forms
' ************************************************************

Option Compare Database
Option Explicit
Public Type form_aux
level As Byte
Parent() As Form
End Type

' ************************************************************
' Value of a form field to use as parameter in queries
'Parameters
' 1.) Name of sought form (or mask containing [#*?] characters).
' 2.) Name of sought field.
' The other optional parameters are used when the form and the field are not opened.
' Function value
' Value of form field ("" instead of Null) or
' Null in cas eof non existing field.
' ************************************************************

Public Function var_form_field(
form_name As String,
field_name As String, _
Optional form_openargs As String = "", _
Optional enable_manual_input As Boolean=True, _
Optional default_value As Variant, _
Optional Prompt as string="", _
Optional Title as string="", _
Optional input_mask As String = "*", _
Optional ul_case_input As String = "UL", _
Optional lr_trim_input As String = "LR&quot As Variant
Dim strdescription As String
Dim ctl As Control
Dim varff As Variant
Dim frm As Form
Dim found_form As String
Dim found_field As String
Dim Title0 as String
Dim Prompt0 as String
a:
On Error Resume Next
'Is the form opened?

found_form = Forms(form_name).Name
If Err.Number = 0 Then
'Does such field exist on the form found opened?
found_field = Forms(form_name).Controls(field_name).Name
If Err.Number = 0 Then
'If yes has it value?
var_form_field = Nz(Forms(form_name).Controls(field_name).value)
If Err.Number = 0 Then
'Yes, it has value.
Exit Function
Else
'No, it has not value.

End If

End If
End If
' I loop thru the open forms.
Err.Clear
For Each frm In Forms
varff = var_aux_form_field(form_name, field_name, frm)
If Not IsEmpty(varff) Then
var_form_field = varff
Exit Function
End If
Next
' No match, stop?
var_form_field = Null
Select Case enable_manual_input
Case True
If Prompt="" Then
Prompt0 = "Value of its field »" & field_name & "« ?"
Else
Prompt0 = Prompt
End If
If Title="" Then
Title0 = "Form »" & form name & "« is not opened"
Else
Title0 = Title
End If
Do
var_form_field = InputBox(Prompt0, Title0, default_value)
If InStr(1, UCase$(ul_case_input), "U", 2) > 0 Then var_form_field = UCase(var_form_field)
If InStr(1, UCase$(ul_case_input), "L", 2) > 0 Then var_form_field = LCase(var_form_field)
If InStr(1, UCase$(lr_trim_input), "L", 2) > 0 Then var_form_field = LTrim(var_form_field)
If InStr(1, UCase$(lr_trim_input), "R", 2) > 0 Then var_form_field = RTrim(var_form_field)
Prompt0 = "Input mask = »" & input_mask & "«."
Title0 = "Give regular value to the field"
Loop Until var_form_field = "" Or var_form_field Like (input_mask)
Case False
Err.Raise vbObjectError + 1, "The function »var_form_field« did not find the field »", _
field_name & "« of the form »" & field_name & "« ."
End Select
v:
End Function


'Auxiliary function
'Parameters
'1.) Sought form (name or mask containing [#*?] charakters).
'2.) Sought field name
'3.) Currently investigated form object
'Result
'Value of field (Null string instead of Null value)
'In case of non-existing field Empty.
PrivateFunction var_aux_form_field(form_name As String, field_name As String, frm As Form) As Variant
Dim ctl As Control
Dim varff As Variant
Dim exact_name As Boolean
Dim field_exists As Boolean
Dim found_field As String

varff = Empty
exact_name = Not (form_name Like ("*[#*?]*&quot)

If (exact_name And (frm.Name = form_name)) Or _
((Not exact_name) And (frm.Name Like (form_name))) Then
On Error Resume Next
found_field = frm.Controls(field_name).Name
If Err.Number = 0 Then
' Form has such field.
var_aux_form_field = Nz(frm.Controls(field_name).value)
If Err.Number = 0 Then
'Located field has value.
Else
'Located field has not value.
GoTo try_aux
End If
Exit Function

Else
' Form name matches but it has not such field name.

End If
Else

'Form name does not match.
End If
try_aux:
'Is the field on one of its subforms?

For Each ctl In frm.Controls
If ctl.ControlType = acSubform Then
varff = var_aux_form_field(form_name, field_name, ctl.Form)
If Not (IsEmpty(varff)) Then
Exit For
End If
End If
Next ctl
Set ctl = Nothing

var_aux_form_field = varff
End Function

Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"

 

[Spookarino]

Cheers Gentlemen.... Used both your tips and it's all running smoothly now.