Adding text to variable on control

[freenet_2k@hotmail.c]

Hi All,
Two weeks ago I was able to get the help I needed to finish a project which had a deadline that was fast approaching. I'd like to start by saying thanks to all of you wonderful and helpful folks on this forum. You saved my job! Now I have a new situation/problem and once again, my deadline is fast approaching. I have some code that basically returns a custom error msg when a user tries to enter a new inventory item # that already exists in a table. The msg box instructs the user to add the characters "DUP-" in front of the item # to prevent duplicate entries on the table. That all works great, but instead of popping up an error msg, I'm trying to make the code automatically add the characters "DUP-" to the front of the number that they entered and I can't figure out how to do it or if it's even possible. Can anyone help me out or at least point me in the rifght direction? Here's the existing code. Thesection I need to modify is below "If Err.Number = 3022 Then" (obviously). Thanks again to everyone who comes here to help out morons like me!

Private Sub SbmtNewItem_Click()
'DoCmd.Maximize
 
'Go to section ERRORTRAP if error occurs
On Error GoTo ErrorTrap
 
Dim MfgAfterNewSubmit As String
 
'Create a variable to hold the manufac number
MFGCATALOGNUM.SetFocus
MfgAfterNewSubmit = MFGCATALOGNUM.Text
 
'Close this AddNewItemForm and go back to the Main form
DoCmd.Close
 
'On Main form clear out the manufac number, requery the combobox, and reset the value from the variable
Forms!Main!Combo61.Undo
Forms!Main!Combo61.Requery
Forms!Main!Combo61.Text = MfgAfterNewSubmit
 
ErrorTrap:
If Err.Number = 3021 Then
Err.Clear
End If
 
If Err.Number = 3022 Then
MsgBox "Duplicate Mfg Catalog Number detected.  Please place DUP- at the beginning of your new catalog Number.  (Example: DUP-1234)"
End If
 
 
End Sub

 

[CyberLynx]

To automatically prefix already existing data is nothing new and is relatively easy to accomplish. During the process though, you need to consider the possibility that perhaps there will be three or maybe four or more identical Manufacturer Part Numbers that will be entered into the database during its lifetime. In this situation you will need some sort of incremental prefix for your Part Numbers.

Here is your procedure again with that very mechanism included:

Private Sub SbmtNewItem_Click()
   [COLOR="DarkGreen"]'If there is nothing within the MFGCATALOGNUM Text Box
   'then why bother contiuing. Inform User:[/COLOR]
   If Nz(Me.MFGCATALOGNUM, "") = "" Then
      MsgBox "There is no Manufacturer Number supplied!", _
             vbExclamation, "Insufficient Data"
      Exit Sub
   End If
   
   [COLOR="DarkGreen"]'Go to ERRORTRAP if error occurs[/COLOR]
   On Error GoTo ErrorTrap
 
   [COLOR="DarkGreen"]'Declare a variable to hold the manufacturer number[/COLOR]
   Dim MfgAfterNewSubmit As String
   [COLOR="DarkGreen"]'Declare a Variable for incremental purposes.[/COLOR]
   Dim x As Integer
   
   [COLOR="DarkGreen"]'Fill the Variable[/COLOR]
   MfgAfterNewSubmit = Me.MFGCATALOGNUM
 
