Database Manipulation!!!

[Israelsimba]

im facing a challenge here peple. i will give as much detail as i can.
i am trying to come up with code such that when a button is clicked it runs through a field "copiesremainng" in my subscription table and subtracts 1 from every record in that field. this has to be achieved by clicking only once.the code im using and is not doing anything nor giving me a syntax error, is as follows

With ri
        .ActiveConnection = deSub.conn
        .LockType = adLockOptimistic
        .Source = "select * from cooperatesubs"
       .Open
   End With

       Do Until ri.EOF = True
       q = ri.Fields("copiesout")
       r = ri.Fields("numberofcopies")

      s = r - q 'reducing the number by the copies to be given out  every time
       ri.Fields("copiesremaining") = s 'updating the field with the new figure
       ri.Update
       ri.MoveNext
     Loop
   Exit Sub

[debasisdas]

You need to call AddNew or Edit method before calling Update.

[Israelsimba]

i have added the .addnew method to look like the following but it now ads a new row with blank fields and only one field at the end.

With ri
         .ActiveConnection = deSub.conn
         .LockType = adLockOptimistic
         .Source = "select * from cooperatesubs"
         .Open
         End With
          
         Do Until ri.EOF = True
         q = ri.Fields("copiesout")
        r = ri.Fields("numberofcopies")
         s = r - q 
           ri.addnew
        ri.Fields("copiesremaining") = s 
        ri.Update
        ri.MoveNext
        Loop
        Exit Sub

i cant also find the .edit on the dropdown instead it is giving me the .editmode method. isnt there a way i can udate the row to make the effect on one row because if i add a new record everytime it becomes a very big database within a year.i simply need it to insert a new figure in a single field.

thanx anyway

[vb5prgrmr]

here is a one shot deal that you can use with the command object...

strSQL = "UPDATE cooperatesubs SET [cooperatesubs].[copiesremaining]=[cooperatesubs].[numberofcopies]-[cooperatesubs].[copiesout]"
Cmd.Execute strSQL

and if you open your connection to the database with pessimistic locking, the whole table will be updated in one shot without errors from other people accessing the data in a multi user environment.

Good Luck

[debasisdas]

i have added the .addnew method to look like the following but it now ads a new row with blank fields and only one field at the end.

that is because you have assigned value only for one field (copiesremaining) not for others.

[Israelsimba]

my question is that i dont want it to add a new record because i will have an unnecessarilly too big table at the end. i want it to juss update the affected record. i want just one field(copiesremaining) changed...not the entire row. hope you get the sense

[vb5prgrmr]

See my post...

[Israelsimba]

thanx guys but i should declare strSQL as a string and whats with Cmd. how do i declare it or what do i put there. is it a recordset.

strSQL = "UPDATE cooperatesubs SET [cooperatesubs].[copiesremaining]=[cooperatesubs].[numberofcopies]-[cooperatesubs].[copiesout]"
         Cmd.Execute strSQL

and also how do i ensure that it runs through all the records in the table updating every (copiesremaining) field.

[jireh]

OK I get your point. It should be done like this...

With ri        
     .ActiveConnection = deSub.conn        
     .LockType = adLockOptimistic        
     .Source = "select * from cooperatesubs"      
     .Open          

Do Until .EOF
  .Edit
  .Fields("copiesremaining") = .Fields("numberofcopies") - .Fields("copiesout")
  .Update       
  .MoveNext     
Loop

.Close
   
End With

Question: Are you sure with your query statement? All records will be updated. I think there must be a WHERE clause statement on here.

[vb5prgrmr]

No, with mine, all rows are updated with the information from each row. (If I remember my syntax correctly, just give it a try and see what happens.)

Good Luck

[quhad99]

how to insert memo in system that fully design in microsoft access 2007??
can sum1 tell me how??

[Israelsimba]

jireh....my challenge still remains on the .edit part. i still dont get .dit from the drop down. so if i use it i recieve a compile error that suggetsts i should declare it.

