Autonumber in form and subform question

[helen269]

I have a form and subform as shown below. The order number in the main form and in the subform is entered manually but of course ideally the Order Number should be an autonumber.

I've tried to change it by going through the steps to change the number field to an auto number (delete the relationships, make the change, put the relationships back). Both Order Number fields should be of the same type otherwise I can't make a relationship between them and the Order Number in the subform won't automatically be the same as in the main form.

The trouble is, being an autonumber, once I start to enter item no.2 on the subform the Order Number field goes to 2 when it should still be Order Number 1.

Is there a way of making the Order Number field an autonumber but have the subform keep that number on records higher than 1?

Thank you.

 

[vbaInet]

Helen I don't completely follow, but welcome to the forum first and foremost.

Basically, your main form's Order Number can be AutoNumber or Number and the subform's Order Number can be Number (not AutoNumber). The main form's Order Number should drive the subform's Order Number. By drive I mean that the Order Number on the subform should not be manually entered or tampered with at all, it should be automatically completed by linking the main form to the subform via the Order Number field.

If you were to use a Number data type for your main form's Order Number, you will need to use code to get it to Auto Increment. This is your best option since you already have existing records and if you were to switch to Auto Number the numbers will not be in synch with those in your subform's record source.

Does all this make sense?

 

[helen269]

Helen I don't completely follow, but welcome to the forum first and foremost.

Basically, your main form's Order Number can be AutoNumber or Number and the subform's Order Number can be Number (not AutoNumber). The main form's Order Number should drive the subform's Order Number. By drive I mean that the Order Number on the subform should not be manually entered or tampered with at all, it should be automatically completed by linking the main form to the subform via the Order Number field.

If you were to use a Number data type for your main form's Order Number, you will need to use code to get it to Auto Increment. This is your best option since you already have existing records and if you were to switch to Auto Number the numbers will not be in synch with those in your subform's record source.

Does all this make sense?

Thanks, it's good to be here!

Thank you for your reply. So you're saying that Autonumber and number can be linked on the relationship table? Can I enforced Referential Integrity? I thought two fields in a primary/foreign key pair had to be of exactly the same type.

I'm still building the databaseand only have a few test records in there so if I have to delete the lot to reset relationship I can, at least on a backup copy in case it doesn't work.

I'm a total Access newbie (hence my predicament) so unless someone tells me exactly what code to cut and past into I-don't-know-where then I can't do that.

 

[vbaInet]

AutoNumber and Number are of the same type (which is number) so they can be linked together. The names are different, one auto increments and the other doesn't but they are both number types. You can enforce referential integrity with any of the cascade options you wish.

Experiment on both AutoNumber and Number data types to get the hang of it.

The DMax() function will get the maximum number of a field in a particular table. All you do is add 1 to what is returned. Here's what it will look like:

Nz(DMax("[COLOR=Red]FieldName[/COLOR]", "[COLOR=Red]TableName[/COLOR]"), 0) + 1

Substitute FieldName and TableName for the appropriate names and put it in the Default Value property of the the textbox that is bound to the field.

The Nz() function will convert Null to an alternative value which in this case is 0. The reason for this is because when your table is empty and you run the DMax() funtion alone, it will return Null and you can't add 1 to Null, so we give an alternate value of 0 using Nz() and add 1 to it, i.e. 0 + 1 = 1.

More about these functions here:
http://www.techonthenet.com/access/functions/domain/dmax.php
http://www.techonthenet.com/access/functions/advanced/nz.php

 

[helen269]

Thanks, I'll try both methods. . What a learning curve! Phew!

 

[helen269]

Mixing the Autonumber and the Number types worked! So much for "you can't mix the data types". As you say, they are the same even if they look different. Many thanks!

 

[vbaInet]

You're welcome!