Full Version: inserting rows with a sub table
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
Roaders
Jun 24 2006, 08:37 AM
Hi Guys

This is very annoying as this is the second time I've had to write this post so please forgive me if am a bit economical with my words!

Anyway, I have a DB, which I've attached with 2 tables.

items:
itemID
itemName
auctionPrice

ingredients
parentID
ingredient ID
quantity

I have a form called items that I use to create new items and add ingredients to them.
What I want to do is to be able to add new ingredients to the items table with this sub form - this would mean that the subform would have to create new item records and also display auction price, and while we're here may as well include line price too.

I would also like to include the total price on the main item form and the profit. I have a query that displays this data but I can't get it to appear on the form.

Any help greatly appreciated.

Many thanks in advance

Giles
norie
Jun 24 2006, 10:43 AM
Giles

What you need to do is write code for the NotInList event that adds the new item to the item table.

Something like this perhaps, though I'm having some problems with it because of the relationships in your database.
CODE
Private Sub ingredientID_NotInList(NewData As String, Response As Integer)

Dim Resp

Dim strSQL As String

Dim qu

    Resp = MsgBox("This is a new item, do you wish to add it?", vbYesNo)

    

    If Resp = vbYes Then

        Response = acDataErrAdded

        qu = InputBox("Please enter the quantity")

        strSQL = "INSERT INTO items ( itemName, auctionPrice ) VALUES ('" & NewData & "', " & qu & ")"

        CurrentDB.Execute strSQL

    Else

        Response = acDataErrContinue

    End If

    

End Sub


Edited by: norie on Sat Jun 24 11:46:20 EDT 2006.
Roaders
Jun 24 2006, 12:09 PM
Thanks, I'll give that a go.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.