Advanced LookUp and Append

[andysgirl8800]

First of all, thanks to everyone for participating in this site. It has been the most helpful by far that I've come across. That said, I am seeking assitance yet again.

I am trying to do something of a lookup function and am not sure where to start.

I have a field on my form called booktype that will already have a value in it, like "Fiction". I'd like to add a button that, when clicked, will look on a seperate table, tblBooks, find "Fiction" in the list (column 1), and insert its related data (column 2) into a text box txtCategory on my form as appended data, so that the user would see the data, like "Thriller, Romance, Childrens," etc in the text box.

So, ultimately, I want to append the data in the second column of the tblBooks related to the search word (i.e. "Fiction") to the table on which the form is built, tblBookRequest, in the txtCategory field. After the data is appended, I need to be able to edit it within the text box without it affecting the saved data on the tblBooks, hence the need to append it.

Finally, I also need the ablility to add a new book type if it is not found in the tblBooks list. So, if "Fiction" was not in the table, I'd like a pop up form to add it to the table for current and future use.

I've read many posts about DLookUp functions, NotInList edits, and append queries, but I can't seem to find something that will accomplish what I need. Any input would be greatly appreciated. Thanks in advance for your help!

 

[wiklendt]

you may need a many-many table instead of Dlookup.... not entirely sure of what you're wanting to happen, you description is quite confusing.

I have a field on my form called booktype that will already have a value in it, like "Fiction". I'd like to add a button that, when clicked, will look on a seperate table, tblBooks, find "Fiction" in the list (column 1), and insert its related data (column 2) into a text box txtCategory on my form as appended data, so that the user would see the data, like "Thriller, Romance, Childrens," etc in the text box.

sounds like you need a combo box instead for that field, where the source is the tblCategories. this will allow your user to simply use a drop-down box to select the catergory of the book.

So, ultimately, I want to append the data in the second column of the tblBooks related to the search word (i.e. "Fiction") to the table on which the form is built, tblBookRequest, in the txtCategory field. After the data is appended, I need to be able to edit it within the text box without it affecting the saved data on the tblBooks, hence the need to append it.

do you mean you want to be able to search for all books within a catergory? i would not put this in the same form as your add book form (which i presume is what the first question was about?) i would have a separate form, where you have a listbox of your categories, and another listbox containing the books that are included in that category...

 

[andysgirl8800]

Thanks for your reply! I realize it may have been confusing. Perhaps if I explained a little better. I upload existing book orders from a spreadsheet from another department. This order already has the book type listed. So when I import it into my database, it's already in that field as "fiction". I am trying to limit the total amount of data entry, so I'd like to keep the data that I've transferred. That's why I need a lookup kind of function. When the button is clicked, I'd like it to look at what is in the book type field, find it's match in the table, and copy over the related data into another field in my form. Does that make any better sense?

 

[wiklendt]

ok, you need to normalise the data. there has been an application made by Uncle Gizmo, made to automate this process for imported data, exactly for situations like you find yourself in now.

here are the instructions/info and application:
http://www.access-programmers.co.uk/forums/showthread.php?t=143986

 

[wiklendt]

also, what i think you are wanting is to save the book type of the book you are currently viewing in a form by selecting from a list of types available. if this is so, then it will easily be done (AFTER you normalise your data) with a combobox - no searching or buttons required.

 

[andysgirl8800]

Thanks wiklendt. My data is normalised. The problem I've encountered with combo boxes is that the data is not permanently moved to new field, but rather is "viewed" in that field and cannot be edited. I will need to be able to edit the data that the combo box populates in a way that is customized to each record, but will not affect the saved data.

In other words, if I choose "Fiction" from a list of values, and it populates "Crime, Horror, Romance, Children, Historic, Military," I need to be able to delete "Crime" and "Historic" and add "Sci-Fi", depending on what the customer has ordered already, or wants to discard voluntarily from their choices, but I don't want to alter the saved selections. If I choose "Fiction" for another order, or another record, I want the same 6 list of "Crime, Horror, Romance, Children, Historic, Military," to be available again for editing. Does that make sense? I'm trying to find the best way to do this automatically.

