Code based off combo box selection to dictate another controls info.

[nmart1230]

I have a combobox that has different record types. I would like to have code run based off of selection to change data in one of the controls on the same form. So say someone selects OMPF in the carton # control it adds a 1 in front of the number or if someone selects FOIA in the carton # control it adds a 0 in front of the number. Here is the code I was trying to work with.

Private Sub BOX_NUMBER_AFTERUPDATE()
If Me.RECORD_TYPE = OMPF Then
Me.CARTON__ = "1" + Right([BOX NUMBER], 3)
Else
Me.CARTON__ = "1" + Right([BOX NUMBER], 3)
End If
Me.TEAM_NUMBER = Left([BOX NUMBER], 1)
End Sub

Thanks in advance!

[June7]

Put OMPF within quote marks: "OMPF"

Use & characters as concatenation operator, not +.

Why are you concatenating "1" in both - should one of those be "0"?

[nmart1230]

Okay, I tried your suggestions and still not working. Also, about the "1" and one of them should be "0". Well, really depending on the combo selection the carton # should be what I am concatenating or nothing at all. So, if the chosen option is "OMPF" then all the carton #'s have to begin with "1" and nothing else. If the chosen option is "STR" then all the carton #'s should begin with a "0" and so forth. I just didn't know how else to write it in code. I also moved the code to RECORD_TYPE_AFTERUPDATE because the user enters the box number before they make the combobox selection. So, I figured that was a better spot for the code that way if they make an incorrect selection and then change it the CARTON # should change accordingly. I did that and it still doesn't work.

[June7]

Seems the second "1" should be "0".

What does 'not working' mean - error message, wrong results, nothing?

Are there other options than "OMPF" and "STR"?

I do wonder about the __ at the end of Me.CARTON__.

If you want to provide db for analysis, follow instructions at bottom of my post. Identify the objects involved.

[nmart1230]

Sorry, not working means nothing is happening. Is their are four options in the dropdown. They are "CLAIM", "STR", "OMPF", & "FOIA". Well the control is named "CARTON #" but if I under the event tab on the property sheet and choose an event to put code in that's what Access calls it automatically. I will provide db tomorrow as it is almost the end of the workday for me.

[June7]

Should not use spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be CartonNum or Carton_Num for field and tbxCarton for textbox, otherwise must use [].

Try:

Me.[CARTON #]

Have you run Debug > Compile on the code?

[nmart1230]

Alright, I figured it out. The dropdown was going off the ID of the table and not the actual field name. So, the code looks like this.

Private Sub RECORD_TYPE_AfterUpdate()
If Me.RECORD_TYPE = "3" Then
Me.CARTNUM = "1" + Right([BOX NUMBER], 3)
End If
If Me.RECORD_TYPE = "1" Then
Me.CARTNUM = "0" + Right([BOX NUMBER], 3)
End If
If Me.RECORD_TYPE = "2" Then
Me.CARTNUM = "0" + Right([BOX NUMBER], 3)
End If
If Me.RECORD_TYPE = "4" Then
Me.CARTNUM = "1" + Right([BOX NUMBER], 3)
End If
End Sub

[nmart1230]

Oh, I also changed the name to CARTNUM per your suggestion and it works beautifully. Thanks for your help!

[June7]

Ooops, my first question should have been about lookups.

Are you using lookups with alias in table? Advise not to.

Record_Type is a text type field?

[nmart1230]

Yeah, I thought about shortly after we started messaging. There is a lookup on that table. Yet, another thing I have to fix from this wonderful inherited db.

[June7]

Can leave it as long as you understand what you are dealing with. But might be considerate of your successor if it is eliminated so they don't suffer as you have.

[nmart1230]

Oh, I will most definitely correct it just so my successor won't have to go through the headache. I have one last question on this. Is there code to have automatically switch to the next consecutive number once it reaches the highest it can go before having to switch. i.e. once it reaches 999 it know to put a 1 for 1000 then when it reaches 1999 it changes to 2000 and so on? Or will someone have to go into the code every time to change it manually when it reaches each end of the number range?

[June7]

I expect code will have to be modified. What is BOX NUMBER? Is it alpha/numeric? Why are you pulling the right 3 characters?

Should build a string identifier to always have the same number of characters. If you think the sequence will reach into more than 3 digits then should build for that using placeholder zeros. Otherwise sort won't work properly.

In my db, I have an identifier structured like: YYYYA-XXXX

Example: 2014A-0015

The XXXX part starts over every year. It's never exceeded 4500. Example code in https://www.accessforums.net/program...ple-46469.html

An alternative is to put the 1/0 prefix in one field and the sequence in another number field. Then do calc to concatenate when needed: [Prefix] & Format([Sequence], "00000")