Two error messages needed

[Leesha]

Hi,

I have a frmSalesAdjustments that is populated when a query is run. I need to be able to alert the user when there is no data in the query. I've tried a bunch of different events and different code but nothing is working.

The other alert that I need is if the user enters data into txtAccountNumber I need to be able to be sure that there are 9 letters entered. the text box is set to text and if must have 9 characters / letters in it.

Thanks,
Leesha

[HansV]

1) Is the query run before the form is opened or while the form is opened? If the latter, how?

2) The following code will require the account number to contain exactly 9 characters, or to be blank (empty, null):

Code: Select all

Private Sub txtAccountNumber_BeforeUpdate(Cancel As Integer)
  If Not IsNull(Me.txtAccountNumber) Then
    If Len(Me.txtAccountNumber) <> 9 Then
      MsgBox "The account number should be exactly 9 characters long"
      Cancel = True
    End If
  End If
End Sub

[Leesha]

Hi Hans,

To answer your first question, the query is run after the form is open with code in the click event of a command button.

I've never used "Len" before. Can this be used with text as well as numbers?

Thanks!
Leesha

[HansV]

For the first question, I'll need detailed information.

About Len: it's primarily intended for use with text strings. Len(text) returns the number of characters in text.

[Leesha]

Cool!!

Re the other query, here is the sql:

SELECT tblSales.ID, tblSales.Store_ID AS [STORE ID], tblSales.[Franchise Number] AS FRANCHISE, tblSales.Service_Date AS [SERVICE DATE], tblSales.Net_Sales AS [NET SALES], tblSales.importdate AS [iMPORT DATE], tblSales.EditDate AS [EDIT DATE], tblSales.EditEmployeeName AS EMPLOYEE
FROM tblSales
GROUP BY tblSales.ID, tblSales.Store_ID, tblSales.[Franchise Number], tblSales.Service_Date, tblSales.Net_Sales, tblSales.importdate, tblSales.EditDate, tblSales.EditEmployeeName
HAVING (((tblSales.[Franchise Number])=[forms]![frmSalesAdjustments]![txtFranchiseNumber]) AND ((tblSales.Service_Date) Between [forms]![frmSalesAdjustments]![txtFrom] And [forms]![frmSalesAdjustments]![txtENDDATE]));

The form populates with the data returned between the date range and franchise number. When there is no data, want to put up a msgbox that states there is no data. Right now the user wonders if something went wrong.

Thanks,
Leesha

[HansV]

But how do you use this query? Do you set the Record Source of the form to this SQL, or to a saved query with this SQL?

[Leesha]

Sorry. Yes the record source of the form is set to this SQL.

Leesha

[HansV]

Try this in the code behind the command button, before setting the record source:

Code: Select all

  If DCount("*", "tblSales", "[Franchise Number]=[Forms]![frmSalesAdjustments]![txtFranchiseNumber] AND Service_Date Between [Forms]![frmSalesAdjustments]![txtFrom] And [Forms]![frmSalesAdjustments]![txtENDDATE]") = 0 Then
    MsgBox "There are no records meeting the conditions!", vbExclamation
    Exit Sub
  End If

[Leesha]

Hi Hans,

The error comes up even when there is data in the tables.
I did double check to make sure there was data :-)
Leesha

[HansV]

I'm afraid it's impossible to tell why without seeing the database...

[Leesha]

Here you go!
Leesha

[HansV]

The record source of your form has

...
HAVING (((tblSales.[Franchise Number])=[forms]![frmSalesAdjustments]![txtWalmartNumber]) AND ...

but there is no text box txtWalmartNumber on the form. It should be

...
HAVING (((tblSales.[Franchise Number])=[forms]![frmSalesAdjustments]![txtFranchiseNumber]) AND ...

And the code has

If DCount("*", "tblSales", "[Franchise Number]=[Forms]![frmSalesAdjustments]![Franchise Number] AND ...

which is not what I posted - see higher up in this thread. It should have been

If DCount("*", "tblSales", "[Franchise Number]=[Forms]![frmSalesAdjustments]![txtFranchiseNumber] AND ...

[Leesha]

Sorry for the confusion Hans. In trying to not upload confidential info I had changed it but obviously not consistently.

Leesha