VB Code working but only when I step through

[easygeeza98]

Hi Folks
Iam a newbee to the forum and am having a few problems with Access. Have any of you seen this before in Access 2003.

I have two subforms based on separate queries. I use a mixture of SQL to manipulate the tables Iam dealing with...no heres the strange part. All my code works perfectly fine and does exactly what it should do when a breakpoint it and step thorugh it line by line. I can see the changes happening in the forms as I step through...However when I run it normally without seeing the step throughs it wont update the forms on the refresh event. Its as though it skips it out. I have tried everythng now but cant see why it would work fine in step through mode and not normally. Any ideas or help would be much appreciated?

I have compacted and repaired the database thinking it could be something to do with that but still does the same

Thanks in advance
EasyGeeza

[FishVal]

Hi, EasyGeeza.

Posting the code would certainly help us to figure out how it may be fixed.

Regards,
Fish

[easygeeza98]

Hi, EasyGeeza.
Sorry guys code as follows


Posting the code would certainly help us to figure out how it may be fixed.

Regards,
Fish

Private Sub cmdRemoveFromHire_Click()
'DB connection and Recordset objects
Dim rsItemToMove As ADODB.Recordset


Set DBConn = New ADODB.Connection
Set rsItemToMove = New ADODB.Recordset

' vars for manipulation
Dim LineSelection() As Boolean
Dim CarSelector
Dim ContactName As String
Dim CarMake As String
Dim CarModel As String
Dim CarID As Integer
Dim ContactID As Integer
Dim test As String
If Me.subfrmCarsField.Form.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If

' SQL command obect and instance
Dim SQLCommand As ADODB.Command
Set SQLCommand = New ADODB.Command

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & CurrentProject.Connection

' database connection patch and source detail declared globally
DBConn.Open strConnection 'open DB connection using path

Me.subfrmCarsField.Form.RecordsetClone.MoveFirst 'clone the subforms query results from form selection
'into cloned recordset and move to first position

' loop through rows returned in subform cloned record set and pass to rsSubFormRows RS
While Me.subfrmCarsField.Form.RecordsetClone.EOF = False ' while recordsetclone not at the end of the file
CarID = Me.subfrmCarsField.Form.RecordsetClone("CarID")
ContactID = Me.subfrmCarsField.Form.RecordsetClone("ContactID" )
CarSelector = Me.subfrmCarsField.Form.RecordsetClone("CarSeletor ")
If CarSelector = True Then ' if row selected from form
SQLCommand.ActiveConnection = DBConn ' assign the command object to the relevant connection
'you are about to update/execute so it knows which
' DB connection its dealing with
' put the command SQL string into the object ready for execution / update of table
SQLCommand.CommandText = "UPDATE tblCars SET ContactID = 0 WHERE CarID=" & CarID ' remove person from car
SQLCommand.Execute 'perform above SQL statement
SQLCommand.CommandText = "UPDATE tblCars SET CarSeletor = False" 'reset tickbok
SQLCommand.Execute 'perform above SQL statement


End If
Me.subfrmCarsField.Form.RecordsetClone.MoveNext
Wend

subfrmCarsField.Requery 'refreshes data (from a qryCars) in subform using primary key ContactID feild
subfrmCarsOffHireField.Requery
'refresh both subforms query after table alterations above



End Sub

[FishVal]

Well. The code is not optimal and not always reliable.

Private Sub cmdRemoveFromHire_Click()
'DB connection and Recordset objects
Dim rsItemToMove As ADODB.Recordset
 
 
Set DBConn = New ADODB.Connection
Set rsItemToMove = New ADODB.Recordset
 
' vars for manipulation
Dim LineSelection() As Boolean
Dim CarSelector
Dim ContactName As String
Dim CarMake As String
Dim CarModel As String
Dim CarID As Integer
Dim ContactID As Integer
Dim test As String
 
 
 If Me.subfrmCarsField.Form.Dirty Then
  DoCmd.RunCommand acCmdSaveRecord
 End If
 
' SQL command obect and instance
Dim SQLCommand As ADODB.Command
Set SQLCommand = New ADODB.Command
 
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & CurrentProject.Connection
 
' database connection patch and source detail declared globally
DBConn.Open strConnection 'open DB connection using path
 
 

There is no need to open separate ADODB.Connection as soon as db already has connection via Jet.OLEDB provider. The Connection object is accessible via CurrentProject.Connection property. Moreover, if you will try to run the code having not saved design changes it is likely to fail with "DB locked" error.
There is definitely no need to open separate or use opened one ADODB.Connection to execute SQL command only. DoCmd.RunSQL will be quite enough.

 
Me.subfrmCarsField.Form.RecordsetClone.MoveFirst 'clone the subforms query results from form selection
                                                 'into cloned recordset and move to first position
 
' loop through rows returned in subform cloned record set and pass to rsSubFormRows RS
While Me.subfrmCarsField.Form.RecordsetClone.EOF = False ' while recordsetclone not at the end of the file
 CarID = Me.subfrmCarsField.Form.RecordsetClone("CarID")
 ContactID = Me.subfrmCarsField.Form.RecordsetClone("ContactID")
 CarSelector = Me.subfrmCarsField.Form.RecordsetClone("CarSeletor")
   If CarSelector = True Then ' if row selected from form
    SQLCommand.ActiveConnection = DBConn ' assign the command object to the relevant connection
                                         'you are about to update/execute so it knows which
                                         ' DB connection its dealing with
    ' put the command SQL string into the object ready for execution / update of table
    SQLCommand.CommandText = "UPDATE tblCars SET ContactID = 0 WHERE CarID=" & CarID ' remove person from car
    SQLCommand.Execute 'perform above SQL statement
    SQLCommand.CommandText = "UPDATE tblCars SET CarSeletor = False" 'reset tickbok
    SQLCommand.Execute 'perform above SQL statement
 
 

The logic of code is not clear. First SQL statement sets ContactID to 0 for car(s) having particular CarID. Second SQL statement clears CarSelector field in all records of tblCars (????).

   End If
 Me.subfrmCarsField.Form.RecordsetClone.MoveNext
Wend
 
subfrmCarsField.Requery 'refreshes data (from a qryCars) in subform using primary key ContactID feild
subfrmCarsOffHireField.Requery
'refresh both subforms query after table alterations above
 
 
 
End Sub
 

Anyway, it seems that the same may be achieved with a several SQL statements only. Could you post MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Particulary I mean tblCars and qryCars.