Paste a value to a table which is not opened From Active Form

  • Thread starter Thread starter MIA
  • Start date Start date
M

MIA

I want to paste a value which is on my active Form field
control to Table field which is not opened.

Form Name is "Form1" (This form is based on Table "CALM1")
Form Field Control Name is "LPONUM" (Field type is numeric)
Form Field Control Name is "InvoiceNo" (Field type is
numeric)


Other Table is "MOF2", and its field are :
Field is "LPONUM" (its a numeric field)
Field is "InvoiceNo" (Its a numeric field)


Note: First the Table MOF2 data is posted, then the Form1
is opened to post data.


My requirement:
After Update event of "LPONUM" on form, It will open
the "MOF2" Table with the condition that Table - InvoiceNo
should be same as Active Form InvoiceNo and then paste the
value of "LPONUM" to Table "LPONUM" field. Just to
understand more clear I will write the following incorrect
way code :

IF Tables!MOF2!InvoiceNo=Forms!Form1!InvoiceNo Then
Forms!Form1!LPONUM = Tables!MOF2!LPONUM

The above is not a code , but it is just to understand the
situation well.

can any one advise me.
 
Mia,

You can do this two ways in code, either by opening the target table as a
recordset, or by means of a simple Update query in SQL; I find the latter
simpler (and less resource demanding). Here's what the code would look like:

Private Sub LPONUM_BeforeUpdate(Cancel As Integer)
strSQL = "UPDATE MOF2 SET MOF2.LPONUM = " & Me.LPONUM
strSQL = strSQL & " WHERE MOF2.InvoiceNo = " & Me.InvoiceNo
CurrentDb.Execute strSQL, dbFailOnError
End Sub

HTH,
Nikos
 
Back
Top