Edit field in Table VBA

[Trevor G]

I am seeking to use a module sheet to create a sub/function to update a field if it meets certain criteria

I have a table called = "GDV_Rates_Conventional_Sum"

and I have a fields called "Nf_Risks_Sum"

What I need to do is fill a field if the following occurs

number of Nf_Risks_Sum is above 100 Then place in 1
number of Nf_Risks_Sum is Between 50 and 100 Then place in 2
number of Nf_Risks_Sum is Below 50 Then place in 3

The field to update is called Credibility

Any example would be greatly appreciated

 

[SydneyGeek]

Use an expression in your form's BeforeUpdate event.

Select Case No_of_risks_sum
  Case <50
    Credibility = 3
  Case <100
    Credibility = 2
  Case Else
    Credibility = 1
End Select

Denis

 

[Trevor G]

Use an expression in your form's BeforeUpdate event.

Select Case No_of_risks_sum
  Case <50
    Credibility = 3
  Case <100
    Credibility = 2
  Case Else
    Credibility = 1
End Select

Denis

Denis thank you,

I am aiming to use a module sheet in the VBA side rather than on a form, as it has to update all records in a table, I think I am looking at DAO and Edit recordset, but note sure of code, but I most certainly will consider asking if the case statement is acceptable.

 

[Norie]

Trevor

Is this related to this thread?

If it is then please stick to one thread, and if you have any problems with any suggestions made then post them there.

Did you even try what I suggested?

I know it wasn't a perfect solution but I was only basing what I posted on the information you had provided so far.

 

[SydneyGeek]

Go with Norie's suggestion. You will need to run an update query, which can just be coded in SQL. I suggest that you create the query as normal, copy the SQL to Notepad, delete the query, then use that SQL in the VBA expression.

Denis