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.
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()
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
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