Form Unbound Text Box to enter data into separate table

[mari]

Currently, there is a form that is bound to a qry/table -
I need to add another text box to the form that is not
bound to the same table - In fact it is an empty table
and as data is added to that text box it needs to be
updated into that separate table.
There is no relationship betweeen the two tables.
However, as data is entered into the text box it
automatically needs to update the new table as well as
return to a new record location ( to add the next entry)
once the recordset for the form( not the same) is exited
or updated. Please advise.
Any help is greatly appreciated.

 

[Joe Voll]

hi mari,

i think the easiest way to deal with that is a recordset.

assumed you form is called Form1 and the unbound text box is called
Text1, the Table with the field you wanna to store the value from Text1
is called Table1 and the field in that table is called... yes... Field1.

You have to define an AfterUpdate Event for Text1, so we need to do a
little VBA here.


private sub Text1_AfterUpdate()
Dim strSQL as String
Dim myrst as Recordset

'''initialize SQL String
'''set ID=0 to make sure you dont overwrite any record
strSQL = "SELECT * from Table1 WHERE ID = 0"

Set myrst = CurrentDb.OpenRecordset(strSQL)

if not myrst.eof then
myrst.addnew
myrst.fields("Field1").Value = Me!Text1.Value
myrst.Update
end if

myrst.close: Set myrst = Nothing
end sub

in any need you can place that peice of code in every suitable event,
maybe Form Exit or something. It depends on how and when you want to have
the record updated.

Haope this might help

Joe Voll.

 

[mari]

Thank you very much for your help. It makes sense
However, what is the ID that is being set to "0"?

And I get a message that the object or class does not
support these events when I placed it on the AfterUpdate
event on the text box ,
As well I placed it on Form_exit() I don't receive an
update
..... Can you maybe help me further.. thanks so very much