entering two records from one form ? 1

[bakerajatha]

Any way of updating two records in a table from one form ? There may be a simple way but i just do'nt see it.

As background I'm generating an audit movment trail for repair parts, each part is required to have a movment record ie receiving - stock - local store - workshop etc.
However 99% of egineers movments on a repair job involves both a a movment to take out a faulty part, and one to install a good part. At the moment this involves two entries with a lot of duplicated information ( job ref, part type, location details etc ) which the engineers are getting bored with retyping Therefore is there a way of getting one form to enter the two required records

thanks in advance

 

[Remou]

Hi
Either a recordset or a query would work.
[tt]Query:
strSQL = "UPDATE Table SET Field =" & Me.TextBox
Or
strSQL = "INSERT INTO Table ( Field )
SELECT " & Me.TextBox

Recordset:
Set rs = CurrentDB.OpenRecordset("SELECT * FROM Table WHERE ID = " & Me.ID
...
If rs.EOF
rs.AddNew
rs!Field = Me.TextBox
rs.Update
Else
rs.Edit
rs!Field = Me.TextBox
rs.Update
...
[/tt]

I seem to recall a few post on this ...

 

[bakerajatha]

thanks for your quick responce
Now the only problem is to understand it ;(

cheers

 

[Remou]

In that case, it might be easier to use a subform based on your second table and linked by a suitable field (one to one relationship) to your first table. Add a command button called, for example, cmdUpdate. Then add a little code to the click event for the button, for example:

[tt]Private Sub cmdUpdate_Click()
Me.[tblTable2 subform].Form.txtATextBox = Me.txtSomeTextBox
Me.[tblTable2 subform].Form.txtAnotherTextBox = Me.txtSomeOtherTextBox
'And so on
End Sub[/tt]

 

[bakerajatha]

thanks remou

I think its the rs.Addnew was the bit i was looking for, however a small correction, its not two tables Im updating, its one table with subsequent records. - or rather it is two tables but let me explain

The movment of a part of a single part updates the record for that part as to its location and status, but Im also required to keep a audit trail log of that movment so that movment is as a new record, (but as an audit log these records are never updated or changed, only ever added to).

A replacment of a part therefore generates 4 recordset processes
1, record update of old part to stores/repair/dustbin
2, record update of good part to customer location
3, new record entry on audit-table of old part movment
4, new record entry on audit-table of new part movment

Its the last bit wher im worried if a form can update a table with two new records in one go

Ive probably made it as clear as mud now !

 

[Remou]

To continue with the button:

Private Sub cmdUpdate_Click()
'Needs Microsoft DAO 3.6 Object Library
Dim rs As DAO.Recordset 
Set rs = CurrentDB.OpenRecordset("SELECT * FROM tblAudit")
rs.AddNew
rs!OldPartNo = Me!txtPartNo
rs!Other = Me!Other 
rs.Update

rs.AddNew
rs!NewPartNo = Me!txtPartNo
rs!Other = Me!Other 
rs.Update

rs.Close
Set rs = Nothing
End Sub

Are we getting any where?

 

[bakerajatha]

looks good to me, I'll start poking the code in tomorrow, thanks for all your help