Access Autonumber problem

[beadman]

Hello

I am trying to use autonumber on an invoice with the prexif "CRW", so the Invoice ID will be CRW00001, CRW00002..and so on. I have put a Format of "CRW"00000 in the table design for the field Invoice ID and the tables are succesfully creating the Invoice ID with the "CRW" prefix in an incremental Order..I have created an form and i want the user to be able to use the field Invoice ID (e.g. CRW00001) to search for records of customers etc. I have created a combo box and linked the sub forms Customer to orders.

The problem is that in the Order Form when I enter the invoice ID "CRW00001" in the combobox I get a type mismatch error.

Please help!

[NeoPa]

You tell us little about the form, but I suspect you are trying to enter into a control bound to an AutoNumber field. This is neither possible nor sensible. To help more specifically we would need more specific information.

You could also check out Example Filtering on a Form for an explanation of how to search within forms.

[beadman]

Thanks Neo for replying, My combox is not control bound, It is simply a type mismatch error. Autonumer is a long integer..I have formatted this long integer in the table design to include the prefix before the autonumber.

I have created a form from the table order which has an Order ID as a unique ID, These Orders have a man-toone realtionship with customer.

I have then created another order form(order form 1) and sub-formed the order form with the customer form into a tab page arrangement. They are linked by Order ID.

In order Form 1 i have created a combobox which returns a value with matching Order ID's. However the formats that i definded in the table are not pulling through into the forms. e.g in the table an order ID would be CRW0001 but in the form the ID is 1...When i type CRW0001 into my combobox i get a type mismatch error, i.e i am not allowed to enter Text in a numeric field.

Thanks

[ChipR]

If you've set the Format for the field, you have only changed the display. The value saved in the field is an integer, so CRW0001 won't match it, while typing in 0001 should. I would probably just use a text field for this, and skip the Format.

[NeoPa]

@beadman
This seems entirely contradictory. If the ComboBox (which is a control btw) is not bound, then there is no relation to the table or any of its attributes at all.
@beadman
In this case, as Chip has indicated, you are going about this entirely the wrong way, as well as expressing your problem quite wrongly. I expect this simply means that you're quite new to this, so no harm now we understand what's occurring a little better.

I'm afraid it does mean that you will need to consider going back a step or two and reconsidering how you'd like to approach this whole problem. We can probably help, but you'll need to be clearer about how you communicate the problem to us. I suspect the first step is to determine whether the ComboBox is actually bound to any field in your table.

[ChipR]

NeoPa, would it be correct to say that the control source of the combo box is unbound while the row source is bound to the table? Normally I would refer to this as an unbound combo box, but I realize that's not entirely accurate.

[NeoPa]

Is that what they're describing do you think? If so that would be referred to as unbound. I have trouble seeing how the error message occurs in that scenario. It's hard to conceptualise given the current explanation. Maybe the operator is trying to select an entry by typing it in. It's such a strange approach that it's outside of my experience I'm afraid. A clearer explanation would help, but that's so often true. It's hard to expect someone relatively new to the subject to put it clearly when so many that have been at it for so much longer fair no better.

I think I would still advise the same approach. A couple of steps back to consider what would actually be the best approach. I can't think the present one is likely to yield the expected results. Of course that's so much easier to see from a more experienced perspective.

[nico5038]

You should use a combobox with two columns. The source should look like:

SELECT [id], "CRW" & format([id],"0000") FROM test;

Now keep column 1 the bound column, take care that under format the "Column count" is equal 2 and the "Column width" equals "0" (suppresses the bound column).

This will allow the entering of the CRW, although I wouldn't use this as it's a default and thus something I would show just on a report and as a fixed text on a form.

I did use once a "controlled" invoicenumber (see sample at http://bytes.com/attachments/attachm...insert2000.zip) where the Year is added in front of the sequence number. You could use the same functions to fill the Invoicenumber and drop the autonumber, thus making the ID a text field, that can be used "straight away".

Nic;o)