Adding records to existing table

[Mclaren]

I have a form which when opened to add a new record, and using the ondirty property, should update a table with a certain number of records. I have used the following code:

Private Sub Form_Dirty(Cancel As Integer)
DoCmd.SetWarnings False
If Me.NewRecord Then

For I = 1 To 14
DoCmd.RunSQL "INSERT INTO tbl_Transactions_detail_Data (TransactionID) VALUES (" & Me.tbl_Transactions_TransactionID & ")"
Next
DoCmd.SetWarnings True
Me.Requery
Else
End If

End Sub

The code works but not really, well:

if i am in an existing record, the code adds the required number of records to the other table (not suppose to happen)

If it is a new record the i get a runtime error 3134.

I am sure this is probably because the new record has yet to be saved and as a result the

Me.tbl_Transactions_TransactionID

value is not yet available.

[Mclaren]

Ok, so i have figured out why i cannot insert the new records and that is because the transaction id has not yet been created.

I have tried inserting the below code imediatly after checking if the record is new. this seems to do nothing.

RunCommand acCmdSaveRecord

how can i save the record, so as to create a number in the TransactionId field (which is an Auto Number field). And then use the transactionID value to create the records in the linked table ?

[June7]

The new record will be initiated as soon as a value is entered into any field (other than the AutoNumber of course). Then the acCmdSaveRecord should commit the record (although might not be necessary). Is there some field you could set with a default value? A date field can be set with Date() as the default and the current date will populate the field. That will serve to initiate the record.

The reference Me.tbl_Transactions_TransactionID is odd to me. Is that the name of control on form?

The few times I have tried to use the OnDirty event have been unsatisfactory, never could get it to work way I wanted. I have used the Dirty property for form successfully, as in: If Me.Dirty Then

[Mclaren]

yes, Me.tbl_Transactions_TransactionID, is the name of the control, however, i have fixed this and it is now just me.transactionID

Each time the form is opened, i need to check:

A: if the record is new, if so, then immedialty create 14 records in a table that is linked to the forms record set.

B: if the record is not new, then no action to be taken as the 14 records in the secondary table would have been created when the record was first created.

Basically for each record in the primary table, their must be exactly 14 records in the secondary table (no more and no less). Even if no data is to be inserted into these records in the secondary table.

All this just so i can get a report to look exactly an original paper version of a document that is normally completed by hand.

the document has a transaction id and place for 14 lines of products. these lines may or may not have information stored (i.e there are only 3 products listed so only 3 lines have data). The document is a standard form and is set by head office. iI need my report to look exactly like that document, including empty lines.

[June7]

I Googled: Access report blank rows
Got something you might find interesting as alternative to bloating table with blank records: http://www.utteraccess.com/forum/Cre...3#entry1393413

For the code you posted, try OnCurrent event instead of OnDirty. Also, the default value suggestion.

[Mclaren]

Thanks June7, got the example from utter-access working a treat, saves a lot of unnessesary empty records.

My Report (and form) are now working just right.