subform populate error

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.

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


"Fred Loh" wrote in message
...
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.

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

"Mark A. Sam" wrote:

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


"Fred Loh" wrote in message
...
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.

Will you post your code? It it is Monday, I will be out during the day, but
will check on this post later on.


"Fred Loh" wrote in message
...
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

"Mark A. Sam" wrote:

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


"Fred Loh" wrote in message
...
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.

will post the code tomorrow 15/4/07.

"Mark A. Sam" wrote:

Will you post your code? It it is Monday, I will be out during the day, but
will check on this post later on.


"Fred Loh" wrote in message
...
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

"Mark A. Sam" wrote:

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


"Fred Loh" wrote in message
...
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.

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" wrote:

Will you post your code? It it is Monday, I will be out during the day, but
will check on this post later on.


"Fred Loh" wrote in message
...
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

"Mark A. Sam" wrote:

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


"Fred Loh" wrote in message
...
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.

Fred,

You haven't displayed the methods you are using to add records, but I'll
show you one that works, the AddNew method in DAO. This is on an Access2000
database. I tried this on an Access2007 form and it didn't work for some
reason.

I opened a new form and added an existing subform. I named the subform
control, sfPresses. I put an button on the main form and added this code to
the Click event.

Private Sub Command1_Click()

Dim rst As Recordset
Set rst = sfPresses.Form.RecordsetClone

rst.AddNew
rst![PressNo] = "123"
rst.Update

rst.Close
Set rst = Nothing

End Sub


The record will display on the subform without the need to requery.

God Bless,

Mark A. Sam


"Fred Loh" wrote in message
...

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" wrote:

Will you post your code? It it is Monday, I will be out during the day,
but
will check on this post later on.


"Fred Loh" wrote in message
...
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

"Mark A. Sam" wrote:

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


"Fred Loh" wrote in message
...
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.

the add records method have actually been posted. it is in the
PopulateInvoice() procedure right after the DeleteInvoicesTemp() procedure.
any way, i use your sample code and it worked like a charm. only "Dim rst as
Recordset" gave a "type mismatch" error so i replace it with "Dim rst as
Object". Also, there is no apparent difference between
"mysubform.form.Recordset" and "mysubform.form.Recordset.Clone".

Thanks very much Mark. God bless.

fred


"Mark A. Sam" wrote:

Fred,

You haven't displayed the methods you are using to add records, but I'll
show you one that works, the AddNew method in DAO. This is on an Access2000
database. I tried this on an Access2007 form and it didn't work for some
reason.

I opened a new form and added an existing subform. I named the subform
control, sfPresses. I put an button on the main form and added this code to
the Click event.

Private Sub Command1_Click()

Dim rst As Recordset
Set rst = sfPresses.Form.RecordsetClone

rst.AddNew
rst![PressNo] = "123"
rst.Update

rst.Close
Set rst = Nothing

End Sub


The record will display on the subform without the need to requery.

God Bless,

Mark A. Sam


"Fred Loh" wrote in message
...

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" wrote:

Will you post your code? It it is Monday, I will be out during the day,
but
will check on this post later on.


"Fred Loh" wrote in message
...
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

"Mark A. Sam" wrote:

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


"Fred Loh" wrote in message
...
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.