Create Multiple Records from Single Form

[mcktigger]

Hi

Thanks for your help earlier in the week. All is working perfectly with creating new quotes.

However now got an issue of how to handle the user going back and editing the records. I need to allow the user to edit prices on the form Frm68AQuotes which then propogates through to tblquotelines. Could your code be modified to an Update Query easily?

Also need to handle if they go back and add another new product price. Currently your code would create new records causing duplicates. I tried amending it to

If ctl.Tag = "ED" Then
If ctl.Value > 0 Then
if ctl.oldvalue = 0 then
iProductID = DLookup("[ProductID]", "tblpricelist", "[FormName] = '" & ctl.name & "'")
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO tblQuoteLines (QuoteID, ProductID, SellPrice) VALUES (" & Me.QuoteId & "," & iProductID & "," & ctl.Value & ")")
DoCmd.SetWarnings True


End If
End If
End If
Next

But this is not doing anything - no error and no table update.

Thanks again.

[pbaldy]

That would still create a record (might fail if it hits a key violation). To modify an existing record with SQL you'd run an update query, the basic syntax of which is:

UPDATE TableName
SET FieldName = 123
WHERE FieldName = 123

[rpeare]

This is a bit of a trickier situation. with each row that has a value in the first column you would have to check to see if there's an existing record, if there is, update it, if there isn't run the insert query.

And as pbaldy said, you'd want to generate an UPDATE query for any item that has a value that is not currently in your database.