Solved Inserting concatenated string from an unbound textbox into a field

Sam Summers

Sam Summers

Registered User.
Local time
Today, 00:47
Joined
Sep 17, 2001
Messages
939
Hi again,

I have tried literally hundreds of ways and searched but just cannot get this to work?

I have an unbound textbox "PO_Number" with this as its control source =[Prefix] & [OrderedByInitial] & [POYear] & [PO]

This is to produce a unique PO Number that looks like this PO-T02110036

[Prefix] = PO-
[OrderedByInitial] = T (but this is dynamic depending on who the person ordering it is)
[POYear] = 021
[PO] = 10036 (which is an autonumber)

I am trying to insert the entire number (PO-T02110036) into the current records PONumber field which i also have on the form to see that it is working but so far no luck.

Many thanks in advance if you can point me in the right direction
 
for Old records, use the Current event of the Form:

Private Sub Form_Current()
With Me
If Not .NewRecord Then
If !thePOTextbox & "" <> [PO_Number] Then
!thePOTextbox = [PO_Number]
.Dirty = False
End If
End If
End Sub
 
I'm not sure why you want to store something that can easily be created out of the other fields, but

Something like this (pseudocode) in the on_current event?

Code: 
If IsNull (Me.YourPONumberControl)  Then
    If
    'check all the other values required are avialable
     ' If not exit
     Else
        Me.YourPONumberControl = [Prefix] & [OrderedByInitial] & [POYear] & [PO]
     End if
End if

But if any values change your concatenated value will be rubbish.
 
Thank you guys.

I got it working with Arnels code.
Very pleased.
Thank you
 
mr.sam, if you are interested, you can get the Next Autonumber (before it is created).
if the other fields are readily available:

[Prefix] = PO-
[OrderedByInitial] = T (but this is dynamic depending on who the person ordering it is)
[POYear] = 021

you can generate the "New PO" of the New record on the BeforeInsert event of the form:

Code: 
private sub form_beforeInsert(cancel as integer)
Me!thePOTextbox = [Prefix] & [OrderedByInitial] & [POYear] & NextAutonumber("yourTableName","PO")
end sub

put in a Module:
Code: 
' https://stackoverflow.com/questions/6498221/retrieve-next-autonumber-for-access-table
Public Function NextAutonumber(ByVal pTable As String, _
        ByVal pAutonumField As String) As Long

    Dim cat As Object
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = CurrentProject.Connection
    NextAutonumber = cat.Tables(pTable).Columns(pAutonumField).Properties("Seed")
    Set cat = Nothing
End Function
 
arnelgp said:
mr.sam, if you are interested, you can get the Next Autonumber (before it is created).
if the other fields are readily available:

[Prefix] = PO-
[OrderedByInitial] = T (but this is dynamic depending on who the person ordering it is)
[POYear] = 021

you can generate the "New PO" of the New record on the BeforeInsert event of the form:

Code: 
private sub form_beforeInsert(cancel as integer)
Me!thePOTextbox = [Prefix] & [OrderedByInitial] & [POYear] & NextAutonumber("yourTableName","PO")
end sub

put in a Module:
Code: 
' https://stackoverflow.com/questions/6498221/retrieve-next-autonumber-for-access-table
Public Function NextAutonumber(ByVal pTable As String, _
        ByVal pAutonumField As String) As Long

    Dim cat As Object
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = CurrentProject.Connection
    NextAutonumber = cat.Tables(pTable).Columns(pAutonumField).Properties("Seed")
    Set cat = Nothing
End Function
Click to expand...
Hi Arnel

That could work, thank you.

What i did was use this code:

Code: 
Private Sub OrderedBy_AfterUpdate()

If Me.OrderedBy = "1" Then
Me.OrderedByInitial = "AC"

ElseIf Me.OrderedBy = "2" Then
Me.OrderedByInitial = "M"

ElseIf Me.OrderedBy = "3" Then
Me.OrderedByInitial = "T"

End If

With Me

If Not .NewRecord Then

If !PONumber & "" <> [PO_Number] Then

!PONumber = [PO_Number]

.Dirty = False

End If

End If
End With

End Sub

When the person ordering an item selects there name it automatically creates the unique PO with their initials within it.
"PO" is actually the primary key in the table which i set initially to match our company's current numbering system with an append query from a table with the first number in it.
 
Last edited:
that will work also.
what i did before is have the PO generated (before a record is created) and show it
in the form (with a label saying "Provisional PO Number").
when the actual PO is generated, i change the label caption to "PO Number".

