Can I connect my databases like this? (1 Viewer)

[cclark9589]

I've moved my question to a new thread hoping to get a hit because I'm an impatient SOB!

Here is the link to the thread I was reading and refer to below:
http://www.access-programmers.co.uk/forums/showthread.php?t=200248


In reading this string I am inclined to think this might be the answer for what I am wanting/needing to do with a database I have.

The database is a time and billing system for tracking the billable hours we spend on payroll audits (Jobs). Approximately every quarter I have to prepare a Status report for the fund trustees. These reports list the jobs completed, date of report, period examined, amounts under/over paid on benefit types (health, pension, etc.), accounting fees, and contributions the employer paid in during the time period we examined.

Presently once a job is completed in the database I create an invoice for that job and mark it as completed. I then have to open up an Excel file for that particular trust fund and manually enter the information needed. Then when the status report is needed for the next trustee meeting I ship the spreadsheet to the data controller who has to then put certain information on the spreadsheet before sending it to secretarial who then puts the status report into the meeting agenda.

I would like to steamline some of this process. My thought is on the Jobs form I could place a button that would open a form in another database that is specifically for the status reports. Essentially each fund requires slightly different information or perhaps their fund names are slightly different so when I click this button on the Jobs form I want the data entry form in the status info database for that specific fund to open whereupon I enter the status information.

Does any of this make sense to anyone other than me?

I think this will for a couple of reasons. First, I don't want the status report information in the time and billing database because basically I see them as two different, though related, functions. Secondly and probably most importantly, if I break something and screw things up I want it to happen in the status database with minimal fixes in the time and billing, if at all because the time and billing is working pretty darn good right now.

There is a lot here in this string that I think just might suit my needs. Am I on track here or should I just go and jump into Elliott Bay and take a soak?

 

[Trevor G]

The code in the other thread will open another database so you could at least get it to open your other database based on clicking a button.

As a suggestion you could get it to open a specific form where you have a drop down that will then show you the required record and continue your work.

If you want to automate the process to export the record into Excel then depending on the exact needs here you could do this through the VBA screen, you would need to set a Reference to use Excel code so if you press Alt + F11 on the keyboard then go to the Tools Menu then select References then scroll down the list until you find Microsoft Excel a number then Object Library, place a tick in the check box, save and close then you can go to the Insert Menu and select Module then place in some code like this

Function OpenExcel()
Dim xlApp as Excel.Application
Set xlApp = createobject("Excel.Application")
with xlApp
.workbooks.Open "Path to Workbook and name.xls"
.sheets(Name).Select
.range("Cell").value=Form!Name!FieldName.Value
.range("Cell").value=Form!Name!FieldName.Value
.range("Cell").value=Form!Name!FieldName.Value
.range("Cell").value=Form!Name!FieldName.Value
.visible=True
End with
xlApp = Nothing
End Function

See if any of this will help you get going.

 

[cclark9589]

The Excel thing really won't work because I only need the status reports on a quarterly basis but I have to update the information on a nearly daily basis. As each job is completed and invoiced I need to enter certain information on the status report.

What I have in mind is to store all the status information in one table and then use forms based on queries specific to each fund to enter the information then each quarter when the status report is needed I'll export the query to an Excel file.

I will keep this code handy though because it might come in handy someday.

Thanks, Trevor.