If and Dlookup

[Zinger]

Hi there!

I am a newbie in MS Access VBA and my first time to post here, i am trying to update data from different tables using if condition and dlookup

This is what i wrote:


Private Sub Parameter_AfterUpdate()

If ([Resul_tb]![ typeofsample].Value = "Sea water") Then



VMA = DLookup("VMA", "tb_para", "ID =" & Parameter)

Else
MsgBox ("Something went wrong")

End If

End Sub

Thanks in advance

[June7]

What is the issue? What happens - error message, wrong results, nothing?

Why are you saving same data to multiple tables?

[ssanfu]

Welcome to the forum

In addition to June's comments,
you cannot reference a table like this

If ([Resul_tb]![ typeofsample].Value = "Sea water") Then

Nowhere in your posted code is there a statement that will update anything.

Usually you would use an update query or use VBA to open record sets and do the update.

[Zinger]

i would like to avoid people entering same data everytime in VMA column.
so i want to do something like this:

if (resultados_tb.[typeofsample] = "Sea water") { run dlookup}
elseif(resultados_tb.[typeofsample] = "Wastewater") { run dlookup}....

How can i fix it?

[June7]

Is this a form/subform arrangement?

What is Parameter - the combobox? Code is behind combobox? Combobox on main form?

What is [Resul_tb]![ typeofsample]? Is this a field in the form's RecordSource?

I still don't understand why you need to save the VMA value. If it can be retrieved by a DLookup using an ID as criteria, then it can be retrieved by query that joins tables.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Zinger]

This is my dlookup code and it works perfectly without if condition


Private Sub Parameter_AfterUpdate()

Error line--> If ([resultados_tb].[typeofsample].Value = "Água de consumo") Then

VMA = DLookup("VMA", "tb_para", "ID =" & Parameter) ....


I would like to retrieve value using if condition because my table "tb_para" holds different values o VMA/VMR according to type of sample.


This is how my table looks like.

Sea water Wastewater
ID | Parameter| VMA| VMR | VMA1|VMR1 |
1 | pH | 8 | 8,5 | 5 | 7 |

[June7]

As already explained, cannot refer directly to table like that in VBA code. If typeofsample is a field in the form's RecordSource or name of a control, simply:

If Me.typeofsample = "something" Then