How can I use Certificates I scanned and assign them to truck numbers? 2

[testkitt2]

hello everyone.

I have a DB that maintains truck information...such as plate numbers, titles, inspections, so on and so forth.
These trucks have a certificate for the amount of weight
they can carry. I took all the physical cert's and scanned them. What I want to do is to add these cert's into the DB either as a "report" or a "form" (what ever is best) and assign a truck number to each cert, but the way I want it to work is by way of "stLinkCriteria" when you search for a truck on the main form you can then double click inside of the truck numbers "textbox" and link the cert with the current truck number showing. I want to use something like this to link to the cert (and bring up a popup)

stLinkCriteria = "[truckNumber]=" & "'" & Me![TruckNO] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

How do I add 41 certificates to my DB and how to assign a truck number to each cert. The cert are truck number specific, so the right truck number has to appear on the right cert.
_________________
FYI
current table, form names
mainform name "Unitplus"
table for main form name "tblUnitPlus"
truck textbox name (on mainform) name "TruckNo"

Does anyone have a suggestion...I appreciate any help at all

Thank you
JZ


Testkitt2

 

[Remou]

Not sure I get you, but can you add a field to the truck table that will show the path and file name for the cert? If each cert is named for the truck number it belongs to, the field can easily be filled, if not, it still would not take long to fill in file names. The field can then be used with FollowHyperlink in the click event, or with a command button.

 

[testkitt2]

Thanks Remou,
If I do as you suggested then I would not need a query. I was thinking "query" so that when you click on the command button it will ask "what truck"? but if the cert are trk specific then a query is may not be needed...

When you say "a path" you mean scan all the doc's and store them in a folder and then point to that folder with a hyperlink?

thanks
JZ

Testkitt2

 

[Remou]

Yes. The cert name and path would be stored in a field, then code would open the cert:

[tt]strFilename = Me.Cert '"C:\Certs\P1010108.jpg"
FollowHyperlink strFilename[/tt]

 

[testkitt2]

Thanks again, Remou
I'll give it a try

JZ

Testkitt2

 

[testkitt2]

Thanks Remou,

Your suggestion worked... but there's still a problem... How would user call up a certificate for let's say truck number 300792?

What I'm really trying to do is this:
As the users search for truck numbers on the main form they can view various details of the truck including the trucks "Certificate Number". Now let's say they click on a command button to view the actual "Certificate", I wanted the certificate to come up like a pop up and when you click on the picture of the certificate you are presented with a VBYes, VBNO, and VBCancel for the question " Do you wish to print this Certificate"?

Can I insert each certicate in a report with a textbox at the top corner that already has the trucks number and the cert being viewed actually belongs to that truck; or can I use a form to do this.
I don't think the hyperlink is the right solution to my situation.

Thank you
JZ

Testkitt2

 

[testkitt2]

Remou

Question...?
where would the code below be inserted...?

strFilename = Me.Cert '"C:\Certs\P1010108.jpg"
FollowHyperlink strFilename

Thanks
JZ

Testkitt2

 

[Zion7]

Remou, if you don't mind, I'll offer a word or 2, while I'm perusing.

JZ, In answer to your 1st question, you may consider renaming the files. Ouside of that, I can't imagine how you can determine which file has what certification number?

If you decide to rename, I would try something like this,

strFilename = "C:\Certs\" & Me.Cert & ".jpg"

FollowHyperlink strFilename

Basically, the same thing Remou said.


You could insert the code, on the dblClick event of the "hyperlink" textbox, or even from a command button, I believe.

 

[testkitt2]

Thanks Zion7
for your input.

I had already renamed all the JPG's to the number of the trucks...example 300792.jpg...303281.jpg...and so on. But
the hyperlink thing bothers me a bit.. I have something simular working in this DB..its a Ins card ..that all trucks need to carry in the glove box., This I set up as a report. I first set up a query from "tblTruckNo" with a criteria [what truck?]. The report has a textbox that when you type in the truck number the Ins Id Card prints out with the truck number appearing in the textbox. The thing is the Ins Id card is general..meaning all the trucks can use the same ID card (it not trk specific)..so all I needed was to set up a report with the ins Id card as a background picture.. With my original request I would have to make 49 reports for 49 different certificates..and still it would not solve the problem of how I would call up a specific cert for a specific truck..

Thank to all
JZ

Testkitt2

 

[Remou]

You would not have to make 49 reports, but the yes/no button is a bit more difficult. Here is a link:
How to display an image from a folder in a form or in a report in Access 2000

http://support.microsoft.com/?kbid=210100

With this, you should be able to create a form with an image of the cert and a yes/no button. The code to display the image is:

