Solved Filter a Form with a Lookup Field that only stores the key (1 Viewer)

[kengooch]

I have several list boxes that allow a user to input a value and it then filters the form accordingly.
However, one of the fields is a lookup box and it shows the actual value, but hidden behind that is the record ID.
How can I modify the following code to read the content of the box and interpret it so that it passes the Record ID to the filter.

Form Name: fAccList
Field to filter: tSpecCode (this is the lookup field and though it might show DM, the actual Record ID is an auto field and the value is really 7 )
Text box name: luCode (this is the field where the user enters the value DM that needs to translate and understand that DM is really 7 )
In the AfterUpate Macro under the Where condition I have the following.
[tSpecCode] Like "*" & [Forms]![fAccList]![luCode] & "*"

This works fine for fields that are not linked. Because the value in the field is the exact value to look up. But Somehow if the field shows the lookup value but only stores the IDKey, it doesn't work.

I would also love to see this answer in actual VBA code if possible. For some reason I understand VBA much better than Macro's.

Thanks so much for your help.

Ken

 

[theDBguy]

Hi Ken. If the "lookup" value is a number, then you probably won't use the Like operator.

 

[Isaac]

However, one of the fields is a lookup box and it shows the actual value, but hidden behind that is the record ID.

What specifically do you mean by this? Do you mean that one of the columns holds the record ID but its width is zero, therefore it is hidden?
Can you post the SQL of the listbox's rowsource?
What is "lookup box" - is it a listbox or a combobox?

 

[kengooch]

Yes, that is exactly what I have. Here is the SQL

SELECT tSpecCode.tSpcCodeID, tSpecCode.tSpcCode, tSpecCode.tSpcCodeDesc FROM tSpecCode ORDER BY tSpecCode.tSpcCode;

 

[Isaac]

Wait .. are you saying that you have a Lookup column in your table?

 

[kengooch]

Yes, the Table that the form uses for it's data is normalized and there are several lookup fields on the form tProvider, tpathologist, tDateSF525 and tSpecCode. Staff would like to be able to select the value L or DM or any of the specific codes that are in the tSpecCode drop down list. Once selected, somehow I have to relate the text value L or DM with their corresponding actual unique record ID (tSpecCodeID) which is an autonumber field in the main able, in other words, I have to be able to tell VBA that DM is actually 7, because 7 is what is actually stored in that field, even though it shows DM.

 

[Isaac]

Ok, so you're not using Lookup columns in the table, just lookup controls on the form. Sorry to sound picky, but without precise semantics it actually makes it harder to post back and forth with understanding. I go farther than most people with that subject, in that I also try to avoid calling columns fields, preferring 'fields' to refer to something on a screen, report, or recordset, although Microsoft has made that difficult (mostly only with MS Access product!) by themselves referring to columns as fields..something that really isn't done in proper RDBMS environments.
Anyway, soapbox aside, I had just wanted to make sure you weren't using a Lookup column in the table--since I never use them as a matter of principle and know next to nothing about them.

From what you've described:

I have to be able to tell VBA that DM is actually 7, because 7 is what is actually stored in that field, even though it shows DM

it sounds like it is a matter of the BOUND column.
when you have a Rowsource of a control like Listbox or Combobox--which I think is what you mean by lookup field--you can have any number of columns in that rowsource. But only one column is the BOUND column. Let's say you go to properties and set the Bound column to column 2. That means that whenever you refer to the .Value of that control (assuming it's a single-select listbox, or a combobox), its Value property will contain the data in the BOUND column.
If you want to refer to the item selected in the [listbox or combobox], which is NOT the bound column, then you will need a more fully qualified reference to it - like

Me.ComboboxName.Column(1)

....which would show you the value in the 2nd column of the combobox (whether it was hidden or not). For a single select listbox, it's the same -

Me.ListboxName.Column(1)

...which would show you the value in 2nd column.

 

[Gasman]

Ok, so you're not using Lookup columns in the table, just lookup controls on the form. Sorry to sound picky, but without precise semantics it

My understanding is that field is a lookup field/column in the table, hence the problem.?

 

[Isaac]

My understanding is that field is a lookup field/column in the table, hence the problem.?

If that's the case then I all but give up. We keep going back and forth and it seems like each time, we apply a totally different usage of the term field, lookup, column, etc. I just asked if it was a Lookup column in a table, and got "yes it's normalized", but then "lookup field on form", which doesn't sound like a Lookup column in a Table!
Now looking at it again I am wondering if he just needs a DLOOKUP.
(Take the value from the combobox, aka "lookup field", and dlookup what the table ID column is, and then do something with that).
This would have been so simply stated.

 

[Pat Hartman]

Just get rid of the table level lookup. They were intended as a crutch for people who NEVER wrote code and were incapable of even using queries. They cause way more problems than they solve. Once you get rid of the table level lookup, you will actually know what data you are looking at.

 

[kengooch]

Hey team, So sorry to be so confusing for everyone. But here is the solution written in VBA instead of as a macro
in the field afterevent proceedure

Me.Filter = "tSpecCode = " & Nz(Me.luCode,0)
Me.FilterOn = True

In the main form Current event procedure to synch the two values enter
Me.luCode = Me.tSpecCode

The above works perfectly.

I was also tinking with the Macro side and came up with this, but I still have something wrong in it.
"tSpecCode = " & Nz([Forms]![fAccList]![luCode],0)

Do so appreciate every ones help!! Often the input., questions and comments help me to think through the process and realize things that I couldn't see without the jogging of my thinking by your insights and help.

 

[Pat Hartman]

Apparently we didn't convince you to get rid of the table level lookup field.

 

[theDBguy]

Hey team, So sorry to be so confusing for everyone. But here is the solution written in VBA instead of as a macro
in the field afterevent proceedure

Me.Filter = "tSpecCode = " & Nz(Me.luCode,0)
Me.FilterOn = True

In the main form Current event procedure to synch the two values enter
Me.luCode = Me.tSpecCode

The above works perfectly.

I was also tinking with the Macro side and came up with this, but I still have something wrong in it.
"tSpecCode = " & Nz([Forms]![fAccList]![luCode],0)

Do so appreciate every ones help!! Often the input., questions and comments help me to think through the process and realize things that I couldn't see without the jogging of my thinking by your insights and help.

Hi. Glad to hear you got it sorted out. It looks like you did get rid of the "Like" operator, after all. Cheers!