Parameters on RunCommand acCmdPaste

[Leo_Polla_Psemata]

DoCmd.RunCommand acCmdPaste

Hi, could we add string function on the above line?

For example, we copy on our clipboard one large piece of info, say 100 digits but paste on the field only the Mid(clipboard, 18,5)

 

[CJ_London]

to reference the clipboard directly see this link

Retrieve information from the Clipboard

Office VBA reference topic

docs.microsoft.com

would think it simpler to use acCmdPaste to paste to a textbox (set focus to it first), then use code to interrogate the textbox for the information you require. Note, don't hide the textbox (suspect you cant paste to one that is hidden), instead set all its dimensions to 0

 

[Leo_Polla_Psemata]

to reference the clipboard directly see this link

Retrieve information from the Clipboard

Office VBA reference topic

docs.microsoft.com

would think it simpler to use acCmdPaste to paste to a textbox (set focus to it first), then use code to interrogate the textbox for the information you require. Note, don't hide the textbox (suspect you cant paste to one that is hidden), instead set all its dimensions to 0

Hi, i have tried this already, in the test database, it worked, HOWEVER, in the main DB, one field that i want to paste, is the primary key,
i can't change primary key now. So, i can't paste it in the record (at help field) because i must type the pkey first.
So i wonder IIFthere was a chance of adding sting functions somewhere and paste . In this long line that i retrieve data from, the info i select is not easy to catch and very easily you can copy less or more than desired but is is always there, mid,15,4 & mid 37,3 and so on

 

[theDBguy]

How did you select the text in the first place? Could you only select the part you just want to paste?

 

[Leo_Polla_Psemata]

How did you select the text in the first place? Could you only select the part you just want to paste?

The first info piece i can copy from our only application, it is a very large line with many piece of info in bulk but always on standard position.
Yes, i can copy paste the bits i want manually but this is a bit of a pain and I must be very careful/precise in order to collect the bits I want- no more no less..

Moreover, I select for example the mid(txt,15,4)&mid(txt, 22,2) & mid(txt,36,1) this is a bit of a pain.
Just to give some more info, this is about ships cargo manifest, we retrieve data from our main application, then we must upload
cargo manifest to the local Customs authorities/application.

From our main source, we can retrieve info, in bulk, then it is our job to assemble it to required format and the movie starts.
Sometimes, data arrives too late and leave us with even less time to meet the deadlines.

 

[theDBguy]

The first info piece i can copy from our only application, it is a very large line with many piece of info in bulk but always on standard position.
Yes, i can copy paste the bits i want manually but this is a bit of a pain and I must be very careful/precise in order to collect the bits I want- no more no less..

Moreover, I select for example the mid(txt,15,4)&mid(txt, 22,2) & mid(txt,36,1) this is a bit of a pain.
Just to give some more info, this is about ships cargo manifest, we retrieve data from our main application, then we must upload
cargo manifest to the local Customs authorities/application.

From our main source, we can retrieve info, in bulk, then it is our job to assemble it to required format and the movie starts.
Sometimes, data arrives too late and leave us with even less time to meet the deadlines.

Perhaps it would be easier to understand the issue if you could post a sample text from your application and indicate which parts you want to paste instead.

 

[Leo_Polla_Psemata]

Perhaps it would be easier to understand the issue if you could post a sample text from your application and indicate which parts you want to paste instead.

Have a look

11HIGH CARBON +TBF+ CONTSHIP ECO +190S+ 210505210506 001+BSIU2872993+N4960338 +2210+NF+FI-CY+ N000027BM 26920002000000000000 GC269200004960338

I have typed manually the +
Now, the first, i must make the voyage number which is the first bit TBF and the 190S , in this format "TBF-190 S" , then, Consthip eco is vessel name.
Then it is the date of arrival 05May 2021, Then BSIU and 7 digits is the container name, 2210 the type, FI-CY the move type and so on...
So, i was wandering IIF it is possible to copy the whole line in bulk and paste

 

[theDBguy]

Have a look

11HIGH CARBON +TBF+ CONTSHIP ECO +190S+ 210505210506 001+BSIU2872993+N4960338 +2210+NF+FI-CY+ N000027BM 26920002000000000000 GC269200004960338

