Newbie Questions

Re: Primary key being retained after new record button pushed

kminor — Tue, 21 Apr 2009 15:21:26 GMT

I figured out a solution to my problem. In case anyone else runs into a similar issue, here is what I did:

I created a query in my Access database that pulls the max value of my request ID, plus one. I added that as a secondary data source. On my submit button, I added a rule that runs a query (that secondary data source I just set up), then sets the request ID field's value to that value.

After doing that, I was STILL getting the primary key violation error on submit. I set up an autonumber on the Access table to use as the primary key, instead (just for testing), and allowed duplicate entries on my request ID field. STILL gave the PK violation. This time I knew something fishy was going on. I revisited my main data connection and modified it (went through the motions, didn't change anything, clicked submit). I noticed on the summary screen that it said submit was disabled due to a possible many-to-one relationship. I had a state abbreviation/name lookup table included in the main data connection as well. I realized I didn't need that table after all, deleted it, and all has been working fine ever since!

Long story short: I think all would've worked fine if I'd kept the request ID field as an autonumber and just removed the extra table from my main datasource. Still, I'm not a huge fan of autonumbers, and this solution is nice because I can use the request ID in the e-mail submission. If it was still an autonumber, the ID wouldn't be created until the form was submitted to the database.

Re: Primary key being retained after new record button pushed

kminor — Mon, 20 Apr 2009 19:08:08 GMT

Can anyone help with this, please? I'm really, really stuck.

Primary key being retained after new record button pushed

kminor — Tue, 14 Apr 2009 15:54:04 GMT

I have a new record button on a request form that I created. If a user queries for an existing form (let's say it has a request ID of 15), then presses the new record button, then fills out and submits the form -- the new record is submitted to my Access database with the old request ID even though the request ID field is blank. This causes a primary key violation. Is there a way around this? Thanks for your help.

ETA -- I'm trying to work around this by querying the access DB for a new request ID when the form is submitted. Is this the way to go?