Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable form record sources (2002)

    I would like to create a form that can access the same fields from variable record sources.

    Is there any way to do this, without having to create forms for each table I would like to reference.

    I need the user to be able to update the information in the form, so I have found that trying to join all of my tables together doesn't work.

    I have attempted to play with creating an event procedure, on open, based upon recommendations in postings that I found that seemed similar (but referred to subreports instead of forms or subforms), but I'm a novice at the code and I can't seem to get it right.

    Any suggestions are greatly appreciated. Thanks.

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

    Re: Variable form record sources (2002)

    You will need a form which selects the appropriate table. Then on that form put a button which opens the main form and sets its RecordSource property to the selected table.
    David Grugeon
    Brisbane Australia

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable form record sources (2002)

    Thanks for the suggestion, unfortunately I'm still having trouble.

    I have set up the forms as you suggested and I have tried different combinations of macros and select statements to set the RecordSource property to the selected table, but I either get an error message or my form does shows with all errors. How do I set its RecordSource property to use the value I select from table selection form.

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

    Re: Variable form record sources (2002)

    Assume the combobox from which you select the table is on frmStartForm and is called cboSelectTable
    frmStartForm also has a button called btnGo
    Assume the form you want to display is called frmMainForm

    the code for the click event of btnGo would be like this

    Sub btnGo_Click()
    DoCmd.OpenForm frmMainForm
    Forms!frmMainForm.RecordSource=Forms!frmStartForm. cboSelectTable
    Forms!frmStartForm.SetFocus
    EndSub

    I haven't tried this as I am on the wrong computer at the moment but it should do what you want.
    David Grugeon
    Brisbane Australia

  5. #5
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable form record sources (2002)

    Thanks for your help. It worked great!

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable form record sources (2002)

    As I said in my last note, this worked great! But what if I want to filter the recordsource. How do I add that? For example, I only want to select records from the table that have a name that is not null. If I use the where condition in the DoCmd.Open form it seems to be overwritten when the RecordSource is changed.

    Thanks for all your help it is greatly appreciated.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable form record sources (2002)

    It is no use setting a filter in DoCmd.OpenForm in this case, because - as you found out - changing the RecordSource of the form undoes the filter. There are two possible solutions:

    1. Set the RecordSource to an SQL statement that filters the table. In the following, replace MyField by the name of the field that must not be null:
    Forms!frmMainForm.RecordSource="SELECT * FROM " & Forms!frmStartForm.cboSelectTable & " WHERE MyField Is Not Null"

    2. Set the RecordSource to the selected table, and then set a filter. Again, replace MyField by the name of the field that must not be null:
    Forms!frmMainForm.RecordSource=Forms!frmStartForm. cboSelectTable
    Forms!frmMainForm.Filter = "MyField Is Not Null"
    Forms!frmMainForm.FilterOn = True

    In both cases, "MyField Is Not Null" can be replaced by more complicated WHERE-conditions if desired.

Posting Permissions

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