Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    7

    Unanswered: Combox form and subform

    Hi!

    I would like to receive help for following problem.


    I have the tables :

    CATALOGS – with the fields: ID_CATALOG (number of the CATALOG); NAME OF FASTERNERS and ID_CATEGORY ;

    CATEGORIES – with the fields: ID_CATEGORY and NAME_CATEGORY (like screws, nuts, washers);

    Table 3 - with the fields: ID_CATALOG and ID_CATEGORY (one catalog could have more than one category);


    And I want to build a form with a NAME_CATEGORY COMBOX that when I choose a value it controls a subform that lists all titles of catalogs loaded. Also the available values on the COMBOX, should be the values of the table 3.



    Regards,

    koolaccess

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Is it possible to attach your database to mess with?

  3. #3
    Join Date
    Sep 2004
    Posts
    7
    Attached to msg is the file.

    Regards.
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by koolaccess
    And I want to build a form with a NAME_CATEGORY COMBOX that when I choose a value it controls a subform that lists all titles of catalogs loaded. Also the available values on the COMBOX, should be the values of the table 3.
    Code:
    ' I usually use RecordsetClone with the bookmark property
    ' to run the record change from the combo box.
    ' If you set your form as you have it only
    ' with a sub form and Master/Child fields linked on the ID_CATEGORY
    ' when the main form category changes the sub form will stay in sink.
    ' to set the Master/Child field links you have to access the properties
    ' of the subForm object in the design view (not the form itself) the
    ' subform object will be active when you have a resizable border showing
    ' around the form and the cursor shows a hand when you move over the
    ' border. Then right click and select properties, link settings are on the
    ' data tab. hope thats not as confusing as it sounds.
    
    ' Here's a sample this works off of the on_change event of your
    ' combo box object. and the combo box will need to be unbound
    ' with the Row source type set to Table/Query and the Row Source
    ' set to a query for your list of values.
    
    '--------START------------
    
    Option Compare Database
    
    Private Sub cmboNameCategroy_Change()
    Set rst = Me.RecordsetClone
        strCategoryID = Me.cmboNameCategory
        strCriteria = "NAME CATEGRY = " & strCategoryID
        rst.FindFirst strCriteria
        rstBookMark = rst.Bookmark
        Me.Bookmark = rstBookMark
    End Sub
    ~

    Bill

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I just messed about with your form to show you what I would do. See attached - hope this is what you are looking for.
    Attached Files Attached Files

  6. #6
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    koolaccess,

    I came to work thinking about the form that I modified and I think that you are going to need to make a slight alteration.

    Within the SQL statement of Combo_CategoryName and List_Fixtures you may wish to alter the code relating to CategoryID.

    As it stands, if you put 1 in the ID Catgory it is going to search for *1* so you'll end up with 1, 10, 11, 31, etc...

    Just take the ("*" & ... AND ...& "*") out of the statement for this.
    In addition to this when searching for Screw in the name, it will also look up Screwdriver, ScrewBall... Same solution.

    Christy

  7. #7
    Join Date
    Sep 2004
    Posts
    7
    Hi!

    First of all I wish to thank you for the two solutions for my problem.

    I’m just a beginner so I had to take a time to understand yours solutions.


    Also I would like to put some questions about combox:

    1 - a combox is like a container of information that displays information as result of row_source (query or sql statement). And I can’t use a combox as data control record and to do something similar I have to create procedures like after_update and use requery ( I want that you say about what I wrote is right or not) to update the information displayed.

    2- Error “Class does not support automation or does not support expected interface” – when this error appears I think it is related with the first point.



    Cristy:

    From what I understood of “LOOK UP FOR FASTERNERS BY CATEGORY” form it is based on: two combox unbound and one subform unbound. And each objects has procedures.

    *So, how did you make disappear the data control from the bottom of the “LOOK UP FOR FASTERNERS BY CATEGORY” form?

    *And on “Option Compare Database” where is

    “Pritave Sub List_Fasteners_BeforeUpadate(Cancel As Interger)”

    Is it necessary to make possible for the procedure of the subform to receive the values of the two combox and display the result?



    Savbill:

    I understood what you said I’ll try to implement your solution.


    Regards,

    Koolacess.

  8. #8
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Hey koolaccess,

    To be quite honest I don't really understand what I do myself - its really trial and error stuff.

    Q: So, how did you make disappear the data control from the bottom of the “LOOK UP FOR FASTERNERS BY CATEGORY” form?

    A: If my understanding of the what you asked is correct the datacontrol is the buttons for next record, last record etc? If thats right, if you look at properties of the form, I have set a load of the values to "No" - What I think you are talking about is Navigation Buttons.

    Q: And on “Option Compare Database” where is
    “Pritave Sub List_Fasteners_BeforeUpadate(Cancel As Interger)”

    A: In a sense I have bypassed this by using the requery commands on after_update of the two combos meaning that the beforeUpdate() isn't necessary because the list should be updated with the combos.

    Q: Is it necessary to make possible for the procedure of the subform to receive the values of the two combox and display the result?

    A: As the form I altered stands, the list box should update with both values. however I have once again modified your form because of what I was thinking this morning - I have removed the * because with this, looking at Cat 1 will display Cat1, Cat10, Cat31 etc.. anything with one in.

    Your list box now also includes the 4 values.. CategoryID, CategoryName, FastenerName, CatalogueID.

    My e-mail address is oconnor@excalibutt.freeserve.co.uk and if you do need me to explain, check with MSN messenger see if I'm around. I have trouble explaining in this format - at least with messenger I can show you through.
    Attached Files Attached Files

Posting Permissions

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