Passing Field Name to module for clipboard copy

[Shaunk23]

In my database i have many fields where i need the user to be able to copy the text to clipboard to cut down on human error... Literally there is probably 50-60 of these fields. Currently i have them setup like this on the double click event..

If IsNull(Me.Shipper_Full_Address) = True Then
Exit Sub
Else
Me.Shipper_Full_Address.SetFocus
Me.Shipper_Full_Address.SelStart = 0
Me.Shipper_Full_Address.SelLength = Len(Me.Shipper_Full_Address)
RunCommand acCmdCopy
End If


I know that there is a better way to do this... I had thought maybe create a module with the code & pass the field name the user clicks on to the module?? Any ideas or thoughts on how to do this? Right now i have 50-60 of the above code when i think i could have just one.. thanks..

 

[jzwp22]

50-60 fields in a table sounds like you might have a normalization issue. Could you explain what type of data is in these fields?

Also, how does copying to the clipboard reduce human error relative to data in the database?

 

[Shaunk23]

No they are from various tables... They are almost all text. Its a shipping database... So we have to track many shipments each day.. We also have to fill in customs entries - over 100+ a week... It reduces the error because as long as the person entering the original data enters it correct the the rest of the process is copying it.. so for custom's entries copying things like the address & SSN or Booking number isnt being type.. Its being copied so the user isnt typing it.. meaning as long as it was entered correctly the first time it will go through the process correctly.. make sense.. Think of them as fields like address / phone number / booking number / address / full name / country etc.. So any ideas?

 

[mdlueck]

Code Builder to the rescue!

Private Sub btnGetColNames_Click()

  Dim Rst As Recordset
  Dim f As Field
  Dim strFieldList As String

  Set Rst = CurrentDb.OpenRecordset("tmptblqry_RptCapitalSpendingByProces")

  For Each f In Rst.Fields
    Debug.Print "Public " & LCase(f.Name) & " As Integer"
  Next

  Rst.Close

End Sub

Just customize the table name and what code the Debug.Print statement writes for you.

 

[jzwp22]

In relational databases, you would type the info in once and just reference a foreign key to the record that holds that information. You would not copy the field information over and over again, only the foreign key reference

A typical table might look like this:

tblShipper
-pkShipperID primary key, autonumber
-txtShipperName
-txtShipperAddress


Then if you are shipping an package you would just reference the pkShipperID in the related table

tblPackages
-pkPackageID primary key, autonumber
-txtPackageNumber
-fkShipperID foreign key to tblShipper (describes the shipper used to send the package)

Copying more than just the key field violates one of the primary rules of normalization.

 

[spikepl]

So we have to track many shipments each day

what is the relation of this info to your problem? Surely you are not doing manual tracking ? Major shippers tend to offer access to tracking info through web services, or at least status via an email containing AWB numbers.

We also have to fill in customs entries

If you are copy/pasting then to what? Some web-based entry form? If so, then it might be smarter to let Access write directly to the web page. Update: Further, I'd think that it should be possible to submit the info electronically too, probably again via some web service.