Control to fill parameter query without messege box

[Psycholicon]

Hi, everybody.

I'm wondering if it's possible to fill a parameter in a query using VB without the parameter messege box coming up. The query, when performed, can return any number of results, and I then need to be able to add new ones, so I don't think that opting to put the SQL right into VB is the right choice. I have three different forms that each need to add add this data but each applies to a different situation. I have one form that is linked to the query which opens when the forms need to add this data. What I want to know is if there is a way that the parameter can be filled by a control on the form. The parameter for the field is set to [Enter Drawing Number] and one of the controls that could feed it is [Forms]![Add Drawing]![Drawing Number].

Thanks in advance!

 

[CraigDolphin]

Erm, yes. Just use [Forms]![Add Drawing]![Drawing Number] in the criteria row for the appropriate field in your query. And make sure the form is open before running the query.

 

[Psycholicon]

"Just use [Forms]![Add Drawing]![Drawing Number] in the criteria row"

Sorry, I think I didn't explain it correctly. Each of the three forms has a [Drawing Number] control that I would like to be able to fill the parameter and I'd like it to work with just one query (I could do it with three identical queries except different criteria, but I think that'd just be muddled). So I want to be able to open the form based off the query from any of the forms and have the criteria filled by either Forms![DCNs]![Drawing Number], [Forms]![Add Drawing]![Drawing Number], or [Forms]![Edit Drawing]![Drawing Number]

 

[CraigDolphin]

Hmm. Well, for the query to use a control on a form as a parameter the form must be open when the query runs. So, if all three forms are open you can use each control as a criterion. However, if only one or two of the forms are open, then it will ask for the paramter input manually.

One way to get around this would be to redefine the Record source sql dynamically for the form when opening it.

Thus, in the onload event of the form you might have something like....

dim mycriteriastring as string
If Isloaded("DCNs") then
If Isloaded("Add Drawing") then
If Isloaded("Edit Drawing") then
mycriteriastring = "WHERE [FieldName]=" & Forms!DCNs![Drawing Number] & " Or [FieldName] =" & Forms![Add Drawing]![Drawing Number] & " Or [FieldName]=" & Forms![Edit Drawing]![Drawing Number]
Else
mycriteriastring = "WHERE [FieldName]=" & Forms!DCNs![Drawing Number] & " Or [FieldName] =" & Forms![Add Drawing]![Drawing Number]
End if
Else
mycriteriastring = "WHERE [FieldName]=" & Forms!DCNs![Drawing Number]
End if
Else
If Isloaded("Add Drawing") then
If Isloaded("Edit Drawing") then
mycriteriastring = "WHERE [FieldName] =" & Forms![Add Drawing]![Drawing Number] & " Or [FieldName]=" & Forms![Edit Drawing]![Drawing Number]
Else
mycriteriastring = "WHERE [FieldName] =" & Forms![Add Drawing]![Drawing Number]
End if
Else
mycriteriastring = ""
End if
End if

As long as the query sql produces an updateable query you should be able to add new records.

An alternative method would be to create a global variable in a module and a function to retrieve the variable.

Public GvarDrawingNumber as Integer 'or string or whatever

Public Function GetDrawingNumber() as Variant
GetDrawingNumber = GvarDrawingNumber
End Function

In the after update event for the Drawing Number controls on your three forms put
If Me.[Drawing Number] & "" <> "" Then GvarDrawingNumber = Me.[Drawing Number]

In the criteria of your query place something like Nz(GetDrawingNumber(),0)