Data Entry into Combo Box

[witchkw]

How to set a combo box where the data entry person can start entering data
and it goes to that letter or compination of letters for the user to pick
from but if they contiune to data entry a word and it is not in the combo box
list how do you set the box to allow that data entry word/words to be entered
into the combo box?

I am new to access and would need assistance in this matter. Using access
2003. Any help is greatly appreciated.

 

[Rick Brandt]

How to set a combo box where the data entry person can start entering
data and it goes to that letter or compination of letters for the
user to pick from but if they contiune to data entry a word and it is
not in the combo box list how do you set the box to allow that data
entry word/words to be entered into the combo box?

I am new to access and would need assistance in this matter. Using
access 2003. Any help is greatly appreciated.

ComboBox properties...

AutoExpand = Yes
LimitToList = No

For the second you cannot use a ComboBox that displays one column while
holding the value of another.

 

[fredg]

How to set a combo box where the data entry person can start entering data
and it goes to that letter or compination of letters for the user to pick
from but if they contiune to data entry a word and it is not in the combo box
list how do you set the box to allow that data entry word/words to be entered
into the combo box?

I am new to access and would need assistance in this matter. Using access
2003. Any help is greatly appreciated.

1) Set the Combo Box AutoExpand property to Yes.

2) Set the Combo box LimitToList property to Yes.

3) Code the Combo Box NotInList event:
' The below code assumes all you want to do is add one value to the
table, not several different values.
' Watch out for word wrap on the longer lines.

If MsgBox("Product is not in list. Add it?", vbOKCancel) = vbOK Then
Dim strSQL As String

