Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    82

    Unanswered: 2 Qsns: Combos on forms to populate, Query

    Hi,

    I have two issues I can't get to the bottom of:

    1. Populating a form using a Combo box (the field is a primary key)

    This is a data entry/edit form that has a sub-form. The form property has Data Entry set to "No", so the form opens without data.

    I would like to enable the form for both new data entry as well as edit existing data - and for the latter, I would like the form to be populated based on the combo box selection, with the appropriate records in the sub-form as well. I've tried several event procedures, including the one below I picked from this forum (After Update), to no avail:
    ----------------
    Private Sub BuyingGroupID_AfterUpdate()
    ''' If the data is in other column make this Change in (1):
    ''' cadNameFound = Forms![YourFormName]![MyCombo].Column(n)
    ''' where n is the column number beginning from 0 where your search
    ''' data is

    Dim strSearch As String
    strSearch = Me.BuyingGroupID 'whatever the name of your combo is
    Me.BuyingGroupID.SetFocus
    DoCmd.FindRecord strSearch, acEntire, , acSearchAll, , acCurrent

    End Sub
    ------------------
    If and when I get this to work, I would like to enable another functionality - I have a list box form that is populated based on a query. I would like the user to be able to double-click on any record in the list that would open the above data entry form with the recordset. I've got it to open the form, but it is not getting populated.

    2. Calculation on Query

    My second problem is a query based on three tables (1:M, 1:M) with several calculated fields. One of them, for instance, gives a simple "number of days before submission date". The query works, without any criteria - pulling all data, including negative numbers. When I put in a criteria in the above mentioned calculated field (such as <5, to denote less than 5 days before submission close date), the query has no data.

    The report that is based off of this query acts even more wierd. When I remove all criteria from the query, the report asks for a parameter value ("Enter Parameter Value") for days before close. Whatever number is entered does little - this number is simply entered in the report in the particular field (ie, if I enter the number of days before close as 5, this appears in the report in the field), while the records themselves include all, including those prior to and after the close date. Simply, the query doesn't do what it's supposed to, and the report asks for a parameter value when I haven't specified any in the query.

    Thanks in advance for the help.

    Jabo

  2. #2
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Populating a form :

    -place an unbound combobox in the header that is filled with a selection of your data (for example : ID and Desciption).
    Let's name it cboSeeker.

    -set the source of your form so that the criteria for the ID is referring to that combobox. You could use this SQL code :
    "SELECT * FROM YourTable WHERE ID = Forms!YourForm!cboSeeker;"

    -in the AfterUpdate event of cboSeeker, put this code line : Me.Requery


    So, on opening your form, cboSeeker will be empty and thus no data will be selected by the recordsource of your form.
    Each time you select a value in cboSeeker, the event will be triggered and the form will be requeried. As now there is a value in cboSeeker, the form will display the record, referred to by the combobox.

  3. #3
    Join Date
    Mar 2004
    Posts
    82
    Originally posted by Herman
    Populating a form :

    -place an unbound combobox in the header that is filled with a selection of your data (for example : ID and Desciption).
    Let's name it cboSeeker.

    -set the source of your form so that the criteria for the ID is referring to that combobox. You could use this SQL code :
    "SELECT * FROM YourTable WHERE ID = Forms!YourForm!cboSeeker;"

    -in the AfterUpdate event of cboSeeker, put this code line : Me.Requery


    So, on opening your form, cboSeeker will be empty and thus no data will be selected by the recordsource of your form.
    Each time you select a value in cboSeeker, the event will be triggered and the form will be requeried. As now there is a value in cboSeeker, the form will display the record, referred to by the combobox.

    Hi,

    I ran into a SQL error: "Syntax error in query expression "SELECT...", runtime error 3022 pointing to the "Me.Requery" line.

    I substituted my names for the YourTable and cboSeeker. I should explain that the unbound combobox's control source is set to the ID, the row source type to Table/Query, and the row source to a query from where I choose two columns (ID and name). The bound column is to the ID. The query queries the underlying table.

    On the form property, the record source is the query mentioned above, and the filter has the SELECT statement you gave me. The AfterUpdate has the cmd you gave me.

    Jabo

  4. #4
    Join Date
    Mar 2004
    Posts
    82

    Question REPOSTING: 2 Qsns: Combos on forms to populate, Query

    Clues?

  5. #5
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Sounds like you understand it wrong :

    The SELECT statement I gave you must be put in the recordsource of your form, not in the Filter. Leave the filter blank.

    If still not working, send me a copy of the SELECT statement, maybe you mistyped something.


    CU
    Herm

  6. #6
    Join Date
    Mar 2004
    Posts
    82
    Originally posted by Herman
    Sounds like you understand it wrong :

    The SELECT statement I gave you must be put in the recordsource of your form, not in the Filter. Leave the filter blank.

    If still not working, send me a copy of the SELECT statement, maybe you mistyped something.


    CU
    Herm

    Hello, Herman:

    I made the changes, and it didn't work - gave me a run-time error 3022: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship." etc etc. The SQL code as pasted into the recordsource of the form is shown below:

    SELECT * FROM tblBuyingGroup WHERE BuyingGroupID=[Forms]![frmBuyingGroupsDataEntry]![cboBGselection];

    Thanks for the help.

    Jay

  7. #7
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    I believe your Combobox is a bound one instead of unbound.
    The controlsource of the combobox cboBGSelection must be empty, its row source should be "SELECT BuyingGroupID FROM tblBuyingGroup.
    In its AfterUpdate Event, you must place the code Me.Requery

    The SQL code for the recordsource of the form seems alright as you have copied it.

    The error you get is most probably caused by the fact that your combobox cboBGSelection is bound to the field BuyingGroup. When you enter an ID in it, Access will try to add it to the table and will cause a duplicate value in your index.
    Just remove the controlsource of the combobox and it should work.

Posting Permissions

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