I'm really confused- Despite a good suggestion on using the combo box wizard, I'm still having trouble autopopulating a series of text and combo boxes in a bound tab form I've created. The form is linked to the Item_Detail table, where a number of item-related values are stored. When the value the user enters in ItemID is found in the table already, I would like all these fields to fill in automatically on the form for editing.

Maybe what I need is some code that would do a dlookup? Sorry my knowledge is so basic - I've never needed to make such a complicated form before.

I am not sure what you are doing, but if you have the data in your table you do not want to save that data again in another table, if that is what you want to do. If you are trying to FIND a specific record to display on your form then that is something else. If you can give some more details on what it is you want to do someone here should be able to point you in the right direction.

hth,
Jack

Ok, let's see if I can say exactly what I want to do. There is an Item_Detail table that is an inventory table with a key Item ID, and many fields related to the item entity, like ItemType, Size, Format, etc. The tab data entry form "Item" which I created is bound to this table, though some of the combo boxes on the form offer a dropdown list of values that come from admin tables I created (like Format is a combo box where users choose a format from a list of values in the table AdminFormat.)

What I really want the form to do is to come up blank (which it does), then when the user enters an Item number or selects one from the combo dropdown, it is checked to see if it is new or not. If it is new, the form is clank and ready to go with a new record. If it is not new, the form pulls up all the other values associated with that ItemID record in the Item_Detail table. I believe I need an event procedure on AfterUpdate, and I think Dlookup may be the way to go. But I'm really struggling to get the right code in there. I will attach the table and form if anyone would take a look and see if they know what to do. Sorry to be so new to all this.

You can create your Item ID combo box using the Wizard and on the first screen of the Wizard select the 3rd item, "Find a record...". When you select that record the form will fill with the data from that record. If the record does not exist you can then create a new record.

You have some issues with all of your Admin tables in tha none of them have primary keys and all should have primary keys and that is what you should be storing in "Item_Detail", not the text. Also, you should use standard naming conventions for your objects and you should not use space in any field or object names. In your table "Item_Detail" i would suggest an autonumber is your primary key and use your current ItemID as an ItemNumber. Autonumbers make much better primary keys and is why you should have one for every table in your database.

I hope that I understand what you are trying to do with your form....

Jack

First, you shouldn't start a new thread when following up. This can just confuse matters.

Second, I echo everything that Jack said. You really need to work on adhering to standards.

Frankly, I don't know what's wrong with your form, but something is. However, I was able to recreate the form and get the search combo to work. I've attached a reworked copy.

Scott -

Thank you for jumping in and sorting out the form. I never even gave it a whirl...

Jack

Well I was helping in the other thread so....

Regardless, your assistance is appreciated!

Jack

Apologies - this was my first foray onto a web forum and I didn't understand that the first thread was still active.

Also, many thanks. Scott- I see the new form works well, though I do have a couple of "theoretical" questions stemming from the advice you both offered me. I certainly see the need for standard naming conventions, and that is easily fixed in any case. I am a little less clear/comfortable with abandoning the item number as the primary key that will then call up the rest of the record for editing. How does an autonumber key ensure we won't put in two variants of item number, say qg1 and QG-1, and end up with two records for the same item? I thought by somehow controlling the format of the entry (input mask?) and then making ItemID the unique identifier, we would be forced to enter each item once and only once. Could you explain the advantage of autonumber?

Finally, now that you've both seen my "dream form," any advice on how to make the two boxes on the third tab work? My idea was that on the left, there would be a list of all our exhibits (taken from an exhibit table based on the exhibit entity, which I didn't attach). On the right would be an empty box. When the user double-clicked on specific exhibits on the left, or highlighted one and clicked the arrow button in the middle, the exhibit(s) chosen would move into the righthand box and somehow be recorded in the item table for that item ID. The trouble is on two levels - how to structure the table, when I don't know how many exhibits might eventually be associated with that item ID, and how to code the boxes/button on the form to do what I want.

Anyway, that is a lot to ask, so any ideas on any part of this posting will be welcome. Thanks again for all your help!

If your Items must be unique then you will need code to validate data entry. I realize that this is a bit more work then making the ItemID the Primary Key, but generally speaking autonumbers are much better for making sure each record is uniquely identified and properly related to any related table. You are welcome to stick with your current approach, but most of us use autonumbers for our primary keys and then we validate the ItemNumber when it is entered in a new record. Code validation is not hard and is how I would suggest you approch your database, but it is YOUR choice.

Attached is a simple 'listbox to listbox' demo that you can use on your last tab...

hth,
Jack

Thanks Jack. That listbox demo looks exactly like what I want. When I downloaded it and clicked on a name on the left and the arrow to move it, though, I got an error. It says "Run time error 3073," Operation must use an updatetable query. Is this just because there is no table behind it? Anyway, it looks great, and I love the way you could double-click or use the arrow buttons.

The table "tblEmployeeSelected" holds the ID's of the employees in the right list box. The Row Source for the left combo box determines who you see in that list box. I do not know why you are getting the error, but if you use the same approach (code) in your database it should work.

Good luck and if you hit a wall just let us know....

Jack

Ok, Jack, the listbox seems to be working very well. I've changed table names, populated my tblExhibit with the fields I want, and made it so the form only shows the Exhibit Title. What makes me nervous is the tblExhibitSelected I've not got receiving and storing the choices made in the listbox. How can I best incorporate the info captured there into my big item table?