strSQL = " INSERT INTO YourTable(FieldName) SELECT """ & NewData &
""";"

CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.ComboName= Null
End If

 

[witchkw]

When I set LimitTo List to NO I received a error message advising "The First
invisible Column, which is determined by the ColumnWidths property isn't
equal to the bound column"

How do you suggest I adjust Column Widths property if that is how I would
correct this error. Else what exactly will I need to do.

 

[witchkw]

Trying this I have the form giving me a Run Time error '3192' Could not find
output table 'Complaint Table' when I try adding something new to the combo
box How do you suggest I correct this or what exactly would I need to do.

 

[Rick Brandt]

When I set LimitTo List to NO I received a error message advising
"The First invisible Column, which is determined by the ColumnWidths
property isn't equal to the bound column"

How do you suggest I adjust Column Widths property if that is how I
would correct this error. Else what exactly will I need to do.

You cannot set LimitToList to no if you are not displaying the bound column.
You need to do what Fred advised which will allow the new entry to be added
to the RowSource table.

 

[witchkw]

Thank you for the suggestion. However like I had advised back to Fred when I
do the steps he gave I received an error message of '3192' Could not find
output table 'Complaint Table' when I try adding a new item to the combo box
(a new entry that is not currently on the combo box list). Ho do you
suggesty I correct this or what exactly would I need to do so this error does
not happen.

 

[Rick Brandt]

Thank you for the suggestion. However like I had advised back to
Fred when I do the steps he gave I received an error message of
'3192' Could not find output table 'Complaint Table' when I try
adding a new item to the combo box (a new entry that is not currently
on the combo box list). Ho do you suggesty I correct this or what
exactly would I need to do so this error does not happen.

Post your exact code. Since you have a space in the name (bad idea) you
need to surround it with sqyare brackets.

 

[witchkw]

Rick,

This is what I have coded in the NoInList event

Private Sub Combo56_NotInList(NewData As String, Response As Integer)
If MsgBox("Complaint about Pratitioner or Provider not in list. Add it?",
vbOKCancel) = vbOK Then
Dim strSql As String
strSql = " Insert into ComplaintTable(ProviderComplaintAbout) select"""
& NewData & """;"

CurrentDb.Execute strSql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Combo56 = Null
End If


End Sub

Is this wrong since I get that error message?

 

[Rick Brandt]

Rick,

This is what I have coded in the NoInList event

Private Sub Combo56_NotInList(NewData As String, Response As Integer)
If MsgBox("Complaint about Pratitioner or Provider not in list. Add
it?", vbOKCancel) = vbOK Then
Dim strSql As String
strSql = " Insert into ComplaintTable(ProviderComplaintAbout)
select""" & NewData & """;"

CurrentDb.Execute strSql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Combo56 = Null
End If


End Sub

Is this wrong since I get that error message?

Perhaps Fred can chime in as it appears to me to create an invalid SQL
statement.

I never use the NotInList event. If I want to give the user the option to
add more choices to a ListBox or ComboBox I just use the Double-click event
to open a separate form that is used for that.

 

[fredg]

Perhaps Fred can chime in as it appears to me to create an invalid SQL
statement.

I never use the NotInList event. If I want to give the user the option to
add more choices to a ListBox or ComboBox I just use the Double-click event
to open a separate form that is used for that.

Is the actual table name "Complaint Table" or is it "ComplaintTable"?

The code I posted worked for me.
Perhaps try it this way, using brackets around the table and field
names as well as Values instead of Select. Make sure the names are
spelled correctly.

strSQL = " INSERT INTO [ComplaintTable] ([ProviderComplaintAbout])
Values (""" & NewData & """);"

The above assumes the new data is a text value, and
[ProviderComplaintAbout] is a Text datatype field.

 

[Rick Brandt]

The code I posted worked for me.
Perhaps try it this way, using brackets around the table and field
names as well as Values instead of Select. Make sure the names are
spelled correctly.

strSQL = " INSERT INTO [ComplaintTable] ([ProviderComplaintAbout])
Values (""" & NewData & """);"

The above assumes the new data is a text value, and
[ProviderComplaintAbout] is a Text datatype field.

I think I see the issue with your original posted code. That had SELECT
where above you have VALUES. That was the part that I was questioning.

 

[witchkw]

Fred and Rick,

I did the change you both suggested however i still get the error code of
Run-time error '3192': Could not find output table 'ComplaintTable' and when
I click the debug it takes me to the event procedure code and highlights in
yellow the line CurrentBd.Execute strSql, dbFailOnError. The Provider
ComplaintAbout field is a text field. In addition the table name is
ComplaintTable not Complaint Table with a space so I donot think that is the
problem. The form however I have this combo box on is using a query that
contains this table along with others. Could that possible cause the problem?

Both of your help is helping me alot and I really appreciate it. I now know
more then I previously knew about access. Thank you both. If you could
further help with this problem I would be greatful.
--
witchkw

Perhaps Fred can chime in as it appears to me to create an invalid SQL
statement.

I never use the NotInList event. If I want to give the user the option to
add more choices to a ListBox or ComboBox I just use the Double-click event
to open a separate form that is used for that.

Is the actual table name "Complaint Table" or is it "ComplaintTable"?

The code I posted worked for me.
Perhaps try it this way, using brackets around the table and field
names as well as Values instead of Select. Make sure the names are
spelled correctly.

strSQL = " INSERT INTO [ComplaintTable] ([ProviderComplaintAbout])
Values (""" & NewData & """);"

The above assumes the new data is a text value, and
[ProviderComplaintAbout] is a Text datatype field.

 

[fredg]

Fred and Rick,

I did the change you both suggested however i still get the error code of
Run-time error '3192': Could not find output table 'ComplaintTable' and when
I click the debug it takes me to the event procedure code and highlights in
yellow the line CurrentBd.Execute strSql, dbFailOnError. The Provider
ComplaintAbout field is a text field. In addition the table name is
ComplaintTable not Complaint Table with a space so I donot think that is the
problem. The form however I have this combo box on is using a query that
contains this table along with others. Could that possible cause the problem?

Both of your help is helping me alot and I really appreciate it. I now know
more then I previously knew about access. Thank you both. If you could
further help with this problem I would be greatful.

Do you have a reference set to the Microsoft DAO 3.6 library?

Let's make sure that's the problem.

Change the one line of code from:

CurrentDb.Execute strSql, dbFailOnError

to these 3 lines:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Does the event code work OK now?
If so, you can just leave it and use the RunSQL method,
or...
Open any code window. Click on Tools + References.
Find the "Microsoft DAO 3.6 Object Library" in the list and place a
check mark in the box alongside.
Click OK.

Now you can go back to the CurrentDb.Execute code. It should work.

 

[witchkw]

Fred adn Rick,

With these last instructions it finally worked.

Thank you both so so much I really appreciate your help.