populating fields from a combo box 1

[zestril5]

I had posted a similar problem to this prior but I have changed all the parameters for what I’m doing. So here goes.

I have a form, where in, I want to be able to enter Vendor name, Item No, Item description, date purchased, Qty and Price.
I want to populate the first three fields from a Table (Item Maser List) which contains only those three values, using a combo box. The row source for the combo box is [item Master List].[Name],[Item Master List].[item no],[Item Master List].[description] Since the only thing in the Item Master List table that is unique to each record is the Item No, I made it the primary key for the table.

The control source for the combo box is Name and this fills in just fine. The control source for Item No is =[combo23].column(1) and the control source for the description is =[combo23].column(2) When I choose the name from the list in the combo box the 2 other fields populate with the first occurrence of the name in the table rather than the one I choose. For example… I have 2 entries for the name Sally’s Bird Baths, one with item no 1234 and description BATHS. the other with Sally’s Bird Baths, item no 4321 and Pedestals. Even though I choose the second entry from the table I always get the first entry from the table populating the fields.

I know this shouldn’t be a big problem but I’m not that good with this, and I’ve searched everywhere and tried everything I’ve found to no avail. What am I doing wrong?

 

[THWatson]

zestril5
It would appear to me that you are referencing the columns correctly. But some possible issues...

1. You say that the control source for the combo box is Name. I'm wondering if that's your problem. You shouldn't use Name for the name of a field because Name is an Access reserved word.

2. When you look at the Properties for the combo box, which column is the Bound column?

Tom

 

[zestril5]

I deleted the combobox that was on the form. Put on a new combobox and changed where the informaions was coming from. I changed the bound column to column 1. I also changed Name to VName. I'm not sure if changing the name was necessary because I have used it as a field name many times...but what the heck if you think it might have been a problem I'm going to follow the advise of one who knows. Putting on the new combo box and binding the column worked.
I thank you very much.
One other question. Can I get the combobox to show up sorted by Vname instead of Item no?
Thanks again

 

[THWatson]

zestril5
There are a number of reserved words in Access. One is Name. Even though you haven't run into trouble with that as yet, it will happen somewhere down the line. If you are interested, go to the Microsoft Knowledge Base and look at Article #209187. This gives a list of things we shouldn't use.

As for sorting by VName, here is the procedure...
1. In Design view click on the combo box and then its Properties
2. Click in the Row Source. You will see the SQL for the query that runs in the background behind the combo box. Either click on the elipses (the ... to the right of the combo box) or press <Ctrl>F2. That will take you to the query.
3. Click in the Sort for VName and select either Ascending or Descending, whichever you want.

You may also wish to take away the Sorting for the Item No if there is any sort ascribed there.

Tom

 

[zestril5]

I will take your advise about using "name" for a field.

The sort worked great. I knew I had done it before but just couldn't remember how.

Thank you for your help!!!

 

[zestril5]

OOPS!!
I thought everything was great but found out that although the form fields are being populated the table in which they are to post is only being populate with the first column item (out of 3).
to review for a moment. The combo box source is a table called Master item list which contains Item No, Vname and description. the form fields for these are to be populated from a combo box. Then additional information (like qty and price) are added, and all the information is to be posted into a table called Current items which is the basis of the form. Do I have to use a query here? What must be changed?

 

[THWatson]

zestril5
Let's check to see if I understand correctly...
The form is bound to a table called "Current items". This table has in it 5 fields - VName, Item no, description, quantity, price. So on your form you will have 5 text box controls that are bound to those fields. The first 3 fields are populated from the combo 23 combo box. Right so far?

If so, you need to put code on the AfterUpdate event for the combo box to populate the 3 controls. Something such as...
Me.VName = Me.combo23.column(0)
Me.[Item no] = Me.combo23.column(1)
Me.[description] = Me.combo23.column(2)

I may not have named your Current Items fields properly, but you will get the idea from this.

By the way, it's a good practice to get into not to have spaces in your table field names (e.g. use ItemNbr rather than Item no). They will work with spaces but you always have to remember to put brackets around the field names in queries, forms and reports if you include spaces.

Tom

 

[zestril5]

Thanks Tom,
As usual you advise worked but there is still a problem. I have set the form to be data entry, but only the last entry before exiting the form is posted to the table. Also the form does not clear when returned to the top as with "normal" data entry forms. Is there a setting missing?

Also thanks for the advise about field names and spaces. This may account for why some other things I have done didn't work right off. I eventually got them to work but wasn't sure why. It was probably from adding brackets.

 

[THWatson]

zestril5
So, you make entries in the form 5 different times, but only the values from the 5th entry get posted to the table, and the values from the first 4 don't?

If that's the case, it sounds as if Access isn't saving the record until you completely exit the form, and then it is saving the last one. This may also explain why the values don't clear when you advance to what should be a new record.

You could try adding a "Save Record" command button to the form, so that you press this each time before going to a new record.

Tom

 

[zestril5]

Tom, Thanks for hanging in there on this for me.

I tried adding a "Save Record" command button along with a separate "close" button. When either tabbing, using enter or even mouse clicking on the "Save Record" button, nothing happens. I took away the "close" button wiht the same results.
So I created a macro that closes the form and then opens the form in add mode. That works but of course there is the "flash" on the screen from these 2 operations. Since it's a small form on a relatiely fast computer, I can live with it if I have to, but I was hoping for a better solution.
Again, Thanks for all you help and patience.

 

[THWatson]

zestril5
Something feels strange here. It doesn't make sense to me that you have to close the form in order to save the record.

You are using the form in "data entry" mode. Have you tried using in in normal mode, to see if the same thing happens? (in other words, setting data entry to No)

This has me puzzled.

Tom

 

[THWatson]

zestril5
I just had a thought. (Maybe just a wild idea)

Do you have Name Auto Correct turned on in your Option settings General tab? If so, I suggest you turn it off.

Although Name Auto Correct might seem a desirable feature, it causes all sorts of problems. And I remembered that you changed one field from Name to VName.

I don't know whether or not this might be the problem, but it's worth a shot.
Tom

 

[THWatson]

zestril5
You said that the "Save" command button approach didn't work. Just to check the code you put behind it, the code should be either

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

or

DoCmd.RunCommand acCmdSaveRecord

Tom

 

[zestril5]

Tom, Once again you saved me. Turning off the Name auto correct in the General Options settings did the trick.
I had noticed in previous things I have done that although I had changed a name to a newer "correct" name I sometimes had to start from scratch to get things to work. So this will also be a big help in the future.
Great Tip!! Thanks again. It looks as though everything is now working fine. I really appreciate it.

 

[THWatson]

zestril5
Whew! Looks as if my wild idea had merit. Glad we got it working. I knew there had to be something funny going on.

Name Auto Correct has caused numerous problems and, according to the Microsoft web site, continues to do so even into the Access 2003 version.

Good luck!

Tom

 

[THWatson]

zestril5
I came across an interesting web site that has a number of Access tutorials. Thought you might be interested.

The link is...

http://www.functionx.com/access/

Tom