bound continuous form becoming unsynchronized (help)

[Welie]

I am having a problem an it's driving me crazy, I hope someone can
correct my technique. I can't find a pattern and don't know where to
look to debug this. The problem is on a continuous form, the rows seem
to become unsynchronized. From lack of experience I am probably doing
something wrong. Let me explain...

I have a select query which joins 2 tables, an Appointment table and
Payments table and presents the data as rows on the bound form. The
idea is to generate a list of appointments and allow entry of data to
the appointment table and record payment in payment table. The join is
right a right join on Payments since most times the row does not exist.
The payment table contains a customerID column as does the appointment
table. In order to populate the customerID column on the payment table
I do the following in the afterUpdate event of the text box that
records the payment amount.

Me.[Payments.custom erID] =
Me.recordSet.Fi elds.Item("clea nings.customeri d")

The problem is that sometime the payments.custom erID column contains
the wrong customerID. Often it's the customerID of the row right above
it. The form allows for deletion of rows as well as a button to
refresh the recorderset, but the problem seems to occur even if no
deletes or refresh takes place.

Thanks!
-Elie.

[deluxeinformation]

Welie wrote:

I am having a problem an it's driving me crazy, I hope someone can
correct my technique. I can't find a pattern and don't know where to
look to debug this. The problem is on a continuous form, the rows seem
to become unsynchronized. From lack of experience I am probably doing
something wrong. Let me explain...

I have a select query which joins 2 tables, an Appointment table and
Payments table and presents the data as rows on the bound form. The
idea is to generate a list of appointments and allow entry of data to
the appointment table and record payment in payment table. The join is
right a right join on Payments since most times the row does not exist.
The payment table contains a customerID column as does the appointment
table. In order to populate the customerID column on the payment table
I do the following in the afterUpdate event of the text box that
records the payment amount.

Me.[Payments.custom erID] =
Me.recordSet.Fi elds.Item("clea nings.customeri d")

The problem is that sometime the payments.custom erID column contains
the wrong customerID. Often it's the customerID of the row right above
it. The form allows for deletion of rows as well as a button to
refresh the recorderset, but the problem seems to occur even if no
deletes or refresh takes place.

I am afraid that this design may cause you more problems in the future.
I am assuming you want to tie each payment to a specific appointment
and that there may be more than one payment entered for a specific
appointment. You may want to change your design to a form/subform
arrangement where you have a main form bound to the appointments table
and then a subform on that form which is bound to the payments table.
Users could use the appointments form to navigate to the correct
appointment and then enter one or more payments into the subform.

Bruce

[Welie]

Bruce,
Thx for your reply. It's possible that there is a better design, but in
this case there is a one to one relationship between an appointment and
payment. Each appointment can have only payment. I could still do a
subform but I thought this design was simpler in terms of navigation.
To the user they just need to tab from column to column and then to the
next row.

Thanks.

deluxeinformat. [e-mail] wrote:

>
I am afraid that this design may cause you more problems in the future.
I am assuming you want to tie each payment to a specific appointment
and that there may be more than one payment entered for a specific
appointment. You may want to change your design to a form/subform
arrangement where you have a main form bound to the appointments table
and then a subform on that form which is bound to the payments table.
Users could use the appointments form to navigate to the correct
appointment and then enter one or more payments into the subform.

Bruce

[John Welch]

Well, given what you've said, I think the best idea is that you don't really
need to store the customerID in the payments table. If there really is a one
to one relationship, then every payment knows its appointmentID (I'm
assuming you joined the tables on AppointmentID, no?) and can therefore see
the customerID of that appointment. To get a list of all payments with their
customerID's, just make a query that joins payments to appointments (inner
join this time) and include the appointments.cu stomerID field.

