disable the 'Enter parameter value box'

[sonikudi]

hey guys,


in my vb code i am running a query that has a null column...when i use the front end to run the query it shows a box saying 'Enter parameter value'. The null col cannot be removed and is important for the next step of this qry...

is there anyway i can disable this dialog box from showing up???

Thanks for any help.

 

[dhookom]

Why are you using a parameter prompt and the same query with code? IMHO, parameter prompts are never a good idea faq701-6763.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[sonikudi]

I am not using a parameter prompt.. All my queries are in the VB code and i am using a form for the user to enter whatever parametes i need. The problem is that in my code i have a null column..and there are reasons for which i cannot remove the null column...this column does not exist in the table ..its just an extra column..say col3.

Every time i run the query the 'Enter parameter value' dialog box pops up asking me to enter a value in for that null column. If i don't specify anything and hit ok i get the results i desire...but i don't want the IMHO box to pop up...

Hope this make it clear...
Thanks

 

[PHV]

Double check the spelling of you fields names in your query ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sonikudi]

double checked the spelling. Thats correct. I am adding my code as well, just incase i am missing something obvious, The qry in the bold is the one thats giving me the IMHO box..i need the first 2 queries inorder to run the 3rd qry...

Private Sub NoFailedPCBByET()
Dim qdf As DAO.QueryDef
Dim StrSQL As String

 StrSQL = "SELECT t1.[Error Code],t2.Description,COUNT(t1.[PCB SS #]) AS TtlNumberFailed " & _
" FROM [PCB Incoming_Production Results] AS t1, [PCB Error Codes] as t2 " & _
" WHERE t1.[Error Code]<> 'Pass' AND t1.[Error Code]<> 'n/a' AND t1.[Error Code]= t2.[Code] AND Manufacturer = """ & Me.txtMfg & """ And [Date] Between #" & _
   Me.txtMfgStart & "# AND #" & Me.txtMfgEnd & "# GROUP BY t1.[Error Code],t2.Description; "

    Set qdf = CurrentDb.QueryDefs("qryNoFailedPCBByET")
    'if query exists
    qdf.SQL = StrSQL
End Sub

Private Sub NotHaveED()
Dim qdf As DAO.QueryDef
Dim StrSQL As String
[b]
StrSQL = "SELECT (t1.[Error Code]), NULL AS col3, count (t1.[Error Code])As TtlNumberFailed " & _
"FROM [PCB Incoming_Production Results] AS t1 LEFT JOIN [PCB Error Codes] ON t1.[Error Code] = [PCB Error Codes].CODE " & _
"WHERE ((([PCB Error Codes].Code) Is Null)) AND t1.[Error Code]<>'n/a' GROUP BY (t1.[Error Code]), col3; "[/b]

    Set qdf = CurrentDb.QueryDefs("qryNotHaveED")
    qdf.SQL = StrSQL
End Sub

Private Sub Unionqry()
Dim qdf As DAO.QueryDef
Dim StrSQL As String
  
StrSQL = "SELECT * FROM qryNoFailedPCBByET UNION SELECT * FROM qryNotHaveED; "
Set qdf = CurrentDb.QueryDefs("qryUnionqry")
  'if query exists
 qdf.SQL = StrSQL
End Sub

 

[PHV]

Replace this:
"WHERE ((([PCB Error Codes].Code) Is Null)) AND t1.[Error Code]<>'n/a' GROUP BY (t1.[Error Code]), col3; "
with this:
"WHERE [PCB Error Codes].Code Is Null AND t1.[Error Code]<>'n/a' GROUP BY t1.[Error Code]; "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sonikudi]

Thank you PH!....who woulda thought that such a simple solution could have fixed such an annoying problem...thanks..