Simple referencing, I think! (1 Viewer)

[Pete64]

I have made a validation routine to check through my data to see the values within my table are correct using the code below.
I know theres errors in my table yet no errors are found.

I know its something to do with my referencing of these table fields :RS![C/F] & [BalanceC/F] as when I step through the code no values are shown when I hover over the references, they just show up as "0" yet the other references show values.

Private Sub Command62_Click()
Dim lp As Integer
Dim RS As DAO.Recordset
Dim Cal1 As Double
Dim Cal2 As Double
Dim Cal3 As Double
Dim Cal4 As Double

Set RS = Me.RecordsetClone
RS.MoveFirst
Cal1 = RS![C/F] + RS!EELoan - RS!Profit
Cal2 = [BalanceC/F]
Cal3 = RS!NetProfit - RS!Tax
Cal4 = RS!Profit
For lp = 1 To RS.RecordCount
If Cal1 <> Cal2 Then
MsgBox ("Error 1" & " " & RS![C/F] + RS!EELoan - RS!Profit & " " & "record" & " " & lp & " " & RS![BalanceC/F])
ElseIf Cal3 <> Cal4 Then
MsgBox ("Error 2" & " " & RS!NetProfit - RS!Tax & " " & "record" & " " & lp & " " & RS!Profit)
End If
RS.MoveNext
Next lp
End Sub

I've tried forever to fix this, searched the forum but can't find nothing please help! Cheers Pete ; )

 

[Shadez]

I think i just spotted your problem.

the recordset your referencing is a ADO recordset not a DAO recordset.

try

Private Sub Command62_Click() 
Dim lp As Integer 
Dim RS As adodb.Recordset 
Dim Cal1 As Double 
Dim Cal2 As Double 
Dim Cal3 As Double 
Dim Cal4 As Double 

Set RS = Me.recordset.Clone 
RS.MoveFirst 
Cal1 = RS![C/F] + RS!EELoan - RS!Profit 
Cal2 = [BalanceC/F] 
Cal3 = RS!NetProfit - RS!Tax 
Cal4 = RS!Profit 

For lp = 1 To RS.RecordCount 
        If Cal1 <> Cal2 Then 
              MsgBox ("Error 1" & " " & RS![C/F] + RS!EELoan - RS!
              Profit & " " & "record" & " " & lp & " " & RS![BalanceC/F]) 
        ElseIf Cal3 <> Cal4 Then 
              MsgBox ("Error 2" & " " & RS!NetProfit - RS!Tax & " "   
              & "record" & " " & lp & " " & RS!Profit) 
        End If 
        RS.MoveNext 
Next lp 

End Sub

 

[Pete64]

thanks ; )

I tested your code out but it gives me this -

Dim RS As adodb.Recordset - this brings back the following compile error User defined Data Type not defined

why aint this workin, I ave no idea
I think the correct references are set for that object type but not sure

 

[Shadez]

You must be using access 97

Try adding, from the vb code editor add a refernce to "Microsoft data access components " version 2.5 or higher, if you dont have it download it from microsoft.

 

[Pete64]

thanks for the hint but

OK I've downloaded MDAC and I have got the ADODB references working, but now am getting a Run time error '13' Type Mismatch
at Set RS = Me.RecordsetClone


yeah am using Access 97

 

[Shadez]

lol,

i think you need a few lessons on how to debug.


There is no RecordsetClone property with ado.

you need to use

Set RS = Me.Recordset.Clone

this should solve the problem

 

[Pete64]

lmao funny ; )

The problem aint solved I tried your code back to back it aint workin here so I reverted back to what I originnally had with ADODB silly me. am not as seasoned with programming as some but to insult my debugging skills those are fightin word lol

this is the error I get with the above code and it points to this line and the recordset object:
Set RS = Me.Recordset.Clone

compile error: Method or data member not found

I also search for the objects in ADODB I can only find .RecordsetClone

any more ideas man

 

[Shadez]

Hmmmmmmmmmm,

I have just done a few experiments on this, and it looks like you cant use clone.

you will have to work with the forms recordset directly.

ie

Private Sub Command62_Click() 
Dim lp As Integer 
Dim Cal1 As Double 
Dim Cal2 As Double 
Dim Cal3 As Double 
Dim Cal4 As Double 

with me.recordset
.MoveFirst 
Cal1 = ![C/F] + !EELoan - !Profit 
Cal2 = ![BalanceC/F] 
Cal3 = !NetProfit - !Tax 
Cal4 = !Profit 

For lp = 1 To .RecordCount 
        If Cal1 <> Cal2 Then 
              MsgBox ("Error 1" & " " & ![C/F] + !EELoan -!
              Profit & " " & "record" & " " & lp & " " & ![BalanceC/F]) 
        ElseIf Cal3 <> Cal4 Then 
              MsgBox ("Error 2" & " " & !NetProfit - !Tax & " "   
              & "record" & " " & lp & " " & !Profit) 
        End If 
        .MoveNext 
Next lp 
end with
End Sub

 

[WayneRyan]

Sorry for interrupting, but there is a problem with the code.

Cal1 thru Cal4 are calculated based on the first record.
During execution of the For Loop either the If or the
ElseIf (or neither) will execute ALL OF The time.

In other words:

If the first record is OK, then all records are seemingly OK.

If the first record's balance is wrong, then all records are
checked only for that.

If the first record's profit is wrong, then all records are
checked only for that.

If both are wrong in the first record, then all records are
only checked for balance.

Wayne

 

[WayneRyan]