I have typed manually the +
Now, the first, i must make the voyage number which is the first bit TBF and the 190S , in this format "TBF-190 S" , then, Consthip eco is vessel name.
Then it is the date of arrival 05May 2021, Then BSIU and 7 digits is the container name, 2210 the type, FI-CY the move type and so on...
So, i was wandering IIF it is possible to copy the whole line in bulk and paste

With all those rules? I don't think it would be a simple copy and paste. You will probably have to use some sort of code (custom function) to do something like that.

 

[theDBguy]

With all those rules? I don't think it would be a simple copy and paste. You will probably have to use some sort of code (custom function) to do something like that.

Let me give you an example of what I am thinking using a simpler rule. Let's say I copy the following information from an external system.

0123456789

And my simple rule is I want to paste it in reverse order, as in:

9876543210

What I might do is (using code) pass the original text to a custom function to reverse its order and then assign the result to my field. As for passing the original value to the function, you could paste it to an unbound textbox and then pass the value of the textbox to the function.

Just a thought...

 

[CJ_London]

A few years ago I had to decode something similar - a long string with data in predetermined positions and nothing to split it into smaller chunks

I created a meta table to define where each bit of data started and stopped, then used a cartesian query to get the field values (which would be vertical) using a mid function on which I could use to crosstab to provide a single line

something along these lines

tblMetaData
fldName..CStart..Cend
fName......1...........15
lName.......16.........30
age.............31........33
etc

the query

SELECT code, fname, mid(code,cstart,cend) as cValue
FROM tblCodes, tblMetaData

the crosstab

TRANSFORM First(cValue)
SELECT Code
FROM qry
GROUP BY Code
PIVOT fname

edit: to bind this to a form, you would have to fix the column headers

PIVOT fname IN (fName, lName, Age....etc)

edit#2: the values will all be text so you would need to do some formatting/ use functions like cDate, cLong etc to get the right datatypes. Do this in another query based on the crosstab

 

[Leo_Polla_Psemata]

Private Sub CoPast_DblClick(Cancel As Integer)
    Refresh
    If Not IsNull(voy) Then
    MsgBox "hey there"
    Else
    voy = "AIG-" & Mid([CoPast], 11, 3) & "-" & Mid([CoPast], 37, 3) & " " & Mid([CoPast], 40, 1)
    vess = Trim(Mid([CoPast], 17, 20))
    eta = Mid([CoPast], 46, 2) & "/" & Mid([CoPast], 44, 2) & "/" & Mid([CoPast], 42, 2)
    End If
  
End Sub

As i said, i made a test database in which i used the above code (this my first code ever) and it works just fine.
I paste the bulk line is a "station field" named CoPast (easy to crack the deeper meaning) and then the pingpong is done.
The primary key is an autonumber.

In real database, however, the primary key is the field voy, the "TBF-190 S" which in fact is "AIG-TBF-190 S",
i must add the AIG , is a code for all records, but, because i must first add the PK, the trick doesn't work.

 

[Lampje]

If a + is always used as a separator and the order is always the same, then you could possibly use the Split function.

Private Sub enjoy_DblClick(Cancel As Integer)
Dim res() As String
res = Split(enjoy, "+")
MsgBox res(1) & "-" & res(3) & "-" & res(2) & "-" & Left$(res(4), 7)
End Sub

Gr. Lampje

 

[arnelgp]

where are you getting your data, from same website you posted earlier?
i asked this because if it is on same website as previously you posted, then
it is easier to scraped the data because each "Field" is separated by Tab char (Chr(9)).

 

[Leo_Polla_Psemata]

where are you getting your data, from same website you posted earlier?
i asked this because if it is on same website as previously you posted, then
it is easier to scraped tΗιhe data because each "Field" is separated by Tab char (Chr(9)).

Hi , no it is not from website, it is from our online application that we can retrieve and upload data.
This environment is strictly used by employs.

 

[Leo_Polla_Psemata]

If a + is always used as a separator and the order is always the same, then you could possibly use the Split function.

Private Sub enjoy_DblClick(Cancel As Integer)
Dim res() As String
res = Split(enjoy, "+")
MsgBox res(1) & "-" & res(3) & "-" & res(2) & "-" & Left$(res(4), 7)
End Sub

View attachment 96096

Gr. Lampje

Hi, the + , no, i typed it manually just to show the parts i copy paste, the data are always on the same position and with string functions you can remove not needed and keep the good stuff.