Solved Inserting concatenated string from an unbound textbox into a field (1 Viewer)

[Sam Summers]

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

 

[arnelgp]

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

 

[Minty]

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?

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.

 

[Sam Summers]

Thank you guys.

I got it working with Arnels code.
Very pleased.
Thank you

 

[arnelgp]

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:

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

put in a Module:

' 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

 

[Sam Summers]

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:

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

put in a Module:

' 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

Hi Arnel

That could work, thank you.

What i did was use this 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.

 

[arnelgp]

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.

 

[Sam Summers]

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.

Yes there is that possibility so that may be better.
Thank you once again!
I'm slowly learning.....

 

[Minty]

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

 

[LanaR]

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.

 

[LanaR]

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

 

[Sam Summers]

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

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

 

[LanaR]

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

 

[Sam Summers]

Now nothing works - i've messed it all up.
Back to the drawing board.

 

[Sam Summers]

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?

 

[Minty]

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"

 

[Sam Summers]

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"

Hi Minty, thank you. I'm having a bad day. I will try but not sure if can do it?

 

[Sam Summers]

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"

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?

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

 

[arnelgp]

what happened?
see this simple demo.

 

[Sam Summers]

what happened?
see this simple demo.

Hi Arnel,
Don't know why it all failed but i will use your demo and sort it out.
Once again thank you and thank everyone for helping me!