Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: Combo box and subform help required..

    Here is my problem....

    I have a main form that has a subform pulling in information. The main form only has a combo box in which the user is supposed to select an organization from the combo list, then once this is selected, the subform shows the information for the organization selected. The ideal situation is if you select an organisation and there already exists a record for it, it will show the record, BUT it there is no record it should create a new record for that organization. So my idea was to have an "if" statement first to check to see if the record already exists, if it does go to the first record, if not create one. Here is my code:

    Private Sub cbo_Organisation_AfterUpdate()
    searchtxt = "[OrgID]= " & [cbo_Organisation]
    Me.RecordsetClone.FindFirst searchtxt
    If Me.RecordsetClone.NoMatch Then
    Me.RecordsetClone.AddNew
    Else
    Me.RecordsetClone.FindFirst searchtxt
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    Forms![Tbase].[TBase2].Requery
    Forms![Tbase].[TBase2].Visible = True
    End Sub

    If I select an organization from the combo box that has a previous record, the subform works ok, but if it doesn't have a previous record I don't know how to make it create a new one. I know the problem is in the 1st part of my "if" statement (in bold). As you probably can tell, I am a newbie and I am willing to learn. Any help you can offer will be appreciated.
    Last edited by kobik; 02-13-05 at 22:03.

  2. #2
    Join Date
    Jan 2005
    Posts
    68
    the easiest way to do what you want to do is use the not in list event. you can create a popup form to input the new organization, then call this from the combo box's not in list event (you must select limit to list from the combo box's properties).

    the trick with the not in list event is the response. you will see the response argument in the code that access writes you for the not in list event. once the user adds the data you must set the response to [acDataErrAdded]. This represses the error message, and tells access to requery the database.

    after you have the data it would probably be simpler to reset the recordsource for your subform with the selected organization as the WHERE argument in the SQL string.

    hope this helps

  3. #3
    Join Date
    Feb 2005
    Posts
    5
    But how do I create a record? Let me try and explain by giving an example. Say you have a combo box that has a list of organizations. When you select an organization from the list, the subform will pull in all the data for that organization. Now there were previous transactions for an organization, it works ok, but if there were no transactions, it should create a blank record for that organization using the value from the combo box. In my case, the combo box is pulling from a table of organizations and the subform is pulling from a table of transactions, they are joined by the organization id.

    I gave it a try after the last post and I came up with this code for the combo box:

    Private Sub cbo_Organisation_AfterUpdate()
    searchtxt = "[OrgID]= " & [cbo_Organisation]
    Me.RecordsetClone.FindFirst searchtxt
    If Me.RecordsetClone.NoMatch Then
    Me.Recordset.AddNew
    Me!OrgID = [cbo_Organisation]
    Else
    Me.RecordsetClone.FindFirst searchtxt
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    Forms![Tbase].[TBase2].Requery
    Forms![Tbase].[TBase2].Visible = True
    End Sub

    However, the problem now is that it is creating 2 records rather than 1. Everytime I select an organization from the combo list and there are no transactions for that organization, it will create 2 records in the transaction table, 1 record just has the orgID and the rest of the fields are blank and the other is correct. Please 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
  •