But if you really need to set the customerID field in your other table, you
could get the value from the form instead of trying to get it from the
recordset. You could have an invisible customerID control in the
appointments part that's called txtInvisibleID and another control that's
bound to the customerID field in the payments table, and say
me.txtPaymentID = me.txtInvisible ID
(assuming the appointments.cu stomerID would already have it's value)

hope this helps
-John

"Welie" <we******@gmail .comwrote in message
news:11******** **************@ h48g2000cwc.goo glegroups.com.. .

Bruce,
Thx for your reply. It's possible that there is a better design, but in
this case there is a one to one relationship between an appointment and
payment. Each appointment can have only payment. I could still do a
subform but I thought this design was simpler in terms of navigation.
To the user they just need to tab from column to column and then to the
next row.

Thanks.

deluxeinformat. [e-mail] wrote:

>>
I am afraid that this design may cause you more problems in the future.
I am assuming you want to tie each payment to a specific appointment
and that there may be more than one payment entered for a specific
appointment. You may want to change your design to a form/subform
arrangement where you have a main form bound to the appointments table
and then a subform on that form which is bound to the payments table.
Users could use the appointments form to navigate to the correct
appointment and then enter one or more payments into the subform.

Bruce

[deluxeinformation]

Welie wrote:

Bruce,
Thx for your reply. It's possible that there is a better design, but in
this case there is a one to one relationship between an appointment and
payment. Each appointment can have only payment. I could still do a
subform but I thought this design was simpler in terms of navigation.
To the user they just need to tab from column to column and then to the
next row.

Perhaps it would make sense to combine the two tables into a single
table? If that is not practical then perhaps you should bind your
continuous form to the appointments table only and then have some
unbound fields on the appointment form in which to enter the payment
information. You could then update the payments table in code from the
after update event(s) of the unbound fields.

Bruce

[Welie]

John-

Thanks. I do need to keep the customerID in the payment table becuase
payments could come from sources other then cleanings.

However your second idea is probably the way I need to go. I am not
sure why I decided to access the customerID from the recordset. But is
it possible that the recordset would not match up with the form? I
still don't understand why this would happen.

In general what is the relationship between the results on the form and
the recordset? I know that the recordset is the base source of the
form, but I didn't think it was possible for them to have different
bookmarks.

John Welch (remove remove) wrote:

Well, given what you've said, I think the best idea is that you don't really
need to store the customerID in the payments table. If there really is a one
to one relationship, then every payment knows its appointmentID (I'm
assuming you joined the tables on AppointmentID, no?) and can therefore see
the customerID of that appointment. To get a list of all payments with their
customerID's, just make a query that joins payments to appointments (inner
join this time) and include the appointments.cu stomerID field.

But if you really need to set the customerID field in your other table, you
could get the value from the form instead of trying to get it from the
recordset. You could have an invisible customerID control in the
appointments part that's called txtInvisibleID and another control that's
bound to the customerID field in the payments table, and say
me.txtPaymentID = me.txtInvisible ID
(assuming the appointments.cu stomerID would already have it's value)

hope this helps
-John

[John Welch]

Good questions Welie-
I'm not sure why you had that problem, but a hunch might be that somehow
when the after update event fired, the bookmark was still on the previous
record?? Does the bookmark not move until the record is saved (not dirty any
more)?? Just making stuff up, but ?? You could test it by putting a message
box in the after update event like this:
msgbox Me.recordSet.Fi elds.Item("clea nings.customeri d")
to see what happens
-john

>
However your second idea is probably the way I need to go. I am not
sure why I decided to access the customerID from the recordset. But is
it possible that the recordset would not match up with the form? I
still don't understand why this would happen.

In general what is the relationship between the results on the form and
the recordset? I know that the recordset is the base source of the
form, but I didn't think it was possible for them to have different
bookmarks.

John Welch (remove remove) wrote:

>Well, given what you've said, I think the best idea is that you don't
really
need to store the customerID in the payments table. If there really is a
one
to one relationship, then every payment knows its appointmentID (I'm
assuming you joined the tables on AppointmentID, no?) and can therefore
see
the customerID of that appointment. To get a list of all payments with
their
customerID's , just make a query that joins payments to appointments
(inner
join this time) and include the appointments.cu stomerID field.

But if you really need to set the customerID field in your other table,
you
could get the value from the form instead of trying to get it from the
recordset. You could have an invisible customerID control in the
appointments part that's called txtInvisibleID and another control that's
bound to the customerID field in the payments table, and say
me.txtPaymentI D = me.txtInvisible ID
(assuming the appointments.cu stomerID would already have it's value)

hope this helps
-John

>