Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form query (2003)

    Hi,

    I'm creating a database which will contain annual data for lots of individuals. I need to create a popup box which opens when the user opens a form, which offers the users a choice of years. I need the choice of year to be reflected in the individual records, which already contain a year field.

    Any ideas?

    Katya

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

    Re: Form query (2003)

    Do you mean that you want to filter the records, i.e. only records for the selected year should be displayed in the form? If so, I would use an unbound combo box (i.e. the Control Source property is blank) in the form header or footer.

    You can set the Row Source Type property to Value List and type a series of years in the Row Source property, separated by semicolons:
    1999;2000;2001;2002;2003;2004;2005

    Or you can create a table with a single number field to hold a list of years, and set the Row Source property of the combo box to Table/Query and the Row Source property to the name of the table.

    You need a bit of code in the After Update event of the combo box to set the filter:

    Private Sub cboYear_AfterUpdate()
    If IsNull(Me.cboYear) Then
    Me.Filter = ""
    Me.FilterOn = False
    Else
    Me.Filter = "[YearField] = " & Me.cboYear
    Me.FilterOn = True
    End If
    End Sub

    (cboYear is the name of the combo box and YearField that of the field in the table; replace with the actual names.)

    <img src=/w3timages/blueline.gif width=33% height=2>

    Or do you want to give the user an easy way to select the year for each individual record?

    In that case, I would use a combo box bound to the year field (i.e. the Control Source property is the name of the year field). By binding the combo box to the year field, it will automatically display the year value for the current record. You have the same options for Row Source Type and Row Source as above.

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form query (2003)

    Tha latter. Thanks for the tips.

  4. #4
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form query (2003)

    Edited by HansV to remove white space from screenshot.

    I have managed to create the attached/ following (not sure how the forum works yet!) form which when the command button is clicked - runs a query and then opens another form for data entry. I having problems with the query though. What I want to happen is that the year selected in the first form is automatically entered in records in the second form (which has the same field)

    Any suggestions?

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

    Re: Form query (2003)

    What do you mean by "runs a query"? Is this an action query (such as an append or update query)?

  6. #6
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form query (2003)

    'runs a query' was probably the wrong phrase since it doesn't work. I think i'm trying to create a select query where it looks up the data in the first form and then inputs that information in the second form.

    (Sorry I'm probably not explaining this very well - I've not used access before and I'm struggling!)

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

    Re: Form query (2003)

    Possibility 1:

    You can refer to the combo box in the criteria of a query. Create a query in design view, add the fields you need to the query grid, including the year field, and enter the following in the Criteria line for the year field:

    [Forms]![frm_pdg_date]![cboYear]

    where frm_pdg_date is the name of the form (I copied it from your screenshot) and cboYear is the name of the combo box. This query will display records for the year selected in the combo box. Set the Record Source property of the second form to the name of this query.

    Possibility 2:

    Don't put criteria in the query. Instead, specify a Where-condition in the code that opens the second form:

    Private Sub cmdOpenForm_Click()
    DoCmd.OpenForm "SecondForm", , , "[YearField] = " & Chr(34) & Me.cboYear & Chr(34)
    End Sub

    Here, cmdOpenForm is the name of the command button, SecondForm is the name of the form to be opened, YearField is the name of the year field in the query, and cboYear is the name of the combo box.

  8. #8
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form query (2003)

    I've tried to set up everything using Possibility 1 but I get a " #hame" error message ..................... What does this mean?

    Thanks Katya

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

    Re: Form query (2003)

    Have you substituted the correct names of the form and combo box in the criteria expression?
    What happens if you open the query by itself (while the first form is open)?

  10. #10
    New Lounger
    Join Date
    Nov 2004
    Location
    London, Gtr London, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form query (2003)

    hiya,
    yes the query returns the correct data when the first form is still visible. The year field on the second form is bound to =[query]![queryfield] but returns #name
    does the query persist between the forms? that is, form1 opens - we select a date from the combo and press the cmd button - the form closes and form2 opens, using the macro. Could it be that the query is being zeroed here? (I tried leaving form one open whilst form two opened but it didnt seem to make a difference)

    regards, Katya

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

    Re: Form query (2003)

    The reference to [Forms]![Form1]!... is only valid as long as Form1 is open. If you don't want to see Form1 any more, you can hide it by setting its Visible property to False. (Don't forget to close it when you're done with Form2).

    Is the query the record source of the second form? It should be.

    Otherwise, the second option in my original reply might be better.

Posting Permissions

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