A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

subform's field and corresponding recordset out of sync?



 
 
Thread Tools Display Modes
  #1  
Old November 8th, 2004, 05:58 AM
AB
external usenet poster
 
Posts: n/a
Default 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  
Old November 8th, 2004, 06:07 PM
John Vinson
external usenet poster
 
Posts: n/a
Default

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  
Old November 8th, 2004, 09:12 PM
AB
external usenet poster
 
Posts: n/a
Default

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  
Old November 8th, 2004, 11:54 PM
AB
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.