If statement ref to db table and saving result to db table

[Janine de Lange]

Hi guys.

I’m not familiar with Ms access VB (or any other for that matter)but learning fast. I have tried various methods to achieve the outcome but all have failed.

Background
Front End developed using MS Access Forms which is connected to SQL Server 2005 DB. Allowing users to capture Customer info.

Objective
On value input in on the form it needs to make reference to a scorecard (on db), check the input against the scorecard variables and once matched the points associated with the value to be written to another table in db.
I’m hoping I’m not confusing the matter.
Below is one attempt(failed horribly). Don’t have any idea of how to resolve or achieve the outcome. Would appreciate any assistance

Private Sub Form_AfterUpdate()
Dim db As DAO.Database
Dim SC As DAO.Recordset
Dim OutPut As DAO.Recordset
Dim Value As Integer
 
  Set db = CurrentDb()
  Set SC = db.OpenRecordset("dbo_BBRS_SC", dbOpenSnapshot)
  Set OutPut = db.OpenRecordset("dbo_Cust_RG_Scr_Values", dbOpenSnapshot, dbSeeChanges)
 
  Value = Customer!CB_Industry
 
  If SC.RecordCount = 0 Then Exit Sub
 
  SC.MoveFirst
  ' loop through each record in the first recordset
  Do Until SC.RecordCount = 11 Or ctr > 11
 
      If Value = SC!ID_LKVar Then
        OutPut.AddNew
        OutPut![Score] = SC![Score]
        OutPut.Update
 
    Else
         If Value <> SC!ID_LKVar Then
        OutPut.AddNew
        OutPut![Score] = 99
        OutPut.Update
      End If
 
        ctr = ctr + 1
  Loop
 
  SC.Close
  OutPut.Close
  Set SC = Nothing
  Set OutPut = Nothing
  Set db = Nothing
  End If
End Sub

[NeoPa]

Bonjour Janine (That's all the French I'm allowed due to site regs).

I'm impressed with the way you've laid out the question, so I will certainly try to help. I need you to try to explain in ordinary language though, what it is you are trying to do. Some example data may be helpful in this.

[patjones]

Hi Janine,

As NeoPa points out, a little more information about what you are trying to do (more context) would be useful. In addition, I would ask exactly what is happening to indicate that this is not working. Are you getting a particular error at some point in the code? Looking at your code, I see two things immediately that could cause problems.

The first is that on line #9 you are using the dbOpenSnapshot flag to open the recordset OutPut; however, dbOpenSnapshot is used to open a recordset with fixed records, but you are attempting to change it on lines #20, 21, and 22 by adding a record to it.

The second problem I see is that you are using the RecordCount property in your criteria for the Do Until on line #17. The RecordCount property for a recordset is fixed (unless you are adding or deleting records to it, which is not the case for SC). If SC already has 11 records going into that Do Until comparison, nothing in the loop will execute. You want to loop through SC record by record until you reach the end, so I suspect that you want something more like

Do Until ctr > 11
. . .

Third, you have an End If on line #39 which does not seem to belong there. When you use the one-line version of If...Then as on line #13, the End If is not necessary.

I hope this helps.

Pat

[Janine de Lange]

Back to business
I don’t receive any error msg but the code appears not to find a match. Which is not the case. I will attempt to provide some more detail.

Objective: Design, build and implement a Risk Grade Evaluation Tool which will be used by Credit Managers (Banking Industry) indicating Risk level by means of Grading scale for a specific customer business segment based on customer input data (i.e. Industry, Management of Business, Bank account behaviour, Financial information : Audit statements)

Data Structure (portion of it)
Table 1 : Customer Table
Data Captured by Credit Bank Manager in Branch by (Form)
Data fields i.e Date; Customer Number; Industry Type Total Assets est.

Table 2 : Credit Scorecard Parameters
Parameters for above input data and each with their own ranges. i.e. (will just give 2 examples, in total over a 100 are used)
Score Variable Band/Ranges Points
Industry Type : Charity -10
Local Government -20
FinancialTotalAsset: <250 -25
600 5

With all that said. On input the customer record would look something like this
CustNumber IndusType TotAssets
123344 Charity 300

