Alternative to Look-Up

[Sklyn]

Hi,
I'm hearing that Look-up fields are not so good to use but i want to use the
feature to ensure accuracy in the data, as some of the users are not good
spellers.
I have read to use combo box and value list but can't get it to work.
Either the list of potential data doesn't show up or it won't create a new
record.
I'm am a pretty new user to Access but trying to wrap my head around it,
moving from spreadsheets.

Some background info:
My database is keeping track of fleet vehicles.
I have a main Table & Form "Vehicle Details" using VIN as Primary Key.
On the Form are sub forms "Rego History", "Service History", and "Accident
History"
There are also other Tables/Forms containing other data such as Financial
info, Disposal Info, Other Info.
All tables are Related through VIN#, Subforms 1-many, and others 1-1.

Basically when the user is inputting data for a new vehicle i want them to
be able to select the make (for example) (ie. Toyota) from a list to ensure
it is spelled the same for easy searching and queries. They also need to be
able to add to this list if a New Make is purchased.

I can do this with Look-Up using tbl_Make, but what is a better way to do
this?

Thankyou for any help.

 

[Sklyn]

sorry, forgot to mention I'm using Access 2003.

 

[John W. Vinson]

Basically when the user is inputting data for a new vehicle i want them to
be able to select the make (for example) (ie. Toyota) from a list to ensure
it is spelled the same for easy searching and queries. They also need to be
able to add to this list if a New Make is purchased.

There is absolutely NOTHING wrong with using combo boxes on a Form ("lookups"
if you wish). It's routine, it's normal practice, it's a Very Good Idea.

What people object to is using the "Lookup Wizard" in Tables. Tables are
designed to store data; Forms are used to interact with data. A Lookup Field
in a Table just conceals the actual content of the table and causes confusion.

SO, absolutely - use a Form; use Combo Boxes for Make and Model; use the combo
box's Not In List event if needed to add a new value to the table.

 

[Sklyn]

Yes thats what i meant, trying to get away from look-ups in tables, sorry
could have been clearer.

I just can't quite get the combo box on the form to work right.
I have mostly overcome my previous issue but now i need to be able to allow
the user to add to the list if they need to so that it will also show on the
drop down list.

I have tried the 3 Row Source Types, and they can add data not on the list
but it won't add to the list.

Ur help is appreciated =)

 

[Clif McIrvin]

I have tried the 3 Row Source Types, and they can add data not on the

list
but it won't add to the list.

At a guess, the new data has in fact been added to the table, but is not
showing up in the combo drop-down because the combo's row-source needs
to be requeried after the new record is added.

Something along the lines of

Me.[name of combobox control].Requery

in the NotInList code.

 

[John W. Vinson]

I just can't quite get the combo box on the form to work right.
I have mostly overcome my previous issue but now i need to be able to allow
the user to add to the list if they need to so that it will also show on the
drop down list.

I have tried the 3 Row Source Types, and they can add data not on the list
but it won't add to the list.

um... is there a new RowSourceType that I don't know? The two I'm familiar
with are Value List (which I use very rarely, and which isn't easily edited)
and Table/Query (99% of my combos use it). For a table or query row source,
you can set the combo's Limit to List property to True and use the "Not In
List" event to pop up a data entry form to add new records. See the links
Bruce posted for examples of how to do this.

 

[John W. Vinson]

Access 2003 has a field list. I think earlier versions did, too.

ahhh... right you are. And ditto on its (lack of) usefulness!

 

[David W. Fenton]

ahhh... right you are. And ditto on its (lack of) usefulness!

I've used it in an app where I was allowing the user to create a
custom export. I populated the dropdown list with the list of fields
in a query, and then deisigned the export around that. I don't even
recall how it was actually done, but it was a medical study results
database and the users would need to export arbitrary data sets with
ad hoc choice of variables, so it was the best solution.

It never went into production, though. A management change causes it
to be abandoned at 90% completion. *sigh*

 

[Sklyn]

Thanks for you advice guys, but I'm still havin trouble. I am a complete
novice when it comes to code and I am obviously doing something wrong, so if
I could get some precise steps would be greatly appreciated.

I have used a DB that my mate built which added to the list when it was
closed so next time you open it the new entry was there, no popup windows or
other steps at all, or do you think its better with a seperate form to add to
list?

So, I should be using Table/Query..
Lets say for the Combo box to enter the vehicle Make
my table with the "Makes" is called t_make.
Should this table have a primary key on autonumber?
Also do i need to have a seperate table for each list or can I have 1 table
for all my combo boxes to refer to?
Row source is t_make, or do I need to create a query for this?
Bound Column 1?
Limit to list Yes

Then in Event, Go down to On Not in List
Select [Event procedure] ? then click the ... button and paste in code.
or use builder ?

My database is named Fleet vehicles.

To use the Not In List event the Limit To List property needs to be set to
Yes. Use something like the code found here to add items:

http://www.mvps.org/access/forms/frm0015.htm

Another approach may be found here:

http://www.datastrat.com/Code/NotInListCode.txt

I have not used the second approach, but have had good results with the first.

Yes thats what i meant, trying to get away from look-ups in tables, sorry
could have been clearer.

I just can't quite get the combo box on the form to work right.
I have mostly overcome my previous issue but now i need to be able to allow
the user to add to the list if they need to so that it will also show on the
drop down list.

I have tried the 3 Row Source Types, and they can add data not on the list
but it won't add to the list.

Ur help is appreciated =)

