How to use refresh/requery and move to next record after update

[AppDev63]

I've been struggling with a form. On the left side of the form I've got data from a table of invoices, and on the right side I've got a combo box with data from a table of payments. The user scrolls through the invoices on the left and matches each one up with a payment from the combo box on the right. The common field is an order number, but the relationship between invoices and payments must be created manually by the user matching up the records.

I have two problems:I can't seem to get the requery or refresh working properly - each time the user scrolls to a different invoice on the right, I need the combo box on the left to refresh so that it will only return the payments that correspond to that order number.

My second problem is that whenever the invoice and payment are matched up and the record is saved, the form jumps back to the very first record. I need it to move forward to the NEXT record so that the user doesn't have to keep going back through previously viewed invoices.

I'd really appreciate any help.
Thank you!

[Jim Doherty]

I've been struggling with a form. On the left side of the form I've got data from a table of invoices, and on the right side I've got a combo box with data from a table of payments. The user scrolls through the invoices on the left and matches each one up with a payment from the combo box on the right. The common field is an order number, but the relationship between invoices and payments must be created manually by the user matching up the records.

I have two problems:I can't seem to get the requery or refresh working properly - each time the user scrolls to a different invoice on the right, I need the combo box on the left to refresh so that it will only return the payments that correspond to that order number.

My second problem is that whenever the invoice and payment are matched up and the record is saved, the form jumps back to the very first record. I need it to move forward to the NEXT record so that the user doesn't have to keep going back through previously viewed invoices.

I'd really appreciate any help.
Thank you!

Look at the BOOKMARK property in Access, the general sequence being ....prior to 'saving' set a pointer to the bookmark...save and then return to current record to the bookmark .Plenty of references for this in help

Regards

Jim

[AppDev63]

I didn't know bookmark was the term - thank you. I'll research that in help.

Do you have any advice for my refresh/requery issue?

[Jim Doherty]

I didn't know bookmark was the term - thank you. I'll research that in help.

Do you have any advice for my refresh/requery issue?

Refreshing a combo box based on the value of another combo is very common Access stuff in the afterupdate property setting of the box on the right you should be setting the row source for the combo on the left to requery itself and return records that relate specifically to the bound value selected in the right box with something like Me!LeftComboName.requery.

The fact that your users have to manually insert the link key is somewhat perturbing because in the design process you ought to be keeping that mechanism out of reach of users and let access do it behind the scenes?

Invoices ,orders and payment systems are quite common Microsofts Northwind traders database example gives example of how link keys should behave ie no human interaction

Jim

[AppDev63]

The reason the user has to manually create the relationship between the order and the invoice is because the data is coming from two different systems and there isn't a reliable way to programmatically create the relationship. The invoice amounts are auto-billed to credit cards so an invoice is automatically created (by an outside vendor), and in our in-house system we determine whether the standard charge is the only one that will apply for that month, or whether we need to bill the client for an additional amount. Therefore we have multiple payments for each order.

[Jim Doherty]

The reason the user has to manually create the relationship between the order and the invoice is because the data is coming from two different systems and there isn't a reliable way to programmatically create the relationship. The invoice amounts are auto-billed to credit cards so an invoice is automatically created (by an outside vendor), and in our in-house system we determine whether the standard charge is the only one that will apply for that month, or whether we need to bill the client for an additional amount. Therefore we have multiple payments for each order.

okey cokey that explains it much better I mistakenly took it to be all done it Access I apologise unreservedly if I appeared to be overly critical

Jim

[AppDev63]

No need to apologize - I'm not that good at Access, but I'm not completely hopeless. :-) I really appreciate your help and advice - everything is working perfectly now.

Thanks very much!
Donna