As soon as the user enters the data in the form I want the loop to execute and the points associated with the variable to be stored on different table. i.e.
CustNumber Variable Points
123344 Charity -10
123344 TotAssets 5*
* 300 > than max of 250 and < 600.

Might have elaborated too much but do think its better to provide a picture of whats happening as a whole, to ensure I use the right code in achieving this.

Regards
Janine

[patjones]

Hi Janine,

Thanks for explaining what you're trying to do. It is more or less what I was picturing but it is always good to have clarification.

Before the Do Until loop executes, how many records does SC have in it? I ask this because I believe that your loop is not running.

In addition, as I pointed out yesterday I believe that using the dbOpenSnapshot parameter when opening the OutPut recordset might problematic for you. Try changing dbOpenSnapshot to dbOpenDynamic.

Finally, it looks to me like the placement of the End If on line #39 is not correct. I believe that it needs to be on line #30 just below the End If on line #29. The way it is now, the Do loop terminates before the first If statement inside ends. In fact, I'm surprised that even compiles.

Pat

[NeoPa]

This is hard to follow Janine. There are various entities which are names with spaces embedded within them. As you don't indicate where the names end it's hard to read you.

Is there maybe a table called [CustNumber Variable Points]? If so, then the details of this table are missing and probably (with my still very hazy understanding of what you're trying to do) quite important.

[Janine de Lange]

I have made the changes to the code reflecting "dbOpenDynamic" and moving the "End if" . I now recieve a Run time Error 3001 - Invalid Argument
on the below
Set OutPut = db.OpenRecordset("dbo_Cust_RG_Scr_Values", dbOpenDynamic, dbSeeChanges)

[Janine de Lange]

Apologies. the table is to be the "output table" with the fields [CustNumber]; [Variable]; [Points] and the input for it is to be derived from the loop.

[patjones]

Hi Janine,

I see that you indicated the question as being answered, but from your last two posts it seems that is not the case. Are you still getting the 3001 runtime error? If you are, then I will have to look into that.

Pat

[Janine de Lange]

Apologies for that. Still getting the error.

[Janine de Lange]

Hi All.

Is it possible to called a SQL stored procedure in a form? I orginaly created a sql Stored proc and then later on attempted to replace it with the VB loop. Could I have your opinion on this in terms of which would be best practice ?
Regards
Janine

[patjones]

Hi Janine,

I apologize for being so slow in replying to you. Could you try doing this:

Set OutPut = db.OpenRecordset("dbo_Cust_RG_Scr_Values", , dbSeeChanges)

and seeing if you still get the error?

Pat

[Janine de Lange]

Hi Guys

I have amended the code and the below seems to be working but the problem now is by the "Add new" with Error" Object read only". Not the case. Please assist !

Private Sub BTCustValidation_Click()
Dim MyDB As DAO.Database
Dim SC As DAO.Recordset
Dim Output As DAO.Recordset
Dim GRPID As Variant
Dim CustNum As Variant
Dim SubVarID As Variant
Dim Score As Variant
Dim ScDate As Double
Dim i As Integer
 
Set MyDB = CurrentDb()
Set SC = MyDB.OpenRecordset("dbo_BBRS_SC")
Set Output = MyDB.OpenRecordset("dbo_Cust_RG_Scr_Values", , dbSeeChanges)
 
GRPID = SC![GRP_ID]
CustNum = Cust_Number
SubVarID = SC![Sub_Var_ID]
Pionts = SC![Score]
ScDate = Date
'ORG TYPE'
 
For i = 0 To SC![ID_LKVar] = 11#
If ID_OrgType = SC![ID_LKVar] Then
Output.AddNew
    Output![GRP_ID] = GRPID
    Output![PK_Customer] = CustNum
    Output![Sub_Var_ID] = SubVarID
    Output![Score] = Pionts
    Output![Score_Date] = ScDate
Output.Update
    End If
Next
For i = 0 To SC![ID_LKVar] = 11#
If ID_OrgType <> SC![ID_LKVar] Then
MsgBox "No Match"
End If
Next
End Sub

[patjones]

Hi Janine,

I haven't worked with SQL Server back-ends very much, but I'm just wondering if there is something in the SQL Server settings for this table that might prevent additions and updates? I cannot see anything in this code that would cause this error.

Pat