Automatically Removing Certain Numbers in Text Box (1 Viewer)

[unluckytoe]

Hello All:

I have an issue that I have been searching for but I have yet to find an answer, or maybe it doesn't exsist. So here it goes:

In a text field on a form I'm using a bar code scanner to scan a FedEx label, the output shows a lot more than just the tracking number. The tracking number (in red), is the only thing that I want to record in that field. Is there any coding that would remove the non-red numbers when scanned in. Here's the example:

39458388310000017901804524392014

Thank you for any help that you can provide.

Brian

 

[Scooterbug]

I'm assuming that all the tracking numbers are the same number of digits. You can use the following code:

dim sMyNumber as text
 
sMyNumber = mid(me.TrackingNumber,17,12)

This will tell access to go the 17th number and grab the next 12.

 

[unluckytoe]

Scooterbug:

Thank you so very much, I just have one other question. I am just learning how to use Access 2007, where would I place this code?

Brian

 

[Scooterbug]

Well, you should ask yourself, "Will I ever need to use the whole Tracking Number?" The reason is, you can store the whole tracking number, and format a text box to just display the number you want.

In the case of not wanting to store the whole tracking number:

I would put the code on the Before Update of the text box that the number is being brought into. Go into the properties of the form, then the properties of the control. There should be a tab that say "Events". Click on that, and click in the field labeled "Before Update". You will see a little box with 3 dots appear on the right hand side. Click that and select Code Builder.

You will now see the VBA Editor. The event will already be generated for you. It will look something like this:

Private Sub TextBoxName_BeforeUpdate(Cancel as Integer)
 
End Sub

You will put the code between the Private Sub and End Sub. I would also throw in a check to make sure that the number in the field is an actual tracking number. So it would look like this:

Private Sub TextBoxName_BeforeUpdate(Cancel as Integer)
 
dim sMyNumber as string
 
If len(me.TextBoxName) <> 12 then
   sMyNumber = mid(me.TrackingNumber,17,12)
End If
 
End Sub

The If statement checks to see if the length of the data in the text box is not equal to 7. If that is true, it pulls the number out. Remember to substitue TextBoxName for the actual name of your text box.

Now, if you want to keep the actual tracking number, you could simply format the data to show the 12 digit number. You could use create a field in the query underlining the form like this:

MyNumber: mid([YourNumberFieldNameHere,17,12)

 

[unluckytoe]

I must be doing something wrong, I created a dummy DB for testing, and I can't seem to get it. I scan the number 3 times, and all 3 times is shows all numbers. I made sure to change the names where you told me. I attached the DB, and if you have time, would you take a look at it and let me know what I'm doing wrong? Thank you so much.

Brian

 

[Scooterbug]

I dont have A2007. Can you save the database as a MDB file? Then I can take a look at it.

 

[unluckytoe]

Here you go, again, thank you for all your help.

 

[Scooterbug]

Here ya go. I was a bit off on the code...

Added a new field to store the Number. I also added a field to show you that you can simply format a field to display the number without actually storing it.

 

[unluckytoe]

Scooterbug:

You are the best...thank you so much. I just have one more thing, and I promise I wont ask you anything more again.

In addition to FedEx, we also use UPS, now when I scan a UPS bar code (UPS bar codes are all the same length) the last two numbers show up in the "Modified Number" field. Is there a way to hide those last two digits without effecting the FedEx portion. See record 7 for my example.

And again, thank you so very much!

Brian

 

[Scooterbug]

It's because Access doesn't know it's a UPS barcode. There needs to be some criteria to test to determine where the barcode comes from. Is there anything in the FedEx barcode that will always be constant? Same question for UPS?

Worse case, you can use an Options button to determine. I'm leaving here is a min...so if I dont get back to ya, I will tomorrow.

 

[Scooterbug]

Is there something that is always present in a UPS barcode? And what number are you attempting to pull out of the UPS tracking Number?

 

[unluckytoe]

Scooterbug:

Again thank you for all your help. Besides the actual lengths of the tracking numbers, there is nothing that is always present between the two numbers. I have scanned the two examples here. After I changed my DB with your supplied code, the numbers in red are showing in the "modified number" field.

39458388310000017901904524412014 FedEx

1ZY625630243001165 UPS

If there is a way to just remove the "65" when a UPS tracking number is scanned that would be awesome. If not, that's cool.

One more question, how did you learn about coding for Access? What would be the best way for a person getting started in this to learn?

Brian

 

[Scooterbug]

Replace the code with the following:

Dim sMyNumber As String
 
If Len(Me.TrackingNumber) <> 12 Then
   sMyNumber = Mid(Me.TrackingNumber, 17, 12)
    If Len(sMyNumber) < 12 Then
        sMyNumber = ""
    End If
   Me.ModifiedNumber = sMyNumber
Else

End If

As for learning how to code in Access....practice and trial and error. There forums here are a good site, and the help file in the VBA is also a good source to learn syntax and such.

 

[unluckytoe]

Scooterbug:

That works perfectly. Again thank you for all your help. I have another task I'm trying to learn about, eventully I think I'll get it.

Thanks...

Brian

 

[Scooterbug]

Happy to help!

 

[unluckytoe]

Scooterbug:

Again, I can't thank you enough for all your help. I'm learning more and more each day, but this little problem has me stumpped. Here's what I'm trying to do.

I have two tables, Devices and Projects. They both have forms with the same names. I have a combo box (Project Name) in the Devices form that pulls up and shows select information on the Projects table that is used for reference. Now here's my problem, everytime I save a record based on the combo box selection, I would like to have the count placed in a box called "Number of Units Completed".

I have created a dummy DB that will show you what my DB looks like and what I have been struggling with.

I hope that you can help me, but if not, I completely understand.

Thank you,

Brian

 

[Scooterbug]

A simple Dcount will do the trick for you. I am assuming you are looking for a total number of devices that are found in the table Devices.

Put a text box on the form. Set the control source to:

=nz(DCount("*","devices"),0)

that will count up the number of records in the table Devices. You can requery the box when you click your Save button. Just add: me.TextBoxName.Requery at the end of the code under that button.

 

[unluckytoe]

Scooterbug:

Hey that's a cool little trick. However, the value doesn't change when I select a different project name. In case you missed it, I attached a DB to my last post. Hopefully this will help.

Brian

 

[Scooterbug]

You can add criteria to the dcount() function. Put this in for the control source:

=nz(DCount("*","devices","[ProjectID]=" & [Forms]![devices]![combo22]),0)

Also, you will want to get rid of the Table Level Lookup in your Device table. Here is a link as to why you shouldn't use them at table level.
http://www.mvps.org/access/lookupfields.htm

I've attached the db with those corrections for ya.

 

[unluckytoe]

Scooterbug:

You are truly awesome, thank you for all your help. I did exactly what you told me to do in regards to my table level lookup fields and removed them all.

I have just one more request if you will.

I have two tables: Projects and Inventory

Both tables have a single bound form: Project Details and Inventory Details

When I place text in Text Box "Project Name" in the Projects Details form, I need that field to copy, paste, and save information to Text Box "Project Inventory" in the Inventory Details form. This information needs to be saved in both tables.

I use Project Details (Projects) to collect information about a project via the Data Collection emails. Inventory Details (Inventory), is where I record and continually track the inventory for that project. Having the name of the project copied and pasted automatically from the two text fields would be great.

I would send you my DB but it wont let me convert it down so you can see it as I'm sure this would probably make more sense.

I have been reading a lot here and I see that many people are doing it but I don't understand their methods yet.

Could you help?

Brian