strange problem

[Dimitris Nikolakakis]

I have a form named FOrders with fields:
FactoryID (text) and some other fields.

I have a button on the form 'Command88' that in OnClick:
**************************************************************************
Private Sub Command88_Click()
Dim Response

If FactoryID = "KNIPEX" Then

DoCmd.TransferText acExportFixed, "QOrderASCII(KNIPEX)",
"qOrderExportKNIPEX", "c:\order.txt"
Response = MsgBox("bla bla bla bla bla bla", vbOKOnly, "bla bla bla")
If MsgBox("Question", vbYesNo + vbQuestion) = vbNo Then
Cancel = True
Me.ActiveControl.Undo
Else: SendMessage "C:\order.txt"
Me!DateEmailed = Now()
End If
Else
If FactoryID = "IRWIN" Then
DoCmd.OutputTo acOutputQuery, "qOrderExportIRWIN", acFormatXLS,
"c:\order.xls", 0
Response = MsgBox("bla bla bla", vbOKOnly, "bla bla bla")
If MsgBox("Question", vbYesNo + vbQuestion) = vbNo Then
Cancel = True
Me.ActiveControl.Undo
Else: SendMessage "C:\order.xls"
Me!DateEmailed = Now()
End If
End If

End If

End Sub
**************************************************************************

The problem is that:

If the factory is KNIPEX I have no problem........but...

if the factory is IRWIN then if in the question I answer YES (which means
that it sends a mail with SendMessage module) it sends the mail and no other
problem.
If I answer NO then, in a magical way, the query qOrderExportIRWIN is
'damaged'...it only has "SELECT;" and the rest of the code is missing

CODE of QRY is:
****************************
SELECT [OrdersTx].[OrderNo], RTrim([OrdersTx].[StorageItem] &
Space(20-Len([OrdersTx].[StorageItem]))) AS StorageItem,
[OrdersTx].[Quantity]
FROM OrdersTx
WHERE [OrdersTx].[TypeID]="ORD" And
[OrdersTx].[OrderNo]=[Forms]![FOrders]!OrderID
ORDER BY [OrdersTx].[StorageItem];
******************************

Any help is appreciated.....


thanks in advance
Dimitris Nikolakakis

 

[Dirk Goldgar]

I have a form named FOrders with fields:
FactoryID (text) and some other fields.

I have a button on the form 'Command88' that in OnClick:
************************************************************************
**
Private Sub Command88_Click()
Dim Response

If FactoryID = "KNIPEX" Then

DoCmd.TransferText acExportFixed, "QOrderASCII(KNIPEX)",
"qOrderExportKNIPEX", "c:\order.txt"
Response = MsgBox("bla bla bla bla bla bla", vbOKOnly, "bla bla bla")
If MsgBox("Question", vbYesNo + vbQuestion) = vbNo Then
Cancel = True
Me.ActiveControl.Undo
Else: SendMessage "C:\order.txt"
Me!DateEmailed = Now()
End If
Else
If FactoryID = "IRWIN" Then
DoCmd.OutputTo acOutputQuery, "qOrderExportIRWIN", acFormatXLS,
"c:\order.xls", 0
Response = MsgBox("bla bla bla", vbOKOnly, "bla bla bla")
If MsgBox("Question", vbYesNo + vbQuestion) = vbNo Then
Cancel = True
Me.ActiveControl.Undo
Else: SendMessage "C:\order.xls"
Me!DateEmailed = Now()
End If
End If

End If

End Sub
************************************************************************
**

The problem is that:

If the factory is KNIPEX I have no problem........but...

if the factory is IRWIN then if in the question I answer YES (which
means that it sends a mail with SendMessage module) it sends the mail
and no other problem.
If I answer NO then, in a magical way, the query qOrderExportIRWIN is
'damaged'...it only has "SELECT;" and the rest of the code is missing

CODE of QRY is:
****************************
SELECT [OrdersTx].[OrderNo], RTrim([OrdersTx].[StorageItem] &
Space(20-Len([OrdersTx].[StorageItem]))) AS StorageItem,
[OrdersTx].[Quantity]
FROM OrdersTx
WHERE [OrdersTx].[TypeID]="ORD" And
[OrdersTx].[OrderNo]=[Forms]![FOrders]!OrderID
ORDER BY [OrdersTx].[StorageItem];
******************************

Any help is appreciated.....


thanks in advance
Dimitris Nikolakakis

You're saying that after you click this command button and answer "No"
to the MsgBox, if you later go back and open the stored query
"qOrderExportIRWIN" in SQL View, there is nothing there but "SELECT;"?
That is bizarre!

I see two problems with your code, but I don't see how they could cause
that result. First, you write

Cancel = True

but the Click event procedure has no Cancel argument and you have no
variable named "Cancel" defined, at least not at the procedure level.
It's not clear what you mean to accomplish with that assignment.
However, I conclude that you have your VB options set to not require
variable declarations (no "Option Explicit"), so the statement should
accomplish nothing. I can see no reason why that minor problem would
have any effect on the query.

Second, you write

Me.ActiveControl.Undo

in the Click event procedure of your command button. I fail to see what
this can achieve, since the active control at this point must be the
command button itself. Again, though, I don't see how it can cause the
effect you describe.

May I suggest that you add the line

Debug.Print CurrentDb.QueryDefs("qOrderExportIRWIN").SQL

at the top of that event procedure and again at the bottom? Then click
the button (with FactoryID = "IRWIN"), answer "No" to the MsgBox, and
then check the Immediate Window for the output of the Debug.Print
statements. That will at least confirm or refute the assertion that the
query is being modified by this code.