first form to open at the first unused ID #

[reservedbcreater]

when u open the first form(of 17 forms in total) i want it to open at the
first unused ID#???????????ID is a field on all 17 form pages(its a
survey)

 

[reservedbcreater]

let me better explain....i have 18 surveys that i have entered in to the
forms. each set of 17 forms now has data for id field #1-18. when i
start a new set of forms by opening the first form i want that form to
start at id #19 (the next unused id), then i already know how to code it
so all the following 16 pages of the survey will set themselves to waht
the id was in the first form.

 

[John Vinson]

when u open the first form(of 17 forms in total) i want it to open at the
first unused ID#???????????ID is a field on all 17 form pages(its a
survey)

If you set a Form's Data Entry property to Yes it will open at a new,
blank record.

I'm not certain what you mean by "first unused ID". What is the
datatype of the ID? Autonumber will automatically increment (possibly
with gaps). If you want the 17 forms to all use the same ID you'll
need some programming (which I see you're getting in another thread).

I *am* concerned about your design, however! 17 different forms? or
are they similar forms? How are your tables structured for this
survey? Have you looked at Duane Hookum's excellent At Your Survey
sample database at

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

John W. Vinson[MVP]

 

[John Vinson]

let me better explain....i have 18 surveys that i have entered in to the
forms. each set of 17 forms now has data for id field #1-18. when i
start a new set of forms by opening the first form i want that form to
start at id #19 (the next unused id), then i already know how to code it
so all the following 16 pages of the survey will set themselves to waht
the id was in the first form.

Sorry... but this design IS SIMPLY INCORRECT. You're on the wrong
track and will get into more and more trouble.

Stop. Take a breather. It really sounds like you're making the very
common mistake of starting your design with Forms. That's like
building a house starting with the windows, and then trying to figure
out how to pour the foundation!

I may be misunderstanding the situation; could you explain your table
structure, and why there are 17 forms? What are their Recordsources?
How are the tables related?

John W. Vinson[MVP]

 

[reservedbcreater]

ok le me explain......its a survey
there are 17 tables
each with a corresponding form
each table/form represents a dif section of the survey.

so each entire survey has 1 ID#.
so u fill out 17 forms in a row, ur doen the survey.....all those 17 forms
were say ID #1...when u start a new survey i want ID to automatically start
at 2(the next unused ID)

 

[John Vinson]

ok le me explain......its a survey
there are 17 tables
each with a corresponding form
each table/form represents a dif section of the survey.

so each entire survey has 1 ID#.
so u fill out 17 forms in a row, ur doen the survey.....all those 17 forms
were say ID #1...when u start a new survey i want ID to automatically start
at 2(the next unused ID)

I'm sorry, but I still think your table design IS SIMPLY INCORRECT.

A Survey with 17 sections should have *one* table, with *one* form.

That table would have a field indicating which section this question
belongs to. I'd see the following tables:

Questions
QuestionNo <integer question number>
Section <1 to 17>
Question <text>

Surveys
SurveyID <autonumber perhaps>
<who filled out the survey, when, etc.>

Answers
SurveyID <long integer link to Surveys>
QuestionNo <link to Questions>
Answer

It's pretty clear that you're storing data (section numbers) in table
names and probably also storing data (questions) in field names. This
may make for a bit less work entering data in the short run but it
will make getting the data out MUCH more difficult, and will become an
absolute nightmare if there are any changes made to the survey in the
future.

HOWEVER...

to answer your question, since you may be stuck with this *very bad*
design...

you'll need to explicitly pass the ID from the first form (where it
might be an Autonumber) to each subsequent form. This can be done in
two ways:

- Put sixteen Subforms on separate tab pages of a Tab Control on the
form for the first survey, and use the SurveyID as the master/child
link field

- or, if you really want seventeen standalone forms, "push" the ID
into the form by passing its value in the Form's OpenArgs parameter,
and using the Form's Open event to set the value:

DoCmd.OpenForm "formname", OpenArgs := Me!txtID

and then in each Form's Open event

Private Sub Form_Open(Cancel as Integer)
If Me.OpenArgs & "" <> "" Then
Me!txtID = Me.OpenArgs
End If
End Sub

John W. Vinson[MVP]

John W. Vinson[MVP]

 

[reservedbcreater]

but i have id as number not autonumber and all the relationships are made
so i cant change it

 

[John Vinson]

but i have id as number not autonumber and all the relationships are made
so i cant change it

Ok. Sorry I can't help. I'll stand by my advice that you need to
reconstruct your tables, but if you cannot do so, then you'll just
have to use the VBA code suggested elsewhere in the thread.

John W. Vinson[MVP]