CheckAgain:
   [COLOR="DarkGreen"]'See if the Part Number is already in Table[/COLOR]
   If Nz(DLookup("[[COLOR="Red"]MFGCATALOGNUM[/COLOR]]", "[[I][COLOR="Red"]yourPartsTableName[/COLOR][/I]]", _
         "[[COLOR="Red"]MFGCATALOGNUM[/COLOR]]='" & MfgAfterNewSubmit & "'"), "") <> "" Then
      [COLOR="DarkGreen"]'Whoa...yes it is! Well then lets add DUPx- to
      'the number (x is a incremental number DUP1,
      'DUP2, etc.). We have to also make sure that
      'DUPx doesn't already exist in table as well.
      'For example, What if there were 6 parts with
      'identical Part Numbers from different manufacturers?
      '--------------------------------------------------
      'See if a DUPx prefix is already attached to the Part Number[/COLOR]
      If Left$(UCase$(MfgAfterNewSubmit), 3) = "DUP" Then
         [COLOR="DarkGreen"]'Yes it does, so lets get the DUP number and increment it[/COLOR]
         x = CInt(Mid$(MfgAfterNewSubmit, 4, InStr(MfgAfterNewSubmit, "-") - 1))
         x = x + 1
         MfgAfterNewSubmit = "DUP" & CStr(x) & Mid$(MfgAfterNewSubmit, InStr(MfgAfterNewSubmit, "-"))
      Else
         [COLOR="DarkGreen"]'No it doesn't, So lets start our first DUP Prefix (DUP1-).[/COLOR]
         MfgAfterNewSubmit = "DUP1-" & MfgAfterNewSubmit
      End If
      [COLOR="DarkGreen"]'Let's check our new part number now to see if that
      'number already exists[/COLOR]
      GoTo CheckAgain
   End If
   
  [COLOR="DarkGreen"] 'On Main form clear out the manufac number, requery the combobox,
   'and reset the value from the variable.
   'YOUR COMBO BOX MUST HAVE IT'S NOT IN LIST PROPERTY SET TO 'NO'.[/COLOR]
   Forms!Main!Combo61.Undo
   Forms!Main!Combo61.Text = MfgAfterNewSubmit
   
Exit_SbmtNewItem_Click:
   [COLOR="DarkGreen"]'Close this AddNewItemForm and go back to the Main form[/COLOR]
   DoCmd.Close acForm, "AddNewItemForm"
   Exit Sub
   
ErrorTrap:
   MsgBox Err.Number & " -- " & Err.Description
   Resume Exit_SbmtNewItem_Click
End Sub

(Ensure the items in red are correct)

Now please keep in mind that this has not been tested since I do not have your DB in front of me.

.

 

[freenet_2k@hotmail.c]

CyberLynx, thanks so much for your assistance. It worked perfectly just the way you entered it. I'm sorry it took me so long to reply and to thank you for your help. I don't want you to think I am unappreciative because I'm not. I RELLY do appreciate your help. I wish some day I could be even half as sharp as you when it comes to this db stuff. I feel so dumb sometimes because I'll look at someone elses code and I can't figure out what the heck it is supposed to do. Anyway, thanks a million my friend!

To automatically prefix already existing data is nothing new and is relatively easy to accomplish. During the process though, you need to consider the possibility that perhaps there will be three or maybe four or more identical Manufacturer Part Numbers that will be entered into the database during its lifetime. In this situation you will need some sort of incremental prefix for your Part Numbers.

Here is your procedure again with that very mechanism included:

Private Sub SbmtNewItem_Click()
   [COLOR=darkgreen]'If there is nothing within the MFGCATALOGNUM Text Box[/COLOR]
[COLOR=darkgreen]  'then why bother contiuing. Inform User:[/COLOR]
   If Nz(Me.MFGCATALOGNUM, "") = "" Then
      MsgBox "There is no Manufacturer Number supplied!", _
             vbExclamation, "Insufficient Data"
      Exit Sub
   End If
 
   [COLOR=darkgreen]'Go to ERRORTRAP if error occurs[/COLOR]
   On Error GoTo ErrorTrap
 
   [COLOR=darkgreen]'Declare a variable to hold the manufacturer number[/COLOR]
   Dim MfgAfterNewSubmit As String
   [COLOR=darkgreen]'Declare a Variable for incremental purposes.[/COLOR]
   Dim x As Integer
 
   [COLOR=darkgreen]'Fill the Variable[/COLOR]
   MfgAfterNewSubmit = Me.MFGCATALOGNUM
 
