Retrieve Autonumber before insert

[pandapark]

Hi

I have a form with unbound fields which, assuming validation is met, are inserted into the live table.
my first problem was one of the fields, saref, is a concatenation of the directorate field and the autonumber field. I got round this by using a GetUserLogon script, which captures the users network logon and inserts that into a table. Then i can do a select max where the current user is equal to the GetUserLogon name ie. this is unique
My other problem is i have a sub-form which is part of the validation. At least one entry needs to be inserted into this sub-form but to achieve the validation I use a recordset which uses the autonumber field as the link (which I don't know until after the validation is checked)
any ideas?
hope I make sense !

regards
tony

 

[batteam]

What you could do is do a DMax on your autonumber field before record entry. This will give you the last one created in your table. Once you have that number captured, simply add one (1) to it, and this resulting sum will be your next autonumber.

Hope this helps.

 

[pandapark]

Hi

I've used DMax before. the problem is I'm in a multi-user environment and up to 30 people will use this Database at any one time. DMax doesn't always work when two people add a record at the same time

thanks anyway

 

[LittleSmudge]

If you have data in your main table that is validated by data in your subtable then either :-

A) You have calculated fields floating around that shouldn't be there

or

B) You need a serious rethink about Normalisation of your schema.


I'd take a LOT of convincing that the schema you have is anything like complying with Codd rules ( Normal forms ) etc.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[pandapark]

Hi

what I've got is a main form which does the validation. there is one sub-form on the main form which has data in a different table - i.e. a many (sub-form records)-to-one(main form record) link - part of the validation, simply using a recordset, checks that at least one record is in the sub-table using the Ref field as the link, which I won't have as they are unbound fields.
Do you think I'm doing this wrong ?

what i thought of doing was when the user adds a record I use the SetMainFormAccess Function to tell me the user is adding - have a hidden text box called counter which is set to zero. Whenever the user adds to the sub-form it adds one to the counter and if they delete from the sub-form it deletes one from the counter - on the click of Commit to Database set run the validation checking counter is greater than zero?

I must admit I'm aware of normalisation but none of us here really get the time to think it through which is a shame and means we end up fire-fighting more than we'd like


regards

 

[LittleSmudge]

Uggg - that last statement DILLS me with horror.


But anyway, my approach would be

Allow users to open main form and create a new record

Force main form to save prior to user adding records in sub form - thus you get your PK

Then simply prevent users from leaving ( closing ) the main form unless at least one record exists in subform
You can do that check 'on the fly' at the time the user attempts to 'close the form' rather than when they 'save the record'. You then don't have any need to keep some shaky count number going throughout the process.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.