Connection Object Question [Resolved]

[Simply Me]

Hi! I have this two table namely Books and BorrowedBooks. The books table has field named as "Available" which has Yes/No data type. What i want to achieve is that when I issue a certain book, I would like the the "Available" field which is by default is checked [the check box] would be unchecked. Right now i have this code: I dunno know if what im doing actually is correct.

Private Sub cmdIssueBook1_Click()
Dim sSQL1 As String
Dim sSQL2 As String
 
sSQL1 = "INSERT INTO BorrowedBooks (IDNO,Title,AccNum,DateBorrowed,DateDue) VALUES" & _
    "('" & lblStudIDDisp.Caption & "','" & lblBook1Title.Caption & _
     "','" & lblAccNum1.Caption & "','" & lblDateBorrowedDisp.Caption & _
     "','" & lblDateDueDisp.Caption & "')"
    MyConn.Execute sSQL1, , adCmdText + adExecuteNoRecords
        
    MyConn.Execute sSQL1, "UPDATE books SET Available = TRUE"
        
If MyRs6.State = 1 Then MyRs6.Close
sSQL2 = "SELECT LName,FName,MI from borrowers where LName= '" & lblStudLNameDisp.Caption & "'AND FName='" & lblStudFName.Caption & "' and MI='" & lblStudMIDisp.Caption & "'"
MyRs6.Open sSQL2, MyConn, adOpenKeyset, adLockOptimistic
 
MsgBox "Book has been recorded to the account of " & lblStudFNameDisp.Caption & " " & lblStudMIDisp.Caption & " " & lblStudLNameDisp.Caption & "."
End Sub

my first time to use .execute

[dglienna]

First question is "Does it work?" I would think that you'd have to tell it which record in the Books table that you want to update. Other than that, it looks correct to me,but it is awfully late around here

[Simply Me]

First question is "Does it work?" I would think that you'd have to tell it which record in the Books table that you want to update. Other than that, it looks correct to me,but it is awfully late around here

it does record the book the borrowedbooks (twice though) but not unchecked the book in the books table.

How do i specify which book is it? ahhh, I will use the primary of that book. but how?

[dglienna]

it does record the book the borrowedbooks (twice though) but not unchecked the book in the books table.

How do i specify which book is it? ahhh, I will use the primary of that book. but how?

Don't know why it does it twice.

something like this?

"UPDATE books SET Available = TRUE WHERE IDNO = " & text1.text

[Simply Me]

Don't know why it does it twice.

something like this?

"UPDATE books SET Available = TRUE WHERE IDNO = " & text1.text

it is still not unchecking my yes/no field in my books table.

btw im using access

[Mc Brain]

Try this:

MyConn.Execute sSQL1, "UPDATE books SET Available = [B]1[/B] " & _
  & "WHERE IDNO = " & lblStudIDDisp.Caption

[Mc Brain]

One question, though.... if your book is borrowed, shouldn't the Available field change to 0??

[Simply Me]

One question, though.... if your book is borrowed, shouldn't the Available field change to 0??

My Available field is uses the Yes/No (by default is checked) data type in access. so if it borrowed then will be unchecked.

ooopppsss my code is =true. thats why im not getting anything. I'll try it again.

I tried using 0 and still not giving me correct result

[Simply Me]

anyone here please?

[dglienna]

Try -1, False, 0, 1, True, "1", "-1", "0" one of them should work
(I was going to say use false last night)

[Simply Me]

Try -1, False, 0, 1, True, "1", "-1", "0" one of them should work
(I was going to say use false last night)

tried this double qoute and the others and it is error. tried single Qoute, still not unchecking.

MyConn.Execute sSQL1, "UPDATE books SET Available = "0" WHERE AccNum = '" & Val(lblAccNum1.Caption) & "'"

[dglienna]

it's probably the other section. the acc num, try to see if the where part is right.

[Simply Me]

it's probably the other section. the acc num, try to see if the where part is right.

Even though i remove this:

WHERE AccNum = '" & Val(lblAccNum1.Caption) & "'"

still not unchecking the availabe field.

[dglienna]

Try putting a valid number in instead of the field.
then change the values. I googled and found this:

UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'

[dglienna]

Here is the sql code out of access with a yes/no field and the correct field info.
It unchecks the checked checkbox.

UPDATE FICHAS 
SET FICHAS.[check] = '0'
WHERE (((FICHAS.Factores)='1'));

an old db that i had on my system. aded the check field, changed factores to 1 and then ran it. it changed check to unchecked (0)

