By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Insert into question?

P: n/a
Ron
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
Sep 4 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.