Code Help

[jcaptchaos2]

I am a novice when it comes to Access so need some help, I had created a tracking system in excel but have been task to change it to access, I have a work order that I am scanning and in excel I am using this code

Private Sub txtitemno_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    txtshop.Text = Mid(txtitemno.Text, 6, 5)
    txtopno.Text = Mid(txtitemno.Text, 11)
    abort = True
    txtitemno.Text = Mid(txtitemno.Text, 1, 5)
End Sub

I have set up a form in access and want to do the same thing, scan a bar code and it goes into 3 different text boxes. Is there code like above that would do that for me and if so could you give me an idea of what it would be and where I would place it?

 

[Norie]

Is the barcode going to be stored in a table?

 

[jcaptchaos2]

No just the number that is scanned, so for example the number is going to always be 12 or 13 numbers long and the first 5 numbers would go into the first text box the second 5 numbers would go into the second text box and the remaining numbers would go into the last text box either 2 or 3 numbers. So if my number was 1234598745663 the results would be:
Text box 1 = 12345
Text box 2 = 98745
Text Box 3 = 663
on the form.

 

[revans]

I don't see why your code won't work exactly as you have it, with one change. MSForms.ReturnBoolean should be Boolean.

hth,

Rich

 

[jcaptchaos2]

Where do I put in in access

 

[jcaptchaos2]

Ok more info as I can not get the code to work and it might be because my text boxes in access are names different. When I change it everything turns red. Here is what I changed it to,

Private Sub Item Number_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    Shop Order Number.Text = Mid(Item Number.Text, 6, 5)
    Op Number.Text = Mid(Item Number.Text, 11)
    abort = True
    Item Number.Text = Mid(Item Number.Text, 1, 5)
End Sub

but this does not work, really need help with this

 

[jcaptchaos2]

Changed it to

Private Sub Item_Number_BeforeUpdate(Cancel As Boolean)
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    Shop Order_Number = Mid(Item_Number.Text, 6, 5)
    Op_Number.Text = Mid(Item_Number.Text, 11)
    abort = True
    Item_Number.Text = Mid(Item_Number.Text, 1, 5)
End Sub

so no more red errors but don't work

 

[revans]

Changed it to

Private Sub Item_Number_BeforeUpdate(Cancel As Boolean)
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    [Shop Order_Number] = Mid(Item_Number.Text, 6, 5)
    Op_Number = Mid(Item_Number.Text, 11)
    abort = True
    Item_Number = Mid(Item_Number.Text, 1, 5)
End Sub

so no more red errors but don't work

I changed one text box name to be surrounded by brackets. While Access allows you to name your controls almost anything you want, names with spaces cause problems and need to be handled with brackets. Sometimes even that doesn't work depending on the name.

It turns out I gave you bad info last time. Cancel should be Integer, not Boolean. Sorry. Even more than that, I'd suggest changing this to an After Update event procedure. I tried implementing your code on a sample form and found that as part of this code you are changing the data in Item_Number, which is where the data comes from. Access tries to save this data but finds it's been changed and has a problem. I'd suggest the After Update event because Access will save the original data then after the "update" it will change it happily.

Here's what I came up with.

Private Sub Item_Number_AfterUpdate()
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    [Shop Order_Number] = Mid(Item_Number.Text, 6, 5)
    Op_Number = Mid(Item_Number.Text, 11)
    abort = True
    Item_Number = Mid(Item_Number.Text, 1, 5)
End Sub

hth,

Rich

 

[jcaptchaos2]

Rich, thanks for your help, I did place that code in the after event property sheet for the Item Number text box, when I scanned the barcode it still put the whole number in the item number box. Any other ideas?

I get an error also, " The value you entered isn't valid for this field."


also this part of the code comes up in yellow when the debugger comes up.

[Shop Order_Number] = Mid(Item_Number.Text, 6, 5)

Private Sub Item_Number_AfterUpdate()
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    [Shop Order_Number] = Mid(Item_Number.Text, 6, 5)
    Op_Number = Mid(Item_Number.Text, 11)
    abort = True
    Item_Number = Mid(Item_Number.Text, 1, 5)
End Sub

 

[jcaptchaos2]

Ok this code here is working thank you so much for your help, I changed the type to "Double" in the table properties and no error also.

Private Sub Item_Number_AfterUpdate()
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    Shop_Order_Number = Mid(Item_Number, 6, 5)
    Op_Number = Mid(Item_Number, 11)
    abort = True
    Item_Number = Mid(Item_Number, 1, 5)
End Sub
[/code