Edit Current Record Only

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a DB that contains 18 forms. The user populates one form at a time
and clicks a continue button at the bottom to move to the next form. I have
a query running when they click the next button that pulls the Branch # and
Date and populates the next Form. All that seems to works fine, except that
it gives the user the option to add and edit other records beside the one
they are adding. With the way the mouse wheel scrolls through records, I can
see the wrong records being edited.

I tried setting the Data Entry to YES, but this bypasses the last record
that has the Branch # and Date from the query that ran and only shows a blank
form. I am using the acLast command to jump to the last record on each form.

Is there a way to go to the last record and lock out all pervious records
and the option to add records?

Thanks.
 
Tim

If you want a form to only display a single record for editing, you have to
tell Access which record. If you tell Access the form can see ALL the
records in the table, then you will be able to see (and edit) ALL the
records.

If you use a query to "fill" the form, you can specify in the query that it
only returns the record you wish to see/edit. You do this by using the
Selection Criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff, thanks for your quick reply.

Basicly, what you are saying is since I built the forms from the tables and
not from update queries, I am out of luck?
 
Tim

If you can't change the "source" for your form, then yes, you are... but you
can EASILY change the source!

Create the query on the table. For testing purposes, use a single row's ID
as a selection criterion in the query.

Open the form in design view. Open the form's properties and find the
source -- change it from the table to the query.

Now open the form -- you should only see the one record that the query had
as the selection criterion (for rowID).

To make this work, add an unbound combo box (the header is a good location)
to the form in design view. Make the source of the rows in that combo box a
query that returns rowID, and something the users will be able to recognize
and pick, then make the first column (rowID) have a column width of zero(0).

In the combo box's AfterUpdate event, requery the form with something like:

Me.Requery

The net effect is that when you open the form, nothing's in the combo box,
so the query that feeds the form has nothing in the criterion, and returns
nothing. When you pick an item (row) in the combo box, the AfterUpdate
event tells the form to re-query itself, now finding the one row/record that
had been picked.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Just a little bit of instruction here because of this that you wrote:

"I am using the acLast command to jump to the last record on each form."

You probably didn't realize this, but unless you use a query to set a sort
order, Access does not necessarily display the records in any particular
order. If you have a primary key field, the initial view should be sorted by
that primary key, but the order can change. So, you'd better make sure that
acLast will ALWAYS take you to the last record. I would highly suggest using
queries instead of the table within your forms.

It is easy to transition.

1. Open your form in design view.

2. Go find the Record Source in the properties.

3. Click on the elipsis (...) that appears when you put your mouse over the
area next to the table name in the Record Source Property.

4. It will ask you if you wish to create a query based on the table.

5. Say YES.

6. Drag and drop the fields into the grid.

7. set the sort order for the field in the query that you want to use to
make sure that the order is the way you want it.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista
 
As far as the mousewheel whizzing thru your records, willy nilly, a gentleman
by the name of Stephen Lebans has a sample database that addresses this
problem and can be downloaded at:

http://www.lebans.com/mousewheelonoff.htm

First, download and unzip the db and take a look. Go into your db and goto
File > External Data > Import and import the module modMouseHook from the
sample database.

Next make sure you have the included file, MouseHook.dll, in the same folder
your database resides in.

And finally, in the first form to load in your db use this code:

Private Sub Form_Load()
' Turn off Mouse Scroll
blRet = MouseWheelOFF
End Sub

The mousewheel will no longer scroll while you're in Access.

BTW, ACC2007 has a similar function built in.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
Thanks to everyone for their help!

But, I must be missing something. I would think this should be easier than
this.

Changing the Form to update from a Query rather than a Table is easy enough,
but it still gives the user the option or appility to view other records
beside the one they are adding to the database. If I use a Group By query to
only show the last KeyId, Branch # and Date added, they can not continue
updating the Form, due to a Group By Query not being updatable.

Maybe I didn't make my problem clear. The user starts ona blank FormA and
enters about 10 fields of data. Two of which are the Branch # and Date.
Once they have completed FormA, they click the Continue buttom at the bottom
to jump to FormB. They need to enter anywhere from 20 to 120 fields of data
on FormB. And so on and so forth for 16 more Forms that build 18 different
tables. I had to be able to link all the Tables together, so I am running a
query that passes the KeyId, Branch # and Date from the previous table to the
next Table to link all 18 tables together.

I would think I "Should" be able to run a Select Query and add a Where
statement to select the Last or Max KeyId (the KeyId is a autonumber field
from the first table). I would guess it would be something like the
following, but I keep getting errors. I am only showing upto section B2.
The actually query goes to B18.

SELECT [tlbB-ReceivingandWarehousing].Key,
[tlbB-ReceivingandWarehousing].[Br #], [tlbB-ReceivingandWarehousing].Date,
[tlbB-ReceivingandWarehousing].[B1 Stat], [tlbB-ReceivingandWarehousing].[B1
Comments], [tlbB-ReceivingandWarehousing].[B1 Responsibility],
[tlbB-ReceivingandWarehousing].[B1 Completion Date],
[tlbB-ReceivingandWarehousing].[B2 Stat], [tlbB-ReceivingandWarehousing].[B2
Comments], [tlbB-ReceivingandWarehousing].[B2 Responsibility],
[tlbB-ReceivingandWarehousing].[B2 Completion Date]
FROM [tlbB-ReceivingandWarehousing];
WHERE [tlbB-ReceivingandWarehousing].Key = (SELECT
LAST([tlbB-ReceivingandWarehousing].Key));
 
Back
Top