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 82

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


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

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

626 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