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

Ok, Jack. thanks for the not in list code - I put some of it in and modified it to refect my fields/tables/forms. It seems close to working (I get the pop-up, but it gives an error message next and doesn't create a new record in the form). I must have missed something - I didn't quite get how the two forms in the sample DB lined up with my one Item form.

I am attaching the file, and any troublehooting you can do will be welcome. As it stands now, the output for the exhibit listbox is a simple table like the one in your demo. But I have created the output table I think would be correctfor my situation - it just isn't linked to the items clicked in the listbox on the form. This is tblExhibitsSelected2.

P.S. Though I have the ItemID in a combo box on the form, I'm not sure how the filtering you suggest would apply in this situation. I'll be in all day tomorrow, so I'll check for your post then. Thanks for all your ideas.

Kim -

I was thinking about the filtering as I was driving into town and I think I went too far. I think you only need one combo box as the other FK should come from the main form...

I will be looking at your db shortly...

Jack

Kim -

Boy, was I wrong! I was thinking that there was something else involved here but I had your db confused with about 9 others!

Let me ask you a question or two and that should get me back on track... Now you have the two list boxes workng in that you can transfer data from one to the other. Do you want the left hand list box to show only exhibits that belong to the selected ItemID at the top of the form? So if ItemID is QG04 then the left list box will show Bruce and Byrd and ItemID QG30 you will see Smith, Jones and Puddington. Is that right? If that is right, and after looking at your table tblExhibitsSelected2, I think you are on the right track and I think I understand what you want.

I looked at the Not In List thing and you already have the form open so it might be better to move to a new record on the form and start entering data rather than trying to use the fancy code in the Not In List event. Will that work for you? Also, I notice that ItemID is the primary key and I would suggest that you make an Autonumber the Primary key but keep your ItemID as just another field in your table though you will use it to find data, etc. Using an autonumber as the primary and foreign key is just a bit cleaner...

Let me know if I am on the right track with the list box and what you think of the Not In List and Primary Key suggestions...

Jack

Kim -

Attached is a modified version of your database. Only the first two Items work for the list boxes, but I think this is what you are after. To get all the exhibits to show in your left list box you wll need to create a form based on the table "tblExhibitsSelected2" where you can add exhibits for specific items.

I went ahead and changed ItemID to ItemNumber and added an autonumber Primary key called ItemID. Better to do it now than after you have lots of data in the db.

I hope this is what you are after....

Jack

Dear Jack-

Thanks for all these suggestions. This is really odd, but when I open up the table you just attached, there are no list boxes for exhibits visible at all. What do you think happened?

What I want is for the list box on the left to draw from the full list of exhibits from tblExhibit. Then, the user can pick from that full list only those exhibits in which this particular item appeared. All this was working ok in the version I posted yesterday. The problem comes next:

Then, when each exhibit is selected and goes into the righthand listbox, I believe I want it to create a new record in the tblExhibitsSelected2, where each exhibit selected will be associated with the current ItemID that was active on the form. Then later, I thought I could query both the tblItem and the tblExhibitsSelected2, and merge them on their shared field ItemID to give people a report with all the information they might want to see about the item.

Also, I really like your idea of not having an event procedure for not in list at all. But when I try to create a new ItemID without it, the form won't go to a blank, new record in the table. It gets stuck on one of the records I already have in the table. How do I get it to set the tab form for data entry for a new record if the ItemID is new?

Best, and thanks,
Kim

Kim -

DANG! I got it backwards when it comes to the list boxes! I have filtered the left list box by the ItemID rather then adding the ItemID to the right list box.... OK. I will go back to your previous version of the program and see if I can't get what you want.

Sorry that I got it backwards.... As soon as I think I have it working I will post the db here and I will see what I can do about the Not In List event as wel...

Jack

Thanks Jack! How you keep all this straight is beyond me, but I am certainly grateful. I will look for a further post later.

This thread appears to be EXACTLY what I've been scratching my head over for the past week.
Unfortunately, I have Access 97 at work and Access 2000 at home.

Can you post a copy of the repaired database in one of these 2 versions so I can peek at the code?

Thanks so much!
Paul

Kim -

Here is Version3. I have not looked at the Not In List yet as I wanted you to take a look and see if I have this part right at last.... I will look at the Not In List bit in just a moment.

Jack

Welcome to Utter Access Forums!

I think you should send a PM to kbraun and ask if it is OK with me to attach to your post a copy of the database. I suppose since it is posted anyway it is OK, but it seems like the thing to do. Also, there are two versions, one in which the left combo box is filtered by the selection in the main form and one where the right list box is fitered by the selection in the main form.

The dbs posted are in Access2000.

Jack

Kim,
I just got called back into this thread and one thing that concerned me was your frequent referall to a primary key on your "form". Forms do not have primary keys, only tables have primary keys. I think you may need to look at Access a bit differently then you are to understand it better.

Access is an object oriented database. Everything in Access is an object that can be manipulated by changing its properties either manually or programatically. So a table is an object and one of its properties is the primary key. Forms are objects which contain other objects called controls. Forms may or may not be bound to a table or query. Controls on forms moy or may not be bound to fields in that table or query.

By looking at each object in Access separately, you may get a better understanding of how things work together.

HTH

Wow - a lot of interest! Thanks to all of you.

Paul - feel free to download and learn whatever you can from the database. I'm just learning Access myself, and this is a non-proprietary project I'm doing as a volunteer for my university.

Jack - I think the listbox scenario looks just right, but I can't exactly tell what will happen on the righthand listbox or table side, because I get a runtime error 3073, Operation must use an updateable query when I try to move the exhibts from the left to the right. When I try to debug it, the debugger goes to this sub: Private Sub exhibits_DblClick(Cancel As Integer) and this line: DoCmd.RunSQL mysql. What do you think?

Scott- Welcome back. You are right, I was getting sloppy with my terminology, as my focus was on explaining something very hard for me to imagine. Of course, ItemID was the key to the Item table (no longer, as I believe Jack has given me an autonumber key to keep things straight), and the form is bound to the table, and the objects on the form mirror and provide input to the fields in the table - but it is good to keep these concepts separate. Thanks for helping me keep my practice in line with the thoery behind Access.

Kim -

Hmmm. Are you using the db (ver3) I sent you? I do not get this error and if I double click or use the arrows between list boxes I do not get a code error. Now the list on the left does NOT change when you move an item to the right as I thought that it was to stay unchanged, but I am easily confused.

If you get the error with the version I sent you then try a compact and repair and see if that helps. Close out Access and then restart and see if that fixes it. The code is working here and I don't know why it is not working for you.

Let me know if you continue to have this error pop up....

Jack

Dear Jack,

You understood perfectly about not removing an exhibit from the left listbox just because it has been selected for the right.

Now for the odd bit, I've tried downloading it again (yes the new Ver3 file) compacting and reparing it, renaming it, opening it on another computer - all to no avail! Now I consistently get the error Run-time Error 3134. Syntax Error in Input Into statement. When I debug it it goes straight to that same line I mentioned before. What next? Do you think something happened to it on upload?

Best, Kim

Kim -

DANG and DRAT! Here is the code for the dblClick event in the right list box:



Here is the code for the double click in the left list box:



Compare these to what you have as the error is in the code between the For Each and DoCmd.Setwarnings code.

I have posted the db again and if this one does not work then send me an email address and I will send it that way, if you like.

Jack

Dear Jack,

I'm still having problems with that 3134 error - syntax error in INSERT INTO statement. Any thoughts? Did anyone else who downloaded have trouble with the listbox?

I'm also still having problems with my not-in-list code, so any suggestions there will also be appreciated.

Many thanks for the interest in this db,
Kim

Kim -

Send me your version of the db (I think we have done this before, but I am can't recall for sure) and I will see if I get the syntax error....

I would suggest making a duplicate of your current form and open it for the Not In List event of the combo box. At this point it seems to be the path of least resistance.... Maybe not the slickest solution, but it should work!

Jack

Dear Jack, I'm back at it again. I have created a new item entry form that is basically a copy of the old one, though in the new form I have removed the not in list event procedure from the ItemNumber combo-box. Now when I enter a new item ID in frmItem, it does pull up a blank frmItemNew. But it doesn't automatically populate the combo box in the new, blank form, as I wish it would. What is worse, when I try to put in the new item number into frmItemNew, it rejects it because it says I must pick from one in the list... Do you know how can I get the new form to pop-up with the item number I entered already in the combo box and with a a blank tab form set to that new record in the table?

On the old probem of the listboxes, I'm still getting unfortunately getting the same 3134 error - and it doesn't seem to matter what version I use it as (2000 or converted to 2003). I wish I knew what I'm doing wrong. On 3 computers now I get the same error.

All best,
Kim

Hiya Kim!

Welcome back!

I have your latest db and this is what I would like you to try...

1. Delete the Listbox to Listbox subform from the last tab in the Form frmNewItem (we can talk abou this later).
2. Rename the combo box at the top of the form from ItemNumber to cboItemNumber in frmNewItem or delete it completely, which is my suggestion.
3. Place the code below in the On Load event of the form frmNewItem:

If Not IsNull(OpenArgs) Then
Me.ItemNumber = OpenArgs
End If

4. Using the Wizard add a Close command button to frmNewItem.
5. Close and save the form.
6. Open form frmItem in design mode. Open the property sheet for the combo box at the top of form.
7. Change the "rs.FindFirst..." line of code to read:

rs.FindFirst "[ItemID] = " & Me![ItemNumber]

8. While in that code add this line of code just after the Dim rs As Object:

Me.Requery

Compact and save your db and see if it you can add a new ItemNumber by typing it into the combo box on the top of the form...

Jack

Hello Jack!

Ok, much progress so far. The new form does allow me to input data about the new item number I created. I did keep the combo box, renaming it as you suggested rather than deleting it as you recommended. However, the new number doesn't appear in the new form's combo box, which just comes up blank. Maybe this would be ok if I could get rid of the header altogether (combo box and all), and cause the new form to come up directly over the old form, making the whole process appear more seamless. I'm going to play with this now and post it soon for your comments.

Many thanks!

Kim -

Change the combo box to a Text box and then bind it to ItemNumber. Now it will show the new item number when that form opens...

Good luck and I am happy to hear that you are making progress!

Jack

Jack -This is awesome! I couldn't quite get the second form to pop up over the first form, but the text box idea worked wonders, and I think the button to save and close is pretty clear now. Maybe I should close the first form when the second opens, and then when the user saves and closes the second form, re-open the first. Do you think that would be even more friendly? How exactly would I do that, if you think it is a good plan?

Other questions - smaller - that I've been saving for when the big things were functioning (and please just say the word and I can start a new thread if these problems don't interest you):

1.) If I have a thumbnail picture file assoc. with each item (see tab 3), can I browse for the path and file from a control on the form (you know, like Windows explorer shows you the drives and you click until you find your file?), and then somehow put the file selected in into my table as an OLE object?.

