Limit to List - Set to No, Problem

[Mary]

I have a combo box on a form that allows users to select names from a list.
These names are populated from a query.
The combo box has two colums, Key# and Name
Name is set to 1.802" and the Key# is set to 0"
I would like to set "limit to list" to NO but the computer won't let me.

The pop up tells me what to do but as a newer user to Access I don't
understand how to follow the seemingly simple directions.
The Pop-up reads: The first visible column, which is determined by the
ColumnWidths property isn't equal to the bound column. Adjust the
ColumnWidths property first and then set the LimitToList property.

Sounds so simple... and while I do see the column widths listed above the
limit to list parameter under the LookUp tab, I don't understand what the
computer wants from me.

Any help is greatly appreciated.

 

[Klatuu]

What version of Access are you using?

 

[Mary]

Thank you for your response, I am using Access 2003, SP3

 

[Klatuu]

If you haven't installed the hot fix for SP3,check this site:
http://support.microsoft.com/kb/945674

What you are describing, I have never seen. The Bound column property is
not determined by the first visible column of the Column Widths property.
This is something I do all the time except I have the key value as the first
column with the width set to 0" and the bound column set to 0.

If Key# is your actual field name, you should change it. Neither spaces nor
any special characters other than the underscore should be used in names. At
least put brackets around it so Access can understand it is a name:
[Key#]

 

[Mary]

Thank you Dave, I'll try the hotfix and see if that solves the problem. Your
advice on naming is duly noted, learning bit by bit... I really appreciate
your time and help.

Thanks again.

If you haven't installed the hot fix for SP3,check this site:
http://support.microsoft.com/kb/945674

What you are describing, I have never seen. The Bound column property is
not determined by the first visible column of the Column Widths property.
This is something I do all the time except I have the key value as the first
column with the width set to 0" and the bound column set to 0.

If Key# is your actual field name, you should change it. Neither spaces nor
any special characters other than the underscore should be used in names. At
least put brackets around it so Access can understand it is a name:
[Key#]
--
Dave Hargis, Microsoft Access MVP

Thank you for your response, I am using Access 2003, SP3

 

[Klatuu]

I just realized I had it backwards. You are trying to set it to No.
Sorry, that is what happen when you set it to know under the circumstances
you describe. You will have to do what the instructions state or leave it as
Yes.

Why are you wanting to set it to no?
Is it a bound or unbound combo?

Again, I apologize for misunderstanding the post.

--
Dave Hargis, Microsoft Access MVP

Thank you Dave, I'll try the hotfix and see if that solves the problem. Your
advice on naming is duly noted, learning bit by bit... I really appreciate
your time and help.

Thanks again.

If you haven't installed the hot fix for SP3,check this site:
http://support.microsoft.com/kb/945674

What you are describing, I have never seen. The Bound column property is
not determined by the first visible column of the Column Widths property.
This is something I do all the time except I have the key value as the first
column with the width set to 0" and the bound column set to 0.

If Key# is your actual field name, you should change it. Neither spaces nor
any special characters other than the underscore should be used in names. At
least put brackets around it so Access can understand it is a name:
[Key#]
--
Dave Hargis, Microsoft Access MVP

Thank you for your response, I am using Access 2003, SP3

:

What version of Access are you using?

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box on a form that allows users to select names from a list.
These names are populated from a query.
The combo box has two colums, Key# and Name
Name is set to 1.802" and the Key# is set to 0"
I would like to set "limit to list" to NO but the computer won't let me.

The pop up tells me what to do but as a newer user to Access I don't
understand how to follow the seemingly simple directions.
The Pop-up reads: The first visible column, which is determined by the
ColumnWidths property isn't equal to the bound column. Adjust the
ColumnWidths property first and then set the LimitToList property.

Sounds so simple... and while I do see the column widths listed above the
limit to list parameter under the LookUp tab, I don't understand what the
computer wants from me.

Any help is greatly appreciated.

 

[Mary]

Thank you again for the hotfix link, it is going to clear up some errors that
have been troubling me - thought it was something I was doing wrong...

I redid the combo lookup and left out the KeyNumber field and I was allowed
to set the LimitToList to "No". This will work for my purposes. My hope is
that I won't have to generate a report in the future that requires me to link
the names with the record since the primary key is off, but this particular
table should grow very slowly and I can work with it if need be. I may look
back in a few months and discover some strange thing I did wrong...

Thank you again for your time and assistance, you have already made my life
in access so much less troublesome! (goodbye error messages and sudden
closures of access)

If you haven't installed the hot fix for SP3,check this site:
http://support.microsoft.com/kb/945674

What you are describing, I have never seen. The Bound column property is
not determined by the first visible column of the Column Widths property.
This is something I do all the time except I have the key value as the first
column with the width set to 0" and the bound column set to 0.

If Key# is your actual field name, you should change it. Neither spaces nor
any special characters other than the underscore should be used in names. At
least put brackets around it so Access can understand it is a name:
[Key#]
--
Dave Hargis, Microsoft Access MVP

Thank you for your response, I am using Access 2003, SP3

 

[Ken Sheridan]

Why do you want to be able to enter values other than those listed? If you
want to add a new name, then the LimitToList property should be True (Yes)
and the control's NotInList event procedure used to insert a new row into the
referenced table when you type a name not in the list into the control.
Here's an example which does this for cities, using a combo box set up
exactly as yours was originally, inserting (subject to user confirmation) a
row into a table Cities with columns CityID (autonumber primary key) and City:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

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

Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"

strSQL = "INSERT INTO Cities(City) 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

Set cmd = Nothing

End Sub

Ken Sheridan
Stafford, England

Thank you again for the hotfix link, it is going to clear up some errors that
have been troubling me - thought it was something I was doing wrong...

I redid the combo lookup and left out the KeyNumber field and I was allowed
to set the LimitToList to "No". This will work for my purposes. My hope is
that I won't have to generate a report in the future that requires me to link
the names with the record since the primary key is off, but this particular
table should grow very slowly and I can work with it if need be. I may look
back in a few months and discover some strange thing I did wrong...

Thank you again for your time and assistance, you have already made my life
in access so much less troublesome! (goodbye error messages and sudden
closures of access)

If you haven't installed the hot fix for SP3,check this site:
http://support.microsoft.com/kb/945674

What you are describing, I have never seen. The Bound column property is
not determined by the first visible column of the Column Widths property.
This is something I do all the time except I have the key value as the first
column with the width set to 0" and the bound column set to 0.

If Key# is your actual field name, you should change it. Neither spaces nor
any special characters other than the underscore should be used in names. At
least put brackets around it so Access can understand it is a name:
[Key#]
--
Dave Hargis, Microsoft Access MVP

Thank you for your response, I am using Access 2003, SP3

:

What version of Access are you using?

--
Dave Hargis, Microsoft Access MVP


:

I have a combo box on a form that allows users to select names from a list.
These names are populated from a query.
The combo box has two colums, Key# and Name
Name is set to 1.802" and the Key# is set to 0"
I would like to set "limit to list" to NO but the computer won't let me.

The pop up tells me what to do but as a newer user to Access I don't
understand how to follow the seemingly simple directions.
The Pop-up reads: The first visible column, which is determined by the
ColumnWidths property isn't equal to the bound column. Adjust the
ColumnWidths property first and then set the LimitToList property.

Sounds so simple... and while I do see the column widths listed above the
limit to list parameter under the LookUp tab, I don't understand what the
computer wants from me.

Any help is greatly appreciated.

 

[Bill]

I'm trying to give your code snippet a try but Access isn't liking "Dim cmd As ADODB.Command"

All along so far all I've had to use is db = database and set that too CurrentDb() in order to run and execute queries. How would I modify this snippet to work with my set up?? Or what am I missing?

Thank in advanced.

 

[Larry Linson]

In a module window, Tools | References, and be sure the reference for
Microsoft ADO is checked. Should it be ADO.Command rather than
ADODB.Command?

Larry Linson
Microsoft Office Access MVP

 

[Ken Sheridan]

You are quite right about the need for a reference, to the Microsoft ActiveX
Data Objects Library in this case. The library is referenced as ADODB.

Or DAO could be used:

Dim dbs AS DAO.Database
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set dbs = CurrentDb
Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

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

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

Since virtually all machines these days are going to have ADO installed on
them, another option would be to use Late Binding, so that no reference is
required. Of course, you then need to provide the values for any ADO
constants you might be using.

 

[David W. Fenton]

Bill wrote in

I'm trying to give your code snippet a try but Access isn't liking
"Dim cmd As ADODB.Command"

What makes you think you need ADO in the first place?

All along so far all I've had to use is db = database and set that
too CurrentDb() in order to run and execute queries. How would I
modify this snippet to work with my set up?? Or what am I
missing?

CurrentDB is a function/method provided in two places in the Access
object hierarchy, under Application and under DAO. If you're using
CurrentDB then you shouldn't be mixing in ADO.

 

[David W. Fenton]

Since virtually all machines these days are going to have ADO
installed on them,

But every machine without exception that has Access installed has
DAO, so why bother with ADO?

another option would be to use Late Binding, so that no reference
is required. Of course, you then need to provide the values for
any ADO constants you might be using.

Why use ADO in the first place? There are fewer than a half dozen
things that ADO provides that DAO lacks.