Form Issues

[Leesha]

Hi,
I've attached a stripped down version of a DB. The form that opens is set to allow dates within the specified date range to be entered when the user clicks the down button. If the user gets to the end of the month they are alerted that there are no more days available to enter. This works fine. They ae funning into two issues:

1. If they edit the net sales, row moves and often times adds a new row, resulting in duplicates for a date.
2. If the user is entering dates and net sales and stops before getting to the end of the month, and then starts when they left off the form works fine. However if they put in a wrong date, for example one that has already been entered, they end up with duplicate dates.

I need the form to not allow duplicate dates under any circumstances and to allow edits without changing the view of the form.

Thanks,
Leesha

[HansV]

You have code in NET_SALES_AfterUpdate that explicitly creates a new record when the user edits net sales. If you do not want that, remove that code.

You might create a unique index on Service_Date. That way, Access will not allow duplicate dates to be entered.
You can also expand the Before Update event of Service_Date:

Code: Select all

Private Sub SERVICE_DATE_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.txtStoreID) Then
        MsgBox "Please enter the Store ID."
        Me.txtStoreID.SetFocus
        Cancel = True
    ElseIf DCount("*", "dbo_tblSales", "Service_Date=#" & Format(Me.SERVICE_DATE, "mm/dd/yyyy") & "# AND ID<>" & Nz(Me.id, 0)) Then
        MsgBox "This service date has already been entered!", vbExclamation
        Cancel = True
    End If
End Sub

[Leesha]

Hi Hans!
The code in the NET_SALES afterupdate is the code they love. I took that out to prevent duplicates etc. and then they complained re having to enter each date. Unfortunately there are almost 2000 stores so I wouldn't be able to create a unique index for the Service_Date. I'll give your suggestion on the before update a shot.
Thanks!
Leesha

[Leesha]

Hi Hans,
I tried the code you suggested on the Before Update and that worked if the user is trying to enter a date that is already entered so that is great! There is still the issue when a change is made to the actual net_sales that is already entered. It creates a new row with a duplicate date. I realize this is coming from the after update code but am not sure how to fix it.

I also just noticed that the Before-Update code isn't letting me put in a new date that hasn't been entered on the form because it sees it in tblSalesAdjustments. Ugh.

Leesha

[HansV]

I think you should remove the code that creates a new record from the After Update event of the Net Sales text box.
You might use a command button to create a new record.

[Leesha]

They are sure to complain if I give them a new click on a command button. They've used this code for years (that's how long ago you gave it to me) to race through a from to add all the days in a month, vs having to enter each date by hand. When I took that ability away in order to prevent duplicate dates they let me know they didn't like it. The only other thing I can think of is to give them a command button that would enter all the days in the date range they have entered at the top of the form, using an update query to enter the dates into tblSalesAdmjustments, however I'm not sure what the code would look like to enter all the days for the month. Would that be possible?
Leesha

[HansV]

Perhaps this?

Code: Select all

Private Sub cmdNewMonth_Click()
    Dim dtm As Date
    Dim d As Date
    Dim sql As String
    On Error GoTo ErrHandler
    If IsNull(Me.txtWalmartNumber) Then
        Me.txtWalmartNumber.SetFocus
        MsgBox "Please enter the Walmart number!", vbExclamation
        Exit Sub
    End If
    dtm = InputBox(Prompt:="Enter the first day of the month to add", Default:=Date)
    dtm = dtm - Day(dtm) + 1
    If DCount("*", "dbo_tblSales", "Service_Date=#" & Format(dtm, "mm/dd/yyyy") & "#") > 0 Then
        MsgBox "This month has already been added!", vbExclamation
        Exit Sub
    End If
    For d = dtm To DateAdd("m", 1, dtm) - 1
        sql = "INSERT INTO dbo_tblSales (Service_Date, [Walmart Number]) " & _
            "VALUES (#" & Format(d, "mm/dd/yyyy") & "#, " & Me.txtWalmartNumber & ")"
        CurrentDb.Execute sql, dbFailOnError
    Next d
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
End Sub

[Leesha]

Nice popup! Unfortunately it gives me the error that this date has already been entered. I'm assuming it's because it's already in tables for other stores. Is there a way to adjust the code so that it looks for the Service_date and the [Walmart Number] at the same time?

[HansV]

Yep, I should have taken that into account. Change

Code: Select all

    If DCount("*", "dbo_tblSales", "Service_Date=#" & Format(dtm, "mm/dd/yyyy") & "#") > 0 Then

to

Code: Select all

    If DCount("*", "dbo_tblSales", "Service_Date=#" & Format(dtm, "mm/dd/yyyy") & _
            "# AND [Walmart Number]=" & Me.txtWalmartNumber) > 0 Then

[Leesha]

Now I get an ODBC--call failed error. The backend tables are in SQL. I don't know if that makes a difference. I put them in access in the stripped down version to send to you.

[HansV]

Does this work?

Code: Select all

    If DCount("*", "dbo_tblSales", "Service_Date='" & Format(dtm, "yyyy-mm-dd") & _
            "' AND [Walmart Number]=" & Me.txtWalmartNumber) > 0 Then

[Leesha]

I get an error "data type mismatch in criteria expression."

[HansV]

I'm sorry, I don't know what else to suggest.

[Leesha]

I appreciate the help!!