CODEWITHSUNDEEP
vbams-access
Roland
Roland

Reputation: 67

Adding lines to a table caused error in code that was working before

I have code that prompts the user to enter a value for "RAMsize" and stores that value in a table. Another field, "Likelihood", cannot be larger than "RAMsize". I had it working so that if "RAMsize" was greater than "Likelihood" it would set "Likelihood" to 0 to avoid conflict, and this was functioning properly with no issues. Then I added more rows to the Likelihood table, and the validation rule errors started appearing again. I haven't been working in VBA for very long so I'm not sure how to make the code apply to all rows of the table.

The Code in question:

Option Compare Database

Private Sub Command0_Click()

Dim RAMsize As Variant
Dim Lhood As Integer
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tLikelihood")


'Promts user to enter desired matrix dimensions
RAMsize = InputBox("Please enter your desired matrix dimensions", "Matrix Dimensions")


'Ends process if user clicks 'cancel' or gives no input
If RAMsize = "" Then
    MsgBox "Dimension selection was canceled", vbOKOnly

    GoTo Exit_DimSelect
End If

'Checks if RAMsize causes conflict with validation rule in tLikelihood
RAMsize = CInt(RAMsize)

Lhood = rs!Likelihood

If RAMsize < Lhood Then

    rs.Edit
    rs!Likelihood = 0
    rs.Update

End If


'Saves the user input for RAMsize in the tRAMSize table for later use
Set rs = db.OpenRecordset("tRAMSize")


rs.Edit
rs!Size = RAMsize
rs.Update

Any help is appreciated

Upvotes: 0

Views: 25

Answers (1)

braX
braX

Reputation: 11755

If I am understanding what you are trying to do correctly, you can use this if you place it right after the line RAMsize = CInt(RAMsize)

db.Execute "UPDATE tLikelihood SET Likelihood = 0 WHERE Likelihood > " & RAMsize

and then remove everything after that. That will affect all the records in the table where Likelihood > the value you prompted the user for.

Upvotes: 1

Related Questions