Private Sub Form_Current()
   On Error Resume Next
   If Not IsNull(Me![ImagePath]) Then
   Me![ImageFrame].OLETypeAllowed = 1
   Me![ImageFrame].SourceDoc = Me![Imagepath]
   Me![ImageFrame].Action = 0
   End If
End Sub

You can then open a report with similar code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Me![Imageframe].picture = me![Imagepath]
End Sub

There is more detail in the link.

FollowHyperlink is easier in that it will open the file in whatever is the default program for such files (*.jpg are quite likely to open in a browser, but this is not certain), and you will then have to ask the user to choose print, and to not fiddle with the image if your default program is an editor. Whether you choose one or the other depends on your users. The code would go well in an OnClick event, a Command Button might be best:

Sub ShowCert_Click()
   If Not IsNull(Me.Cert) Then
      strFilename = Me.Cert 
      FollowHyperlink strFilename
   End If
End Sub

 

[testkitt2]

Thank you Remou

With all due respect I tried the path you suggested (Microsoft ref) and it was a bit too much. Anyway how would a user print a cert for a specific truck from the main form?
If the DB is transferred to another dept would the folder with the images have to go along as well or are they embedded into the DB.
I was hoping for something like you....search for a truck number on the main form and found... double clicking inside of the trucks number textbox and up comes a pop up of the certificate.
Now I'm confused...
thanks
JZ

Testkitt2

 

[Zion7]

JZ, since your path is hardcoded, the database should always be able to find it. So moving to a different departmenmt, will not affect the image, PROVIDED, the new department, can find the path. (No Network changes, or directories changing). If not, keep the database, and your images, in the same directory, move them together. Keep it as a link.

It sounds like you have 49 images. Rather tedious..

Private Sub cboTruckNo_AfterUpdate()

Dim strCert As String, strFileName As String

SElect Case cboTruckNo
Case 23:strCert = "000258"
Case 34:strCert = "9087986"
Case 45: strCert = "..."
....
End SElect

strFilename = "C:\Certs\" & strCert & ".jpg"

Me![Imageframe].picture = strFileName
End Sub


If there is some correlation between truckNº, and certNº, then maybe a loop

Private Sub cboTruckNo_AfterUpdate()

Dim strCert As String, strFileName As String

SElect Case cboTruckNo
For x = 1 to 49
Case x:strCert = "00025" & x
Next
End SElect

strFilename = "C:\Certs\" & strCert & ".jpg"

Me![Imageframe].picture = strFileName
End Sub

...you get the idea

 

[Remou]

It is the fact that you want to include a button that says 'Do you want to print this cert' that makes it necessary to include more coding, otherwise, FollowHyperlink would work very well.
If you import the certificates, it will bloat the database, it is usually not recommended. If the certs are named for the trucks, you do not even have to provide a field with the path and cert. Let's say that your path is:
[tt]"C:\Trucks\Certs\"[/tt]
You can then say on your form:

Private Sub txtTruckID_DblClick(Cancel As Integer)
    Dim strPath, strFileName, strExtension
    
    strPath = "C:\Trucks\Certs\"
    strFileName = Me.txtTruckID '10101
    strExtension = ".jpg"
    'C:\Trucks\Certs\10101.jpg
    FollowHyperlink strPath & strFileName & strExtension
End Sub

But this will not allow you to include a

VBYes, VBNO, and VBCancel for the question " Do you wish to print this Certificate"?

Users will have to choose print from the associated program.


If you want to do this, I think you need to go the longer way around. Sorry you did not like the link, but I think you can use bits of it. I will show here the essential (I think) bits. (By the way, all this is quite similar to the Employee Form in the NorthWind sample database.) Once again, because you wish to have a 'Do you want to print' question, you have to do the extra work of creating both a form, to view the certificate, and a report, to print the certificate. You could print the form, but I do not think the image would be good.

1. Create a form called, say, Certs. Add an image control. If the image control insists on an image, load something small. Set the Size mode to zoom and the Picture Type to linked. You should now be able to delete the picture you used for set up; the Picture property will read '(none)'. Add a command button called, say, cmdPrint, with a caption Print. here is all the code for this form:

Private Sub cmdPrint_Click()
'Remove acViewPreview to go straight to print
    DoCmd.OpenReport "Certs", acViewPreview
End Sub

Private Sub Form_Current()
   Me![CertImage].Picture = Me.OpenArgs
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim strName
If Me.OpenArgs <> "" And InStr(Me.OpenArgs, ".") > 0 Then
    strName = Dir(Me.OpenArgs)
    If strName = "" Then
        MsgBox "Cannot find this Cert"
        Cancel = True
    End If
Else
    MsgBox "An error has occurred"
    Cancel = True
End If
    
End Sub

2. Add this code to the double click for truck number (I have called the truck number TruckID):

