?
Solved

access 2010 editing issue

Posted on 2014-11-12
7
Medium Priority
?
149 Views
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
0
Comment
Question by:willcox
[X]
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
7 Comments
 
LVL 83

Expert Comment

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

Expert Comment

by:Nick67
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.MoveLast
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
0
 

Author Comment

by:willcox
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
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

Author Comment

by:willcox
ID: 40440514
Forgot to attach the pic - here it is
form1.jpg
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 1500 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.MoveLast
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.
0
 

Author Comment

by:willcox
ID: 40456534
Nick67

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
0
 

Author Closing Comment

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

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

718 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