Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Set combo box and printing

    I'm an infrequent reader of this particular forum, so point me in the right direction if it's been covered. This is for a recruiter DB with recruits, company names and 1 or more contacts at the company.

    2 problems involving a subsubform (on a subform) on a form:
    1. Main form has fields for a recruit including a field to one (of the possibly several) contact at the company. Contacts are in a separate table. Call this field BTIndex. Main form also has a field for the name of the company, which is pulled from another table. The subform just has the name of the company. The subsubform pulls a contact thru a combo box and shows the contact's info (phone number, address, title, etc).

    No problem pulling a contact from those for the company with a combo box (they're sorted alphabetically). However, once a contact has been pulled, how can I make sure that, next time I look at that recruit's record, the combo box is set to the previously-selected contact and the fields on the subsubform are for that contact (not the first alphabetical contact)?

    2. When I go to print the recruit form, I am getting all of the contacts for the company, not just the one selected for the recruit. Right now, I've just saved the form as a report and put in a print preview button. If the company has 4 contacts, all 4 are shown in the preview. When I print the form, same thing. I just want the chosen one. Haven't played around with the report yet.

    Thks

    Fred

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

    Re: Set combo box and printing

    First, tell us what version of Access you're using. Then tell us about the tables underneath those forms and subforms. You can only see items you've previously selected if a combobox is bound to an underlying field. We can't make sugggestions without information about how the tables are structured.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Set combo box and printing

    Charlotte,

    First, apologies on not mentioning the Access v#. I'm using 97 with SR2 but will convert it to 2000 for the end-product.

    As far as the underlying structure, that's hard to describe in words - right now the DB is about 450KB. Not sure if it's acceptable to send big files to the board. I know other boards ask for "small files".

    Let me try some words.
    3 applicable tables for this issue:
    Placement Orders: 1 rcd per recruit containing various fields. Relevant ones here are:
    - many other fields for the recruit (name, birthdate, starting salary), terms with the client as far as billing, etc. - not really relevant
    - client ID: links to table with client names
    - BT Index: links to "bill-to" record in Bill-To table with one record per billing-contact.

    Client Names: just a client ID field (primary key) and a Client Name field.

    Bill-To table: BT Index (primary key), a Client ID to link this record back to the client, and name, title, addr, phone number fields describing the contact. A billing contact only works for 1 company.

    Relationships:
    Client Names to Bill-To Table 1-many (each client can have 1 or more billing contacts)

    Placement Orders to Bill-To: 1-1

    Client Names to Placement Orders: 1 to many

    The main form I'm concerned with right now is the Placement Order form. If I try to create a single form using the Placement Order and the Bill-To, I get several errors - most saying that there's not enough room for Access to create the form and it creates a blank.

    There is a combo box on this form to allow selection of the client so that we know who the recruit is working for. No problem here. This is bound to the Client ID in the Placement Order table.

    What I did to get around the size problem was create a subform for the client which is linked to the Placement Order by the Client ID. That subform is a tiny one just so that I can get the Client ID down to the bill-to records (I also had problems getting the Client ID from the Placement Order form to the Bill-To subform if I did this directly). I then have a subsubform for billing contacts within the Client subform (which is in the Placement Order form).

    I have a combo box on the bill-to subsubform that shows the applicable contacts for the client that the recruit will work for. The idea is to pick one for this Placement Order. The combo box shows the contacts in alpha order.

    I have the selection part down. I can pick a contact and that gets recorded back into the Placement Order table as its BT Index value.

    However, when I close the Placement Order form and re-open it to a given record (ie, a recruit), the billing-contact combo box shows the first alpha contact for the client, rather than the one for this recruit. The subsubform shows the relevant for the first alpha contact. Nothing has changed in the underlying Placement Order table. I would find this disconcerting (if the billing contact is not the first alpha contact) and think it has to be re-selected. So Problem #1 is how to show the assigned contact in the combo box and his/her info rather than the first alpha. Of course, the combo box could be used to select a dif billing contact for the recruit (eg, a mistake was made the first time around).

    The user of the DB also wants to print the Placement Order for a single recruit. So I did a Save As Report on the Placement Order form (will modify the Report later to tidy things up). I included a Preview Report button and actually printed the report. In both cases, all of the client's billing contacts are shown rather than the one assigned for the Placement Order record (recruit). If a client has 4 contacts, all 4 are printed; if it has 3, 3 are printed. I only want 1 - the one chosen. This is problem #2.

    To get a single Placement Order to print (rather than all the Orders/Recruits in the DB), I included a where clause in the code that the Print Preview button created. I'd think something similar to this is needed to get the 1 billing contact. However, I'm not sure what to do here since the combo box is not showing the right contact once I close and re-open the form (Problem #1).

    Thanks for the help. If you want me to send the file, let me know. I think the above is all the relevant info.

    Fred

Posting Permissions

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