Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    10

    Unanswered: Forms and queries

    Hi everyone,

    I made a form to INPUT new records, so in this form I input everything into the fields and then press a button to ADD this data to the database. Thats working fine, but now I need to make a form to choose which record I want to EDIT.

    I want to EDIT the record based on a query with two criteria which will HAVE to result in a unique record, DATE and NAME.

    I prefer to use the same form and have a TWo step process.

    - choose the NAME from a combo box, and this will FILTER results

    - show the name i selected with the DATE (from another combo box).

    How can I do this, Im an IT guy but have novice knowledge in databases.

    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here are two possible solutions (I suppose that the form receives its data from a table named MyTable) that has a unique id field named ID):

    1. On the form, place a combobox with these properties:
    Name: Combo_ID
    RowSourceType: Table/Query
    RowSource:
    Code:
    SELECT "<New>" FROM MyTable UNION SELECT MyTable.ID FROM MyTable
    2. In the AfterUpdate event code of this combo, place this code:
    Code:
    Private Sub Combo_ID_AfterUpdate()
    
        Dim rst As DAO.Recordset
        
        If Me.Combo_ID.Value = "<New>" Then
            Me.DataEntry = True
        Else
            Me.DataEntry = False
            Set rst = Me.RecordsetClone
            rst.FindFirst "ID = " & Me.Combo_ID.Value
            Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
        End If
    
    End Sub
    If you select <New> in the combo, the forms is in DataEntry mode: it goes to a new record and you cannot edit the existing ones. If you select an ID in the combo, the forms is in Edit mode, you can modify the values of the existing rows.

    2. if you want to limit the form to one record when in Edit mode, you can use a filter instead if simply searching for a specified record. The code then becomes:
    Code:
    Private Sub Combo_ID_AfterUpdate()
      
        If Me.Combo_ID.Value = "<New>" Then
            Me.FilterOn = False
            Me.AllowAdditions = True
            Me.DataEntry = True
        Else
            Me.DataEntry = False
            Me.AllowAdditions = False
            Me.Filter = "ID = " & Me.Combo_ID.Value
            Me.FilterOn = True
        End If
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jan 2010
    Posts
    10
    Thanks for your help but the thing is I'm still lost.

    The form I have ONLY enters NEW records, there isn't an option to search or bring up existing records.

    I didn't understand in the above post how to filter Name and Date (which will definitely produce a unique result) and bring up the records based on choosing two combo boxes...

    I'll explain again:

    I have Name and Date. When I choose a Name there will ONLY be one Date to it, so I want to choose Name from a combo box and the next combo box (or any other control) will filter to produce the date that corresponds to the Name. I do have an ID for each record of course but I don't want to search by ID.

    The form as it is right now ONLY allows me to input new records when I click a button (twice, first time to clear the fields cuz it opens with the last records, and second time to submit and create new record).

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm more confused than ever.

    If your form only enters new records, there is nothing to filter. How does it work by the way? As far as selecting by name, date or by any other criteria is concerned, its difficult to help without knowing the organisation of your table(s).

    Could you please post relevant information about the form (properties from the Data tab mainly, code executed), as well as the structure of the table(s) or query that is used as the recordsource for the form?
    Have a nice day!

  5. #5
    Join Date
    Jan 2010
    Posts
    10
    Ok i'll explain in simpler and clearer terms.

    The database is a checklist entry system. We have an inspector going around our shops and checking for certain points such as cleanliness and sample conditions. The fields are yes/no (check-box) and text fields (for him to enter from 1 to 4).

    The form's header has a combobox where you choose the "name" and "date" A name can have ONLY one date there is no linkage in the database but the practical process prevents the inspector from visiting more than one showroom a day. It also has a button to INPUT the data. It will not input as long as the required fields are empty.

    The details part of the form has tabs, each tab is a category which looks like the paper form he has, so he goes to the showrooms, he fills out the paper and then the next day fills out the database.

    There has been no VB or any of that added to the form. I need to be able to EDIT a record that I have already inputted via this form, now I figured that the easiest way to cal up a certain record is to filter Name and Date as they are easy to use and must result in ONE record only.

    Thanks for your help.

Posting Permissions

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