I should also mention that a "book list" is only an example. I am working with confidential data that I cannot post here. A book list best demonstrates what I am trying to do. Thanks for your help with the normalising and thanks for taking the time to reply.

 

[wiklendt]

no worries - i enjoy problems, and enjoy access, so this is really right up my interests ;-)

i'm not sure i understand - is this going on an invoice? or a request for particular categories or something? i understand you're using an analogy of book list, so that's all i can follow with: see, if the customer has ordered things, each book, i would assume, has already been pre-defined with a category and genre, so a query/filter should bring up the book category and genres already.... i'm lost when you say "discard voluntarily from their choices".... is this being used as a marketing tool?

these categories look like you have a heirarchy.... i.e., either fiction or non-fiction. now, fiction can be crime, but so can non-fiction. so you'll need a dropdown for category, and then sounds like you need a Many-to-Many table for genre, so that a book can be "crime" and "thriller" and "historic" (say) at the same time. the fiction or non-fiction is a separate field in the book table (as a lookup from a tblCategories).

so there should be a separate lookup (table) for category (fiction, non-fiction, etc) and a separate lookup (table) for genre (crime, romance, etc) as one is not directly related to the other... and then in your form you would have a dropdown for category and a subform (of the M-M table) for genre.

i presume at the moment you are using memo or text fields - don't, you'll regret it later. use the M-M option. the M-M table then uses dropdowns to select "crime" and/or "sci-fi", etc... (you can use a not-in-list event for easily adding new genres)

a report is then easily achieved with the proper query source.

am i getting closer to what you need...?

 

[andysgirl8800]

*L* Closer....yes. Perhaps a different analogy would help. Again, I am importing my data from another source, after normalising and automating the import process of course. I think you are getting closer with the heirarchal assumption.

My target field will already contain data. Lets say it will contain the word "bicycle". I also have my table, called "tblModeofTransport", in which there are only 2 columns, "type" and "details". It may look something like this:

Type ..........Details
_____________________
Auto ..........SUV, convertable, van, compact, truck, golf cart, hybrid
Train ..........commuter, tourist, metro, underground, steam, electric
Bicycle .......recumbant, stationary, motorcycle, scooter, tricycle
Boat...........yacht, row, speed, sail, canoe, catamaran, kyak, cruise liner
Plane..........bi-plane, concorde, F-16, 747, propeller, sea plane, private jet


"Bicycle" is in this table. Because it is already imported from the external data, I'd like to avoid having to input it again, if possible. If I have to use a combo box, so be it. Ideally, because "Bicycle" is already there, I'd like to create a function (macro, module, button, etc) that when performed, will look at "bicycle", compare it to the "tblModeofTransport", see "recumbant, stationary, motorcycle, scooter, tricycle" as its details, and append it to my second table upon which the form is built, "tblEquipmentRental" in the field "EquipmentDetails".

Once the information is in that field, I'd then like to be alble to manually edit the details so that I can remove "motorcycle" and "scooter" and type in "kid trailer". That way, these edits are tied only to the record I'm working with and will not alter the information in the "tblModeofTransport". That's why I (believe) I need to actually append the data to my "tblEquipmentRental" so as not to corrupt the original data.

The customer may want to rent ALL of those pieces of equipment, only some of those items, or may suggest some that are not already in the list. Their edits would be unique to their situation. Is that any more helpful? Again, thanks tremendously for your assistance!

 

[wiklendt]

ok, that DOES make more sense...

here's what i think you need to do.

you need to split your tblModeOfTransport into individual tables. this will be better than transferring text to a memo box because you keep data separate, and then you'll be able to keep track of how much each customer/renter needs to pay (one who rents a bi-plane will pay less, presumably, than one who rents a bi-plane, a concorde, an F-16, a 747, a propeller plane, a sea plane, and a private jet) - extracting that sort of data from a memo box is more effort than it's worth, so you really want to make sure access stores each transportation device for ease of downstream uses.

e.g.:

tblBicycle
BicycleID (PK; autonumber)
BicycleType (text; the data would then be individual fields with "recumbant", "stationary", "motorcycle", "scooter", "tricycle")

tblAuto
AutoID (PK; autonumber)
AutoType (text; etc...)

