Adding Data to a Field

[Desitech]

I want to create a command button on a form that will populate a certain field in a certain table for that record with a hyperlink address (just the text) and add another field from that record to the string. For Example:

On Click, fill the field [DRAWING HYPERLINK] in the Table "ASSEMBLY" with W:\engineering\PRODUCT ENGINEERING\ASSEMBLY\PDF\ [field data from record].pdf

field data from record entitled [ASSY DOC]

The data in that field is A-2000-1

So the DRAWING HYPERLINK field in Table ASSEMBLY would have this data in it:

W:\engineering\PRODUCT ENGINEERING\ASSEMBLY\PDF\ A-2000-1.pdf

Just by Clicking the command button on the form.

I think this would be simple, but I am having trouble. Any help is greatly appreciated.

Thanks

[JustJim]

@Desitech
Hi,
By "field data from record entitled [ASSY DOC]" do you mean that [ASSY DOC] is the name of a field in another table and that that field contains the data "A-2000-1" in the record which is showing on the form when you click the button you want to code?

If that is the case, I think you are correct that this should be fairly simple, but I'll wait for confirmation.

Jim

[Desitech]

Yes, but not in another table. The Form I am using is entitled FrmAssembly that is linked to a table entiled TblAssembly

[ASSY DOC] is the title of one of the fields in the same table

Fields in table looks like this:

DOCID ASSY DOC DESCRIPTION DATE BY DRAWINGHYPERLINK

Data in record looks like this:

DOCID =1
ASSY DOC= A-2000-1
DESCRIPTION = DH Assembly
DATE = 1/1/09
BY = DBT
DRAWING HYPERLINK = W:\engineering\PRODUCTENGINEERING\ASSEMBLY\PDF\ A-2000-1.pdf

[JustJim]

@Desitech
Even easier. Don't do it! If the text before and after the data in [ASSY DOC] is fixed, then it does not need to be stored in the table. Any time you need to use the full string you just build it up. eg.

strDrawing_Hyperlink = "W:\engineering\PRODUCTENGINEERING\ASSEMBLY\PDF\ " & Me.txtAssyDoc.Value & ".pdf"

assuming that txtAssyDoc is a text control on your form which holds tblAssembly.[ASSY DOC] for the required record.

Jim

[Desitech]

That must be close. I am not getting any error messages, but the command is not populating the DRAWING HYPERLINK field.

[Desitech]

I also need to populate the DRAWING HYPERLINK Field because I have another command button looking at the field to launch the hyperlink.

[JustJim]

@Desitech
It's not supposed to. You don't need that field. All the information is already either (1) stored in your table or (2) known and fixed.

When you need to use the fixed data and the stored data to do something (eg open the pdf file) you build the whole string by concatenating the known data with the stored data and then use that string to, again for example, open the file.

Jim

[JustJim]

@Desitech
We're a little out of synch here, but no worries!

Forget the first button and have the second button build the string I talked about before and use that string to execute the hyperlink.

Jim

[Desitech]

So are you saying with that line of code you sent, all I have to do is click on the command button and it will open that file I am looking for? If so, Then StrDrawing_Hyperlink must be wrong, because The Drawing Hyperlink fields are blank. The Code is not doing anything when I click on the command button.

[JustJim]

@Desitech
No, sorry, let's back up a little. The code in your button would be something like

Private Sub btnYourButton_Click()
Dim strHyperlink As String
strHyperlink = "W:\engineering\PRODUCTENGINEERING\ASSEMBLY\PDF\ " & Me.txtAssyDoc.Value & ".pdf"
Application.FollowHyperlink strHyperlink, , True
End Sub
 

The concept is that you don't have to store what ends up as the string strHyperlink in your table because you can build it from the data that is stored there.

Is that making sense? It's half past three in the morning here and I may well be gibbering!

Jim

[Desitech]

Yes that makes sence. But when I run the code, it doesn't like this line:

Application.FollowHyperlink strHyperlink, , True

[JustJim]

@Desitech
Add the following lines

Private Sub btnYourButton_Click()
Dim strHyperlink As String
strHyperlink = "W:\engineering\PRODUCTENGINEERING\ASSEMBLY\PDF\ " & Me.txtAssyDoc.Value & ".pdf"
 
 Debug.Print strHyperlink
Stop
 
Application.FollowHyperlink strHyperlink, , True
End Sub

Now when you click on the button, the code will stop after it has printed the value of strHyperlink to the Immediate Window (if you can't see the Immediate Window down the bottom of the Visual Basic window, either press <Ctrl>-G or click on View --> Immediate Window).

Check the value of strHyperlink as it is printed in the Immediate Window. I suspect that
A) there is an extra space in there between PDF\ and the value. I wasn't sure if you meant it to be a space, or
B) the whole string is not a "followable" hyperlink, and if you want to open a .pdf file, we have to do it another way. I'll think on that for a while.

Jim

[Desitech]

Yes...That's all it was was a space....It work...Ya Hooo!!!!!!

THANKS FOR ALL YOUR HELP !!!!

[JustJim]

@Desitech
Absolutely my pleasure. Moral of the story - don't store stuff you don't need to!

Have a good weekend

Jim

eta: I've still got it!