Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms & Subforms (Access 2002)

    I am stuck again with the same issue as before and I do not understand what I am doing wrong. I have looked at my previous posts and have had no luck in catching what I am doing wrong. I want to create a form that will let me enter data, I would like to enter the Vendor Data first hit a button and enter the rest of the info but when I do that I can not enter part and part desc. it does not allow it and if I change it to Parts first and have a button for the rest I can not enter the cost and retail. I am stumped. Here is the DB in Access format.


    Rbelly
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms & Subforms (Access 2002)

    I don't understand why you're opening the second form and then trying to filter it. Is there a reason you don't simply pass the VendorID in a WhereCondition to the second form so that it opens on that record? It's far faster than filtering the form after the fact. The immediate problem you're having is because the query behind your second form doesn't contain all the fields it needs. You need all the join fields in the query, so try this instead of what you have now:

    SELECT Printer.PrinterModelNumber, Printer.PrinterId, Parts.PrinterId AS partsPrinterID, Parts.PartID,
    Parts.PartNumber, Parts.PartDescription, Price.Cost, Price.Retail, Price.VendorID, Price.PartID AS PricePartID
    FROM Printer INNER JOIN (Parts INNER JOIN Price ON Parts.PartID = Price.PartID) ON Printer.PrinterId = Parts.PrinterId;

    If you are allowing more than one entry in the Price form for a particular vendor, then you also need to set the VendorID in the Price form's current record, no matter how many records the user may enter.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms & Subforms (Access 2002)

    Charlotte,
    I want to compare prices from 4 vendors for parts when I have to order them. So each vendor will have one price per part. Do I create a query you sent and then create the form?

    Rbelly

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms & Subforms (Access 2002)

    You have two tables with a one to one relationship and much of the same data. Is there any reason you cannot add the Cost and Retail fields to the Parts table and do away with the Price table? This would make all you are trying to do much easier.

    Also you are likely to run into trouble using field names the same as control names. I know that is how access sets them up but it is really worth changing the control VendorID to tboVendorID etc., to avoid confusing yourself and access about what you are referring to.

    You probably need, instead of the vendor form, a combobox on your Price form. That way you only have the one form. The combobox would be unbound and use the vendors table as its list source. You would then use its change event to set a filter on the form. The form will then show all part numbers for that vendor.

    You will need to deal with vendors not in the list, and with partnumbers not in the list or not allocated to that vendor yet. I would do those by putting on AddNewRecord buttons and making sure the linking information to vendor number is filled in in code for each new record.
    David Grugeon
    Brisbane Australia

  5. #5
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms & Subforms (Access 2002)

    Please ignore my last post! I did what you told me to do and I now understand how a query works to update the tables! I will work with this and test it late Tue or Wed and see where I get stuck.

    Thank you very much for you HELP

    Rbelly

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms & Subforms (Access 2002)

    Ah. More info. In that case you will need the separtate price table but it will have a one to meny relationship, not one to one. (Many on the Price side). I would take out the duplicate info. Take the part number out of the Price table, and take the Vendor out of the Parts table.
    David Grugeon
    Brisbane Australia

  7. #7
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms & Subforms (Access 2002)

    Ok
    I went back to all my posts and recreated the database. I think I got everything correct in my tables but I still have issues with the forms. All I want to do is to have a form that will allow me to enter the vendor, the type of printer, the part number, part desc. and cost and retail. I see now when i create the form with the Parts button how it says filtered on the bottom. I still do not understand what I am doing wrong. I have 2 books on Access and have had no luck in figuring out what I am doing wrong. What books would you recommend?

    Thanks
    Rbelly
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms & Subforms (Access 2002)

    Try this

    I have moved the relationship diagram around - that make no difference to anything.

    I have created a form which does more-or-less what you want.

    It needs some further development as it relies on the access navigation buttons which are not ideal, but it may show you a way forward.
    David Grugeon
    Brisbane Australia

  9. #9
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms & Subforms (Access 2002)

    Thanks I will keep at it!

    Rbelly

Posting Permissions

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