Conditions in subform

[Rockape]

Hi all.

Grateful for some advice.


I have a form and a subform. The subform lists a number of records in continuous form format.

This comprises of items bought. each item has a supplier, an invoice number, product, cost, received and paid.

Note in this list there may be several records which have the same invoice number but different product. Needless to say that the supplier is the same for each invoice eg.

[Supp]: [invoice no] : [product] : [rec'd] : [pay]
ABC : 12345 : forks : y : blank
ABC : 12345 : spoons : y : blank
XYZ : 12346 : knives : y : blank
ABC : 12347 : tablecloth : y : blank

The problem I am having is that i would like to tag , for example all invoices pertaining to ABC. However I would like a condition that if I select two different suppliers some form of message box would appear preventing the user to proceed.

Grateful for ideas

 

[gemma-the-husky]

can you just have a supplier drop down in the main form, or subform header or footer, and use that. Then you can only get one active selection.

 

[Rockape]

hi gemma,

not quite the idea...the list of records is a list of all the invoices. the idea is simply to tag those that I want to include.

 

[VilaRestal]

Then a multiselect list box instead

Some code on its update event to check only one supplier is selected within the selection.

That would be the easiest way

 

[boblarson]

Then a multiselect list box instead

Some code on its update event to check only one supplier is selected within the selection.

That would be the easiest way

To build on this suggestion you could put some code in the click event of the listbox to set its row source again and only to the supplier selected. Then all others wouldn't even be there to select from.

 

[Rockape]

Hi

thats a good idea but can the suggestion of disabling the ineligible records be incorporated in my scenario, i.e. the moment say I tag supplier ABC, then say all other suppliers are disabled?

 

[VilaRestal]

Yes with Bob's suggestion you would in effect hide all other entries for other suppliers.
It would take a bit of code. You're going to need to reselect the first selection after its made because changing the row source will unselect it.
And you're going to need to allow users to unselect all, which would change the rowsource back to showing all.

 

[Rockape]

Hi VilaRestal,

Thanks, yes that would be great. so far the idea is to

1. select an invoice.
2. Those records which do not match the supplier for that record would either be hidden.
3. I could then select from those invoices that i would want to select.
4. a reset button to reselect those that are hidden..


How can I do that???

 

[VilaRestal]

Hehe,

It won't be simple but not too complex either.

First get a multiselect listbox with the appropriate rowsource

Then you're going to need to be constructing the code in its AfterUpdate event (or Click)

And as you say a reset button. The code for that will simply put the listboxes recordset back to the original and set its value to null.

Have a think yourself about the code needed to change the rowsource of the listbox to show only those suppliers equal to the supplier in the item selected. Google 'access multiselect listbox' if you're not familiar with them.

 

[gemma-the-husky]

rockape

are you trying to physically set a flag in the invoice details table, or something?

why?

you can just have a query that filters the rows for the current customer and selected supplier. I just don't see why a simple combo box or list box doesn't solve your problem.

what do you want to do with the selections for the selected supplier after you make the selection?

 

[VilaRestal]

It would indeed be easier to have a combobox listing suppliers, which when one is chosen changes the rowsource of a multiselect list box to show only those items for that supplier.

The more complex way of one multiselect list box that initially shows all but hides items for suppliers not the same as the first selected might be more user friendly but it's potential for bugs is greater, which definitely aren't user friendly if they crop up.

 

[Rockape]

Hi Gemma,

I am trying to assign a number of invoices to a cover reference which I will enter in the main Form...


I am preparing a sample of what I'm doin so I can post it... i'll be preparing it in A03..

Thanks

rockape

are you trying to physically set a flag in the invoice details table, or something?

why?

you can just have a query that filters the rows for the current customer and selected supplier. I just don't see why a simple combo box or list box doesn't solve your problem.

what do you want to do with the selections for the selected supplier after you make the selection?

 

[Rockape]

Hi,

already have ... but I dont think its what I want! but we'll research it again..

Thanks

Hehe,

It won't be simple but not too complex either.

First get a multiselect listbox with the appropriate rowsource

Then you're going to need to be constructing the code in its AfterUpdate event (or Click)

And as you say a reset button. The code for that will simply put the listboxes recordset back to the original and set its value to null.

Have a think yourself about the code needed to change the rowsource of the listbox to show only those suppliers equal to the supplier in the item selected. Google 'access multiselect listbox' if you're not familiar with them.

 

[Rockape]

Hi,

I am attaching sample database.

Grateful for advice:

____________________

frm_LPO

The main form is where enter data related to the covering reference i.e. A No.etc.
In the subform within I enter the items bought.

________________________________________________


frm_DeptPV

In the Main Form I enter a covering number and date

The subform is there so That I can tag those LPOs that I want to assign to the coverng reference number.

_______________________________________________


Ideally I would like to tag say all the ABC and then assign the covering reference to these selected to the pertinent tables so that I can later carry out some reports.