If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
subform populate error
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. |
#3
|
|||
|
|||
subform populate error
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. |
#4
|
|||
|
|||
subform populate error
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. |
#5
|
|||
|
|||
subform populate error
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. |
#6
|
|||
|
|||
subform populate error
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. |
#7
|
|||
|
|||
subform populate error
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. |
#8
|
|||
|
|||
subform populate error
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. |
Thread Tools | |
Display Modes | |
|
|