howto? programatically update/refresh a table after inserting a new row wit SQL

[Azvareth]

Hi!

I'm new here but I need an answer to a question:

I have a form, this form has details about products to sell, in the form header I have added a button to import xmldata containing a collection of products and properties of it. The import is done with msXMLDOM...... I retreive all values and build a string that I post into the database with:
DoCmd.RunSQL "INSERT INTO tabProdukt .......

Problem 1.
how much I trial and error, I can't get the table to update/refresh so the data is "actually" there (if I press F5 in the table the data shows)

Problem 2.
may be related to problem 1. the forms detailview does not show imported data either...

I am stucked and hope someone knows (yes I have searched all over the wwworld for an answer - but without luck)

//Azvareth

[Stewart Ross]

Hi. After your INSERT statement, to show the new records in your form you need to requery the form's underlying dataset (which is otherwise in the state it was when data was read from that dataset just after the form was loaded).

Add the following statement below your DoCmd.RunSQL:

Me.Requery

which should help, assuming that the code is running on the same form that you want to requery. I am also assuming that it is not a subform of the main form you need to requery, as it would be referred to slightly differently:

Me.subformcontrolname.form.Requery

-Stewart

[Azvareth]

Thank you!

It seems to work.

Strange, I thought I tried that along with refresh and other things, I might have done something wrong with that command (referencing wrong objects or so...)

//Azvareth

[Edit]
actually the code itself is running inside an class module (the only way (I assume) to attach event notification/handlers from an activeX object (WithEvents) but the class module is Owned by the Form so I guess the Form "own" the code.
[/Edit]

[Stewart Ross]

Glad this works for you Azvareth. Event handlers do indeed need to be within class modules declared With Events as you say. Every form has its own class module, instantiated automatically when the form is opened, so it does own the code associated with it.

The Refresh command would seem on the face of it to be related to Requery but they are different - refresh shows any edit changes but not new records added. Requery shows all changes, but at the expense of reloading the dataset.

Requery can be applied to any open form (or to a control within a form, such as a combo box) from code running outside of that form by using the fully qualified name of the form or control instead of the shorthand Me operator. For example:

Forms("someformname").Requery
Forms("someformname").comboname.Requery
Forms("someformname").subformcontrolname.Form.Requery

and so on.

-Stewart

[Azvareth]

Thanks again, I do understand... and I am a bit experienced with development, mainly Delphi and jscript (HTA apps), but not a bit with Access and have only used VB and VBA very little, but I do understand the concept.

It is no big deal, but I did not use the method you descriped to access the form (I like to call it: reference the form.object directly) I paste in my whole class module here there might [be something someone else could use] be some other thing that is wrong.

'import powerlister xmldata module

Option Compare Database
Option Explicit

Public creationDate, selectedFile As String
Dim fDialog As Office.FileDialog
Dim WithEvents oDOM As MSXML2.DOMDocument


Private Sub Class_Initialize()
  Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  Set oDOM = New MSXML2.DOMDocument
End Sub

Private Sub Class_Terminate()
  Set oDOM = Nothing
  Set fDialog = Nothing
End Sub

Public Function selectXML() As Boolean
  selectedFile = ""
  selectXML = False
  With fDialog
    .AllowMultiSelect = False
    .Title = "Select a File to Open"
    .filters.Clear
    .filters.Add "Powerlister filer", "*.pxml"
    .filters.Add "XML filer", "*.xml"
    .filters.Add "Alla filer", "*.*"
    If .Show Then
      selectedFile = .SelectedItems.Item(.SelectedItems.Count)
      If loadXML() Then
        selectXML = True
      Else
        selectedFile = ""
      End If
    End If
  End With
End Function

Private Function loadXML() As Boolean
  loadXML = False
  oDOM.async = False
  loadXML = oDOM.Load(selectedFile)
End Function

Private Sub oDOM_onreadystatechange() 'wait until whole document is loaded
  If oDOM.ReadyState = 4 Then parseXML
End Sub

Private Sub processAuctionItem(ByRef AuctionItems As IXMLDOMNodeList)
Dim i As Integer
  Dim szProduct, szTitle, szStartPrice, szCategory As String
  Dim bSold, bActive, bPayed, bPrivate As Boolean
  Dim dateBought, dateSold, datePosted As Date
  Dim currencyBought, currencySold, currencyPost, currencyPostAdjust, currencyPackage, currencyWork As Currency

  For i = 0 To AuctionItems.length - 1
    Select Case AuctionItems.Item(i).nodeName
      Case "title": szTitle = AuctionItems.Item(i).Text
      Case "description": szProduct = AuctionItems.Item(i).Text
      Case "startprice": szStartPrice = AuctionItems.Item(i).Text
    Case Else
    End Select
  Next i
  
DoCmd.SetWarnings False 'supress warning reqs
' BEGIN TEST
DoCmd.RunSQL "INSERT INTO tabProdukt (" & _
                                      "[Produkt]," & _
                                      "[Kategori]," & _
                                      "[Inköpsdatum])" & _
                                      " VALUES (" & _
                                      "'" & szTitle & vbCrLf & vbCrLf & szProduct & vbCrLf & vbCrLf & "'," & _
                                      "'" & szTitle & "'," & _
                                      "#" & creationDate & "#" & _
                                     ");"
Form_frmProdukt.Requery
' END TEST
DoCmd.SetWarnings True ' allow warning reqs

  
End Sub

Private Sub parseXML()
Dim i As Integer

  If oDOM.childNodes.Item(0).nodeName = "powerlisterarchive" Then
     creationDate = oDOM.childNodes.Item(0).Attributes.getNamedItem("date").nodeValue
      For i = 0 To oDOM.childNodes.Item(0).childNodes.length - 1
        processAuctionItem oDOM.childNodes.Item(0).childNodes.Item(i).childNodes
      Next i
  End If
End Sub

What happens here is that I instansiate (or how you spell it) this module from my mainform with a buttonclick

Private Sub btnImportXML_Click()
  Dim importFile As String
  
  Set import = New cImport
  If import.selectXML() Then
    MsgBox import.selectedFile 'TEST
  End If
  Set import = Nothing
End Sub

Then every import related thing is done within the class module, and the reference to the form is directly: Form_frmProdukt .Requery

//I know it was a bit OT but there might be something purhaps, that could help someone else...

//Azvareth

Glad this works for you Azvareth. Event handlers do indeed need to be within class modules declared With Events as you say. Every form has its own class module, instantiated automatically when the form is opened, so it does own the code associated with it.

The Refresh command would seem on the face of it to be related to Requery but they are different - refresh shows any edit changes but not new records added. Requery shows all changes, but at the expense of reloading the dataset.

Requery can be applied to any open form (or to a control within a form, such as a combo box) from code running outside of that form by using the fully qualified name of the form or control instead of the shorthand Me operator. For example:

Forms("someformname").Requery
Forms("someformname").comboname.Requery
Forms("someformname").subformcontrolname.Form.Requery

and so on.

-Stewart