Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple use of same form? (XP)

    I have a form that I use for entering and viewing records (in this case, jobs). I need to search for past jobs by job number so I intend to use a parameter query where the user inputs the job number and the found job is displayed with this same form. Is this possible or must I copy the form then associate it with the query instead of the table?

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

    Re: Multiple use of same form? (XP)

    You can use a combo box to let the user select a job number, and write code in the After Update event of the combo box to filter the form:

    Private Sub cbxJobNumber_AfterUpdate()
    Me.Filter = "[JobNumber] = " & Me.cbxJobNumber
    Me.FilterOn = True
    End Sub

    If you want to open the form from another form, you can use the WhereCondition argument of DoCmd.OpenForm to set a filter:

    DoCmd.OpenForm FormName:="frmMyForm", WhereCondition:="[JobNumber] = " & Me.cbxJobNumber

    Both examples assume that JobNumber is numeric. Of course, you will have to substitute the appropriate form, control and field names.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple use of same form? (XP)

    Thank you Hans. My knowledge of Access & VBA is too limited to fully understand your answer. I did copy the form and change the record source to the query instead of the table. The problem I have now is that the form has two subforms that are both linked to the original jobs table. Since I now use the query as the record source instead of the table, the subforms do not display any data. What did I do wrong? How can i correct this?

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

    Re: Multiple use of same form? (XP)

    The original form and both forms are all bound to the same table? What is the use of that?

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple use of same form? (XP)

    Actually, only the original form is bound to the table. The second form is bound to a parameter query that searches the table for whatever the user inputs in the parameter dialog box. The result is displayed in the form. The problem is that the subforms do not display any data. I suspect this is due to the fact that the original form (which I copied when I created the second form) was bound to the table. The original form, by the way, works very well for entering new jobs and viewing other jobs. I had hoped I could use the original form to do a query by example but I found that when I type in an existing job number in that control, it is rejected because the job number already exists. So, the only way I could do it (limited by knowledge and experience) was to construct a parameter query and display the results in the second form which I bound to the query.

    I admit it is a clumsy design and I will get better as I learn more about Access and databases.

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

    Re: Multiple use of same form? (XP)

    You can't type a search value in a control that is bound to a field, because Access will try to save the value in the table.

    Try the following instead in the original form (make a backup of the database first):

    - Open the form in design view
    - Make sure that the Wizard button on the Toolbox toolbar is "on".
    - Add a new combo box to the form, for example in the form header or footer.
    - The Combo Box Wizard will start.
    - In the first step, select the third option (look up a record in the form.) Then click Next.
    - In the second step, select the Job Number field, then click Next.
    - In the third step, adjust the column width if necessary, then click Next.
    - In the fourth step, enter the label for the combo box, then click Finish.

    The Wizard will create the combo box and the VBA code that will look up a record, you don't have to write any code yourself.

Posting Permissions

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