Input Mask (1 Viewer)

[Tina Brev]

Help anybody!

I have 2 forms: frmSource (data came from 5 tables and consist of textboxes, comboboxes, listboxes, etc.) and frmDestination.

I would like to add another field in the Destination table called AssemblyPartnumber. The system requires that we generate a unique partnumber

Example: A37xxxx-45xxxx-1xxxx

Where:
A=Assembly (required)
37=ID number (first autonumber assigned by Access)
45=ID number (2nd autonumber assigned by Access)
1= ID number (3rd autonumber assigned by Access)
xxxx= last four digit for each partnumber

Using a Input Mask, how can I force a letter A at the beginning of the partnumber (as shown in my example) then concatenate combinations of 3 controls from the frmDestination but only using the last 4 or 5 digits of each unique partnumbers?

Note1: No relationship in my tables.
Note2: These referenced partnumbers are columns from listboxes and are not visible on the frmSource.

Thank you in advance for suggestions or comments

 

[Chris RR]

Hi,
Well, what I think I would do is maybe have the user key in the bits they know, then hit some sort of "Create New Assbly" button.

Behind that button, I'd run a function that builds the new assembly number. Functions are pretty easy to test & you'd be sure that the assembly numbers were coming out correctly.

Before I'd show it back to the user or write it to the file, I'd do a Dlookup on the destination table to make sure that it doesn't already exist.

Are the three autonumber fields to be assigned based on the first two characters in each part of the assembly number? Or would they all be the same:
A370017-451000-10047 vs A371234-451234-11234
You might want to set up three tables, one for each group, to keep track of the last number used?

I see from your other post that you're a Vikings fan, :-( but good luck with this anyway.

 

[Tina Brev]

Hello Chris,

Once again thank you for your reply.

To answer your questions:

On my frmSource, I have 3 listboxes where RowSource are coming from 3 different tables. Each of these listboxes included ID # and Partnumbers columns although I have the width set to 0 (zeros).

On the frmDestination I added another textbox
where: txtAssyPartnumber = [CuttingTool.ID] & [CuttingTool.Partnumber] & "-" & [Adapter.ID] & [Adapter.Partnumber] & "-" & [Insert.ID] & [Insert.Partnumber]

Notes:
1. I need to put a letter A at the beginning of the AssyPartnumber.
2. How can I force to display only the last 4 or 5 digits of these individual piecepart?

Examples of actual piecepart partnumber:
Cutting Tool:02-18-01285
Adapter:03-05-03281-3
Insert:01-04-0044

The partnumbering scheme is in order for the system to be able to identify which combinations of tools are being used in case of rejects etc.

I hope I made this a little less confusing?

Regards,

[This message has been edited by Tina Brev (edited 01-11-2001).]

[This message has been edited by Tina Brev (edited 01-13-2001).]

[This message has been edited by Tina Brev (edited 01-14-2001).]

 

[ntp]

If you only want to display tle last 4 or 5 digits you could create another textbox control on your form. This one is unbound though. The one you have "txtAssyPartNumber" is bound to some field I assume. Create another one, say, txtAssyPartNumberVis" which is for display purposes only. So you would have something like, txtAssyPartnumberVis = [Insert.ID] & [Insert.Partnumber], which I believe gives you the last 4 or 5 digits of the assembly part number. Your txtAssyPartNumber will have visible property set to false. So your assigning a value to that still. Your table gets the Assembly part number, but your user does not see that. They only see a textbox showing 4 or 5 digits as the case may be.

As for the 'A'
txtAssyPartnumber = "A" & [CuttingTool.ID] & [CuttingTool.Partnumber] & "-" & [Adapter.ID] & [Adapter.Partnumber] & "-" & [Insert.ID] & [Insert.Partnumber]

Is that what you mean?

Did you just delete this question from some where else? I was answering then when I tried to post it was gone.

ntp

 

[Tina Brev]

Hello and thank you for replying to my question.

Yes that's exactly what I meant. And also Yes that I tried to repost then
decided to delete it because I thought that Chris RR would reply back to me.

I didn't realize that you were in the process of replying to my question. I
apologize and thank you! for not giving up on me.
I do have a question though, and hopefully you will answer? ( if it
will not take too much of your time).

I thought that in order to force to display the last four or five digit from
the right it goes something like this?

Right([txtPartnumber], 4)

Please note on the Adapter partnumber where there is an extra -3 how do I
handle that? Can I use the IIf function where if condition is true, then do
this, otherwise do that? Don't laugh for I'm still not at all good with
this. ; )

Adapter:03-05-03281-3


[This message has been edited by Tina Brev (edited 01-15-2001).]

 

[ntp]

Your adapter part number is of a format 00-00-00000-0. Try this,

Mid([txtAdapterNumber],7,4)

This will return from the adapter number, 03-05-03281-3, four characters from the seventh character, 0328

ntp

 

[Tina Brev]

Hello!!

I thought you deserted me. Thank you! Please make sure and check back I will get back to you if I run into another problem

Thanks