Solved

access 2010 editing issue

Posted on 2014-11-12
7
138 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
  • 4
  • 2
7 Comments
 
LVL 78

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

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

Accepted Solution

by:
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.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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now