Basically when the user is inputting data for a new vehicle i want them to
be able to select the make (for example) (ie. Toyota) from a list to ensure

[quoted text clipped - 10 lines]

SO, absolutely - use a Form; use Combo Boxes for Make and Model; use the combo
box's Not In List event if needed to add a new value to the table.

 

[Rudolf Lamour]

Thanks for you advice guys, but I'm still havin trouble. I am a complete
novice when it comes to code and I am obviously doing something wrong, so
if
I could get some precise steps would be greatly appreciated.

I have used a DB that my mate built which added to the list when it was
closed so next time you open it the new entry was there, no popup windows
or
other steps at all, or do you think its better with a seperate form to add
to
list?

So, I should be using Table/Query..
Lets say for the Combo box to enter the vehicle Make
my table with the "Makes" is called t_make.
Should this table have a primary key on autonumber?
Also do i need to have a seperate table for each list or can I have 1
table
for all my combo boxes to refer to?
Row source is t_make, or do I need to create a query for this?
Bound Column 1?
Limit to list Yes

Then in Event, Go down to On Not in List
Select [Event procedure] ? then click the ... button and paste in code.
or use builder ?

My database is named Fleet vehicles.

To use the Not In List event the Limit To List property needs to be set
to
Yes. Use something like the code found here to add items:

http://www.mvps.org/access/forms/frm0015.htm

Another approach may be found here:

http://www.datastrat.com/Code/NotInListCode.txt

I have not used the second approach, but have had good results with the
first.

Yes thats what i meant, trying to get away from look-ups in tables,
sorry
could have been clearer.

I just can't quite get the combo box on the form to work right.
I have mostly overcome my previous issue but now i need to be able to
allow
the user to add to the list if they need to so that it will also show on
the
drop down list.

I have tried the 3 Row Source Types, and they can add data not on the
list
but it won't add to the list.

Ur help is appreciated =)

Basically when the user is inputting data for a new vehicle i want
them to
be able to select the make (for example) (ie. Toyota) from a list to
ensure
[quoted text clipped - 10 lines]
SO, absolutely - use a Form; use Combo Boxes for Make and Model; use
the combo
box's Not In List event if needed to add a new value to the table.

 

[Sklyn]

Thankyou to everyone for your fantastic advice and assistance.
You make it a pleasure learning Access..
Your time is very much appreciated.
Thanks again..

Whether you need a separate makeID autonumber primary key for the t_make
table depends on whether the make values are distinct, i.e., can the same
term apply to more than one make of vehicle. I'd have thought this unlikely.
Values such as city names can legitimately be duplicated for instance, but if
another manufacturer started calling their cars Jaguars I think the lawyers
would soon be in action! So there should be no reason why you can't use the
make column as the primary key, and there are situations where it has
advantages to do so. If you do use a 'natural' key rather than a 'surrogate'
autonumber key, however, be sure to enforce cascade updates in any
relationships between the table and other tables.

The second consideration is whether the t_make table has columns other than
make. If so then the NotInList event procedure'scode would need to open a
form to add the data into these columns too (this is true with cities for
instance where you'd want to add a value in a state or region column for the
new city). I'll assume that t_make has just the one column, make.

So, a combo box to select a make would have a RowSourceType of 'Table/Query'
and a RowSource of:

SELECT make FROM t-Make ORDER BY make;

You don't need to save this query, just enter the above SQL in the properties
sheet as the RowSource property. Make sure the control's LimitToList
property is True (Yes). Its other properties can be left as the defaults.

The code for its NotInList event procedure would be like this:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list of makes?"

strSQL = "INSERT INTO t-make(make) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

You can create as many combo boxes of makes as you wish using the same table,
e.g. you might have a bound one in a form for entering vehicle data, and an
unbound one in a form for filtering records to a selected make.

Ken Sheridan
Stafford, England

Thanks for you advice guys, but I'm still havin trouble. I am a complete
novice when it comes to code and I am obviously doing something wrong, so if
I could get some precise steps would be greatly appreciated.

I have used a DB that my mate built which added to the list when it was
closed so next time you open it the new entry was there, no popup windows or
other steps at all, or do you think its better with a seperate form to add to
list?

So, I should be using Table/Query..
Lets say for the Combo box to enter the vehicle Make
my table with the "Makes" is called t_make.
Should this table have a primary key on autonumber?
Also do i need to have a seperate table for each list or can I have 1 table
for all my combo boxes to refer to?
Row source is t_make, or do I need to create a query for this?
Bound Column 1?
Limit to list Yes

Then in Event, Go down to On Not in List
Select [Event procedure] ? then click the ... button and paste in code.
or use builder ?

My database is named Fleet vehicles.

To use the Not In List event the Limit To List property needs to be set to
Yes. Use something like the code found here to add items:

[quoted text clipped - 25 lines]

SO, absolutely - use a Form; use Combo Boxes for Make and Model; use the combo
box's Not In List event if needed to add a new value to the table.