Message Box from query when opening form

[Ms Kathy]

I have a command button which uses a macro to open a form for the user to enter an invoice. Here is the macro info: F_Invoice, Form, , , , Normal

Since I don't want the user to be able to open an invoice that had already been entered the query has a parameter of InvoiceDate = is null.

This works, in that the user can't access that invoice for editing. However, it shows a blank invoice. I would rather it show a message box saying something like "Invoice had been entered already"

I've been looking around on this forum and on google, and found something that may (or may not) work?:
If Dcount("*","queryB") = 0 Then
MsgBox "no data available"
Else
DoCmd.OpenForm "FormB"
End If

But I don't know where to place this? I'm probably going about this wrong . . . any direction would help!! Thank you.

 

[Ms Kathy]

How do I make 2 commands happen? I want to open the form and also show a message box (show a message box only if the invoice had been entered already).

 

[AliyuKatsina]

If F_Invoice is your form then you need to create an OnOpen event to open the form display your message when the invoice has been entered.

 

[Ms Kathy]

This is the query (jpg attached) that is the record source for the Invoice form.

There is a command button on a navigation page that opens this Invoice form.

If the invoice was entered previously the date and weight had been filled in so the query then returns no record and the Invoice is blank. It is at this point I would like a message box to pop up.

 

[AliyuKatsina]

Is this Query expected to return one record?

 

[Ms Kathy]

Yes, one record.

 

[Ms Kathy]

I googled some more and found this code which I tried but get error messages (I don't know anything about code - I just entered it exactly as written - so I don't know what's wrong or if it's even a possibility):

Private Sub C_OpenInvoice_Click()
On Error GoTo Err_C_OpenInvoice_Click
Dim stDocName As String
stDocName = "F_Invoice"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim intHolder as integer intHolder=DCount("Trans_InvoiceDate","Q_Invoice_EnterWeight")
If intHolder > 0 Then
stDocName = "F_Invoice"
DoCmd.OpenForm st DocName,,,stLinkCriteria Else
MsgBox "That Transaction has been Invoiced"
End If
Exit_C_OpenInvoice_Click:
Exit Sub
Err_C_OpenInvoice_Click:
MsgBox Err.Description
Resume Exit_C_OpenInvoice_Click
End Sub

 

[Ms Kathy]

here's the error

 

[pr2-eugin]

Are you sure that the button name is C_OpenInvoice, spelling checked? What exactly does the error say? The screenshot shows only the section where the error is.

 

[Ms Kathy]

The button name is correct. Here is the message I got.

 

[pr2-eugin]

Edit your code properly !

Private Sub C_OpenInvoice_Click()
On Error GoTo Err_C_OpenInvoice_Click
    Dim stDocName As String
    Dim intHolder As Integer 
    
    intHolder = DCount("*","Q_Invoice_En terWeight")
    If intHolder > 0 Then
        stDocName = "F_Invoice"
        DoCmd.OpenForm stDocName,,,stLinkCriteria 
    Else
        MsgBox "That Transaction has been Invoiced"
    End If

Exit_C_OpenInvoice_Click:
    Exit Sub
Err_C_OpenInvoice_Click:
    MsgBox Err.Description
    Resume Exit_C_OpenInvoice_Click
End Sub

 

[Ms Kathy]

I entered your code and this is the message I got (query parameter of entering a transaction number).

 

[Ms Kathy]

The entire T_Transaction table (*) from which these fields are part of is in the query. (I see that it's not visible in that screen shot.) Those fields that are "not shown" are placed there for the query parameters only. I'm asking for the "Trans_AutoID" so that only one record comes up to be invoiced. The other two fields, "Trans_Weight" and "Trans_InvoiceDate" have the parameter of "is null". This is my attempt at prohibiting the user from entering the same transaction number more than once (so the invoice cannot be modified after the original entry). I hope this answers your question. I have limited knowledge.

 

[Ms Kathy]

I did notice that space; however the space is not in the code.

 

[Ms Kathy]

The code that Paul E provided worked after I edited the query (I got rid of the parameter of "Enter Transaction ID"). So now this code will pull up all the transactions that have not been invoiced. And if there are none the message box is displayed. So that works. However, the problem is that if there is more than one transaction to be invoiced you can go from record to record and back again. That enables the user to modify invoices. They should only be able to enter the data once, print the invoice, and then go on to the next one. Another issue that popped up is the print command. Using this code and pulling up all open invoices the print command button prints all invoices. (Where as before I was using the parameter in the query "Enter Transaction ID" to open only one transaction, then the print button printed just that one) . . .