Autonumber problems

[jds]

OK here it is as plain as I can put it without confusing myself. I am trying to get to forms to link together and use the same run of Autonumber between the 2 of them. (i.e. open first form and # would be 1...when I open the second form the number should start at 2) I have muddled my way through most of this without getting too much help, but this has got me stumped.

Also on the same note, is it possible to have a "Set button" for the autonumber. What I mean is can I have a button that won't allow the number to appear until I choose it? If not thats OK.

Any help would be greatly appreciated.

Thanx
JDS

 

[raindrop3]

Hello,

(I am not sure if I understand your problem)

If the forms are based on the same table, it should automatically generate a next number. If you open form1 and it shows a 1, then the 1 is stored in the table. If you open form 2 then autonumber generates a 2.

If they are not based on the same table. (Only form 1 is based on the table with autonumber kolom) then you should write some code to find the highest number of the table and generates a nummer one higher. ('On open' event of the second form.)

For further help: I keep an eye on this post.

Greetings,

Albert

 

[jds]

Thanx for your timely reply

Yes it is 2 seperate tables. Now not to sound like a pest, but could you give me some example coding that I should enter for an onopen expression? You could post here or email me at fireman_gcfd@yahoo.com

Thanx again

JDS

 

[raindrop3]

Hello,

I post the anwer here, so someone can correct me if I'am sending you in the wrong direction.

Your demands: If form 1 has a record with number 1 then you don't want form 2 to have a record with number 1, but with number 2. And so on.(For clearity)

The first table must be autonumbering. The second not! The second must be numeric. (The number kolom)

After the 'On open' propertie of the second form, type the following:

Dim db as dabatase
dim rc as recordset

set db = currentdb()
set rc = db.openrecordset("SELECT number from [tblnaam] ORDER BY number")'tblname is the name of the autonumber table. Use the brackets. number is the name of the colom.

rc.movelast 'Gives an error if there ar no records! Moves to the last and highest record.

textbox_on_second_form.text = rc("number") + 1 'number in the rc("number") is the name of the autonumber kolom in the first table. ('Autonumber table')

'I am not sure of the 'textbox_on_second_form.text. If access complains about the foces try using:

- textbox_on_second_form
OR
- 'textbox_on_second_form.setfocus
textbox_on_second_form.text = .......

NOTE: the datatype of the second form (table) should not be autonumber!

If you need more help, just post.

Greetings,

Albert

 

[Pat Hartman]

What are you trying to do with the autonumber of the second table? What would you want its value to start at if the the autonumber on the first form were 2? Do you have a 1-to-many relationship betwee table1 and table2? If you do, the correct way to establish this relationship is to store the "key" of table1 in a column in table2. The primary key of table2 may also be an autonumber but it is independent of the autonumber in table1. The key field from table1 that you stored in table2 is referred to as a foreign key and will be used in any join operation to link the rows of table2 to their parent rows in table1.

In the button click event of the form where you are opening the second form, you need to save the main form record BEFORE opening the second form -

DoCmd.RunCommand acCmdSaveRecord

As to your second question, Once you start editing the record, Access assigns the autonumber. The only way to get around that would be to use an unbound form which is a lot more work. If you are worried about missing/unused autonumbers forget it! They are not a problem. Autonumbers are meaningless. They are simply a way of uniquely identifying a single row in a table. If it disturbs you to see gaps, change the autonumber property so that the value assigned is random rather than sequential.

 

[jds]

Thanx for all your replies

I guess I should have mentioned what these forms were for. I am building a Fire Dispatch report form. The reason I have 2 seperate forms is because we have 2 seperate Fire Stations that we need to track by Incident number. I will try all the suggetions that have been given to me and I will let you know how I make out with them.

Thnx
JDS