Combo Box with NotInList procedure not working

H

Hoppy

Hello, I'm just learning Access programming and I wrote a database for our
office. It works fine except for one combo box on one of the forms. The
combo box DOES work on my home PC which is where I worked on this one form.
But it DOESN'T work when I install the form in the database at my office! At
work the database resides on our server and at home it's just on my PC. I've
been working on this for two weeks and can't figure out the problem. Would
appreciate any help.

There isn't an error message but the program opens up the VBA procedure and
it's highlighted with YELLOW. There's no clues as to why it stopped there.

I have the combo box Limit to List set to YES
I have the RecordSource Type set to Table/Query
The RowSource is set to COMPANYTABLE Query (it sorts Company Names
Alphabetically)
The database has TWO tables one table just for CompanyNames with one field
"Company Name"
The other table has the same field "Company Name" but also contains numerous
other fields not involved in this procedure.
The name of the combo box is "Company"

When a user enters a company that isn't on the list I want a message box to
open asking if they want to add it. If they select Yes the name is added to
the table "COMPANYTABLE" and that's it. The table gets the name sorts it
into the list of names.

The error is in the combo box procedure for the NotInlist.

Here's the code I copied from the procedure, the error occurs at the
db.Execute "INSERT INTO [COMPANYTABLE] ([COMPANY NAME]) VALUES (""" & NewData
& """)", dbFailOnError

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

Dim db As Database
Set db = CurrentDb

Response = False
Me.COMPANY = Null
msg = "The company: " & Chr(13) & Chr(13) & NewData & Chr(13) & Chr(13)
msg = msg & "is not in the list. Do you want to add it?"
typ = vbExclamation + vbYesNo
ttl = "New Item"
resp = MsgBox(msg, typ, ttl)
If resp = vbNo Then
Me.COMPANY.Undo
Response = acDataErrContinue
Else
db.Execute "INSERT INTO [COMPANYTABLE] ([COMPANY NAME]) VALUES (""" &
NewData & """)", dbFailOnError
Me.COMPANY.Requery
Me.COMPANY = NewData
Response = acDataErrAdded
End If

End Sub

Any suggestions would be very much appreciated! I have the same program on
my PC that we have at work Access 2003. However, there are permissions and
things on the database at work. Thanks in advance for any help! Let me know
if anyone needs more information.
 
T

Tom Wickerath

Hi Hoppy,
At work the database resides on our server and at home it's just on my PC.
Click to expand...

You should split the database into two files: a "front-end" (FE) application
file, and a "back-end" (BE) data file. Only the BE database should be shared
on a file server; a copy of the FE application file should be installed on
each user's local hard drive. For more information, see the section of this
paper that talks about splitting:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

There isn't an error message but the program opens up the VBA procedure and
it's highlighted with YELLOW. There's no clues as to why it stopped there.
Click to expand...

What happens if you press the F5 key when the code suspends? Does it finish
to completion as expected? If so, you have a "ghost" break point. Close
Access. Make a backup copy of your database (you already have this, right?)
using Windows Explorer. Then click on Start | Run and enter the following
command:

msaccess /decompile

Navigate to your application, and hold the Shift key down the *entire* time
that it is opening. You should open to the database window (or Navigation
Pane in Access 2007). While continuing to hold the Shift key down, do a
compact and repair operation:

Tools | Database Utilities | Compact and repair database
(or Office button, Manage, Compact and repair in A2007)

Finally, open any existing code module and re-compile your VBA code, by
clicking on Debug | Compile {ProjectName}

where {ProjectName} is the name of your VBA project.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hoppy said:
Hello, I'm just learning Access programming and I wrote a database for our
office. It works fine except for one combo box on one of the forms. The
combo box DOES work on my home PC which is where I worked on this one form.
But it DOESN'T work when I install the form in the database at my office! At
work the database resides on our server and at home it's just on my PC. I've
been working on this for two weeks and can't figure out the problem. Would
appreciate any help.

There isn't an error message but the program opens up the VBA procedure and
it's highlighted with YELLOW. There's no clues as to why it stopped there.

I have the combo box Limit to List set to YES
I have the RecordSource Type set to Table/Query
The RowSource is set to COMPANYTABLE Query (it sorts Company Names
Alphabetically)
The database has TWO tables one table just for CompanyNames with one field
"Company Name"
The other table has the same field "Company Name" but also contains numerous
other fields not involved in this procedure.
The name of the combo box is "Company"

When a user enters a company that isn't on the list I want a message box to
open asking if they want to add it. If they select Yes the name is added to
the table "COMPANYTABLE" and that's it. The table gets the name sorts it
into the list of names.

The error is in the combo box procedure for the NotInlist.

Here's the code I copied from the procedure, the error occurs at the
db.Execute "INSERT INTO [COMPANYTABLE] ([COMPANY NAME]) VALUES (""" & NewData
& """)", dbFailOnError

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

Dim db As Database
Set db = CurrentDb

Response = False
Me.COMPANY = Null
msg = "The company: " & Chr(13) & Chr(13) & NewData & Chr(13) & Chr(13)
msg = msg & "is not in the list. Do you want to add it?"
typ = vbExclamation + vbYesNo
ttl = "New Item"
resp = MsgBox(msg, typ, ttl)
If resp = vbNo Then
Me.COMPANY.Undo
Response = acDataErrContinue
Else
db.Execute "INSERT INTO [COMPANYTABLE] ([COMPANY NAME]) VALUES (""" &
NewData & """)", dbFailOnError
Me.COMPANY.Requery
Me.COMPANY = NewData
Response = acDataErrAdded
End If

End Sub

Any suggestions would be very much appreciated! I have the same program on
my PC that we have at work Access 2003. However, there are permissions and
things on the database at work. Thanks in advance for any help! Let me know
if anyone needs more information.
Click to expand...
 
H

Hoppy

Hi Tom,
Sorry about the double posts, I couldn't find my post so of course I
re-posted.

Thank you for your reply. I didn't use F5 when the code stopped, but I can
do that. and I canalso do the compact and repair. I can't create a BE and
FE because our office has "Thin Clients" no hard drive. I can do it from the
server and also re-compile the code.

Besides all that, is my code correct? I've seen examples of the INSERT INTO
and the table called isn't in brackets or parens but mine is, like I said
before it works on my PC at home and it works with or without the brackets!

Thanks for your help, it's much appreciated.
Hoppy.
Tom Wickerath said:
Hi Hoppy,
At work the database resides on our server and at home it's just on my PC.
Click to expand...

You should split the database into two files: a "front-end" (FE) application
file, and a "back-end" (BE) data file. Only the BE database should be shared
on a file server; a copy of the FE application file should be installed on
each user's local hard drive. For more information, see the section of this
paper that talks about splitting:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

There isn't an error message but the program opens up the VBA procedure and
it's highlighted with YELLOW. There's no clues as to why it stopped there.
Click to expand...

What happens if you press the F5 key when the code suspends? Does it finish
to completion as expected? If so, you have a "ghost" break point. Close
Access. Make a backup copy of your database (you already have this, right?)
using Windows Explorer. Then click on Start | Run and enter the following
command:

msaccess /decompile

Navigate to your application, and hold the Shift key down the *entire* time
that it is opening. You should open to the database window (or Navigation
Pane in Access 2007). While continuing to hold the Shift key down, do a
compact and repair operation:

Tools | Database Utilities | Compact and repair database
(or Office button, Manage, Compact and repair in A2007)

Finally, open any existing code module and re-compile your VBA code, by
clicking on Debug | Compile {ProjectName}

where {ProjectName} is the name of your VBA project.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hoppy said:
Hello, I'm just learning Access programming and I wrote a database for our
office. It works fine except for one combo box on one of the forms. The
combo box DOES work on my home PC which is where I worked on this one form.
But it DOESN'T work when I install the form in the database at my office! At
work the database resides on our server and at home it's just on my PC. I've
been working on this for two weeks and can't figure out the problem. Would
appreciate any help.

There isn't an error message but the program opens up the VBA procedure and
it's highlighted with YELLOW. There's no clues as to why it stopped there.

I have the combo box Limit to List set to YES
I have the RecordSource Type set to Table/Query
The RowSource is set to COMPANYTABLE Query (it sorts Company Names
Alphabetically)
The database has TWO tables one table just for CompanyNames with one field
"Company Name"
The other table has the same field "Company Name" but also contains numerous
other fields not involved in this procedure.
The name of the combo box is "Company"

When a user enters a company that isn't on the list I want a message box to
open asking if they want to add it. If they select Yes the name is added to
the table "COMPANYTABLE" and that's it. The table gets the name sorts it
into the list of names.

The error is in the combo box procedure for the NotInlist.

Here's the code I copied from the procedure, the error occurs at the
db.Execute "INSERT INTO [COMPANYTABLE] ([COMPANY NAME]) VALUES (""" & NewData
& """)", dbFailOnError

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

Dim db As Database
Set db = CurrentDb

Response = False
Me.COMPANY = Null
msg = "The company: " & Chr(13) & Chr(13) & NewData & Chr(13) & Chr(13)
msg = msg & "is not in the list. Do you want to add it?"
typ = vbExclamation + vbYesNo
ttl = "New Item"
resp = MsgBox(msg, typ, ttl)
If resp = vbNo Then
Me.COMPANY.Undo
Response = acDataErrContinue
Else
db.Execute "INSERT INTO [COMPANYTABLE] ([COMPANY NAME]) VALUES (""" &
NewData & """)", dbFailOnError
Me.COMPANY.Requery
Me.COMPANY = NewData
Response = acDataErrAdded
End If

End Sub

Any suggestions would be very much appreciated! I have the same program on
my PC that we have at work Access 2003. However, there are permissions and
things on the database at work. Thanks in advance for any help! Let me know
if anyone needs more information.
Click to expand...
Click to expand...
 
H

Hoppy

Hi Ryan,

Thank you for the resource links! I surely need the tutorials!

Thanks much, Hoppy

ryguy7272 said:
Here's a couple more resources for you:
http://www.access-experts.com/default.aspx?selection=TutorialSplitDB&sm=18

http://allenbrowne.com/ser-01.html


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Tom Wickerath said:
Hi Hoppy,
At work the database resides on our server and at home it's just on my PC.
Click to expand...

You should split the database into two files: a "front-end" (FE) application
file, and a "back-end" (BE) data file. Only the BE database should be shared
on a file server; a copy of the FE application file should be installed on
each user's local hard drive. For more information, see the section of this
paper that talks about splitting:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

There isn't an error message but the program opens up the VBA procedure and
it's highlighted with YELLOW. There's no clues as to why it stopped there.
Click to expand...

What happens if you press the F5 key when the code suspends? Does it finish
to completion as expected? If so, you have a "ghost" break point. Close
Access. Make a backup copy of your database (you already have this, right?)
using Windows Explorer. Then click on Start | Run and enter the following
command:

msaccess /decompile

Navigate to your application, and hold the Shift key down the *entire* time
that it is opening. You should open to the database window (or Navigation
Pane in Access 2007). While continuing to hold the Shift key down, do a
compact and repair operation:

Tools | Database Utilities | Compact and repair database
(or Office button, Manage, Compact and repair in A2007)

Finally, open any existing code module and re-compile your VBA code, by
clicking on Debug | Compile {ProjectName}

where {ProjectName} is the name of your VBA project.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hoppy said:
Hello, I'm just learning Access programming and I wrote a database for our
office. It works fine except for one combo box on one of the forms. The
combo box DOES work on my home PC which is where I worked on this one form.
But it DOESN'T work when I install the form in the database at my office! At
work the database resides on our server and at home it's just on my PC. I've
been working on this for two weeks and can't figure out the problem. Would
appreciate any help.

There isn't an error message but the program opens up the VBA procedure and
it's highlighted with YELLOW. There's no clues as to why it stopped there.

I have the combo box Limit to List set to YES
I have the RecordSource Type set to Table/Query
The RowSource is set to COMPANYTABLE Query (it sorts Company Names
Alphabetically)
The database has TWO tables one table just for CompanyNames with one field
"Company Name"
The other table has the same field "Company Name" but also contains numerous
other fields not involved in this procedure.
The name of the combo box is "Company"

When a user enters a company that isn't on the list I want a message box to
open asking if they want to add it. If they select Yes the name is added to
the table "COMPANYTABLE" and that's it. The table gets the name sorts it
into the list of names.

The error is in the combo box procedure for the NotInlist.

Here's the code I copied from the procedure, the error occurs at the
db.Execute "INSERT INTO [COMPANYTABLE] ([COMPANY NAME]) VALUES (""" & NewData
& """)", dbFailOnError

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

Dim db As Database
Set db = CurrentDb

Response = False
Me.COMPANY = Null
msg = "The company: " & Chr(13) & Chr(13) & NewData & Chr(13) & Chr(13)
msg = msg & "is not in the list. Do you want to add it?"
typ = vbExclamation + vbYesNo
ttl = "New Item"
resp = MsgBox(msg, typ, ttl)
If resp = vbNo Then
Me.COMPANY.Undo
Response = acDataErrContinue
Else
db.Execute "INSERT INTO [COMPANYTABLE] ([COMPANY NAME]) VALUES (""" &
NewData & """)", dbFailOnError
Me.COMPANY.Requery
Me.COMPANY = NewData
Response = acDataErrAdded
End If

End Sub

Any suggestions would be very much appreciated! I have the same program on
my PC that we have at work Access 2003. However, there are permissions and
things on the database at work. Thanks in advance for any help! Let me know
if anyone needs more information.
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Hoppy,

Brackets are required if you use spaces, special characters, or some
reserved words when you assign names to things in Access, including field
names, table names, names of controls on forms, and variable names. The best
practices, of course, are to avoid these types of newbie mistakes.

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

The brackets certainly won't prevent code from running correctly, even if
they are not required.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
H

Hoppy

Hi Tom,

Thank you so much for your reply, at least I know my syntax is correct! I
look forward to work on Monday so I can try your suggestions.

Thanks again and Happy New Year!
Hoppy
 

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

Insert Into error 1
Creating a Search using a combo box and then creating a report. 4
Help fixing my NotInList code for a combo box 3
Help with this Event Procedure 14
notinlist event procedure not working 1
NotInList when Split 3
Combo box 0
Strange results for NotInList combo 2

Top