Replies

Steve–

Thanks for your reply. Since I find the Access chart-making feature rather limiting, I really need to keep Excel in the loop. So…

I am trying to figure out the code necessary for updating the links in Excel and saving the file. I have created a reference in Word to the Excel object, and added the the following code to the Word’s Open procedure:

Private Sub Document_Open()

Dim ES_MCRTOT As Object
Set ES_MCRTOT = GetObject(“es_mcrtot.xls”)
With ES_MCRTOT
.Sheets(“Data_Totals”).Select
.Range(“A2”).Select
.Selection.QueryTable.Refresh
.Sheets(“Totals”).Select
.ActiveWorkbook.Save
End With

End Sub

The idea is to have Word automagically open the Excel file, refresh the data and save the file. When I open the Word file that contains this Open procedure, the embedded charts update their link, but there isn’t any indication that the procedure fires. I have tried to add a breakpoint to the procedure, but it doesn’t seem to save so that when I save the procedure, close Word and reopen the document, my breakpoint is no longer there.

So…Does anybody know what I’m doing wrong–and once I get the procedure to fire, does anyone see anything wrong with my code?

Steve–

Thanks for your reply. Since I find the Access chart-making feature rather limiting, I really need to keep Excel in the loop. So…

I am trying to figure out the code necessary for updating the links in Excel and saving the file. I have created a reference in Word to the Excel object, and added the the following code to the Word’s Open procedure:

Private Sub Document_Open()

Dim ES_MCRTOT As Object
Set ES_MCRTOT = GetObject(“es_mcrtot.xls”)
With ES_MCRTOT
.Sheets(“Data_Totals”).Select
.Range(“A2”).Select
.Selection.QueryTable.Refresh
.Sheets(“Totals”).Select
.ActiveWorkbook.Save
End With

End Sub

The idea is to have Word automagically open the Excel file, refresh the data and save the file. When I open the Word file that contains this Open procedure, the embedded charts update their link, but there isn’t any indication that the procedure fires. I have tried to add a breakpoint to the procedure, but it doesn’t seem to save so that when I save the procedure, close Word and reopen the document, my breakpoint is no longer there.

So…Does anybody know what I’m doing wrong–and once I get the procedure to fire, does anyone see anything wrong with my code?

Graham–

Thanks for the information. I’ll keep your Make Table solution handy. I am interested in your “care needs to be taken when opening tables” comment. Is this because Access has a hard time handling the number of records (which I’m sure is true–one of the files in this database has over 8M records), OR are you saying this causes a performance degradation in the AS/400. Does querying the AS/400 cause any performance issues? My marching orders are that my solution should have little or preferrably no decernable performance degradation on the AS/400.

Randy

Graham–

Thanks for the information. I’ll keep your Make Table solution handy. I am interested in your “care needs to be taken when opening tables” comment. Is this because Access has a hard time handling the number of records (which I’m sure is true–one of the files in this database has over 8M records), OR are you saying this causes a performance degradation in the AS/400. Does querying the AS/400 cause any performance issues? My marching orders are that my solution should have little or preferrably no decernable performance degradation on the AS/400.

Randy

Thanks for you help.

Thanks for you help.

Thanks to hans and Wendell for helping me figure this out. I manually added in the apostrophe in Excel prior to importing it to Access, and everything worked fine. Now I get to go back and find out what was different in that field in the first place.

Hans–

Yes, it does seem strange. The data starts out as an Excel file. I import it into Access then back out again as a text file so that I can get the fields encapsulated by quote marks. (If you know of an easier way to do this, I’m all ears.) There really is no database, but I attached the one that included the table imported from the Excel file, the original Excel file and the text file that was exported.

Here is my process.

1. Import TestDemos.xls to Access using File|Get External Data…|Import…
2. In the Import Wizard, check No Primary Field. Import the header row to make troubleshooting easier. Accept all other defaults.
3. Once the data has been imported, export the newly created table by right clicking the table and click Export…
4. Change Save As Type to Text Files and click Export
5. On the first screen of the Export Wizard, note that the SSN field has quotemarks surrounding the field, whether blank or populated. Notice also that other fields are blank and have quotemarks surrounding the field. Everything looks as it should here.
6. On the second screen, ensure that the delimiter is a comma and that the Text Qualifier is a quotemark. This should be the default. Click Finish.

