Copying from a table to another table

[MenessFreak]

Hi guys, i got a problem here.

I have two tables, table A and table B. Table A is a master table where it contains various values. I want to do it such that if user keyed in value X and value Y into Form B (linked to table B), it will check Table A for value X and value Y. If the values matched, it will display a new value (value Z) that is taken from Table A into Form B.

If IsNull(Me.txtValueX) Then
MsgBox "Please type Value X", vbInformation, "Value X Required"
Me.txtValueX.SetFocus
ElseIf IsNull(Me.txtValueY) Then
MsgBox "Please type Value Y", vbInformation, "Value Y Required"
Me.txtValueY.SetFocus
Else
If (IsNull(DLookup("[Value X]", "tblA", "[Value X] ='" & Me.txtValueX.Value & "'"))) Then
MsgBox "Unknown Value X"
ElseIf (IsNull(DLookup("[Value Y]", "tblA", "[Value Y] ='" & Me.txtValueY.Value & "'"))) Then
MsgBox "Unknown Value Y"
Else
????

I am stuck at this part where i am not sure how to retrieve value C out.

Please help.

Thanks in advance!

 

[Galaxiom]

If the Z value is unchangingly dependent on the X and Y then it would not be stored but calculated when required.

Either way the easiest way to retrieve it is:

DLookup("[Value z]", "tblA", "[Value x]='" & Me.txtValueX & "' AND [Value y]='" & Me.txtValueY & "'")

If this expression returns a Null then one of the field values does not exist.

 

[MenessFreak]

If the Z value is unchangingly dependent on the X and Y then it would not be stored but calculated when required.

Either way the easiest way to retrieve it is:

DLookup("[Value z]", "tblA", "[Value x]='" & Me.txtValueX & "' AND [Value y]='" & Me.txtValueY & "'")

If this expression returns a Null then one of the field values does not exist.

I am kinda newb in vba. Can you please advice where do i put the codes into?

 

[Galaxiom]

Please indicate if the the Z value is unchangingly dependent on X and Y.

 

[MenessFreak]

Sorry I am not sure what you meant by Z value unchangingly dependent.

But basically, table A contains X, Y, Z and all these values can be changed if Form A (linked to table A) is changed

 

[Galaxiom]

So they are intended as default values for new records?

 

[MenessFreak]

So they are intended as default values for new records?

Table As' value X,Y,Z can be changed from form A depending on the sure.

Form B is more like a searching purpose. It takes in user's input of value X and Y, in order to search table A and displaying value Z. At the same time, table B will be keeping the records of the input X, Y and the output Z.

 

[MenessFreak]

Table As' value X,Y,Z can be changed from form A depending on the sure.

Form B is more like a searching purpose. It takes in user's input of value X and Y, in order to search table A and displaying value Z. At the same time, table B will be keeping the records of the input X, Y and the output Z.