Firstly, I'd like to say this is my ICT coursework, and I'm unfortunately pretty inexperienced with Access. I can use VBA to an extent, but I really need help!
I've created a database based on a beautician's spa. Basically, I think there may be a problem with the relationships in the database, but I don't know how to fix the relationship problem, and how to carry out the resulting code. (Apologies in advance, you'll have to dumb a lot of this down for me
)
Basically, one client can make many bookings. Each booking has many parts to it (i.e: you can have a manicure and a pedicure in the same appointment). Each appointment is related by a link table to the stock table. So in short, many stock items appear in one booking (but there are many sessions within one booking, so shouldn't it be many to many?).
(Relationships attached as image)
I presume this is right? You see, it has to be totally normalised for the system to work. It works one a one-many basis (one product can appear in several bookings), but it doesn't work on a many-many level. I need help with this!
Basically, the code so far works this way:
The customer picks what type of sessions they want within the booking. Then, the VBA coding determines which code should be relative to that stock (eg: nail polish is code "CN1", Spray Tan equipment is "CN2"). When this is identified, it appears in a subform, along with the relivant quantity. When this is clicked, the quantity gets 1 taken away from it, so the product is accounted for by the company's stock.
However, this only works for the first session selected in the booking, and doesn't work after that. (I.e: One to many, but not many to many). How can I change this?
The coding:
And
I'd really appreciate any help! Thanks in advance!
I've created a database based on a beautician's spa. Basically, I think there may be a problem with the relationships in the database, but I don't know how to fix the relationship problem, and how to carry out the resulting code. (Apologies in advance, you'll have to dumb a lot of this down for me
)Basically, one client can make many bookings. Each booking has many parts to it (i.e: you can have a manicure and a pedicure in the same appointment). Each appointment is related by a link table to the stock table. So in short, many stock items appear in one booking (but there are many sessions within one booking, so shouldn't it be many to many?).
(Relationships attached as image)
I presume this is right? You see, it has to be totally normalised for the system to work. It works one a one-many basis (one product can appear in several bookings), but it doesn't work on a many-many level. I need help with this!
Basically, the code so far works this way:
The customer picks what type of sessions they want within the booking. Then, the VBA coding determines which code should be relative to that stock (eg: nail polish is code "CN1", Spray Tan equipment is "CN2"). When this is identified, it appears in a subform, along with the relivant quantity. When this is clicked, the quantity gets 1 taken away from it, so the product is accounted for by the company's stock.
However, this only works for the first session selected in the booking, and doesn't work after that. (I.e: One to many, but not many to many). How can I change this?
The coding:
Code:
If Me.Appointment_Type = "Hair/Waxing" Then
Me.Text76 = "CN00012"
ElseIf Me.Appointment_Type = "Manicure/Pedicure" And Me.ManiPedi.Column(0) = "Deluxe Pedicure & Polish" Then
Me.Text76 = "CN00013"
ElseIf Me.Appointment_Type = "Manicure/Pedicure" And Me.ManiPedi.Column(0) = "Deluxe Manicure" Or Me.Appointment_Type = "Manicure/Pedicure" And Me.ManiPedi.Column(0) = "Basic Manicure" Then
Me.Text76 = "CN00014"
End IfAnd
Code:
Me.Product_Code = Forms![FRMBooking]![Text76]
If Me.Product_Code = "CN00012" Then
Me.Quantity = Me.Quantity - 1
ElseIf Me.Product_Code = "CN00013" Then
Me.Quantity = Me.Quantity - 1
ElseIf Me.Product_Code = "CN00014" Then
Me.Quantity = Me.Quantity - 1
... etc etc.I'd really appreciate any help! Thanks in advance!
