Can Excel requery an Access Front End?

[April15Hater]

Hey guys,

I have a multiselect listbox (lstClosedProduction) in an Access form. The user will make a selection, hit a button, and it will export data to an excel invoice template. Among the data it exports is the unique ID's of the items selected in the multiselection list box.

After the user hits the finalize button on the excel invoice template, I have it so that it connects to the back end via ADO, updates the records corresponding to the unique id's of the items selected, then saves and closes. However after excel updates the unique id records, I need the listbox to requery so the finalized items disappear from the listbox. How can I go about doing this?

Thanks,

Joe

 

[Banana]

Let's make sure I understand the situation right.

Users has a Excel template which will be run outside Access after they've made their choices inside Access and pressed the export button, right? They have a button in the Excel template that needs to go back and requery the listbox somehow? And the requery should only happen when they choose to finalize it?

If so, then I would look into using GetObject() call to grab the current Access instance and automate it as the usual.

pseudo code: (you need to check the correct syntax and how to use GetObject())

Dim ax As Access.Application

Set ax = GetObject("ACCESS")

With ax
   .Forms("NameOfForm").Controls("NameOfListBox").Requery
End With

End With

HTH.

 

[April15Hater]

Sigh.... due to micromanagement, I have to come back to this later. But that looks like exactly what I need. I'll post back when I can. Thanks again for pointing me in the right direction.

 

[April15Hater]

Banana, worked like a charm! Microsoft has a good article at http://support.microsoft.com/kb/147816

Final Code:

Dim objAccess As Object
Set objAccess = GetObject("J:\Interfaces\Source Code\CCE Services Inc - Full Front End.mdb")
With objAccess
     .Forms("frmProductionStep6a").Controls("lstClosedProduction").Requery
End With

 

[April15Hater]

It worked a couple of times, but now it give me the following message and locks up excel: Microsoft Excel is waiting for another application to complete an OLE action. Any Ideas?

The problem code is towards the very bottom:

Sub FinalizeInvoice()
Dim intAccountingContactID As Integer
Dim intProductionInvoiceID As Variant
Dim strFilename As String
Dim strFilepath As String
Dim intFunctionID As Integer
Dim objAccess As Object
'Set objAccess = GetObject("C:\CCE\CCE Services Inc - Managers Suite.mde")
Set objAccess = GetObject("J:\Interfaces\Source Code\CCE Services Inc - Full Front End.mdb")
'=================================================================================
'==Define Variables                                                             ==
'=================================================================================
intFunctionID = Application.WorksheetFunction.VLookup("FunctionID", Sheets("Customer Info").Range("A1:B65536"), 2, False)
intAccountingContactID = Application.WorksheetFunction.VLookup("AccountingContactID", Sheets("Customer Info").Range("A1:B65536"), 2, False)
'=================================================================================
'== Make New Invoice Record in the Database                                     ==
'=================================================================================
intProductionInvoiceID = NewInvoiceRecord(intAccountingContactID, intFunctionID)
ActiveWorkbook.Sheets("Invoice").Range("H2").Value = intProductionInvoiceID(1)
'=================================================================================
'== Assign InvoiceID to Detail Record                                           ==
'=================================================================================
For Each PIDCell In Range(Sheets("ProductionID").Cells(1, 1), Sheets("ProductionID").Cells(Rows.Count, 1).End(xlUp))
    Call AssignInvoiceID2Detail(PIDCell.Value, CInt(intProductionInvoiceID(0)))
Next PIDCell
'=================================================================================
'== Clean-up Invoice                                                            ==
'=================================================================================
With ActiveWorkbook
    .Sheets("Customer Info").Visible = xlVeryHidden
    .Sheets("ProductionID").Visible = xlVeryHidden
    With .Sheets("Invoice")
        .Shapes("Button 2").Delete
        .Range("H2").Value = intProductionInvoiceID(1)
    End With
End With

'=================================================================================
'== Save PDF and XLS                                                            ==
'=================================================================================
strFilepath = "C:\CCE\"
strFilename = intProductionInvoiceID(1)
ActiveWorkbook.SaveAs strFilepath & strFilename
Call PrintToPDF(strFilepath, strFilename)


'=================================================================================
'== Requery Access frmProductionStep6a listbox                                  ==
'=================================================================================
With objAccess.Forms("frmProductionStep6a")
     .Controls("lstClosedProduction").Requery
     .Controls("lstFunction").Requery
End With
Set objAccess = Nothing
End Sub

 

[Banana]

Hey, sorry I dropped this thread.

I wonder if it may be actually easier for you to use events from Excel and respond to it.

If the code is behind the form, then it's a class module and you can declare your Excel variable like this:

Private WithEvents xlWs As Excel.Worksheet

Then assign a event handler for the xlWs that will be raised from clicking the button and have it requery the listbox. That way we don't have to worry about conflicts in accessing the same instances in different scopes.

 

[April15Hater]

Then assign a event handler for the xlWs that will be raised from clicking the button and have it requery the textbox.

Got it up until this part. How do I assign an event handler to xlWs?

 

[Banana]

After you declare the variable, the xlWs should be available in the left combobox on the top of code windows, then you can select appropriate events in the right combobox (it also gives you a default event but that may not be what you want anyway)

 

[April15Hater]

Ohhhh, I get it now, wow....that is really really smart man. I never knew you could do that, and even if I did, i don't even think I would have had the capacity to come up with that as a solution, lol!

I did change it to:

Private WithEvents xlWs As Excel.Workbook

With this for the event handler:

Private Sub xlWs_BeforeClose(Cancel As Boolean)
Me.lstClosedProduction.Requery
Me.lstFunction.Requery
End Sub

I know, I got a little greedy requerying 2 listboxes, lol. But the button's code does include application.quit, so that event works perfect! Once again, you grace me with another aha moment. Thanks a million!

 

[Banana]

Actually, I only thought of your thread after answering a post on newsgroup asking about capturing events in Excel from Access and thought, "hey wouldn't that be more reliable than GetObject?!?", so you really should thank that poster over at the newsgroup.

Glad to help.