LOOKUP In Forms

[Caddie]

Hi -

I have a form that contains contract details from a service details table, and I have a separate table that contains a Product Catalogue. What I'm trying to do is use a lookup to populate the product item # and product item description from the product catalogue table.

On the form I inserted a combobox that pulls the item# and item description fields from the product catalogue. When I view the form I am able to use the drop down arrow to select the appropriate product item #; however I can't figure out how to pull the information for the product description. I know it is stored in the combobox because I can see it.

Can someone please assist me with this? Also to complicate things what I'd like to do is allow the user the ability to "edit" the item description if need be so there is an item description field in the service detail table that this information needs to be stored in.

I hope this makes sense.

Here is the code I have so far:

Private Sub Combo29_AfterUpdate()
Me.[Item Number] = Me.Combo29.Column(0)
Me.[Item Description] = Me.Combo29.Column(1)
End Sub

In the combobox I have the bound column set to 0 (the first column is also the primary key), the column count is 2.

Shouldn't this insert the item description from the combobox into the item description field on the service details table?

Thanks so much!

 

[dkinley]

Hello.

Everything looks fine, except I am not so hot on how you have things labeled.

Let's make some assumptions.

- In the [Product Catalogue] table you have two fields, [Item Number] and [Item Description].

- In the [Service Detail] table you have two fields, [Item Number] and [Item Description].

- So in the [Service Detail] form you have to controls. One is the combo box [Combo29] and the other is [Item Description].

- You select the combo box which has 3 fields, [Some ID Field], [Item Number] and [Item Description] from the [Product Catalogue] table. and the [Item Description] from the [Service Detail] form does not or will not allow an update.

See why I am not so hot on the naming scheme? In my opinion, the spaces make for unnecessary typing and makes it difficult to differentiate control names from field names. I personally use camel case in all naming (tables/forms/queries etc). Example, tProductCatalogue, fProduct Catalogue, etc.

Moving on, let's suppose you renamed your combo box to cmbItemNum and the other control on the form to txtItemDesc.

Then update your code to look like ...

Private Sub cmbItemNum _AfterUpdate()
     Me.txtItemDesc = Me.cmbItemNum.Column(1)
End Sub

Set the column count to 2 and spacing accordingly.

Now for the control source of cmbItemNum, use [Item Number] and the control source of txtItemDesc use [Item Description].

This will allow the user to select the item number and that number will be stored in the [Item Number] field. The [Item Description] field will be initially populated with the data from the [Product Catalogue]. Because this field is sourced with an underlying field, the user can elect to add to or remove from the initial data to customize it for that order.

Hope that helps,
-dK

 

[Caddie]

Hi -

I think I've done everything you've requested but I must be missing something.

Would you mind taking a quite look at the attached to see what I'm doing wrong?

Please disregard the layout of the form, I'm trying to get it to work before I will worry about the presentation later.

Thanks so much.

 

[SOS]

Instead of

Me.Item_Description = Me.cmbitemNum.Column(1)

it should be

Me.Item_Description = Me.cmbitemNum.Column(2)

Because the Item Description is the third field.

 

[Caddie]

Hi -

I tried changing it to (2) but it's still not working. BTW, why two if I'm only pulling 2 fields? Any other suggestions?

 

[dkinley]

Actually, no you are not. You are pulling 3 fields. The ID field, the item number field, and the description field.

The fix is to go to the properties of your combo box and select the Format tab. In the number of columns, input 3. Under that, add another ;0" to hide the 3rd column.

Then as SOS says (I had it wrong because I assumed ... well I am not sure what I assumed), modify the AfterUpdate property to ...

Me.Item_Description = Me.cmbitemNum.Column(2)

You are storing the ID field, displaying the Item Number and storing the description in another field (and allowing manipulation).

-dK

 

[Caddie]

I am sincerely sorry to keep bugging you two gentlemen, but I've tried those changes (see attached) and I still can't get it to work.

If you wouldn't mind, can you please take another look?

Thanks.

 

[dkinley]

Am I missing what you want to accomplish? It seems to be working correctly now.

I opened the Service Details form and click the combo box. It auto-populated the description control correctly. I then inputted some dates (required) to 'move' to the next (new) record thus saving the one I just updated.

I checked the Service Details table and the data was there.

If your problem is storing the ID number and not the Item number, then you need to change your bound column to 2.

-dK

 

[Caddie]

Are you messing with me, really? When I use the drop down arrow I see two item numbers, when I select one the item description field stays blank. What's going on?

 

[SOS]

Worked for me - see attached revised sample.

Also, what version of Access are you using and what service packs are you at?

 

[dkinley]

Are you messing with me, really?

Not at all. I opened it in 2007 but don't think it is smart enough to figure it all out if it won't work in 2003. I say this because I never had a version in 2003 that had an issue and I currently work with some '03 versions and make changes in '07 and save it back.

It might be some of the naming stuff I alluded to. Confusion in the field name vs control name .... I have no idea ...

Here are some things that might of help so you can eliminate any potential problems as you delve deeper into the development cycle, though:

http://www.mvps.org/access/general/gen0012.htm
http://www.allenbrowne.com/AppIssueBadWord.html

-dK

 

[Caddie]

I'm using Access 2007. This is very confusing, how could it work for you two, but not for me?

 

[dkinley]

No idea ... here is my information:

Access 2007 (12.0.4518.1014) MSO (12.0.4518.1014)

Again, this the way I've always set this stuff up without an issue. Check this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=184965

I just posted a sample DB there. Now, the form is unbound and such but just to see if it works for you. There is really no difference in what you want to do and the sample except store the data in a bound form.

-dK

 

[Caddie]

Okay, something strange is happening with my Access then, I opened the file you uploaded in the other post and it isn't working for me. How do you check the service pack information on Access?

 

[SOS]

Are you on Service Pack 2 for Access 2007? If so, you probably need this hotfix.

 

[Caddie]

Wow, I'm at a loss now. I was hoping the HOTFIX would fix my problem but it didn't. There must be something strange going on.

 

[dkinley]

The only thing I can think of is that you have custom formatting in the underlying tables, but this was supposed to only effect Service Pack 3. To my knowledge there is no hotfix for this issue as of yet.

You can attempt to remove the custom formatting in the tables (and apply it on the form controls) to see if that provides resolution.

-dK

 

[Caddie]

It's a good thought, but I don't think that's the case because it isn't working with the db that you sent me either.

 

[Caddie]

GOT IT! There was a "warning" message in Access that said some "features" were not installed Click Here for options, I did and now it works!

Thanks guys!

 

[SOS]

Great! (whew! glad that is over )