Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a single form for multiple tables (2002)

    I am trying to use a single form to save to one of several tables according to the selection in a combo box. It is an inventory of equipment, and each type of equipment has several fields unique to only that type. If "Computer" is selected, I want the record saved to the Computers table. If "Projector" is selected, I want it saved to Projectors table.

    I figured this would avoid hundreds of Null value fields that would exist if I used a single table for all equipment. I don't know if the space and speed saved is even worth the pain in my ass this is becoming, but I would appreciate any input re: all that.

    If it's worth doing, is it even possible to change the recordset on the fly like that? I tried putting an If statement in the Change event for the combobox, which set Me.RecordSource = "Computers", or whatever, but that produces of all things a "Value doesn't meet validation rule" error, which makes no since to me since I have set no validation rules anywhere. Same goes if I try the following on the Change event:

    Sub ChangeRecordsetProperty(strTable As String)
    Dim frmNewRecords As Form
    Dim rstNewRecordset As New ADODB.Recordset

    Set frmNewRecords = Forms(Forms.Count - 1)
    rstNewRecordset.Open ("SELECT * FROM " & strTable), _
    CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Set frmNewRecords.Recordset = rstNewRecordset
    Stop
    End Sub

    where I call that with ChangeRecordsetProperty("Computers") etc.

    I don't really know what I am doing here, but please if anyone knows what my problem is, I'd love to hear it.

    Thanks,
    DP

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a single form for multiple tables (2002)

    I think what you want to do could be done. But I don

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a single form for multiple tables (2002)

    One strategy to consider: create one table with all the fields that are common to all equipment types e.g. tblEquipment. Items like Name, Manufacturer, Model, Serial Number, etc belong in the main table. One of the fields in the table is the EquipmentTypeID. Then create a series of other tables containing the unique fields for each equipment type. Link the tables using one-to-one relationships.

    Then create a single form that deals with the common fields. Include a subform, but leave it's SourceObject blank. Create a series of subforms, one for each equipment type (I know you wanted a single form, but bear with me...). Use the EquipmentID field as the linking field to the subform. Ensure each of the subforms also has a consistently-named ID field.

    Using AfterUpdate event of your combo box, assign the appropriate SourceObject to the subform object according the the current equipment type. You could also filter the main form to show only records of the selected equipment type.

    This provides user with the functionality of a single form, even though multiple forms are required.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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