Hi All,
I've got a table called tblTransactions. With a couple different forms, I
allow users to add/update records in that table. Within the table there's a
PK called transactionID. Following suggestions from long ago, I don't show
the user that number, but still needed to show something (for reporting
purposes) and so have another control called transnum that I do show the
user. That number is figured by =dmax("tblTransaction", "transnum") + 1.
Error trapped so that if the user tries to add a transaction at same time as
another, it increments that max number, and so forth. As long as it's on a
form, where I can control the errors, it's works fine.
Now I want to do something else. I've got another form that adds payments
from a company in batches. I keep these payments separate until some
requirements are met, then post them as a "batch" into the tblTransaction
table. Everything works fine, except I have no idea how to increment that
transnum field within a batch process like this. If I do the Insert Into
statement without that control, it works fine (but can't be used because the
transnum control is null). If I do it with the control (as above) it only
inserts the 1st record, and the remaining ones aren't done because of a "key
violation" because of course this transnum field needs to be unique, and for
all rows after the 1st one, isn't.
How can I increment a control's value (and error trap it so that another
user, also inputing into the tblTransaction table can still use it too)
within an Insert Into statement so I can copy rows of data from one file to
another?
Or, am I up the proverbial creek without a paddle?
TIA,
ron