Sorry for interrupting, but there is a problem with the code.

Cal1 thru Cal4 are calculated based on the first record.
During execution of the For Loop either the If or the
ElseIf (or neither) will execute ALL OF The time.

In other words:

If the first record is OK, then all records are seemingly OK.

If the first record's balance is wrong, then all records are
checked only for that.

If the first record's profit is wrong, then all records are
checked only for that.

If both are wrong in the first record, then all records are
only checked for balance.

Wayne

 

[WayneRyan]

Private Sub Command62_Click() 
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String

Dim lp As Integer 
Dim Cal1 As Double 
Dim Cal2 As Double 
Dim Cal3 As Double 
Dim Cal4 As Double 

Set dbs = CurrentDb
sql = "Select * from YourTable"
Set rst = dbs.OpenRecordset(sql)

While Not rst.EOF and Not rst.BOF
   Cal1 = ![C/F] + !EELoan - !Profit 
   Cal2 = ![BalanceC/F] 
   Cal3 = !NetProfit - !Tax 
   Cal4 = !Profit 

   If Cal1 <> Cal2 Then 
      MsgBox ("Error 1" & " " & rst![C/F] + rst!EELoan - rst!Profit & " " & "record" & " " & lp & " " & rst![BalanceC/F]) 
   End If

   If Cal3 <> Cal4 Then 
      MsgBox ("Error 2" & " " & rst!NetProfit - rst!Tax & " " & "record" & " " & rst!lp & " " & rst!Profit) 
   End If 
   rst.MoveNext 
   Wend
End Sub

Wayne

 

[Pete64]

Thanks for all the help BUT...

Now I get errors on records that arent real errors
I don't know why this aint working.
I am positive its something to do with the way these table values are set in brackets rst![C/F] but I can't change the field names as it wrecks all my data inside the fields. When I set a break point in the code I follow the values being set and declared through out and it seems to show no values for the rst![C/F] bracketed fields and shows values for the fields with no brackets. The calculated is showing up on the msgbox correct but they arent real errors eg. Error 1 150.09 150.09

It calculates only the values without brackets

HELP!!

 

[WayneRyan]

oops,

Private Sub Command62_Click() 
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String

Dim lp As Integer 
Dim Cal1 As Double 
Dim Cal2 As Double 
Dim Cal3 As Double 
Dim Cal4 As Double 

Set dbs = CurrentDb
sql = "Select * from YourTable"
Set rst = dbs.OpenRecordset(sql)

While Not rst.EOF and Not rst.BOF
   Cal1 = rst![C/F] + rst!EELoan - rst!Profit 
   Cal2 = rst![BalanceC/F] 
   Cal3 = rst!NetProfit - rst!Tax 
   Cal4 = rst!Profit 

   If Cal1 <> Cal2 Then 
      MsgBox ("Error 1" & " " & rst![C/F] + rst!EELoan - rst!Profit & " " & "record" & " " & lp & " " & rst![BalanceC/F]) 
   End If

   If Cal3 <> Cal4 Then 
      MsgBox ("Error 2" & " " & rst!NetProfit - rst!Tax & " " & "record" & " " & rst!lp & " " & rst!Profit) 
   End If 
   rst.MoveNext 
   Wend
End Sub

All should have had the rst prefix ...

Post your code if it doesn't work.

Wayne

 

[Pete64]

Sorry heres the code

Private Sub Command60_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String

Dim lp As Integer
Dim Cal1 As Double
Dim Cal2 As Double
Dim Cal3 As Double
Dim Cal4 As Double

Set dbs = CurrentDb
sql = "Select * from test"
Set rst = dbs.OpenRecordset(sql)

While Not rst.EOF And Not rst.BOF
Cal1 = rst![C/F] + rst!EELoan - rst!Profit
Cal2 = rst![BalanceC/F]
Cal3 = rst!NetProfit - rst!Tax
Cal4 = rst!Profit

If Cal1 <> Cal2 Then
MsgBox ("Error 1" & " " & rst![C/F] + rst!EELoan - rst!Profit & " " & "record" & " " & lp & " " & rst![BalanceC/F])
End If

If Cal3 <> Cal4 Then
MsgBox ("Error 2" & " " & rst!NetProfit - rst!Tax & " " & "record" & " " & lp & " " & rst!Profit)
End If
rst.MoveNext
Wend
End Sub

 

[WayneRyan]

Pete,

It looks OK. What's the error?

Is it possible to post the db?

Wayne

 

[WayneRyan]

We could also take out the "lp" out of the msgbox.

Wayne

 

[Pete64]

I could post the form object in a database but not the data


The validation calculations are showing up on the error msgbox as errors but they arent real errors for example

Error 1 150.09 150.09 - in this case they equal exactly the same value

IF cal1 <> cal2 then
display error

It calculates only the values without brackets

HELP!!

 

[Pete64]

I've taken out lp and thats fine
heres the object I don't know if that will help at all

its access 97

 

[SilentBreaker]

try to adjust the sql statements:

sql = "Select [C/F], EELoan, Profit, NetProfit, Tax from test"

Recordset needs a set of field names for reference

 

[Pete64]

OK i've made the amendments you recommended but to no avail it does and works exactly with the same run time errors. I still think its something to do with the fact that some of the table fields are referred to with the [] - this is because you can't refer to fields with some invalid characters, one of these is the "/" this slash is in two of my field names in the table C/F and BalanceC/F references to the field name.

I am very stuck am gettin no where but am very greatful of all your help please keep posting am sure we will nail it soon