Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014

    Unanswered: Need help with saving/editing a record using a form with a combo box

    So this is what I'm trying to do:

    I have a form that I need to use to add new clients to a table in my database, lets call it tblClients. On this form I have a combo box which, when selected, will drop down with the first and last names of all clients in tblClients, as well as their Client ID. Once you select a client from the drop down menu, it populates all of the fields in the form with that clients info (Client ID, fname, lname, address, phone number, etc. etc.). I need two other things on this form: one button that will save or modify whatever current record is currently pulled up, and one button that will start the process for entering a new client, so basically it would blank out all of the fields and fill the Client ID field with the current number +1.

    As of right now I have the form made and the combo box works, in that I have 3 dummy clients in the tblClients and when I select each client from the combo box it will populate the fields on the form with the info. I'm using some simple VBA on the combo box such as

    Me.FirstName = Me.cboBox.Column(1)

    and that seems to all work, but the problem is trying to save/modify data to tblClients. The only record in the actual table that gets modified seems to be the first record. For example, if I pull up client with ID #3 and change his address or phone number, what happens when I click to save the changes it takes client ID #3 record, overrides it with client ID #1, and then client ID #3 is the same as client ID #1 except with the old information. I'm not quite sure why this is happening. Any ideas? Keep in mind that I would say I just have an intermediate knowledge of Access and VBA coding, although I'm pretty good at understanding syntax and figuring out how to modify code to adapt to what I need. I feel like I just kind of need a push in the right direction as to what to do here.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    Let's suppose that:
    1. The form is bound to the table tblClient.
    2. [Client ID] is the Primary key columns of that table.
    3. [Client ID] is numeric.
    4. [Client ID] appears in Column(0) (leftmost) in the combo.

    1. Do not to use the values found in the columns of the combo to feed the various controls of the form. This is used when the RowSource of the combo is different than the RecordSource of the form, i.e. the form is not bound to the data source of the combo.
    2. Use the value for [Client ID] in the combo to change the current record of the fom, like this:
    Private Sub ComboClient_AfterUpdate()
        FindRecord Me.ComboClient.Column(0)
    End Sub
    Private Sub FindRecord(Byval ClientID As Long)
        Dim rst As DAO.Recordset
        Set rst = Me.RecordsetClone
        rst.FindFirst "[Client ID]=" & ClientID ' if [Client ID] is not numeric, use:
                                                ' "[Client ID]='" & Me.ComboClient.Column(0) & "'"
        If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
        Set rst = Nothing
    End Sub
    3. For adding a new client, create a command buton (Cmd_ClientAdd) and use this code:
    Private Sub Cmd_ClientAdd_Click()
        Const c_SQL As String = "INSERT INTO tblClients ( [Client ID] ) VALUES ( @I );"
        Dim lngNewID As Long ' This suppose that [Client ID] is numeric.
        lngNewID = Nz(DMax("[Client ID]", "tblClients"), 0) + 1 ' Nz() is used because DMax() will return Null when the table is empty.
        CurrentDb.Execute Replace(c_SQL, "@I", lngNewID), dbFailOnError
        FindRecord lngNewId
    End Sub
    Have a nice day!

Posting Permissions

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