Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 6th, 2004, 12:18 PM
Authorized User
 
Join Date: Sep 2004
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default A Access Form issue

Hi all,

I have a form in Access which has 3 combo boxes representing the entities accountname, date and report type respectively. The value in these comboboxes is coming from 3 separate tables which are 2 column tables: one for say account name and one for the primary key.
Now according to the chosen combination in the form what happens is that on pressing a command button 'pdf display' a pdf file opens up which is named as 'accountname' + 'reporttype' + 'date'. For eg:
If the user choses Microsoft and Report1 and 10/04/2004 then the report which opens up is named microsoftreport110/04/2004.pdf.

Now i was trying to get this functionality in VBA code (given below). What happens but is that instead of the value stored in cboaccountname, which is the accountname the user has chosen in the combo box, the text cboaccountname itself gets transferred. I tried giving it as &cboaccountname& but of no use. The same is the case with report type and date.
Any ideas !

Private Sub cmddri_Click()

Dim pdflink As String

If IsNull(Cboaccountnamei) Or Cboaccountnamei = "" Then
        MsgBox "Select Account Name"
        Cboaccountnamei.SetFocus
    ElseIf IsNull(cboreporttypei) Or cboreporttypei = "" Then
        MsgBox "Select Report Type"
        Cboaccountnamei.SetFocus
    ElseIf IsNull(cbodatesi) Or cbodatesi = "" Then
        MsgBox "Select Date"
        Cboaccountnamei.SetFocus

    Else

pdflink = "\\lvamfs01\shared\axysreportscreation\" + Cboaccountname.Value + cboreporttype.value + cbodate.value + ".pdf "

Debug.Print pdflink

FollowHyperlink pdflink, , True

End If

End Sub

Cheers
Goels

 
Old October 6th, 2004, 03:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

First, use & to concatenate strings, not +

See if that works.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old October 7th, 2004, 04:14 AM
Authorized User
 
Join Date: Sep 2004
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Greg,

I used & instead of + but the result is the same i.e instead of the value within cboacountname say Microsoft getting passed, it's position in the table i.e say if it is record no.2, 2 gets passed. I also tried using cboaccountname.Text but i get the error message like 'Can't set a Property unless control has the focus'

Chow
Goels




 
Old October 7th, 2004, 06:01 AM
Authorized User
 
Join Date: Sep 2004
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi all,

I have solved the issue of cboaccountname.Value not showing the requisite value. What happens is that by defult it takes the value in column 0 which is the primary key value. So we have to give the statement as :

…..+ cboaccountname.Column(1)+……

Now it will pass the account name which the user has selected.

The issue that remains is when now I click on the ‘display report’ button an MS Office pop-up box opens which says ‘Are you sure you want to open the file. It might have virus etc..so on’. Now if I click the ‘OK’ button on this pop-up box everything is fine and the report opens. But if I click on the ‘Cancel’ button I get an error saying:

Run-time error ‘16388’

The hyperlink cannot be followed to the destination.

What I am not able to get is where does this pop-up box come from when I have not included it in my code. Also if it is an embedded property of ‘Follow.Hyperlink ‘ how can I then stop the error box opening using VBA when I click on ‘Cancel’ button on the MS Office pop-up box.

Cheers
Goels


 
Old October 7th, 2004, 06:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by goels
 The issue that remains is when now I click on the ‘display report’ button an MS Office pop-up box opens which says ‘Are you sure you want to open the file. It might have virus etc..so on’. Now if I click the ‘OK’ button on this pop-up box everything is fine and the report opens. But if I click on the ‘Cancel’ button I get an error saying:

Run-time error ‘16388’

The hyperlink cannot be followed to the destination.
What you can do is do some error trapping. Change your event procedure like this:

Public Sub YourProcedureName()

On Error GoTo YourProcedureName_Err

{The rest of your procedure goes here}

YourProcedureNameExit:
   Exit Sub

YourProcedureNameErr:
   If Err.Number = 16388 Then
      'The error is OK. Do Nothing.
   Else
      MsgBox Err.Description, vbExclamation, "Error " & Err.Number
   End If
   Resume YourProcedureNameExit
End Sub

Change "YourProcedureName" to match the real name.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old October 7th, 2004, 06:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Oops... I wasn't consistent. Get rid of the underscore in this line

On Error GoTo YourProcedureName_Err


Is now

On Error GoTo YourProcedureNameErr


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old October 7th, 2004, 06:58 AM
Authorized User
 
Join Date: Sep 2004
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Greg i just did that and it's working perfectly fine.

Chow
Goels






Similar Threads
Thread Thread Starter Forum Replies Last Post
Issue Opening Form in Access 2007 Laurel Access 0 July 20th, 2007 03:59 PM
Issue with refresh of data in form bradbritton VB Databases Basics 1 February 24th, 2006 12:32 AM
SQL Access/ASP.NET data access issue saeta57 SQL Server ASP 1 July 4th, 2004 04:29 PM
SQL Access/ASP.NET data access issue saeta57 Classic ASP Databases 1 July 4th, 2004 03:32 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.