Passing data to 'query parameter' in a query via form

[vinil]

SENARIO:
I have created a query in MS Access 2000, which requires a query parameter to be entered by the user. For example, under 'Query Parameter'

Parameter: IntfID
Data Type: Long Integer

and then I have one of the column which requires [IntfID]

When I run the query, pop-up dialog-box appears with 'Enter Parameter Value' IntfID. If I supply the IntfID value the query runs fine.

PROBLEM:
I now wish to call this query from various 'subforms' and pass IntfID parameter value automatically, without the parameter dialogue box appearing.

Because the field in different subforms are called by different names, I cannot use the FormName!SubFormName!FieldName method in the query criteria, else the program becomes complicated.

Is there any way I can pass the parameter/data to IntfID without the use of FormName!SubFormName!FieldName?

 

[BillPower]

Can you post the existing Query's SQL as is.

What do you use the Query for, a Form's Recordsource, Sub Form's Recordsource, Control's Rowsource.

Lastly, can you post the code that you use to run the Query. Give as much information as possible.

The saying "Less is more" doesn't apply to this platform.

 

[gsgriffin]

If you're attempting to run this report from various subforms that are open when you run it, make sure that your variable is named the same in each subform and make the criteria:

[Me]!IntfID

T-T-F-N

 

[vinil]

Hi Bill,

1. The query SQL is as follows:

PARAMETERS IntfID Long;
INSERT INTO tblWBS_Estimate ( WBS, BusinessArea, Activity, [IT System], Description, Start, Finish, Duration, ResourceID, Effort )
SELECT [BusinessAreaCd] & "." & [OA_Code] & "." & [tblSys/Interface]![SystemID] & "." & [InterfaceID] & "." & [Development Phase ID] AS WBS, tblSystem.BusinessAreaCd AS [Business Area], tblOtherActivity.OA_Code AS Activity, [tblSys/Interface].SystemID AS [IT System], "Intf: " & [InterfaceDescription] & ": " & [Development Phase] AS Description, #12/30/1899# AS Start, #12/30/1899# AS Finish, 0 AS Duration, 0 AS Resource, 0 AS Effort
FROM tlkpDevelopmentPhase, tblOtherActivity, tblSystem INNER JOIN [tblSys/Interface] ON tblSystem.SystemID = [tblSys/Interface].SystemID
WHERE (((tblOtherActivity.OA_Code)=8) AND (([tblSys/Interface].InterfaceID)=[IntfID]))
ORDER BY tblSystem.BusinessAreaCd, tblOtherActivity.OA_Code, [tblSys/Interface].SystemID, [tblSys/Interface].InterfaceID, tlkpDevelopmentPhase.[Development Phase ID];

Please note that in order to get the correct data type (at the table end) I have supplied default data for those columns that are created by the query. For example I entered #00:00:00# for Start and Finish, which appears as #12/30/1899# in the SQL here.

2. This query is used for, generating and appending several lines of records (e.g. 6 lines) to the ‘WBS’ table against each new entry for the ‘Interface’ table. Both these tables are associated with the subforms.

3. When there is a new entry in the subform sfrmInterface the AfterInsert event runs following programme, which gives the user option to create the WBS lines, if the answer is yes, the program calls the CreateWBS sub, which in turn calls two similar queries (based on the type of main form):

Private Sub Form_AfterInsert()
On Error GoTo Err_Form_AfterInsert

Dim iSysID As Integer
Dim stIntfDirection As String
Dim stMsg As String
Dim stInput As String
Dim stDocName As String
Dim stCurntFrmName As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 'Save record

stMsg = "Would you like to create corresponding WBS for this new interface? "
stInput = MsgBox(stMsg, vbYesNo, "Corresponding WBS for New interface&quot

Select Case stInput
Case vbYes
Call CreateWBS
Case vbNo
End Select
PairedSysEntry

Exit_Form_AfterInsert:
Exit Sub

Err_Form_AfterInsert:
MsgBox Err.Description
Resume Exit_Form_AfterInsert

End Sub


Private Sub CreateWBS()
On Error GoTo Err_CreateWBS

Dim stCurntFrmName As String
Dim stDocName As String

stCurntFrmName = Application.CurrentObjectName
If stCurntFrmName = "frmMetricWBS_Estimates" Then

stDocName = "qryNewIntfWBS_HL"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "qryNewIntfWBS"
DoCmd.OpenQuery stDocName, acNormal, acEdit

ElseIf stCurntFrmName = "frmSystemProfile" Then

stDocName = "qryNewIntfWBS_HL1SP"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "qryNewIntfWBS1SP"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End If

Exit_CreateWBS:
Exit Sub

Err_CreateWBS:
MsgBox Err.Description
Resume Exit_CreateWBS

End Sub

 

[BillPower]

Hi vinil,

Make a copy of your DB before attempting this.

Hopefully your app has a Form that is always open, if not create a hidden Form that Loads at Start Up. In the examples below this is called StartupFormName.

Create an Unbound Text Box on this Form, call it txtIntfID, set its Visible property to No if on a Visible Form. Format it as Number - Long Integer.

In all the Forms that Run/Open your Query, immediately before the Code that Executes the Query enter:

On a Main Form:
Forms!StartupFormName!txtIntfID = Me!DifferentIntfIDControlName

On a SubForm:
Forms!StartupFormName!txtIntfID = Me!SubformName.Form!DifferentIntfIDControlName

From your Query Remove the Parameter for IntfID.

In the Criteria box for the Field InterfaceID enter:

Forms!StartupFormName!txtIntfID

I'm not able to test this here, but in theory, this should work ok. Forms!StartupFormName!txtIntfID becomes the Query's Criteria no matter which Form you Run the Query from.

I hope I've understood your requirements, I sometimes do completely read Threads the wrong way. Let me know if this makes sense and how you get on.

Bill