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
  • 4
  • 2
LVL 80

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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.


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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Open Office very very slow 3 153
MS Project 2013 - notes documentation 8 78
SQL 2016 licensing 6 61
Word: Is there a way to "hide" text? 12 36
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

856 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