CheckAgain:
   [COLOR=darkgreen]'See if the Part Number is already in Table[/COLOR]
   If Nz(DLookup("[[COLOR=red]MFGCATALOGNUM[/COLOR]]", "[[I][COLOR=red]yourPartsTableName[/COLOR][/I]]", _
         "[[COLOR=red]MFGCATALOGNUM[/COLOR]]='" & MfgAfterNewSubmit & "'"), "") <> "" Then
      [COLOR=darkgreen]'Whoa...yes it is! Well then lets add DUPx- to[/COLOR]
[COLOR=darkgreen]     'the number (x is a incremental number DUP1,[/COLOR]
[COLOR=darkgreen]     'DUP2, etc.). We have to also make sure that[/COLOR]
[COLOR=darkgreen]     'DUPx doesn't already exist in table as well.[/COLOR]
[COLOR=darkgreen]     'For example, What if there were 6 parts with[/COLOR]
[COLOR=darkgreen]     'identical Part Numbers from different manufacturers?[/COLOR]
[COLOR=darkgreen]     '--------------------------------------------------[/COLOR]
[COLOR=darkgreen]     'See if a DUPx prefix is already attached to the Part Number[/COLOR]
      If Left$(UCase$(MfgAfterNewSubmit), 3) = "DUP" Then
         [COLOR=darkgreen]'Yes it does, so lets get the DUP number and increment it[/COLOR]
         x = CInt(Mid$(MfgAfterNewSubmit, 4, InStr(MfgAfterNewSubmit, "-") - 1))
         x = x + 1
         MfgAfterNewSubmit = "DUP" & CStr(x) & Mid$(MfgAfterNewSubmit, InStr(MfgAfterNewSubmit, "-"))
      Else
         [COLOR=darkgreen]'No it doesn't, So lets start our first DUP Prefix (DUP1-).[/COLOR]
         MfgAfterNewSubmit = "DUP1-" & MfgAfterNewSubmit
      End If
      [COLOR=darkgreen]'Let's check our new part number now to see if that[/COLOR]
[COLOR=darkgreen]     'number already exists[/COLOR]
      GoTo CheckAgain
   End If
 
  [COLOR=darkgreen] 'On Main form clear out the manufac number, requery the combobox,[/COLOR]
[COLOR=darkgreen]  'and reset the value from the variable.[/COLOR]
[COLOR=darkgreen]  'YOUR COMBO BOX MUST HAVE IT'S NOT IN LIST PROPERTY SET TO 'NO'.[/COLOR]
   Forms!Main!Combo61.Undo
   Forms!Main!Combo61.Text = MfgAfterNewSubmit
 
Exit_SbmtNewItem_Click:
   [COLOR=darkgreen]'Close this AddNewItemForm and go back to the Main form[/COLOR]
   DoCmd.Close acForm, "AddNewItemForm"
   Exit Sub
 
ErrorTrap:
   MsgBox Err.Number & " -- " & Err.Description
   Resume Exit_SbmtNewItem_Click
End Sub

(Ensure the items in red are correct)

Now please keep in mind that this has not been tested since I do not have your DB in front of me.

.

 

[freenet_2k@hotmail.c]

BTW... I understand that I need to be aware that I may need to make the added text sequential in the future to compensate for multiple duplicate and triplicate numbers, but actually it's extremely rare that they come across even two duplicate numbers, so I think this will solve the problem permanently. Thanks again!

 

[CyberLynx]

BTW... I understand that I need to be aware that I may need to make the added text sequential in the future to compensate for multiple duplicate and triplicate numbers, but actually it's extremely rare that they come across even two duplicate numbers, so I think this will solve the problem permanently. Thanks again!

Glad it worked for you and always keep in mind:

It takes only one rare occasion to accidentally blow up a Nuclear Reactor.

I wonder of often they said the word "rare" while building the Space Shuttle.

.