here is the code. there are 2 subforms - one for invoices and one for
customer info. the code for the customer info is the same as that for the
invoices - only the table is different. i had 2 versions of code to delete
the tables but the result is the same only version 1 runs slower than
version
2.
***
Private Sub comboSelectAInvoice_AfterUpdate()
HideSubforms 'hide the subforms.
PopulateCustomerInfo 'fill textboxes with customer information.
DeleteInvoicesTemp 'clear data from subform.
PopulateInvoice 'fill subform with data from selected invoice.
Me.subformInvoice.Form.Requery
Me.subformInvoice.Form.Repaint
DeleteCustomerInfoTemp'clear data from subform.
PopulateCustomerInfo 'fill subform with data from customer info.
Me.subformCustomerInfo.Form.Requery
Me.subformCustomerInfo.Form.Repaint
Me.comboSelectAInvoice.SetFocus
Me.commandSave.Enabled = False
Me.Refresh
Me.Repaint
ShowSubforms 'show the subforms.
End Sub
Private Sub DeleteInvoicesTemp()
'delete all records from the temp table.
'version 1
Dim cnnDB As ADODB.Connection
Set cnnDB = New ADODB.Connection
cnnDB.Open DATABASE_CONNECTION_STRING
cnnDB.Execute "DELETE FROM InvoicesTemp"
cnnDB.Close
Set cnnDB = Nothing
'**************************************************
'version 2
Dim cnn1 As ADODB.Connection
Dim rsInvoicesTemp As ADODB.Recordset
' open a connection.
Set cnn1 = New ADODB.Connection
cnn1.Open DATABASE_CONNECTION_STRING
Set rsInvoicesTemp = New ADODB.Recordset
rsInvoicesTemp.CursorType = adOpenKeyset
rsInvoicesTemp.LockType = adLockOptimistic
rsInvoicesTemp.Open "InvoicesTemp", cnn1, , , adCmdTable
If (rsInvoicesTemp.RecordCount > 0) Then
rsInvoicesTemp.MoveFirst
Do While Not rsInvoicesTemp.EOF
rsInvoicesTemp.Delete
rsInvoicesTemp.MoveNext
Loop
End If
rsInvoicesTemp.Close
Set rsInvoicesTemp = Nothing
cnn1.Close
Set cnn1 = Nothing
End Sub
Private Sub PopulateInvoice()
'populate subform with data of invoice selected from the combo.
Dim cnn1 As ADODB.Connection
Dim rsInvoices As ADODB.Recordset
Dim rsInvoicesTemp As ADODB.Recordset
Dim strSQLString As String
Set cnn1 = New ADODB.Connection
cnn1.Open DATABASE_CONNECTION_STRING
' open invoice temp table.
Set rsInvoicesTemp = New ADODB.Recordset
rsInvoicesTemp.CursorType = adOpenKeyset
rsInvoicesTemp.LockType = adLockOptimistic
rsInvoicesTemp.Open "InvoicesTemp", cnn1, , , adCmdTable
strSQLString = "SELECT * " & _
"FROM [Invoices] " & _
"WHERE ([InvoiceID] = '" & Me.textboxInvoiceID
& "');"
' open invoice table.
Set rsInvoices = New ADODB.Recordset
rsInvoices.CursorType = adOpenKeyset
rsInvoices.LockType = adLockOptimistic
rsInvoices.Open strSQLString, cnn1, , , adCmdText
Select Case (rsInvoices.RecordCount)
Case Is > 0
rsInvoices.MoveFirst
Do While Not rsInvoices.EOF
With rsInvoicesTemp
.AddNew
![InvoiceID] = rsInvoices![InvoiceID]
![Description] = rsInvoices![Description]
.Update
End With
rsInvoices.MoveNext
Loop
Case Is = 0
End Select
rsInvoicesTemp.Close
Set rsInvoicesTemp = Nothing
rsInvoices.Close
Set rsInvoices = Nothing
cnn1.Close
Set cnn1 = Nothing
End Sub
***
Mark A. Sam said:
Will you post your code? It it is Monday, I will be out during the day,
but
will check on this post later on.
hi Mark
.
i am running the code from a combo box's afterupdate event. the combo
box
is
on the form which already is open. the form is not bound to a table or
query.
fred
:
Fred,
Are you running the code from either the main form's Load or Open
event?
Is
so, then the subform should display the records after the main form
opens
without requerying. How are you running the code?
God Bless,
Mark A. Sam
on my form I have a subform with an underlying table "invoices":. i
have
code
that deletes all records from the "invoices" table and populate it
with
new
records ("using ado recordset methods). the subform is then suppose
to
display the newly populated records.
however, everytime the app is started and the code is run for the
first
time, the subform is blank with no record. i checked the "invoices"
table
and
found that it does have the records in it. on subsequent running of
the
code,
the subform displays the record correctly. in my code i have tried
mysubform.form,.requery and repaint and i have also tried me.repaint
even
though the main form has no bound data.
how can i ensure that the subform displays the new records correctly
everytime, especially the first time the app starts up and the code
is
run?
thanks for your help.
