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

Select multiple records on continuous form

PhilOfWalton
Expert 100+
P: 1,430
I have a continuous form showing expenditure transactions, broken down into what the money was spent on.



In the underlying table there is a "TransID" AutoNumber, a "TransNo" which generally is the next number up unless there is a combined transaction in which case they are the same, and a "Combined" - Yes/No

In the case of the 2 transactions dated 13th November, this was a single visit to a supermarket where food costing £25.74 was bought and £50 cashback. I want to show this as a single amount of -£75.74 in the transaction column as per this picture.
At the moment I am doing this manually by ticking the “Combined” column and changing the “Trans No” to the same value.

It’s messy because I do checks in the AfterUpdate that if the Combined is set to true there must be more than one transaction and also a check that the Trans Nos are the same.
What I want to do is select both records (highlighted – as if I were going to delete them) and apply the setting Combined = True and setting the Trans No to the first number of the group by pressing a command button.
So, has anyone got an elegant solution storing the relevant transaction IDs

Interestingly, if on the OnCurrent, I do a Debug.Print TransID, and highlight a number of records, it only prints the first record in the series.

Phil
Nov 17 '16 #1

✓ answered by jforbes

I'm not sure the Combined Column is getting you much. That might be because I don't fully understand its purpose. If it is just to identify that a record is part of a combined transaction, then I would change the field from a Boolean to an Integer and call it something like SubTrans. That way you would basically have a Compound Key with the TransNo and SubTrans:
Expand|Select|Wrap|Line Numbers
  1. 13/11/2016 1715 1 Cash Withdrawl
  2. 13/11/2016 1715 2 Food & Drink
  3. 14/11/2016 1716 1 Banco Santander(Abbey)
  4. 15/11/2016 1717 1 Domestic & General(Fridge)
By doing something like this, you can do a couple things to make data entry easier
  • Defaulting the SubTrans based on what is entered into the TransNo Field, in the AfterUpdate Event. So when a duplicate TransNo is entered, the next available SubTrans is found and populated into the SubTrans.
  • You could also create the SubTrans Field as a ComboBox with the numbers 1 through 20, to make it easer than having to type the value in, if it is even needed. People like to use their mouse.

If you do something like this, you don't need to do anything elaborate. If you still would like to explore options, that's cool also.

Share this Question
Share on Google+
3 Replies


jforbes
Expert 100+
P: 1,107
I'm not sure the Combined Column is getting you much. That might be because I don't fully understand its purpose. If it is just to identify that a record is part of a combined transaction, then I would change the field from a Boolean to an Integer and call it something like SubTrans. That way you would basically have a Compound Key with the TransNo and SubTrans:
Expand|Select|Wrap|Line Numbers
  1. 13/11/2016 1715 1 Cash Withdrawl
  2. 13/11/2016 1715 2 Food & Drink
  3. 14/11/2016 1716 1 Banco Santander(Abbey)
  4. 15/11/2016 1717 1 Domestic & General(Fridge)
By doing something like this, you can do a couple things to make data entry easier
  • Defaulting the SubTrans based on what is entered into the TransNo Field, in the AfterUpdate Event. So when a duplicate TransNo is entered, the next available SubTrans is found and populated into the SubTrans.
  • You could also create the SubTrans Field as a ComboBox with the numbers 1 through 20, to make it easer than having to type the value in, if it is even needed. People like to use their mouse.

If you do something like this, you don't need to do anything elaborate. If you still would like to explore options, that's cool also.
Nov 17 '16 #2

PhilOfWalton
Expert 100+
P: 1,430
Spot on, thanks.
What I will do is set the default value of SubTrans to 1, then if I alter a subsequent TransNo to the previous TransNo, it would automatically increment the second SubTrans.

Many thanks for a simple solution

Phil
Nov 17 '16 #3

jforbes
Expert 100+
P: 1,107
I'm glad I could help!
Nov 18 '16 #4

Post your reply

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