access 2010 editing issue

Posted on 2014-11-12
Last Modified: 2014-11-20
Access 2010.  I have a simple form to view/edit about a dozen fields in a single table.  Records are found using a combo box to search, then it fills in the other fields.  Search is fine, it finds the record I want and I appear to be able to edit the fields I want.  But when I move to next record (in effect saving the record, I think) and I go look in the table and refresh it, I see the edited record but also the original record as well.  I end up with two records - an unedited version and an edited version.  All I want is the edited version. I gotta be doing something stupid but I can't find it.  Ideas?  thx
Question by:willcox
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 81

Expert Comment

by:David Johnson, CD, MVP
ID: 40440036
you are doing an add not an update..
LVL 26

Expert Comment

ID: 40440202
A sample is always helpful.
"Records are found using a combo box to search, then it fills in the other fields."
That description doesn't sound great.

The form should be bound to the table, and navigable.
The combo box should then be doing something like
Dim db As Database
Dim rst As DAO.Recordset
Dim strCriteria As String
Dim myinput As String
Dim TheJobID As Long

myinput = Me.MySeachComboBox.Value
If myinput = "" Then Exit Sub

set db = currentdb
strCriteria = "JobID  = " & TheJobID
Set rst = Me.RecordsetClone
rst.FindLast strCriteria
 Me.Bookmark = rst.Bookmark
End If

Set rst = Nothing

Open in new window

Nothing should be 'filling in the other fields'
But a sample is critical to knowing exactly what you have on the go

Author Comment

ID: 40440495
Attached is a pic of my form.  

The unbound combo box is what I use to ind a record - that is the only unique field.  The row source for it is all the fields in the table, as below:

SELECT [Master].[ID], [Master].[mTitle], [Master].[mAuthor], [Master].[mHPO], [Master].[mSeries], [Master].[mGenre], [Master].[mPublished], [Master].[mPurchased], [Master].[mLoc], [Master].[mSeq], [Master].[mBobRead], [Master].[mBobFlag], [Master].[mDebRead], [Master].[mDebFlag] FROM Master ORDER BY [mTitle];

The table name is 'Master'.  I use the following code in the 'on change' event for the unbound field:

Private Sub fTitle_Change()
     Me.mTitle.Value = Me.fTitle.Column(1)
     Me.mAuthor.Value = Me.fTitle.Column(2)
     Me.mHPO.Value = Me.fTitle.Column(3)
     Me.mSeries.Value = Me.fTitle.Column(4)
     Me.mSeq.Value = Me.fTitle.Column(9)
     Me.mGenre.Value = Me.fTitle.Column(5)
     Me.mPublished.Value = Me.fTitle.Column(6)
     Me.mPurchased.Value = Me.fTitle.Column(7)
     Me.mLoc.Value = Me.fTitle.Column(8)
     Me.mBobRead.Value = Me.fTitle.Column(10)
     Me.mBobFlag.Value = Me.fTitle.Column(11)
     Me.mDebRead.Value = Me.fTitle.Column(12)
     Me.mDebFlag.Value = Me.fTitle.Column(13)
End Sub

This populates all the other fields as I select the pertinent record from the combo box.  I can then edit the fields I need to, then either go to the next record or search for another.  I discovered I didn't need the 'Save' control, I don't use it.   What's happening, I think, is when I move to a new record it just adds a new one with the edited info, and leaves the original unedited one there also.  What I need it to do is just edit the record, not add a new one.

I've done some experimenting with a completely bound form, but I can't figure out how to do the record search without the bound control.  I can step through record by record, but with nearly 10,000 records that's not really practical when I need to edit just one or two particular ones

Does this help?  thx
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.


Author Comment

ID: 40440514
Forgot to attach the pic - here it is
LVL 26

Accepted Solution

Nick67 earned 500 total points
ID: 40440603
Looking at your image, I see the problem.
What you in reality are doing with your combo box is creating a new record.
All of your other controls are bound, so when your code gives them values, you are creating a new record.
I take it that the comboboxes primary field is an autonumber, right?

What you really want for code is probably
Private Sub fTitle_AfterUpdate()
Dim db As Database
Dim rst As DAO.Recordset
Dim strCriteria As String

set db = currentdb
strCriteria = "ID  = " & Me.fTitle.columns(0)
Set rst = Me.RecordsetClone
rst.FindLast strCriteria
 Me.Bookmark = rst.Bookmark
End If

Set rst = Nothing

Open in new window

This code tells Access to copy the underlying recordset, go to the end of it (since you are more likely to search for recently entered items), search for an ID that matches the one just selected in the combobox, and then move the form to the record with the matching ID.

Author Comment

ID: 40456534

Sorry for the delay.  This is a library application I've been running for years in Excel.  About once a month I have to make sure all the updates are in and generate a couple of reports.  I was determined to get it into Access to make updates, edits and report easier, thus this project.  However, I ran out of time this cycle - I had to go update it again manually and generate the reports due last Friday.  

Anyway, I looked at your code snippet and I'm not sure I understand it 100%, but I haven't dug into it.  It'll be another week or so until I can really get back to this, and I don't want to leave it hanging So I'll close this as answered for now. When I get back into it I hope I can come back to you if I need more help.  thx

Author Closing Comment

ID: 40456535
Still working on it - I'll post back later

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question