Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Unanswered: Can't add or change a record coz ...etc (error message)

    Hi

    Problem summary:
    I get this err message when I try to add a new record into a form\subform:

    ((Can't add or change a record because a recordset is required in table stock))

    Details:

    My database is made up of 3 tables

    stock (*stockID, name, qty, etc) ---> stockID is autonumber
    customer(*customerID, name, etc) --> customerID is autonumber
    invoice (*invoiceID, stockID, customerID, etc) --> invoiceID is autonumber, stockID & customerID is a number (not autonumber)

    I made a query (qryinvoice) that has all Invoice fields + other related fields in customer&stock tables

    When I enter data into the query it accepts all input without any problems

    I made a form with subform, the form is based on the query (qryinvoice), I made the childform linked 2 the master form using the InvoiceID field, that is Master field & Child field is InvoiceID

    When I run the form, it shows the previously entered records fine, but when i try to enter new data, it works fine in the master form but once I try to go to the child form this error message pops up by the 'office assistant' which says:

    Can't add or change a record because a recordset is required in table 'stock'.

    BTW: the InvoiceID in the child form shows as an Autonumber ..is this the problem? or what?

    Note: The autonumber of the invoiceID in the master form is generated perfectly, but I don't see it in the Child form when T try to go there.

    Any help is highly appreciated.

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I don't think you specified what your child form is comprised of. This might help. If you are using this to enter invoices, you should consider making the master form based on your stock table, then the subform based on the qryInvoice and having the join through stockID.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Sep 2003
    Posts
    8
    Thanks a lot dear for the help!

    But...

    I am so much confused

    I really don't understand how to base the master form on the stock table when i want the stock table to be my child form (thesubform)

    I tried to have the join thru the stock id but it does not work...

    well, maybe i don't know the basic rule of creating a master\child form of a 1:M relationchip?

    can anyone tell me the basic procedure of creating a form\subform of my database?

    the database basically consists of the following


    stock (stockID, name, qty)
    Invoice ( I got confused here coz I need a unique number for the invoice but at the same time I want to order more than one product, so I can't set invoiceID as a primary key otherwise I won't be able to order more than one product thru within order) ... anyway it should have the following records ... invoice, stockID, date...etc )

    The form should have the invoice number in the master form

    and in the subform i should order as many products as i can within this invocieID

    can someone help me?

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    The basis of having a Master->Child form/subform lies best in the referential integrity between the tables/data from which the forms are based.

    An example: If I sell tires, I can sell Goodyear, Bridgestone, etc. Each of these tires will have models within their lines, Bridgestone-Blizzak, Bridgestone-Aquatred, etc. If I am to create the tires and tireModel tables, I will have referential integrity between them 1:M Tires-TireModel. The master form will be based on tires and the subform will be based on TireModel, I will join them through TireID which exists on both tables and is unique to Tires. Tired yet?

    With the data in your first post it looked as though StockID was the unique identifier and it existed on your invoice table, hence my suggestion. I think what you are probably looking for is an Invoice Table and an Invoice Items table. Denormalizing the data, I assume you would have your CustomerID on your Invoice table. To get stock on your invoice, you probably want the stockID on your Invoice Items table as a dropdown. Personally, I can't see the value in trying to link stock and invoices as they are not really related in a direct fashion. Just as customer and stock aren't really realted, although you sell and invoice stock to a customer.

    Hope this helps.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •