• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

Modify / Print Access Forms within VB 6 (not VB.net)

Is there a way to connect and make changes to Access reports from VB?

I have a form that is on Access and don't want to go through the pain of converting it to VB, so I figured I could connect and make changes to the Form controls of Access via Visual Basic 6...

So, first is how do I open an mdb file from Access.  Next is how do I reference and change various properties of a particular Accessform?  And lastly, how do I print it?

Thanks.

<removed email- bingie, ee pe>
0
cherocks
Asked:
cherocks
  • 6
  • 4
1 Solution
 
Bob LambersonSoftware EngineerCommented:
This is an example using access objects.

Option Explicit
'select MS Access 10 and MS ADO 26 libraries from the references list.
Dim app As Access.Application

Private Sub Form_Load()

Set app = OpenCurrentDatabase("C:\AAA testing\AccessStuff\TestMDB\firstimportCompact.mdb")
app.Forms("frmCompactRepair.frm").Controls("txt1").Top = 200         'change controls on form
app.Reports("yourReport").Controls("header1").Caption = "new caption"      'change controls on report
PrintForm app.Forms("frmCompactRepair.frm")              'print a form

End Sub

Hope this is helpful, but post any questions you have.

Bob
0
 
cherocksAuthor Commented:
Oops...  thanks about the email...

You stated to have MS Access 10 and MS ADO 26 libraries from the references list....

I have Microsoft Access 9.0 Object Library and Microsoft ADO 2.5 for DLL and Security.... and it isn't working....  Where can I find these libraries?  (I've googled 'em and have not been successful)

Dave
0
 
Bob LambersonSoftware EngineerCommented:
What error are you getting and where does it highlight in the code?

Bob
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cherocksAuthor Commented:
Compile error:

Expected function or variable

'OpenCurrentDatabase' is highlighted.

Dave
0
 
cherocksAuthor Commented:
What is the exact name of the library (as shown in the References section)?  Just want to make sure I select the right things...
0
 
Bob LambersonSoftware EngineerCommented:
Cherocks,
I have something wrong in the code there. I will work on it and post. Sorry for the screw up. Maybe someone else can see it in the meantime.

The Access library is MS Access Object Library 10.0  and the file name is MSACC.OLB - is access 2002 or xp.
The ADO library is MS Active X Data Objects 2.6 library and the file name is msado26.tlb.
Bob
0
 
cherocksAuthor Commented:
I got it to OPEN the database...  but the .Forms() command didn't work...  it says the form doesn't exist...  I typed in the name of the form "Testing" and also tried "Testing.frm" and it didn't like either reference...  In addition, the PrintForm command didn't work either...

Option Explicit
'select MS Access 10 and MS ADO 26 libraries from the references list.

Private Sub Form_Load()

Dim objAccess As Access.Application
Set objAccess = New Access.Application

'Open the database and run the macro
With objAccess
    .OpenCurrentDatabase "C:\AAA testing\AccessStuff\TestMDB\firstimportCompact.mdb"
    .Forms("frmCompactRepair.frm").Controls("txt1").Top = 200         'change controls on form
End With

'Set app = OpenCurrentDatabase("C:\AAA testing\AccessStuff\TestMDB\firstimportCompact.mdb")



End Sub

0
 
cherocksAuthor Commented:
Ok...  I figured it out...  Combining some of Bob's code and some that I found online, here's is how you edit / print a form in Access using VB...

Option Explicit
'select MS Access 10 and MS ADO 26 libraries from the references list.

Private Sub Form_Load()

Dim objAccess As Access.Application
Set objAccess = New Access.Application

'Open the database and run the macro
With objAccess
    .OpenCurrentDatabase "C:\AAA testing\AccessStuff\TestMDB\firstimportCompact.mdb"
    .DoCmd.OpenForm ("Testing")
    .Forms("Testing").Controls.Item("Label999").Caption = "Text"
    .DoCmd.PrintOut acPrintAll, 1, 1, acHigh, 1, False
    .CloseCurrentDatabase
    .Quit
End With

End Sub
0
 
Bob LambersonSoftware EngineerCommented:
cherocks,
    Sorry I abandoned you there. I got caught up in some training and just dropped the ball.
You did good figuring the problem out and should ask that the question be paq'ed and points refunded.

Bob
0
 
cherocksAuthor Commented:
Bob,

Thanks for the help though...  your code gave me a search point to find other code and I combined that with experimentation to find out the answer...

Dave
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now