By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,795 Members | 1,216 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,795 IT Pros & Developers. It's quick & easy.

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

P: 12
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!
Aug 23 '07 #1
Share this Question
Share on Google+
6 Replies


Jim Doherty
Expert 100+
P: 897
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
Aug 23 '07 #2

P: 12
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?
Aug 23 '07 #3

Jim Doherty
Expert 100+
P: 897
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
Aug 23 '07 #4

P: 12
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.
Aug 24 '07 #5

Jim Doherty
Expert 100+
P: 897
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
Aug 24 '07 #6

P: 12
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
Aug 24 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.