Code to update a single record

[ETID]

I am trying to build a survey DB, and I need an identifier number, for each person that paticipates in the survey,


I want to, (on the loading of a form) check the value that is in a single record table, enter that into a variable, then change the single record in that table to increase by 1

then i can build a "blind" survey user ID from the variable that is extracted.


I need code to pull the value from the table, pass it to a variable, increase the table value by 1 and overwrite the existing number in the table.

sound doable?

 

[aexley]

Hi.

There are two ways that I know of that I thnk will achieve what you are after.

1) Use an Autonumber field as a primary key. This will increment for each new record. All you have to do to on your form is set the RecordSource property for the particular control to whatever the name of your Autonumber field is. Autonumber is fine but I've encountered problems when working with a multiuser DB regarding Autonumber 'skipping' values although this shouldn't be a problem if there is only you using the DB at any one time.

2) This piece of code which was supplied by a helpful member of TekTips:

private sub Form_BeforeInsert(cancel as integer)
if dcount("MyField","MyTable&quot=0 then
me.MyField=1
else
me.MyField=dmax("MyField","MyTable&quot+1
endif
End Sub

Again all you would need to do is reference the field ("MyField&quot that you are using as the identifier number for your participants.

Hope this helps.

aexley

Quote for the day:"Being apologised to by a machine (when on hold, etc) isn't a courtesy, it's an insult. It's not as if the machine really cares is it?" - Me