in my database why next, previous and delete not work?

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

in my database why next, previous and delete not work?

Post by siamandm »

hello all
in the attached database, when you open the form ( backup add/edit customer), the buttons next, previous and delete are not working
i don't know why this happening
your help is much appreciated.
Order P0181 (2).zip
Regards
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: in my database why next, previous and delete not work?

Post by HansV »

The record source of that form is qryCustomerDetail.
The Criteria for the Customer_Number column in that query refers to [Forms]![backup Add/Edite Customer]![cboCustomerName]
So the form will only display the customer selected in cboCustomerName. You cannot move to other records using Next or Previous.
Solution:
- Clear the criteria of Customer_Number from the query.
- Change the After Update event procedure of cboCustomerName to jump to the selected customer.

You cannot delete customers that have orders in the Order table. So you'd have to delete all related records from Order and from Order_Product first.
If you want to delete related records automatically (which is dangerous!), you'd have to tick the check box 'Cascade Delete Related Records' in the relationship between Customer and Order and in the relationship between Order and Order_Product.

S0069.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: in my database why next, previous and delete not work?

Post by siamandm »

Thanks. Alot for the quick reply
So you mean i have to put a button to clear the criteria of Customer_Number from the query, how to do this please?

The second option also seems to be good option but i dont know how to do it :(
I appreciate your help

Regards

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: in my database why next, previous and delete not work?

Post by HansV »

Remove [Forms]![backup Add/Edite Customer]![cboCustomerName] from the Criteria row of the query qryCustomerDetails.
Change the After Update event procedure of cboCustomerName to:

Code: Select all

Private Sub cboCustomerName_AfterUpdate()
    With Me.RecordsetClone
        .FindFirst "Customer_Number=" & Nz(Me.cboCustomerName, 0)
        If .NoMatch Then
            Beep
        Else
            Me.Bookmark = .Bookmark
        End If
    End With
End Sub
Do you want to remove all orders related to a customer automatically when you delete the customer?
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: in my database why next, previous and delete not work?

Post by siamandm »

Yeas, please.
Thanks in advance

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: in my database why next, previous and delete not work?

Post by HansV »

Activate the Database Tools tab of the ribbon.
Click on the Relationships button.

S0070.png

Double-click the line that joins Customer to Order.
Tick the check box 'Cascade Delete Related Records' (as shown in my previous reply).
Click OK.

Do the same for the line that joins Order to Order_Product.

You can then close the Relationships window.

From now on, when you delete a customer, Access will automatically delete all orders of that customer, and all related records in the Order_Product table.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: in my database why next, previous and delete not work?

Post by siamandm »

Dear Hans,
You are a star thank you so so much for your always support.
Im sure i will never be able to find someone else helping me like you
Kind regards