[Simply Me]

Here is the sql code out of access with a yes/no field and the correct field info.
It unchecks the checked checkbox.

UPDATE FICHAS 
SET FICHAS.[check] = '0'
WHERE (((FICHAS.Factores)='1'));

an old db that i had on my system. aded the check field, changed factores to 1 and then ran it. it changed check to unchecked (0)

can you explain further please?

[dglienna]

I added the field check and then changed a record in the db, and made the factores field equal to 1. I then checked the checkbox.

I then messed around making a query until I had one that did what I wanted.
I then posted the sql for you. that sql unchecks the box where factores=1

[dglienna]

MyConn.Execute sSQL1, "UPDATE books SET books.Available = '0' WHERE books.AccNum = '" & Val(lblAccNum1.Caption) & "'"

Maybe?

[Simply Me]

MyConn.Execute sSQL1, "UPDATE books SET books.Available = '0' WHERE books.AccNum = '" & Val(lblAccNum1.Caption) & "'"

Maybe?

Tried that one too.

[dglienna]

Get a valid book number (Ill subtitute 999 for this example, and try this. if the book isn't a numeric value you would have a problem

MyConn.Execute sSQL1, "UPDATE books SET books.Available = '0' WHERE books.AccNum = '999'"

[Simply Me]

Get a valid book number (Ill subtitute 999 for this example, and try this. if the book isn't a numeric value you would have a problem

MyConn.Execute sSQL1, "UPDATE books SET books.Available = '0' WHERE books.AccNum = '999'"

not working still....

[dglienna]

Show your code with the valid book number. Are you sure the query is even executing? Did you step through it using F8? It worked on my machine.
Is there a problem with your connection string, the .Execute method, or something else not related to the Update?

[Simply Me]

Show your code with the valid book number. Are you sure the query is even executing? Did you step through it using F8? It worked on my machine.
Is there a problem with your connection string, the .Execute method, or something else not related to the Update?

Dim sSQL1 As String
Dim sSQL2 As String
Dim sql As String

sSQL1 = "INSERT INTO BorrowedBooks (IDNO,Title,AccNum,DateBorrowed,DateDue) VALUES" & _
"('" & lblStudIDDisp.Caption & "','" & lblBook1Title.Caption & _
"','" & lblAccNum1.Caption & "','" & lblDateBorrowedDisp.Caption & _
"','" & lblDateDueDisp.Caption & "')"
MyConn.Execute sSQL1, , adCmdText + adExecuteNoRecords

MyConn.Execute sSQL1, "UPDATE books SET Available = '0' WHERE AccNum = 100000

[Mc Brain]

Are you sure you don't have an On Error Resume Next somewhere? Maybe you have one, the code is raising an error... and going on with the next line.

[dglienna]

Maybe because you left off the quotes?

MyConn.Execute sSQL1, "UPDATE books SET books.Available = '0' WHERE books.AccNum = '100000'"

that's how outlook does it. after it works, you can remove things.
paste this in and try it.

[Simply Me]

Are you sure you don't have an On Error Resume Next somewhere? Maybe you have one, the code is raising an error... and going on with the next line.

no i dont have any

[dglienna]

Did you try my last suggestion?

[Simply Me]

thanks for your replies buddies your replies helped me alot and special thanks to erickwidya.

this is my final code and it is working now.

sSQL1 = "INSERT INTO BorrowedBooks (IDNO,Title,AccNum,DateBorrowed,DateDue) VALUES" & _
    "('" & lblStudIDDisp.Caption & "','" & lblBook1Title.Caption & _
     "','" & lblAccNum1.Caption & "','" & lblDateBorrowedDisp.Caption & _
     "','" & lblDateDueDisp.Caption & "')"
    
    MyConn.Execute sSQL1, , adCmdText + adExecuteNoRecords
    sSQL = "UPDATE [books] SET [Available] = 0 WHERE AccNum = '" & Val(lblAccNum1.Caption) & "'"
    MyConn.Execute sSQL

[dglienna]

thanks for your replies buddies your replies helped me alot and special thanks to erickwidya.

this is my final code and it is working now.

What did he say?
Glad it's working, but as I said, I ran an update that worked exactly the way that I said to do it.

[Mc Brain]

I can't believe it!! The only difference are the brakets!! Are you telling me that 'Available' is a reserved word for SQL and/or Access???

[dglienna]

I thought about that, but decided prolly not. He didn't say what it was.