etc, etc....

i would then have a form with each tables as the recordsource for a multi-select listbox, where you can select any/all the items in each group. if you have many many more than just 5 groups a more elegant approach may be required here, but i'd start with that.

i'm not sure how to make access store multi-selection for each customer, you can do a search in these forums as i know the question has been asked before. alternatively, you can have two listboxes for each transport group, an "available" list and a "selected" list. you can then send (by OnDoubleClick event) each item as required from the available to selected listbox.

================================

if you REALLY don't want to change your tables, i would have a listbox with tblModeOfTransport as your source. you can then have an OnDoubleClick event, where the selection of one of these passes the data of the second column into the memo box, where you can then change the words (be careful, a list/combo box only stores 255 chars, if your fields have more or are likely to have more than 255 chars you will need to play with recordsets)

================================

see if any of those ideas takes yoru fancy and we can run with one... (i DO still consider myself an amateur [take note my total number of post-to-date is actually less than yours!!], so i am very welcome for a guru to jump in and say "WRONG WRONG- THIS IS THE WAY TO DO IT!!")....

 

[wiklendt]

oh, if you go the "available" and "Selected" way, keep in mind to make the recordsource for your listboxes a query, coz you'll need to use the finctionality of queries to make the setup work... actually, i've been told all listboxes/combo/forms/reports/etc/etc should be based on queries, but i'm not sure what the harm is every now and then to use tables...?

 

[wiklendt]

i browsed the sample databases available in this forum: see this helps you (the title sounded like it might)

multiselect listboxes to reports
http://www.access-programmers.co.uk/forums/showthread.php?t=105911

i also remember seeing a post for someone doing an equipment hire database or similar, which sounds like exactly what you need (or need an example of)- i've done a couple of searches but can't find it. i think it had attachments, so i'm focusing on those... i'll keep my ear to the ground and let you know if i come across it again... it SEEMed like a good one at the time... but it wasn't related to what i wanted so i didn't make more of a mental note about it... i think it had something to do with listboxes, actually, i'll do some more seraches with that as a keyword...

- maybe even a booking/hiring example would be good to look at for you... there are issues you may need to be aware of such as making sure you don't "double-rent" something (i.e., if you've rented something out, don't allow someone else to rent it too... b/c they won't be able to) a simple "double booking" keyword in this forum search will bring up lots of threads already covered in that topic...

 

[andysgirl8800]

Ok, that was helpful. Thanks for the info.

alternatively, you can have two listboxes for each transport group, an "available" list and a "selected" list. you can then send (by OnDoubleClick event) each item as required from the available to selected listbox.

How do I do this? I think THIS may help me and may be able to accomplish what I need. Unfortunately, sample DBs don't help me a whole lot because I cannot unzip and view the files due to the complicated firewalls on my company's network.

 

[wiklendt]

sample DBs don't help me a whole lot because I cannot unzip

do you mean you can download them, but just not unzip them?

it really IS better to get a sample database b/c explaining it in words is a WHOLE lot more complicated than seeing it first hand in a working example....

i can email you the sample database that i used to develop my listbox from (i WOULD send you my db, but it has confidential info PLUS is about 4mb, as opposed to this TRUE sample db, which has lots of sample listbox stuff, and is only 300 kb...) if that makes it easier.

then you should have a good look at the sample and i can answer any questions you have about the available and selected scenario (seeing as i've successfully put one together myself from this sample)

 

[wiklendt]

(or you can download from home... even on dialup 300kb is not too bad..?)

 

[andysgirl8800]

If you could email a small sample, not in zipped format, I can view it, I think. I completely understand that "pictures are worth a thousand words" and descriptions can get complicated and confusing. The firewalls on my work computer and the administrative restrictions prohibit unzipping files. I can usually DL them, but I cannot unzip them without administrator rights, again, due to the confidentiality of the information that I work with. Document attachements can be viewed, so long as they are not compressed.

I would LOVE to see an example of the double-click, double list method that you described. I think that will serve my purposes. I am still trying to skip a step though (if I understand the process correctly) in that I would still be transporting the item name from an external spreadsheet, which is a process that I have streamlined already. Can I avoid the step of selecting the item name from a drop down in order to populate the selection options? In other words, since the item name will already be there, can I automate the field focus so that it already recognises the user input as if the item was selected from the dorp down?

Thanks tremendously for your help!

 

[wiklendt]

If you could email a small sample, not in zipped format, I can view it, I think.

what's your email? you can PM me that, so that you're not publicly announcing sensitive info ;-)

