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's field and corresponding recordset out of sync?
Hello,
I'm sure this is due to ignorance on my part, but I'm trying to understand how the fields in a form and a form's recordset are related. I thought that the fields in a form, (i.e. 'ID'), correspond to the form's recordset, (i.e. me.Recordset("ID")), but they go out of sync when I have a subform and I create a new record in the subform: The example I have for reproducing it involves: - I create two tables Table1 and Table2. - Table1 has a reference to Table2: 'Table2Ref' - a form frmTable2 with all of Table2's fields - a form frmTable1 with all of Table1's fields, and frmTable2 as a subform. The out of sync behavior happens if: - I select the subform, frmTable1 - create a new entry (hit the * button) - add some data there - select a field in the master form At this point I check the recordset's id vs. the form id and they differ. This contines to be different even if I click back and forth on the subform. Is this the correct behavior? I would think that the two would stay in sync. This behavior doesn't occur if, after editing the new subform, I click the pencil icon to save the subform's record. This makes sense to me, but it seems as if the subform is saved when you click off of it. Normally when you edit a record the '' icon turns into a pencil, and it is saved to the database when you click on it. For a subform this icon changes back to the '' after you exit the subform, but it doesn't seem like the subforms values are saved to the recordset. The reason I'm doing this is that I'd like to allow a user to create and change the record a form refers to by using a subform. I'm using the Enter and Exit events to set and get the subforms record and: - using the recordset's 'findfirst' method for setting it - using the recordset's id (or form id) for getting the current value This works fine for everything but record creation. and using the form's id value works fine too. I just want to understand what is causing this, and I'm worried about there being problems in the future. Maybe my questions a - Is this the best way to allow a user to create/change the record referred to in a form? - Can I/Should I have the subform record be 'saved' (if that is the cause of this)? If so, how do I do this? Thanks! P.S. here is the code for the subform's exit event: Private Sub Child6_Exit(Cancel As Integer) Dim tmp, tmp2 As String tmp = Me.Child6.Form.Recordset("id") tmp2 = Me.Child6.Form.id ' this only pops up when a new record is created, but not saved in the subform ' before leaving subform focus If tmp2 tmp Then MsgBox (tmp & " in recordset not equal to " & tmp2 & " in form.id ") End If End Sub |
#2
|
|||
|
|||
On Sun, 7 Nov 2004 20:58:22 -0800, "AB" abrady0 o'the gmail dot com
wrote: Hello, I'm sure this is due to ignorance on my part, but I'm trying to understand how the fields in a form and a form's recordset are related. There are no *FIELDS* in a Form. There are *CONTROLS* in a form - textboxes, checkboxes, combos, etc. which can be bound to fields in the form's Recordsource. The control is not the field; it's a tool that lets you see and edit the value in the field. I thought that the fields in a form, (i.e. 'ID'), correspond to the form's recordset, (i.e. me.Recordset("ID")), but they go out of sync when I have a subform and I create a new record in the subform: The name of the control is irrelevant for this purpose - the Control Source property determines the value displayed or stored. The example I have for reproducing it involves: - I create two tables Table1 and Table2. - Table1 has a reference to Table2: 'Table2Ref' A "reference"? Do you mean a Foreign Key? How are the tables related? If the foreign key is in Table1, that implies that each record in Table2 can be linked to zero, one, or many records in Table1; the foreign key is always in the "many" side table. Normally in such a relationship one would use a Form based on the "one" side table, with a Subform based on the "many". - a form frmTable2 with all of Table2's fields - a form frmTable1 with all of Table1's fields, and frmTable2 as a subform. This is ok, in some circumstnces, but it's "upside down" in terms of normal practice. The out of sync behavior happens if: - I select the subform, frmTable1 - create a new entry (hit the * button) - add some data there - select a field in the master form At this point I check the recordset's id vs. the form id and they differ. This contines to be different even if I click back and forth on the subform. Is this the correct behavior? I would think that the two would stay in sync. This behavior doesn't occur if, after editing the new subform, I click the pencil icon to save the subform's record. That's because your mainform is based on the dependent table, and the subform on the "main" table. This makes sense to me, but it seems as if the subform is saved when you click off of it. Normally when you edit a record the '' icon turns into a pencil, and it is saved to the database when you click on it. For a subform this icon changes back to the '' after you exit the subform, but it doesn't seem like the subforms values are saved to the recordset. Yes. The subform record (any form record actually) is, by default, saved as soon as you click off it. The subform values almost certainly ARE being saved to the recordset, but since your subform is showing only those records related to the mainform record onscreen, you're not seeing it. The reason I'm doing this is that I'd like to allow a user to create and change the record a form refers to by using a subform. I'm using the Enter and Exit events to set and get the subforms record and: - using the recordset's 'findfirst' method for setting it - using the recordset's id (or form id) for getting the current value This works fine for everything but record creation. and using the form's id value works fine too. I just want to understand what is causing this, and I'm worried about there being problems in the future. I'm not sure why you're doing it this way - it seems the long way about what Access does normally, if you have the master record on the mainform and the related record (or records) on the subform! John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#3
|
|||
|
|||
Hello John,
Thank you for the response. I want to say, in advance, that it helped me tremendously. My apologies for the confusing terminology, I'm new to this Access stuff and still getting ahold of the vocabulary. To clarify: - by 'fields', as you said, I meant the value of that control. (i.e. me.id is a textbox with value '1') - Table1 has a foreign key that refers to table2 'Table2Ref' At this point I check the recordset's id vs. the form id and they differ. This contines to be different even if I click back and forth on the subform. Is this the correct behavior? I would think that the two would stay in sync. This behavior doesn't occur if, after editing the new subform, I click the pencil icon to save the subform's record. That's because your mainform is based on the dependent table, and the subform on the "main" table. My apologies, I don't think I explained this very well. - Let's say that the subform's control named 'id' whose object source is 'id' is displaying '1'. -- its recordset("id") is '1' - I click on the subform and create a new record - I click back to a field in the master form. (presumably saving the records?) -- the subform control named 'id' (object source = 'id') displays '2' -- the subform recordset("id") has a value of 1. This behavior, as far as I can tell, doesn't have to do with the mainform. It seems to be contained entirely in the subform: I'm not sure why the 'id' control and the recordset("id") are getting out of sync. This is just a stab, but my guess is that the active record in a recordset isn't being updated in the special case when a record is created in a subform, while the currentrecord in the form is. So, maybe my real question is: - How do I keep the recordset's current record and a subform's CurrentRecord referring to the same record? Here is one way I found for doing that: Private Sub subformtable2_Exit(Cancel As Integer) If Not Me.SubformTable2.Form.Recordset.EOF Then Dim frm As Form Set frm = Me.SubformTable2.Form Dim rs As DAO.Recordset Set rs = Me.SubformTable2.Form.Recordset ' if the current record and recordset position don't match, fix it. ' so far, only true when a new record was created ' (note: CurrentRecord is 1-based, AbsolutePosition 0-based) If frm.CurrentRecord rs.AbsolutePosition + 1 Then rs.AbsolutePosition = frm.CurrentRecord - 1 End If Me.Table2Ref = rs("ID") End If End Sub This seems like a hack to me. Is there any way to automate this? - a form frmTable2 with all of Table2's fields - a form frmTable1 with all of Table1's fields, and frmTable2 as a subform. This is ok, in some circumstnces, but it's "upside down" in terms of normal practice. Okay, I think this is where I was making things difficult for myself. If I make the subform the 'many' side of it then Access handles the updates automatically. This really takes care of what I'm trying to do. I'm not sure why you're doing it this way - it seems the long way about what Access does normally, if you have the master record on the mainform and the related record (or records) on the subform! All this comes from the fact that I'm doing 'upside-down' foreign keys. In the actual database I'm working in I had envisoned a 'master' form referring to a 'child' form with the 'master' table having a foreign key referring to the child. But clearly the opposite is the norm, and it looks like Access forms are built with that in mind. I think I should do some reading on relational databases. When I was trying to use subforms 'upside-down' I was getting the 'You Can't Assign A Value To This Object' message because of the master/child links. That is the problem I'm trying to solve. Just for my own edification, if anybody has any feedback on the CurrentRecord vs. AbsolutePosition problem I'd love to hear it. Thanks, again, for the response! "John Vinson" wrote in message ... On Sun, 7 Nov 2004 20:58:22 -0800, "AB" abrady0 o'the gmail dot com wrote: Hello, I'm sure this is due to ignorance on my part, but I'm trying to understand how the fields in a form and a form's recordset are related. There are no *FIELDS* in a Form. There are *CONTROLS* in a form - textboxes, checkboxes, combos, etc. which can be bound to fields in the form's Recordsource. The control is not the field; it's a tool that lets you see and edit the value in the field. I thought that the fields in a form, (i.e. 'ID'), correspond to the form's recordset, (i.e. me.Recordset("ID")), but they go out of sync when I have a subform and I create a new record in the subform: The name of the control is irrelevant for this purpose - the Control Source property determines the value displayed or stored. The example I have for reproducing it involves: - I create two tables Table1 and Table2. - Table1 has a reference to Table2: 'Table2Ref' A "reference"? Do you mean a Foreign Key? How are the tables related? If the foreign key is in Table1, that implies that each record in Table2 can be linked to zero, one, or many records in Table1; the foreign key is always in the "many" side table. Normally in such a relationship one would use a Form based on the "one" side table, with a Subform based on the "many". - a form frmTable2 with all of Table2's fields - a form frmTable1 with all of Table1's fields, and frmTable2 as a subform. This is ok, in some circumstnces, but it's "upside down" in terms of normal practice. The out of sync behavior happens if: - I select the subform, frmTable1 - create a new entry (hit the * button) - add some data there - select a field in the master form At this point I check the recordset's id vs. the form id and they differ. This contines to be different even if I click back and forth on the subform. Is this the correct behavior? I would think that the two would stay in sync. This behavior doesn't occur if, after editing the new subform, I click the pencil icon to save the subform's record. That's because your mainform is based on the dependent table, and the subform on the "main" table. This makes sense to me, but it seems as if the subform is saved when you click off of it. Normally when you edit a record the '' icon turns into a pencil, and it is saved to the database when you click on it. For a subform this icon changes back to the '' after you exit the subform, but it doesn't seem like the subforms values are saved to the recordset. Yes. The subform record (any form record actually) is, by default, saved as soon as you click off it. The subform values almost certainly ARE being saved to the recordset, but since your subform is showing only those records related to the mainform record onscreen, you're not seeing it. The reason I'm doing this is that I'd like to allow a user to create and change the record a form refers to by using a subform. I'm using the Enter and Exit events to set and get the subforms record and: - using the recordset's 'findfirst' method for setting it - using the recordset's id (or form id) for getting the current value This works fine for everything but record creation. and using the form's id value works fine too. I just want to understand what is causing this, and I'm worried about there being problems in the future. I'm not sure why you're doing it this way - it seems the long way about what Access does normally, if you have the master record on the mainform and the related record (or records) on the subform! John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
#4
|
|||
|
|||
Quick update to this. I discovered I could sync the currentrecord in a form
with the form's recordset by assigning the form's bookmark to the recordset's bookmark: ' one way of syncing a recordset with a form If Me.Child6.Form.Recordset("id") Me.Child6.Form.id Then Me.Child6.Form.Recordset.Bookmark = Me.Child6.Form.Bookmark End If "AB" abrady0 o'the gmail dot com wrote in message ... Hello John, Thank you for the response. I want to say, in advance, that it helped me tremendously. My apologies for the confusing terminology, I'm new to this Access stuff and still getting ahold of the vocabulary. To clarify: - by 'fields', as you said, I meant the value of that control. (i.e. me.id is a textbox with value '1') - Table1 has a foreign key that refers to table2 'Table2Ref' At this point I check the recordset's id vs. the form id and they differ. This contines to be different even if I click back and forth on the subform. Is this the correct behavior? I would think that the two would stay in sync. This behavior doesn't occur if, after editing the new subform, I click the pencil icon to save the subform's record. That's because your mainform is based on the dependent table, and the subform on the "main" table. My apologies, I don't think I explained this very well. - Let's say that the subform's control named 'id' whose object source is 'id' is displaying '1'. -- its recordset("id") is '1' - I click on the subform and create a new record - I click back to a field in the master form. (presumably saving the records?) -- the subform control named 'id' (object source = 'id') displays '2' -- the subform recordset("id") has a value of 1. This behavior, as far as I can tell, doesn't have to do with the mainform. It seems to be contained entirely in the subform: I'm not sure why the 'id' control and the recordset("id") are getting out of sync. This is just a stab, but my guess is that the active record in a recordset isn't being updated in the special case when a record is created in a subform, while the currentrecord in the form is. So, maybe my real question is: - How do I keep the recordset's current record and a subform's CurrentRecord referring to the same record? Here is one way I found for doing that: Private Sub subformtable2_Exit(Cancel As Integer) If Not Me.SubformTable2.Form.Recordset.EOF Then Dim frm As Form Set frm = Me.SubformTable2.Form Dim rs As DAO.Recordset Set rs = Me.SubformTable2.Form.Recordset ' if the current record and recordset position don't match, fix it. ' so far, only true when a new record was created ' (note: CurrentRecord is 1-based, AbsolutePosition 0-based) If frm.CurrentRecord rs.AbsolutePosition + 1 Then rs.AbsolutePosition = frm.CurrentRecord - 1 End If Me.Table2Ref = rs("ID") End If End Sub This seems like a hack to me. Is there any way to automate this? - a form frmTable2 with all of Table2's fields - a form frmTable1 with all of Table1's fields, and frmTable2 as a subform. This is ok, in some circumstnces, but it's "upside down" in terms of normal practice. Okay, I think this is where I was making things difficult for myself. If I make the subform the 'many' side of it then Access handles the updates automatically. This really takes care of what I'm trying to do. I'm not sure why you're doing it this way - it seems the long way about what Access does normally, if you have the master record on the mainform and the related record (or records) on the subform! All this comes from the fact that I'm doing 'upside-down' foreign keys. In the actual database I'm working in I had envisoned a 'master' form referring to a 'child' form with the 'master' table having a foreign key referring to the child. But clearly the opposite is the norm, and it looks like Access forms are built with that in mind. I think I should do some reading on relational databases. When I was trying to use subforms 'upside-down' I was getting the 'You Can't Assign A Value To This Object' message because of the master/child links. That is the problem I'm trying to solve. Just for my own edification, if anybody has any feedback on the CurrentRecord vs. AbsolutePosition problem I'd love to hear it. Thanks, again, for the response! "John Vinson" wrote in message ... On Sun, 7 Nov 2004 20:58:22 -0800, "AB" abrady0 o'the gmail dot com wrote: Hello, I'm sure this is due to ignorance on my part, but I'm trying to understand how the fields in a form and a form's recordset are related. There are no *FIELDS* in a Form. There are *CONTROLS* in a form - textboxes, checkboxes, combos, etc. which can be bound to fields in the form's Recordsource. The control is not the field; it's a tool that lets you see and edit the value in the field. I thought that the fields in a form, (i.e. 'ID'), correspond to the form's recordset, (i.e. me.Recordset("ID")), but they go out of sync when I have a subform and I create a new record in the subform: The name of the control is irrelevant for this purpose - the Control Source property determines the value displayed or stored. The example I have for reproducing it involves: - I create two tables Table1 and Table2. - Table1 has a reference to Table2: 'Table2Ref' A "reference"? Do you mean a Foreign Key? How are the tables related? If the foreign key is in Table1, that implies that each record in Table2 can be linked to zero, one, or many records in Table1; the foreign key is always in the "many" side table. Normally in such a relationship one would use a Form based on the "one" side table, with a Subform based on the "many". - a form frmTable2 with all of Table2's fields - a form frmTable1 with all of Table1's fields, and frmTable2 as a subform. This is ok, in some circumstnces, but it's "upside down" in terms of normal practice. The out of sync behavior happens if: - I select the subform, frmTable1 - create a new entry (hit the * button) - add some data there - select a field in the master form At this point I check the recordset's id vs. the form id and they differ. This contines to be different even if I click back and forth on the subform. Is this the correct behavior? I would think that the two would stay in sync. This behavior doesn't occur if, after editing the new subform, I click the pencil icon to save the subform's record. That's because your mainform is based on the dependent table, and the subform on the "main" table. This makes sense to me, but it seems as if the subform is saved when you click off of it. Normally when you edit a record the '' icon turns into a pencil, and it is saved to the database when you click on it. For a subform this icon changes back to the '' after you exit the subform, but it doesn't seem like the subforms values are saved to the recordset. Yes. The subform record (any form record actually) is, by default, saved as soon as you click off it. The subform values almost certainly ARE being saved to the recordset, but since your subform is showing only those records related to the mainform record onscreen, you're not seeing it. The reason I'm doing this is that I'd like to allow a user to create and change the record a form refers to by using a subform. I'm using the Enter and Exit events to set and get the subforms record and: - using the recordset's 'findfirst' method for setting it - using the recordset's id (or form id) for getting the current value This works fine for everything but record creation. and using the form's id value works fine too. I just want to understand what is causing this, and I'm worried about there being problems in the future. I'm not sure why you're doing it this way - it seems the long way about what Access does normally, if you have the master record on the mainform and the related record (or records) on the subform! John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
Thread Tools | |
Display Modes | |
|
|