therefore the user will not wonder if he/she will manually enter the PO number.
 
arnelgp said:
that will work also.
what i did before is have the PO generated (before a record is created) and show it
in the form (with a label saying "Provisional PO Number").
when the actual PO is generated, i change the label caption to "PO Number".

therefore the user will not wonder if he/she will manually enter the PO number.
Click to expand...
Yes there is that possibility so that may be better.
Thank you once again!
I'm slowly learning.....
 
Sam, assuming your user is a combo you could simply add a hidden column to the Combobox with their initials.
Then reference that in your code and you don't need to amend your code if you add user 4 or 99 ;)

Me.OrderedByInitial = Me.OrderedBy.Column(2)

Taking it a stage further, if you stored their initials in the base user table you wouldn't need to store this at all you could simply display it by joining to the user table...
Less is more sometimes
 
My understanding was that Autonumbers should be used for nothing more than primary keys. As they can not be relied upon to produce consecutive number. Autonumbers can skip a number sometimes based on failed or retracted update attempts.

I'm pretty sure that a quick search on the subject will produce any number of threads wherein it is recommended to steer clear of using Autonumber for anything other than producing a unique value.
 
Perhaps this sample does what you are looking for

Get next number for primary field

Attached db includes a module with function to assign next number in tables for fields where you do not want you miss the number in between like invoice numbers or voucher numbers for you application When you use autonumber it does not issue same number again if you cancelled it once whether...
www.access-programmers.co.uk
 
LanaR said:
Perhaps this sample does what you are looking for

Get next number for primary field

Attached db includes a module with function to assign next number in tables for fields where you do not want you miss the number in between like invoice numbers or voucher numbers for you application When you use autonumber it does not issue same number again if you cancelled it once whether...
www.access-programmers.co.uk
Click to expand...
Thank you for your help.
Tried that but its beyond my current abilities but i might be able to get it working in the future
 
This Sample DB might also do what you are looking for. Have a look at the form's On Current event and the Combo's After Update event
 

Attachments

Now nothing works - i've messed it all up.
Back to the drawing board.
 
If anyone is out there - after it tried a couple of things yesterday the code i used from Arnel and event i used it in which previously worked, now doesn't !
So nothing is any different (that i can see) but i am back to square one with "PONumber" textbox and table field blank?
 
So what is your current code and what event(s) is it based on.

I will re-iterate that you don't need to store this as you can calculate it and display it at any time?
You could add the calculation to the forms underlying query and display it in a control bound to the calculation - you'd never need to do anything to keep it "Live"
 
Minty said:
So what is your current code and what event(s) is it based on.

I will re-iterate that you don't need to store this as you can calculate it and display it at any time?
You could add the calculation to the forms underlying query and display it in a control bound to the calculation - you'd never need to do anything to keep it "Live"
Click to expand...
Hi Minty, thank you. I'm having a bad day. I will try but not sure if can do it?
 
Minty said:
So what is your current code and what event(s) is it based on.

I will re-iterate that you don't need to store this as you can calculate it and display it at any time?
You could add the calculation to the forms underlying query and display it in a control bound to the calculation - you'd never need to do anything to keep it "Live"
Click to expand...
I am using this currently which worked perfectly yesterday but after trying some of the other suggestions here it now doesn't work at all? Completely baffled?

Code: 
Private Sub OrderedBy_AfterUpdate()

If Me.OrderedBy = "1" Then
Me.OrderedByInitial = "AC"

ElseIf Me.OrderedBy = "2" Then
Me.OrderedByInitial = "M"

ElseIf Me.OrderedBy = "3" Then
Me.OrderedByInitial = "T"

End If

With Me

If Not .NewRecord Then

If !PONumber & "" <> [PO_Number] Then

!PONumber = [PO_Number]

.Dirty = False

End If

End If
End With

End Sub
 
You must log in or register to reply here.

Similar threads

M
working with unbound field in a form
Replies
8
Views
501
CJ_London
C
M
Unbound field in form to display summed field from a query
Replies
3
Views
428
Pat Hartman
P
D
Solved Auto Adjust unbound sub-form depending on which form is loaded into it
Replies
4
Views
487
donkey9972
D
Bobcat42
Solved Problem filling a textbox (format RichText) with Richtext
Replies
3
Views
242
Bobcat42
Bobcat42
B
Solved Remove certain parts of a concatenated textbox
Replies
8
Views
561
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom