MYSQl Update in VBA Access 2007

[Neil07]

Hello, I am doing an assignment in access 2007 and i'm stuck at updating a record.I have rent form where there is a textbox which label is film_ID and aside have a combo box with values:DVD,3D,Blue-ray label Medium.In the medium table, I have 4 fields namely Film_ID,DVD,3D,Blue-ray.I have a save button in the rent form and what I need is:When i clicked on save, it decrements DVD/3D/Blue-ray by 1 of the specified film_ID present in the medium table based on what film_ID and medium I entered in the rent form.

Actually I am trying to use an SQL update statement to perform this task but its not working.

DoCmd.RunSQL "UPDATE tbl_Medium SET DVD='DVD-1' WHERE tbl_medium.Film_ID='Frm_Rent.[Me.txtbox3]' and txtbox8='DVD'"

txtbox1 refers to Film_ID in medium

txtbox3 refers to film_ID in rent

txtbox8 refers to the combo box Medium in rent

I will be very gratefully if you can help me solve this since the submission of the assignment is approaching.

[PhilOfWalton]

I am trying to understand what you want.

Are you saying you have a form that lists a film title and the film media can be either Blue-ray, DVD or 3D.

Or are you saying you could have up to 3 versions of the same film on any or all the three types of media? .... or something entirely different?

Depending on your answer you may or may not have table structure correct

Phil

[Neil07]

I am stating that I have rent form where i can choose which film_ID and what type of medium the client need.Then I have a medium table with 4 fields namely the film_ID,DVD,3D and Blue-ray.In the medium table, the field DVD is the number of copies available for a film and so on for the other also.I want that when I clicked on the saved button in my rent form,it decrements the number of copies by 1 in the medium table based on what what film_ID and medium I chosen in the rent form.

[Neil07]

To reply to your question, Yes in the medium table a film_ID may possess all 3 types of medium as it may have only 2 medium or only one.
For example in the medium table it looks like:

FILM_ID/DVD/3D/BLUE-RAY
100/5/5/5
101/5/NULL/5
And i want if i choose film_ID=100 and medium=DVD in rent and i clicked on save button it updates the medium table and it should looks like:

FILM_ID/DVD/3D/BLUE-RAY
100/4/5/5
101/5/NULL/5


THANKS IN ADVANCE
Neil

[PhilOfWalton]

I think your better approach would be a main form showing the film details
I would have a table TblStock something like

StockID       AutoNumber
FilmID        Long      (To point to the film)
MediaID       Long      (To point to the type of media)
StockBalance  Integer
 

Create a subform based on TblStock with a Combo Box to select the Media. Add 2 command buttons, one to subtract and one to add films back as they are returned.
A simple bit of code for the buttons should do the trick

Aircode:-

Private Sub CmdSubtract_Click
    Dim MyDb as Database
    Dim StockSet as RecordSet
    Dim SQLStg as String
 
    SQLStg = "SELECT TblStock.* FROM TblStock WHERE FilmID = " & FilmID
    SQLStg = SQLStg & " AND MediaID = " & MediaID & ";"
 
    Set MyDb = CurrentDb
    Set StockSet = Mydb.OpenRecordSet(SQLStg)
    With StockSet
        If !StockBalance <= 0 then
            Msgbox "Borrow a film from your mates first!!", vbInformation
            .Close
            Set StockSet = Nothing
            Exit Sub
        End If
        .Edit
        !StockBalance = Nz(!StockBalance) -1
        . Update
        .Close
        Set StockSet = Nothing
        Exit Sub
    End With
 
End Sub
 

Adding a film back into stock is similar but without the check.

If I were doing this for real, I would want to know who the film had been lent to, when and when it was returned ... but that's another story.

Phil

[Neil07]

The system is actually as who told at the end but I tried to save time in explaining only the part I need.The medium table represent the tblStock but actually under a different name.In the rent form it seems like that:

1.Rent_Id (Primary key) (AutoNumber)
2.Customer_ID (foreign key)
3.Film1_ID (foreign key) references film table
4.Medium_1 (which is a combo box with:DVD,3D,Blue-ray)
5.Film2_ID (foreign key) references film table
6.Medium_2(which ......)
7.Issue_Date
8.Return_Date

I do also have a return table but I need firstly to know how to derement the value on renting then i would increment it when the copy is returned.

In the medium table:

1.Film_ID (Foreign key) references film table
2.DVD (Numeric)
3.3D (Numeric)
4.Blue-ray (Numeric)

Note:DVD is actually a field and values entered are the number of copies available and so on for the others.

Through that I do actually know how many copies I have per medium for each film.


I want through an sql statement to decrement the value of the DVD of a specific film_ID i would choose in the rent form.

Thank you for according me your time.
Neil

[Neil07]

I write a logic algorithm in hope you can better understand me.

look up tbl_Medium.[Film_ID]=frm_Rent.[Me.Film1_ID]

if frm_Rent.[Medium_1]='DVD' then

tbl_Medium.[DVD]='DVD-1'

Else if

frm_Rent.[Medium_1]='3D' then

tbl_Medium.[3D]='3D-1'

Else if

frm_Rent.[Medium_1]='Blue-Ray' then

tbl_Medium.[Blue-Ray]='Blue-Ray-1'

end if

End

Thats what I was expecting.

Neil