Private Sub txtTruckID_DblClick(Cancel As Integer)
    Dim strPath, strFileName, strExtension
    
    strPath = "C:\Trucks\Certs\" 'make this the appropriate path
    strFileName = Me.txtTruckID  'for example, 10101
    strExtension = ".jpg"        'the image extension
    
    'Using OpenArgs to pass the image path and name
    DoCmd.OpenForm "Certs", , , , , , strPath & strFileName & strExtension
End Sub

3. The set up for the report is nearly identical to for the Certs form. Add an image control and set the properties as you did with the form. Add this code to the Format event for detail:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Me![CertImage].Picture = Forms!Certs![CertImage].Picture
End Sub

Hopefully, this will work out as you imagined, though you will notice that there is _still_ no vbyes, vbno buttons!

 

[testkitt2]

Thanks Remou and Zion7

To make this a little easier for me....Lets forget the command button:

VBYes, VBNO, and VBCancel for the question " Do you wish to print this Certificate"?

I used the "Hyperlink address" on a command button to point to the folder with all of the scanned "jpg" certs live. This works fine for a minute. But when you click on the command button an explorer window opens showing all of the "JPGs" in the that directory. How can I bring up just the cert I need... by asking for it via "Truck number". after all the cert's are named ...300792.jpg..300792 is an actual truck number.

Thanks to all
JZ

Testkitt2

 

[Remou]

Did you get a chance to look at this bit of my post (?):

Private Sub txtTruckID_DblClick(Cancel As Integer)
    Dim strPath, strFileName, strExtension
'Name of the directory with certs in it:    
    strPath = "C:\Trucks\Certs\"
'Change txtTruckID to the name of the control 
'on your form that has the truck number:
    strFileName = Me.txtTruckID 
    strExtension = ".jpg"
'You should end up with: 
'C:\Trucks\Certs\300792.jpg
    FollowHyperlink strPath & strFileName & strExtension
End Sub

I have called the control with the truck number 'txtTruckID' for testing. You need to change this to the proper name.

 

[Zion7]

Sorry guys, I hope I'm not imposing, at this point.
Remou, all your ideas are right on.
I'm going to suggest this, just to simplify things to start.

JZ, I'm not sure if you want to print, or just view.

For viewing, i'd say put an image object, on the form.
When txtTruckNo is dblclicked, Make the imasge visible, and direct the path.

Private Sub txtTruckID_DblClick(Cancel As Integer)
Dim strPath, strFileName, strExtension

strPath = "C:\Trucks\Certs\" 'make this the appropriate path
strFileName = Me.txtTruckID 'for example, 10101
strExtension = ".jpg" 'the image extension


imgCert.Visible = True
imgCert.Picture = strPath & strFileName & strExtension
End Sub

 

[testkitt2]

Thanks to all...remou and Zion7
Remou I tried your suggestion....But just to be faced with an error msg upon launching the form. Maybe I did not set it up right.


My earlier request to have a command button to view then print "as I quoted" if you want to.... is out the this post and request at this time.

All I want to do is have the user be able to bring up the trucks cert on command.
Even If I have a folder with all the "JPG's.. and set up a hyperlink to that folder... how can the user view or print a particular cert?
All suggestions at this point referring to hyperlinks...are OK....but no one has suggested as to how to link each truck number to each cert via hyperlink...is this even possible?
I have a folder with all the "JPG's" representing the cert's for each truck. I have a hyperlink that points to the folder with the "JPG's" in it.

All of the suggestions given ...are great!
But, If you are in a trucks record and then decide you want to print that trucks cert.how can the hyperlink be set up to bring up that trucks cert by truck number?

Thanks
JZ

Testkitt2

 

[Remou]

JZ please post your code, which should show:
- the name of the form,
- the name of the control that has the truck ID, and
- the name of the folder that contains the jpgs
If it is missing any of this information, please include it, along with the name of a sample jpg.

It must be some small error, which we will be able to spot between us.

 

[testkitt2]

hello to all
This is what I have....

frmSideBar1 (name of small form located on main form like a navigation tool which has the "CmdOver" command button.

C:\Documents and Settings\Testkitt2\Desktop\OverWeights Pics
(C:.... is the directory that holds the "jpg's")

CmdOver (Command button)

OverWeights\300792.jpg (Hyperlink address)

TruckNo (Name of textbox on main form)

tblUnitPlus (name of main form)

Thanks
JZ

Testkitt2

 

[testkitt2]

Just to clarify a bit.

CmdOver is the command button that will show the cert's.

but not to confuse things I mentioned "TruckNo" this is the textbox name on the main form...which I wanted to be able to double click and the current truck number appearing in the "TruckNo" textbox will bring up that trucks cert. Or to be able to do something similiar with the command button "CmdOver"

Thanks again
JZ

Testkitt2