When you view the resulting text file, note that there are no quote marks around the SSN field. The strange thing is that other field that are blank have the quotemarks as they should. It is only the SSN field. I see nothing out of the ordinary with it either in the original Excel file or in the imported table in Excel.

Thanks for your help. I bow to your superior knowledge

I did have the DAO 3.6 reference set, but the ADO 2.1 reference was set with a higher preference.

OK Hans…

One more part of my form I can’t get to work right. I know, I know… I need to start paying you by the hour

The piece you helped me with earlier today added a record to a table. This one is suppose to delete a record from the table (in case the wrong one is added, or something).

Here’s what I have:

Private Sub lstCatResend_DblClick(Cancel As Integer)

Dim rstCatResend As Recordset
Dim var1 As String

‘set var1 to PopID
var1 = Me!lstCatResend.Column(0)

Set rstCatResend = CurrentDb.OpenRecordset(“tblVCXPlus_CatUpdt_temp”, dbOpenTable)
rstCatResend.Index = “PopID”
With rstCatResend
.Seek “=”, var1
.Delete
End With
rstCatResend.Close
Me!lstCatResend.Requery

End Sub

You’ll notice I took your advise and changed the name of the table.

This is the same code I used in another database. It worked there, but here I get a runtime error on the Set rstCatResend line, stating Type mismatch.

I know there is a limitation with OpenRecordset and linked tables, but tblVCXPlus_CatUpdt_temp is not linked. Any thoughts.

Thanks

Yes, tblVCX+CatUpdt_temp is a table. After enclosing it in brackets, it worked like a charm. Thanks for your help!!

Hans–

Thanks for your response. I like your idea of just adding a field to the members table to toggle whether that member needed attention. However, I am reporting out of a SQL database that I cannot modify. So everything I need to do has to be done without altering the underlying database. It presents some challenges sometimes. (Just to be complete, I have created the Access database by linking to all the SQL tables. Only then can I write queries, design forms and add supplementary tables.)

Your suggestion about using an SQL statement really hit the mark. However, the Currentdb.Execute method will not execute. When that procedure fires, the de###### pops up highlighting that line. CurrentDb points to the correct database. strSQL evaluates correctly. Do you have any suggestions as to what might be wrong here?

Sorry about the confusion. I sometimes feel like I put TMI (Too Much Information) in my questions, and then when I don’t…

I use the textbox’ Change Event to trigger a Function that uses the FindFirst Method on the list in Listbox1. This sort of works like a combobox search in that with each letter that is typed into the textbox, the listbox scrolls down to the appropriate spot in the list. The combobox search doesn’t keep the list open, while this type of seach does.

The recordset (a query, actually) for the listbox has multiple columns. In order to make the listbox search solution work, the field I’m searching on must be the bound field. For example, since I’m typing in the last name in the textbox, I have bound the listbox to the column containing the last name. (Actually, a concatenated LastName-comma-FirstName field).

The purpose of searching through the listbox for a particular name is that I want to double click on the item in the list and have a value from a different column populate to a table. ***TMI WARNING—>> This particular form is to be used in management of a membership group. Throughout the week, I will be “selecting” member records that require a specific action. I want to add the PopID to a table. At the end of the week, I process the records in the table using another procedure, clear the table and start over the next week.

So, I want to double-click on a record in the listbox and have a value from the record added to a table. AddNew first comes to mind, but since I want to add a value other than the bound column, I’m unsure how to proceed. Any hints? Thanks.

Some fixes are just too easy. Thanks, Steve!!

Hans–

Thanks for you help. Before I got your response, I worked out the following logic:

Call Time (min): Sum(IIf([time_out]=””,0,(IIf([caller_cd2]=””,(DateDiff(“n”,[time_in],[time_out])),(DateDiff(“n”,[time_in],[time_out])+CInt([caller_cd2]))))))

It works, but yours is not as complex. Nested IIF statements make me