Replies

Hi, Princess. I have had a lot of luck using a macro to do everything it appears you need to do.

I create a macro that uses the TransferSpreadsheet Action and the RunApp Action. In the list of Action Arguements section of the build macro dialog box, there are six boxes that you need to fill in. The only box that is not clear on what needs to be entered is the File Name box. This is an example of what I put in this box: s:IGCNTracking(DataEntry)NewStatus.xls. This tells the TransferSpreadsheet the Excel file to transfer to.

In the RunApp action, there is a Command Line box. This one is a little tricky also. You have to have the path of the Excel executable program and the file name (the same as the File Name in the TransferSpreadsheet Action). This is an example of this box’s contents: c:Program FilesMicrosoft OfficeOfficeexcel.exe s:IGCNTracking(DataEntry)NewStatus.xls. Be sure there is a space between excel.exe and s:.

I think if you try this method, you will like it much better than trying to do this with VBA.

Woof! Woof!

Edited: I went home and looked under Tools. Guess what? My version of Outlook 98 does not have a Services choice. Is there any other way to make the .pst folder smaller? Maybe a third party product? Thanks.

Chris, I am also not that experienced with VBA, but if I look at some code from examples I have seen, the Set rs that you have written isn’t the same. For example, here is the way I have seen it done.

sSQL = “Select distinctrow * From tbl_User_Setup Where UserName = ‘” & _
Me!txtUserName & “‘ and Password = ‘” & Me!txtPassword & “‘;”

Set db = CurrentDb
Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)

Although I don’t know why it wouldn’t work, you have an SQL query querying your query [test-alert]. Now the only problem you may have is that you placed brackets around test-alert. Try running it without the brackets. If that doesn’t work, try the above example.

Also, in the Do Loop, I don’t think you need the rs.MoveNext, but I am not sure. Have fun.

Thanks, Bill. I was able to do what I needed by using your example.

Thanks for getting back. I don’t have a favorite for Google. I type the URL in the address box.

Hi to you both. I had this “problem” yesterday in an unbound report field. I was calculating a percentage from two fields and when one of the fields was empty, I would get a blank value. What I did was add an IIf (immediate If) to the field in the report and it worked fine. For example, =IIf([field]=Null, “0”,[field]).

A few years ago, we needed a time tracking program for 12 staff. I used the same method as Ken suggested. On each employee’s spreadsheet, I placed a sum function in the first row (A1-L1), entered a title for each column in the second row (A2-L2) and set the sum range for the columns (starting a A3-A8000 to L3-L8000). Then, I freeze the first two rows (Windows menu|Freeze Panes). (The macro Ken has will work, but, if the employees always save their spreadsheet where they last entered data, the spreadsheet will open to that location. But, it would probably be better to have the macro.)

In the supervisor’s spreadsheet, I listed all of the employees in column A and for each employee linked to their totals row (A1-L1) to the supervisor’s column B-M. So, every time the supervisor opened his speadsheet, his information was automatically updated. It worked real well.

Then, I created pie charts for each employee that showed how they spent their time. The pie charts were updated automatically when the supervisor’s spreadsheet opened.

Of course, all of the spreadsheets were located on a shared drive.

Rupert, it didn’t work. This time Visual Basic for Applications was checked as Missing (Missing: Visual Basic for Applications), but Visual Basic for Applications was also checked as there. In other word, they both had check marks. And, as soon as I unchecked the Missing box, the program worked.

Brian, that did it. The Microsoft DAO 3.51 Object Library was missing.

Question: It was missing in Rupert’s database but it is in my other databases. Anyone know why?

Rupert,

Thanks. I opened the code and References is there. Whew! But, it still doesn’t work.

This is the line of code that doesn’t work:

fieldTime = Format(Now, “HH:MM”)

It doesn’t recognize “Format”.

I looked for a reference to Format in the Object Browser and could find a specific one. Do you know which Reference I may have missing?

I don’t know where to look for references. I have Access 97 and when I look under the Tools menu, References are not listed. I may have a bad install of Office 97. I don’t know. Do you have any idea why it isn’t under Tools?

Hi, Rupert. I tried it on Access 97. It didn’t recognize the “Format” word in the second form.

Problem solved. I placed the code on the Got Focus event of the strPICLastName control and it worked. Thanks again.

I have the code in the On Open event. The two name fields are on the form as well as the email field. It may be that the On Open event is not the place for the code. I will try it another way. Still, if you guys have any suggestions, please send them and thanks.

Thanks Mark and Paul,

There isn’t a function on the control, that I can tell.

The control is on a form. What I am trying to do is to have the email address for our staff to be written to a field on a form which would then be added to the control source table. We have a standard email address system (probably like most places). Instead of having to write each one, this code, when it works, will do it automatically.

HTH you two and me.