2.) Is there a way to automatically change to caps whatever the user types into item number? So, cc001 automatically shows and stores as CC001?

Thanks again - you'll have to vsit one of our exhibits one day!

Best, Kim

Kim -

A couple of things you can do to get the forms to 'line up'. Open the forms Maximized and then all your troubles are over! If you do NOT want the forms maximized then use the MoveSize code (you will find it in help from the code editor) to set the forms to a specific size and place on the form. This will work to cover the first form. You can also make the popup a tad bigger and that will help too, but if this were my db I would maximize the forms...

1. You can use the CommonDialog Active X Control to open the 'browse' window so the user can click their way to the file they want. I think this control comes with all copies of Access and not just the Developers Edition... Click the More Controls button on the ToolBox menu and scroll down to see if you have "Microsoft Common Dialog Control, Version x.x"....

2. In the After Update event use the UCase() function to update the input.

Me.NameOfControl = UCase(Me.NameOfControl)

I hope this answers your questions...

Jack

Jack - I tried the Active X Control and it is there, but it says I don't have the license to use it... I wonder what I need to do to get permission to it. In any case, once you pick the file name after browsing in the control, does it actually store the OLE object itself in the table, or does it store the file names and path in the table?

Meanwhile, where do I tell a form to maximize on load? That sounds like a good idea to me.

Best, Kim

Kim -

You must have to have the Developers Edition of Access to use the Common Diaglog Control and that is an expensive proposition so it is time to look at other possibilities. The Common Dialong Control will store the path to the file in a field in your table, not the object. You do not want to store a picture in Access because the db will very quickly balloon to a huge size so what you want to do is 'link' to a picture stored on the hard drive. This article may help....

In the On Load [Event Procedure] of your forms put this code:

DoCmd.Maximize

Seems to me you are making good progress. Keep up the good work!!!

Jack

Kim -

I just found the attached demo hear at UA. It was created by Rob of R_Cubed fame and appears to browse without the Common Dialog Box. I have NOT looked at this demo carefully at all, but Rob is a good programmer so there is a good chance that this will work for you.

hth,
Jack

Kim -

You will find the post by R_Cubed here. He gives a lttle background on the demo I just sent you.

Jack

Thanks Jack! The maximize command worked just fine. I also saw how to do it in a macro as I was playing around.

I will work with the OLE stuff when I'm back next week and will post a new version for you to check out then. That demo looks like just the ticket!

Best, Kim

Kim -

Looking forward to seeing you next week... Have a great time between now and then...

Jack