Problem with generating/combining item number with different ID from different Tables

[Jo..]

Hi all,

I am having a problem with generating an "item no" for my product on my database.

The item number format is 000-00000-00, where the first 2 digits define the Category id, 3rd digit defines Sub-category id, the middle 5 digits are auto number as Project id, the last 2 digits are Licence id.

I have created 3 tables for the Category, Sub-Category and Licence.
CatList:

01	CatA
02	CatB
03	CatC

SubCatList:

1	SubCatA
2	SubCatB
3	SubCatC

LicenceList:

01	LicA
02	LicB
03	LicC

On my Form I have combo list set for all Category, Sub-Category and Licence (in words). I need to generate item number when saving the record. I have only managed to show the item number in a textbox using query. But I don't know how to store the numbers into my main database. Please help!

Below is the code I used for the text box.

=DLookUp("[ItemNumber]","[ItemNumberQuery]","[ProjectID]=" & [Forms]![Records]![ProjectID])

And on my query, the item number is calculated:

ItemNumber: Format([CatList].[CatID],"00") & [SubCatID] & "-" & Format([ProjectID],"00000") & "-" & Format([LicenseID],"00")

What is the best way to calculate and store the item number on the Form?

Thank you in advance.
Best Regards,
Jo

[June7]

These comboboxes are not bound to fields - their values are not saved to table?

If you really must save the concatenated value (not sure that is necessary or good idea), use code (macro or VBA) behind form. The real trick is figuring out what event to put the code in.

Me!fieldname = Me.textboxname

Or run an UPDATE sql action.

[Jo..]

Thank you for your reply.

The comboboxes are bound to the fields but only the Words, not the IDs. I am having trouble to get the IDs from the tables.

I am also having trouble to show the item number with textbox. It used to be able to recalculate at save. So Me!fieldname = Me.textboxname is not working for me now.

I want to be able to store the item number in the format 000-00000-00. How to query the tables and store the numbers at the same time in vba?

Thank you.

[June7]

Then why are you saving the words and not the IDs? The comboboxes should have the ID fields and save those. Probably need to set up a multi-column combobox. Review: http://datapigtechnologies.com/flash...combobox3.html

[Jo..]

Then why are you saving the words and not the IDs? The comboboxes should have the ID fields and save those. Probably need to set up a multi-column combobox. Review: http://datapigtechnologies.com/flash...combobox3.html

Thanks, this works! But I do have another problem with another textbox. I needed two autonumber in the same form. How can I do this?

Another autonumber is need for calculating the barcode of the product. But the thing is, access table only allow 1 autonumber for each table. Then I created 2 tables with 2 autonumber, but on the secondary table, I am trying to fill in the first autonumber as a key to the second table to generate the second autonumber. Please, your help will be really useful.

My barcode calculation that used to be on a Query. Yes, I would like to store this barcode on the table.
EAN: "1489705518" & [EAN] & "-" & 10-Right((92+(Mid([ProjectID],1,1)*3+Mid([ProjectID],3,1)*3+Mid([ProjectID],2,1))),1)

[EAN] is the second autonumber, which [ProjectID] is the first autonumber.

Thank you so much for helping me on this.
Jo

[Jo..]

I think I have got the autonumber working!

This is what I used:

CurrentDb.Execute "INSERT INTO EAN (ProjectID) " & _
" VALUES (" & Me.ProjectID & ")", dbFailOnError

I am happy now!