Lookup Field Problem 1

[kevmeister123]

Hi there, I have a problem with my brand new database. I was quite excited and actually proud of the database until i found a serious flaw in it. I can't filter or search through most of it because many of the fields refer to lookup tables, and when i search a field referencing a look up I get "Type Mismatch in Expression"

I have googled this and found that it is not possible to search these fields, but recommends instead using a form to list the values in a combo box, but when the value is selected, it is "stamped" into the table rather than the table just displaying the lookup value.

I have no idea how to do this or implement this into my database, could someone please help.

Many Thanks

Kevin

 

[PHV]

Have a look here:

http://www.mvps.org/access/lookupfields.htm

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kevmeister123]

Hi there PHV.

Thanks for your response, It's not through lack of trying or lazyness but i just dont understand :O(

I already found the first link prior to coming here and is the reason i gave up on lookup fields.

I also have already read the page at the second link (as it's been mentioned many times before) I have actually read and re-read that page like 10 times now and am not really any closer to understanding it. I understand basically what it says, but don't know how to apply any of it to my situation. It was a miricle when i got my database functioning the way it does, using two different main tables and joining them in the relationships window.

The last link is interesting but after about page 3 it gets technical and the technical jargon goes straight over my head. Also where does all this sql stuff get typed in, i can't find how to do it in a table. I found the sql option in a query, but don't want to use query's for entering data etc.
Also is sql the only way to create the form based list in a combo?
Is it not possible to simply select the combo box, tell it where to get it's information for the list, then select the destination of the data once selected? writing it like that sounds so easy, do i really have to learn a new programming language just for this one application?

Thanks again for your help, I'm sorry if i appear a little "thick". it's just this database stuff since day one has confused the hell out of me.

Kevin

 

[PHV]

this database stuff since day one has confused the hell out of me
So, take a course.

 

[MajP]

Kevin
I do not have Access on this computer, but I looked at your DB. One of your lookups uses a table of names (I do not remember what you called it, but I will call it "tblPersonnel"). In your table you had two fields, lets call it "autoPersonnelID" , and "strName". So your data looked like

autoPersonnelID strName
1 John Smith
2 Mike Brown
3 Steve Robinson

Now in your main table you saved a value from tblPersonnel, using a lookup. The lookup is a little evil. It draws the records from tblPersonnel, but what you do not see is that it draws both fields. Unless you specify otherwords it hides the first column (autoPersonnelID) and shows strName. When you select a value it actually puts in the value of the first column. So when you pick John Smith it actually puts 1 into the table, but through formatting of the lookup it shows John Smith. You can see this by building a form and changing the combo box to a text box. You will then see a 1.

So normally DB designers do not let people type directly into tables, but into forms. So you can still have lookup like capability (comboboxes), but on the form. That way if I save 1 in my table I will see a 1 when I look at it, but see John Smith on my form.

So how to fix it. I would go ahead and turn the lookups off in your table. Now your tables will show the data that is really in there. For data entry on a form, you can have a combo box. The combobox will do what the lookup did. It will draw both fields, hide the autoPersonnelID, show strName, but store autoPersonnelID. The wizard should show you how.

In a combo or list box the "rowsource" tells it where to get the records to display, the "controlsource" tells it which field to save the value in (or display if a value is present). If you want to use a combobox for a search control, you do not want to save the value into the table so ensure that there is no field listed in the "controlsource" property. This is called an "unbound" control because it is not tied to a field.

Once you have a unbound "search" control, you will have to write a little VBA. (Again I do not have Acess open, but I thought the Wizard will build a search control. I will need to look. I do not use the wizard). The code needs to fire on the after update of the search control so as you select a value it will find that record. In your case the user will select John Smith from the combo, the combo actually returns 1 and search the main table for record/s with 1 in them.
Provide a little more info on where you want this search control to go, and what you want to search.

 

[PHV]

I thought the Wizard will build a search control
3rd option of the combo wizard.