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.

Hi Hoppy,

> At work the database resides on our server and at home it's just on my PC.

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/.../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.

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" wrote:

> 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.
>

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" wrote:

> Hi Hoppy,
>
> > At work the database resides on our server and at home it's just on my PC.
>
> 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/.../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.
>
> 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" wrote:
>
> > 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.
> >

Hi Ryan,

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

Thanks much, Hoppy

"ryguy7272" wrote:

> Here's a couple more resources for you:
> http://www.access-experts.com/defaul...lSplitDB&sm=18
>
> http://allenbrowne.com/ser-01.html
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Tom Wickerath" wrote:
>
> > Hi Hoppy,
> >
> > > At work the database resides on our server and at home it's just on my PC.
> >
> > 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/.../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.
> >
> > 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" wrote:
> >
> > > 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.
> > >

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/Ap****ueBadWord.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/
__________________________________________

"Hoppy" wrote:

> 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.

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

"Tom Wickerath" wrote:

> 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/Ap****ueBadWord.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/
> __________________________________________
>
> "Hoppy" wrote:
>
> > 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.