Adding a Row--Spreadsheet Simulation

[Guest]

I am trying to get a button to work to add a new record. The glitch is that
I want certain fields to be duplicated, since from the point of view of the
user, they are adding a row between two other rows. Since opening a new
record navigates them away from the original data, I want to do two things:
1) Give them the reference data from the row above the place where
they want to insert the data.
2) I have a field (which is my primary key for the data) which is a
number meant to keep these rows in order during normal functioning of the
form which simulates a spreadsheet. My idea was to tell the button to set
default values of the fields I want duplicated to the values of the record
the person is at when they click on the "Add Item" button, set the default
value of the order number to the previous value plus .0001 and go to a new
record. This would give me lots of room for people to put records between
other records without being too likely to mess up, though it's clearly not
perfect.

My code is doing a beautiful job of duplicating most of the fields and
bringing the user to a new record. But I can't even get it to duplicate the
OrderNo, let alone change it. Whatever I do, that number is set to zero in
the new record. This used to be a primary key, but I even took that
constraint off the original table, with fear and trembling, and it still
doesn't work.

Here's my code:


Private Sub Command30_Click()

'This is to allow the new record to be something that can be edited
Dim NewOrdNo As Variant

NewOrdNo = Me.OrderNo.Value + 0.0001


Me.SubReq.DefaultValue = Chr$(34) & Me.SubReq.Value & Chr$(34)
Me.Div.DefaultValue = Chr$(34) & Me.Div.Value & Chr$(34)
Me.C.DefaultValue = Chr$(34) & Me.C.Value & Chr$(34)
Me.OrderNo.DefaultValue = Chr$(34) & NewOrdNo & Chr$(34)


DoCmd.GoToRecord , , acNewRec

End Sub


Thanks for any help you can give me.

 

[Guest]

You are thinking in Excel, not in Access.
ACCESS IS NOT A SPREADSHEET!

Putting rows between rows in a database has no real effect. How and wereh
the data are stored in a non-issue. How you present it visually, is;
however, trying to manage Access to look and act like a spreadsheet is very
inefficient.

Now, with that said, the solution I usually use when I need to carry data
over from one record to the next is to put something like this in the Click
event of the ADD button:

With Me
.txtSomeControl.Tag = .txtSomething
.chkCheckItOut.Tag = .chkCheckItOut
Docmd.GotoRecord acNewRec
.txtSomeControl = .txtSomething.Tag
.chkCheckItOut = .chkCheckItOut.Tag
End With

 

[Guest]

THANK YOU!

You are thinking in Excel, not in Access.
ACCESS IS NOT A SPREADSHEET!

Putting rows between rows in a database has no real effect. How and wereh
the data are stored in a non-issue. How you present it visually, is;
however, trying to manage Access to look and act like a spreadsheet is very
inefficient.

I respectfullly beg to differ with you.

I am thinking in Access. It is my end users who are thinking in Excel, but
who also need/want their data in Access.
If I were a full-time, fully competent data system designer, no doubt in
time I could make a system that would waft my users seamlessly from Access to
Excel and back again. I am a theology grad student, desperately trying to
leave an old job in a responsible manner so that I can concentrate full time
on such non-database matters as mastering a few foreign languages.

That said, Thank you so much.

This got around the problem of changing the default value of a linked field,
which was where my problem apparently lay.