I am still trying to skip a step though (if I understand the process correctly) in that I would still be transporting the item name from an external spreadsheet, which is a process that I have streamlined already. Can I avoid the step of selecting the item name from a drop down in order to populate the selection options? In other words, since the item name will already be there, can I automate the field focus so that it already recognises the user input as if the item was selected from the drop down?

i guess you could add code like what you would use in a cascading combo situation... but i've never imported from excel, so im' not sure if it's easily integrated. and how you present this external data. (i've never played with external sources, other than images, so not sure how to advise here with any certainty of workability!)

if you are, say, importing it to a local table, then it would be available as a query criteria. so, eg, in the query for your rowsource for the listbox, you could have a criteria saying

[forms]![frmYourForm]![yourControlName]

in the correct column... where yourControlName is the control that holds the information of item name... do you follow? anyway, see where the db gets you and if you have more questions, that's what we're all here for!

 

[wiklendt]

also let me know which version of access you are using, so that i may send you the appropriate format....

 

[andysgirl8800]

Alright, I've been messing around with some code and have my 2 list boxes, "listboxA" and "listboxB". When I use the ADD button to move an item from "listboxA" to "listboxB", it will move over. But if I try to add a second item from the list, it replaces the first item I moved over. How can I get them both to stay there so I can add more?

Both list boxes have the Multi Select property set to "Extended", but I'm not sure if there are other properties that are not set correctly. Also, listboxA is bound to a query, but listboxB is currently unbound. I suspect herein lies the problem. Help??

 

[andysgirl8800]

Also, can I comprise a list of items in which each item in the list is linked to a seperate query, so that when it is selected, it will display a new list of values that can be selected?

For example, a drop down combo box with grocery store categories, like canned goods, paper products, produce, etc. If produce is selected, my "listboxA" populates with a list of things like apples, bananas, peppers, tomatoes, pears, onions, etc, that I can then move items over to my "listboxB" for my final list, which will stay with the record permanently. Can this be done? And how?

 

[wiklendt]

hey andysgirl

just curious: this progress you've made, any of this from the file i sent? or have you just jumped into the deep end....?

also, can you please post the code you're using for these listboxes? we won't be able to trouble shoot until we know what the problem may be (not just the undesirable effect).

For example, a drop down combo box with grocery store categories, like canned goods, paper products, produce, etc. If produce is selected, my "listboxA" populates with a list of things like apples, bananas, peppers, tomatoes, pears, onions, etc, that I can then move items over to my "listboxB" for my final list, which will stay with the record permanently. Can this be done? And how?

this is commonly known as the "cascading combo" - you would use the same principle for listboxes. do a search here on these forums for 'cascading combo' and you'll get more info than i think you'll want! LOL

Both list boxes have the Multi Select property set to "Extended"

if you're only moving one list item at a time, there is no reason to set the listbox property to multiselect at all (let alone extended). if you want/need multiselect then i cannot help you b/c i've never worked with this function of listboxes personally. perhaps there is something in the file i sent you, but i'm not sure. if you do a search on these forums for "multiselect listbox" i'm sure you'll be inundated with threads of similar problems, and hopefully find a solution.

But if I try to add a second item from the list, it replaces the first item I moved over. How can I get them both to stay there so I can add more?

this is where looking at your code would help. however, the way to get the listboxes to work is to have them append and delete (depending on whether you are adding or removing items) a many-many table. i don't have my database with me at the moment (my partner has my laptop for this week while she's away on a study course), so i cannot recall what the second listbox is bound to, but my gut feeling is that it is bound to the many-many table via a query? -> a guru may have to correct me there.

sounds like you're getting close, though, andysgirl. and if you did this off your own bat i'm very impressed with your efforts so far. once you paste your code in, and explain what's what, perhaps the eureka moment won't be too far off! :-D