remembering a list of records.

[Homegrownandy]

This is for a quote/invoice generating invoice. I want to know the best way for access to remember a list of selected records. These records will be selected via a listbox and then the record has a yes/no box that will update to yes.

Once I have created my "list" of records I want, I then want to create a quote. So...

I would have a quote table that describes the quote.. then a junction table between parts and that quote..



This would be as follows:

Quote_ID 1 part 2034
Quote_ID 1 part 2913
Quote ID 1 part 213

and so on...

I'm asking if this is correct because this junction table is going to become very large very quick. Input appreciated.

[John_G]

It looks like a reasonable approach. I don't think you need the Yes/No fields explicitly, though - you can use VBA to go through the items in the listbox and add records to the Junction table corresponding to the selected items. It would depend on how many parts there are to choose from.

It is perfectly normal for a junction table to have a lot of records. It's not usually an issue because you rarely if ever need to look at all of them at once time.

[pbaldy]

This may help:

http://www.baldyweb.com/MultiselectAppend.htm

[Homegrownandy]

Hey, I have the yes/no because I am just using a regular listbox. The user selects a part then presses a right arrow. This will update that part with a tick and refresh both lists I have, making it appear ion the list on the right of the screen.

Im aware of the problems this could cause so on the form open event ill have an update query that will put all parts to NO. Something like this anyway. Ill need to think about that. There are a lot of parts, and they are categorised. This will update via a dropdown box.

As long as I get in place a series of events that wont allow bad data I think ill be good. Or if not ill be back on here!

thanks.

[ssanfu]

I take it that this is not a multi-user dB? If Sally is selecting from the list box (which updates the Y/N field in the table) and John also starts selecting records using the list box, you could have a major problem on someone's invoice! Way over billed.!

[Homegrownandy]

The database is multi user but this part would only be available to one person. But I take your point. I actually did this following a tutorial and I'm beginning to realise that its probably not the best solution.

Maybe instead of doing the yes/no I should append right away for that quote. That quote can be worked on along with another as long as the quote ID is unique. The second list box could show parts that are appended. I'm sure there is a way of excluding already selected records from the first list.

[Micron]

Wouldn't a temp table work? If the db is shared, write user ID to that table also to make the association between selected records and the person using them. I assume you'll put the batch onto the invoice then update the source with some flag or invoice number then wipe the temp WHERE userid = someone.

[Homegrownandy]

I have no doubt it would work but I would like the information to always exist. That way we can amend quotes at a later date. Also we can have associated parts data generated later on, during the construction.

There's nothing set in stone but the idea is to put this data in during the quote then use the same data throughout that project for various reasons. We could also associate certain risks to certain equipment and populate a risk assessment (or part of a risk assessment).

Also. I have little experience with user ID. I've not looked into it much at all. All feedback is good though. Cheers.

Edit: I may look into the user ID anyway see if I can use it during the creation of the "quote".

[John_G]

The junction table you mentioned in the initial post will do what you need - if will give you all the parts that were used in each quote, and the reverse - all the quotes using each part - if you need it.

You don't need to have an extra "checked" field in your parts table - remember that selecting the items in a listbox does not affect the source table for the listbox rows. You can select/unselect rows or requery the listbox as required.

All you need is a bit of VBA code to add the rows to the junction table when you are finished with the quote.

[Homegrownandy]

Okay, so in theory this is all fine. I'm having a problem now excluding results from the first list.

Once a part is selected, it moves onto the list on the right of the form. The criteria for this list is; all parts in the junction table that have the query ID off the parent form.

Problem is, I'm able to append the same part multiple times. Once the part exists in the junction table for the active Quote ID then I want it to no longer be an option.

with the other method I could simply say: selected: false.

This is more difficult. I'm looking online but I can't find anything. Any help appreciated. Even if its just a search phrase.

[Micron]

Make the table field property unique (no duplicates) and suppress warnings about duplicate records. If need be, you can have a composite index by choosing more than one field to make up the index if that's what it takes to make a record unique.

[Homegrownandy]

I did think of that. But is there a way to exclude it from the first list? so its not able to be selected.

[Micron]

Make the query for the first list exclude parts that are in the second list? I'm not sure I completely grasp the structure and certainly don't know the relationships involved. This might be faster if you compact and zip a copy and post it here. Would probably result in more tips and suggestions as well.

[Homegrownandy]

Appreciate your patience. I was making it a lot more difficult than it needed to be. Thanks a lot.

[Micron]

You're welcome. You might find that you have to requery the first list if changes are made to the second one.