Let's say I've got a list of ExhibitIDs the user has selected, but need to associate them with a specific Item ID. I won't ever know exactly how many will be selected, but it could theoretically be as many as there are exhibits. But it would be so messy to add fields in the item table like "ExhibitID1," "ExhibitID2," "ExhibitID3," ....

Thanks again,
Kim

Kim -

You will need to create a related table and save the exhibits as single records in that table. Your table might look something like this:

tblExhibits
ExhibitsID (PK and auto)
MainExhibitID (FK)
ItemID (FK)

In the table above you would use code to add the ItemID to this table as they are selected from the list box on the left. This table is the Row Source for the list box the right but it must be a Subform so that the MainExhibitID (as I called it) can be added by Access so that when you move between exhibits you will see the items you want in the right list box.

I have done the above right out of my head so I may not be right on with everything, but it should be close. Hopefully this will get you started....

Jack

Ah, Jack, I think I see conceptually what you are saying. Let me just check myself. I have successfully made the listbox a subform in my form frmItem (I can't believe how nice it all looks now!).

Now, for the data side: tblExhibitsSelected could still serve as the "receptacle" for what was picked in the listbox, but two additional fields could be added to form a composite key as you noted, so that each record in the table will be unique.

So, it might look like this:

ExhibitID MainExhibitID ItemID
20 25 1
15 25 1
14 25 1

In this scenario, the same item was used 4 times, first in exhibit 25, then in 20, 15, and 14. When I go to report on this, I will be able to join the tblItem and tblExhibitsSelected on the ItemID. Then I can pull together all I know about the item, but don't have to have a bunch of extra fields in tblItem itself.

If this is correct, what sorts of changes will I need to make in the event code in the listboxes in my subform to get all these key values into the table? So far I've been able to "find and replace" my way around that code, but this seems like a bigger problem.

Thanks again, and I'm sorry to take so much of your time.
-Kim

Kim -

This is a bit tricky to explain so I hope I make sense...

tblExhibits
ExhibitsID (PK and auto) This is an autonumber field and is only there to make each record unique
MainExhibitID (FK) This is the Foreign Key and will be filled in by Access as you add records to this table
ItemID (FK) This field will be filled in by code like the "INSERT..." code in the demo I sent you.

Since the table above is a subform and the Master/Child links are ExhibitID (or whatever the primary key is named on the main form) then when you add the ItemID (via code) the ExhibitID will be filled in automatically by Access.

You senario is not correct, but you are on the right track. Your table might look like this

ExhibitID...MainExhibitID....ItemID
...1.............17...............1
...2.............17...............3
...3.............17...............7
...4.............3................2
...5.............3................4

In the above your ExhibitID 17 (The Battle Of Hastings) has three items, 1, 3 and 7. Exhibit 3 (The Glories Of Ancient Rome) has items 2 and 4.

I hope that makes sense and that I am not leading you down the garden path! The old brain gets a bit wonky on a busy day....

Jack

Hmm. Let me reflect on that advice. I'm a bit confused by the names you are using for these 3 IDs. My unique primary key in frmItem is ItemID, and each item may be used in many exhibits, though it is important to know which exhibit it was used in first. The key in the subform table tbl ExhibitsSelected is ExhibitID.

Unfortunately, I have to sign off now, and won't be back on this project until next week. Can I "re-activate" this thread then if I can't puzzle it out on my own?

Thanks again, Jack!

Kim -

You are welcome!

I will be around next week so just add a post to this response and I should be notified that you are around. I'm sorry that I do not have the correct field names for you db but I have deleted my copy. I suspect that you will sort this out, but if you need help I will give it a shot. NO guarantees that I can make work what I have suggested as this is all coming from my brain and NOTHING has been tested...

Jack

Ok, Jack, I'm back on the case here and I think I understand what you meant, but not how to implement it. So, in my main form, Item ID is my unique identifier. Each item can be featured in a number of exhibits. The exhibits in the list box code you gave me come from tblExhibit, where they receive an autonumber key. Now, when I go to output them, I think I want to have a table that will pick up the current Item ID from the form, the exhibit IDs that have been selected (one at a time), and this table will also have its own primary key, an autonumber.

So:

Autonumber------------FK---------FK
ExhibitsSelectedID----ItemID----ExhibitID

1-------------------------QG1------- 25
2-------------------------QG1--------5
3-------------------------QG1--------11
4-------------------------QG2--------3
5-------------------------QG2--------8

Now, here are the problems: I can create such a table easily, but how do I get the listbox code to update it properly? Also, what happens if the user goes in and removes an exhibit from an ItemID? Does the corresponding record in the tblExhibitsSelected get erased? How do I make sure of that?

If I post my .mdb file again, would you mind taking a look at it? I also have a question about how to do a "not in list" event procedure for the ItemID, that would set the form to a new, blank record.

In any case, thanks for all your advice!
-Kim

Kim -

I will do what I can to help you but I am just leaving for a few hours. I will not be back until early afternoon Pacific Time. When I return I will look at what you have so if you can post your lastest and greatest version of your db I will see what you are up too.

You will find the answers to your Not In List event here so that will give you something to fiddle with for a while. In that article you want to use method 2.

One last thing.... to update your ItemID and ExhibitID Foreign Keys you can have those be combo boxes on your form. If you put then in the right order you can 'filter' the second combo so that it only shows items from the selected exhibit or the other way round if that is how it works. This is just a thought that popped into my head if it will work then here and another article on how to 'synchronize' your combos...

I hope this helps....

Jack