vb5prgrmr...i have tried yr cde but i need to know how to declare the Cmd you put in there?? how do i declare it or rather what is it??

thanks guys

[Israelsimba]

yes i want it to update like copiesremaining for everyone in that table thats why i didnt want the where clause. bu still what is Cmd and how do i declare it.

and one other thing...my dropdown list doesnt have the .edit, what could be causing that

[vb5prgrmr]

Dim Cmd As New ADODB.Command
Set Cmd.ActiveConnection = Con 'your connection object
strSQL = ...
Cmd.Execute strSQL

Good Luck

[Israelsimba]

i have tried to impliment it as follows and getting this error :-

Object variable or with block variable not set

.

Dim conn As Connection
Set conn = New Connection
Dim strSQL As String
Dim cmd As adodb.Command
Set cmd.ActiveConnection = conn
strSQL = "UPDATE individualsubs SET [individualsubs].[copiesremaining]=[individualsubs].[numberofcopies]-[individualsubs].[copiesout]"
cmd.Execute strSQL

what could be wrong??

[QVeen72]

Hi,

To Execute SQL Statement, You can also use the "Conn" object...
try this :

Dim Conn As New ADODB.Connection 
Dim strSQL As String
strSQL = "UPDATE individualsubs SET [individualsubs].[copiesremaining]=[individualsubs].[numberofcopies]-[individualsubs].[copiesout]"
Conn.Open "< Your Connection String Here>"
Conn.Execute strSQL

Regards
Veena

[Israelsimba]

i have tried yr code veena but its giving me the following error as well :-

[Microsoft]/[ODBC Driver Manager] data source name not found and no default driver not specified

this is the fullset of thcode im using from the declaration:

Dim conn As Connection
Set conn = New Connection
Dim strSQL As String
strSQL = "UPDATE individualsubs SET [individualsubs].[copiesremaining]=[individualsubs].[numberofcopies]-[individualsubs].[copiesout]"
conn.Open "desub.conn"
conn.Execute strSQL

where desub is my data environment and conn declared as the connection.
i believe im almost there. thanx anyway

[QVeen72]

Hi,

If you already have dataenvironment and connection, then try this :

DataEnvironment1.Connection1.Execute strSQL

Change dataEnvironment and connection names accordingly..

Regards
Veena

[Israelsimba]

hey,
you c when i put it like this

strSQL = "UPDATE individualsubs SET [individualsubs].[copiesremaining]=[individualsubs].[numberofcopies]-[individualsubs].[copiesout]"
deSub.conn.Execute strSQL

it doesnt give me an error but its not effective...its not subtracting from the remaining copies..

thanx

[Israelsimba]

thank you guys. i realised where i was going wrong. i was subtracting copiesout from numberofcopies(which is a constant figure...depicts the number of copies a person orders). it was supposed to be copiesremaining = copiesremaining - copiesout.

thank you all for your contribution.

[codefixer]

'i created a database (subscriptiondb.mdb) and saved it in the 'same folder as the project
'then i designed a table (tbl_subscription) with fields (e.g. Booktitle, 'CopiesRemaining)
'in VB i setted reference to - Microsoft ActiveX Data Objects 2.5
'the following are my variables

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()

'the following code is for connecting to the database (subscriptiondb)
With cn
.Provider = "microsoft.jet.oledb.4.0"
.ConnectionString = App.Path & "\subscriptiondb.mdb"
.Open
End With

End Sub

Private Sub cmdSubscribe_Click()
'the following subtracts 1 from field (copiesremaining) in the table (tbl_subscription)

With rs
If .State = adStateOpen Then .Close
.Open "select copiesremaining from tbl_subscription", cn, adOpenDynamic, adLockOptimistic
If .BOF And .EOF Then 'if table is empty
Exit Sub
MsgBox "Saubscription file is empty", vbExclamation, "File status"
Else 'if not empty
.MoveFirst
Do While Not .EOF
!copiesremaining = !copiesremaining - 1
.Update
.MoveNext
Loop
MsgBox "Subscription done successfully.", vbInformation, "Status"
End If
End With

End Sub