Add Item to Combo Box Using OnNotinList Event

G

Guest

I am working with the example code written by Dev Ashish from The Access Web
site and I have run into a brick wall.

I am trying to debug the following line:
Set rs = db.OpenRecordset("City (table) Query", dbOpenDynaset)
When I run the test the following error message pops up:
An error occurred. Please try again.
No other information is provided.

The following is the code sequence that I have at this time:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub City_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available City Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("City (table) Query", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!CityName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Any assistance that can be offered will be greatly appreciated.
 
G

Guest

Robert,

What is the name of the table, is it really City(Table)Query?

My guess is that you should only name the referring table as your source so
it could be something like this: Set rs = db.OpenRecordset("City",
dbOpenDynaset)

If it's a query you are using choose that name. If neither is the case try
renaming the source without the brackets.

Maurice
 
G

Guest

Maurice,

Thank you for responding.

The actual name of the query is "City (table) Query", additionally the name
of the table is City (table). Either way I get the same result.

If I remove the "()" the message error changes to a Run-time error '3078',
The Microsoft Jet database engine cannot find the input table or query "City
table" in this case. Make sure it exists and that its name is spelled
correctly.

I am unable to reach beyond these two issues.
 
D

Douglas J. Steele

When you have embedded spaces or special characters, you need to enclose the
table or field name in square brackets.

Set rs = db.OpenRecordset("[City (table) Query]", dbOpenDynaset)
 
G

Guest

Douglas,

Thanks for responding.

I just tried that again and again received the Run-time error '3078' as
decribed below.
--
Robert


Douglas J. Steele said:
When you have embedded spaces or special characters, you need to enclose the
table or field name in square brackets.

Set rs = db.OpenRecordset("[City (table) Query]", dbOpenDynaset)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)
Click to expand...
 
G

Guest

I tried it and I got the same error, I stop getting it when I wrote the full
select

Set rs = db.OpenRecordset("Select * From [City (table) Query]", dbOpenDynaset)


Note: make sure that the table name is still the same

--
Good Luck
BS"D


Robert said:
Douglas,

Thanks for responding.

I just tried that again and again received the Run-time error '3078' as
decribed below.
--
Robert


Douglas J. Steele said:
When you have embedded spaces or special characters, you need to enclose the
table or field name in square brackets.

Set rs = db.OpenRecordset("[City (table) Query]", dbOpenDynaset)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Robert said:
Maurice,

Thank you for responding.

The actual name of the query is "City (table) Query", additionally the
name
of the table is City (table). Either way I get the same result.

If I remove the "()" the message error changes to a Run-time error '3078',
The Microsoft Jet database engine cannot find the input table or query
"City
table" in this case. Make sure it exists and that its name is spelled
correctly.

I am unable to reach beyond these two issues.
--
Robert


:

Robert,

What is the name of the table, is it really City(Table)Query?

My guess is that you should only name the referring table as your source
so
it could be something like this: Set rs = db.OpenRecordset("City",
dbOpenDynaset)

If it's a query you are using choose that name. If neither is the case
try
renaming the source without the brackets.

Maurice

:

I am working with the example code written by Dev Ashish from The
Access Web
site and I have run into a brick wall.

I am trying to debug the following line:
Set rs = db.OpenRecordset("City (table) Query", dbOpenDynaset)
When I run the test the following error message pops up:
An error occurred. Please try again.
No other information is provided.

The following is the code sequence that I have at this time:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub City_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available City Name " &
vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the
current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to
re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("City (table) Query", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!CityName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Any assistance that can be offered will be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Robert,

Ofer has just given you a resolution to your problem but I still want to
point out to you the fact that the name of the table is not all that
workable. I know it might be that you got the structure of someone else but
if it is your design please reconsider renaming the table. My guess would be
that it won't be the last time you will bump into brackets, semicolons atc..

Mauirce

Ofer Cohen said:
I tried it and I got the same error, I stop getting it when I wrote the full
select

Set rs = db.OpenRecordset("Select * From [City (table) Query]", dbOpenDynaset)


Note: make sure that the table name is still the same

--
Good Luck
BS"D


Robert said:
Douglas,

Thanks for responding.

I just tried that again and again received the Run-time error '3078' as
decribed below.
--
Robert


Douglas J. Steele said:
When you have embedded spaces or special characters, you need to enclose the
table or field name in square brackets.

Set rs = db.OpenRecordset("[City (table) Query]", dbOpenDynaset)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Maurice,

Thank you for responding.

The actual name of the query is "City (table) Query", additionally the
name
of the table is City (table). Either way I get the same result.

If I remove the "()" the message error changes to a Run-time error '3078',
The Microsoft Jet database engine cannot find the input table or query
"City
table" in this case. Make sure it exists and that its name is spelled
correctly.

I am unable to reach beyond these two issues.
--
Robert


:

Robert,

What is the name of the table, is it really City(Table)Query?

My guess is that you should only name the referring table as your source
so
it could be something like this: Set rs = db.OpenRecordset("City",
dbOpenDynaset)

If it's a query you are using choose that name. If neither is the case
try
renaming the source without the brackets.

Maurice

:

I am working with the example code written by Dev Ashish from The
Access Web
site and I have run into a brick wall.

I am trying to debug the following line:
Set rs = db.OpenRecordset("City (table) Query", dbOpenDynaset)
When I run the test the following error message pops up:
An error occurred. Please try again.
No other information is provided.

The following is the code sequence that I have at this time:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub City_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available City Name " &
vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the
current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to
re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("City (table) Query", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!CityName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Any assistance that can be offered will be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NotInList not firing ? 1
Combo Box to add items to a table 4
Combo Box Adding to List--Error 91 2
Not in list requery issue 4
Help With Code Please 5
NotInList Problem 3
Not in list not working 1
Not In List 2 values 5

Top