One last issue in DB: Can't update text

[chickenfriedsteak]

Sorry to bug the list so much, I've got one last issue I can't work
out, and my brain is fried (by the time this is done, my queries will
likely be pulling over 12,000,000 records and I'm on brain drain mode
right now).

I've finally got all three combo boxes in my last post synchronized
(long story short: I'm using three combo boxes to drill down deeper
and deeper through a SQL query joining three tables, successively
going down through Server Name, then Share Name, then the individual
user - resulting in a text box populated with that user's access
rights to that share on that server). My problem now is that when I
select an entry on the last combo box (user name), and the After
Update event procedure is triggered, I get the following error:

Run-time error '2115':

The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing Microsoft Office Access from
saving the data in the field.

There are no BeforeUpdate conditions or ValidationRule conditions set
on any of my controls, so I'm not sure why it's throwing this. Here's
the event procedure I'm doing on the final combo box After Update:

Private Sub cmbTrusteeUser_AfterUpdate()
Me.txtPermissions.SetFocus
Me.txtPermissions.Text = "SELECT Access FROM" & _
" tblSharePermissions WHERE Server_Name
= """ & _
Me.cmbServerName & _
""" AND Share_Name = """ & _
Me.cmbShareName & _
""" AND TrusteeDomain_Name = """ & _
Me.cmbTrusteeUser
End Sub

*I noted the mismatch in naming conventions between the table column
TrusteeDomain_Name and my cmbTrusteeUser, it's on my list of things to
remediate but I don't think it's causing the error.

Could this be something in the formatting of the fields in the
database tables? The field TrusteeDomain_Name has a slash in it (for
example, "MICROSOFT\BGates001", without quotes). I've tried changing
the assignment to Me.txtPermissions.Value , but then it just puts my
query statement in there.

Thanks

 

[bcap]

If you want the *result* of the query to appear in txtPermissions, rather
than the SQL statement, then you are going about it the wrong way.

Simplest approach is this:

txtPermissions = Dlookup( _
"Access", _
" tblSharePermissions", _
"Server_Name = """ & _
Me.cmbServerName & _
""" AND Share_Name = """ & _
Me.cmbShareName & _
""" AND TrusteeDomain_Name =
""" & _
Me.cmbTrusteeUser)

n.b. it's rarely necessary to use the Text property of a text box because,
as you have obviously discovered, you are forced to give it the focus first.
You can simply assign a value as I have shown above.

 

[chickenfriedsteak]

If you want the *result* of the query to appear in txtPermissions, rather
than the SQL statement, then you are going about it the wrong way.

Simplest approach is this:

 txtPermissions = Dlookup( _
                                        "Access", _
                                        " tblSharePermissions", _
                                        "Server_Name = """ & _
                                                Me.cmbServerName & _
                                                """ AND Share_Name = """ & _
                                                Me.cmbShareName & _
                                                """ AND TrusteeDomain_Name =
""" & _
                                                Me.cmbTrusteeUser)

n.b. it's rarely necessary to use the Text property of a text box because,
as you have obviously discovered, you are forced to give it the focus first.
You can simply assign a value as I have shown above.














- Show quoted text -

I'm getting error "Expected: expression" at the first & _ line carry-
over in the code you provided. Since it started bombing out, rather
than save it in my production DB, I'm back in my test one until I can
fully grasp the concept (I've never used the DLookup in Access
before).

Below is a cut and paste from my test DB; same thing as above, just
with the bad names. Why are the line carry-overs throwing this
error? I've tried it without the line carries, just running it all on
one long line and I get compile error "Expected: list separator or )"
at the Me in Me.Combo11.

Private Sub Combo17_AfterUpdate()

Me.Text24 = DLookup(& _
"Access", & _
"tblSharePermissions", & _
"Server_Name = """ & _
Me.Combo11 & _
""" AND Share_Name = """ & _
Me.Combo15 & _
""" AND TrusteeDomain_Name = """ & _
Me.Combo17)

End Sub

Is there something thrown off in the quotation marks, or maybe the
line carries? Like I said, I want to make sure I fully understand the
formatting and concept before putting into my production DB.

Thanks

 

[bcap]

If you carefully compare what you've implemented to what I posted, you will
see that you've added extra ampersands (&) where you don't need them.

The ampersand is not part of a line continuation. The underscore character
alone implements a line continuation. The ampersand is the string
concatenation operator.

 

[chickenfriedsteak]

If you carefully compare what you've implemented to what I  posted, youwill
see that you've added extra ampersands (&) where you don't need them.

The ampersand is not part of a line continuation.  The underscore character
alone implements a line continuation.  The ampersand is the string
concatenation operator.

Thanks, I caught my typos on the third time I keyed it in by hand.
The Google usenet reader must shave off the spaces after the
underscores in the line continuations, because they weren't there when
I tried to copy-and-paste.

I'm getting a different error now:

Run-time error '3075':

Syntax error in string in query expression
'Server_Name="SERVER01" AND Share_Name="ADMIN$" AND
TrusteeDomain_Name="MYDOMAIN\User001'.

Once again, I don't see where my syntax is bad because from that error
it's pulling everything correctly from the combos in my test
database. I can even find that exact record in the database by hand.
Here's what I have now (hopefully without typos):

Private Sub Combo17_AfterUpdate()

Me.Text24 = DLookup( _
"Access", _
"tblSharePermissions", _
"Server_Name=""" & _
Me.Combo11 & _
""" AND Share_Name=""" & _
Me.Combo15 & _
""" AND TrusteeDomain_Name =""" & _
Me.Combo17)

End Sub

 

[bcap]

One of us has omitted the closing quotation marks on the last line, which
should read as follows:

Me.Combo17 & """")

n.b. yes, that really is *four* double-quote marks!

If you carefully compare what you've implemented to what I posted, you
will
see that you've added extra ampersands (&) where you don't need them.

The ampersand is not part of a line continuation. The underscore character
alone implements a line continuation. The ampersand is the string
concatenation operator.

Thanks, I caught my typos on the third time I keyed it in by hand.
The Google usenet reader must shave off the spaces after the
underscores in the line continuations, because they weren't there when
I tried to copy-and-paste.

I'm getting a different error now:

Run-time error '3075':

Syntax error in string in query expression
'Server_Name="SERVER01" AND Share_Name="ADMIN$" AND
TrusteeDomain_Name="MYDOMAIN\User001'.

Once again, I don't see where my syntax is bad because from that error
it's pulling everything correctly from the combos in my test
database. I can even find that exact record in the database by hand.
Here's what I have now (hopefully without typos):

Private Sub Combo17_AfterUpdate()

Me.Text24 = DLookup( _
"Access", _
"tblSharePermissions", _
"Server_Name=""" & _
Me.Combo11 & _
""" AND Share_Name=""" & _
Me.Combo15 & _
""" AND TrusteeDomain_Name =""" & _
Me.Combo17)

End Sub

 

[chickenfriedsteak]

One of us has omitted the closing quotation marks on the last line, which
should read as follows:

Me.Combo17 & """")

n.b. yes, that really is *four* double-quote marks!

(Snip)

YARGGHHH! that fixed it.

I haven't done this much hassling and cussing at quotation marks since
the last time I had to touch something in C++.

 

[aaron.kempf]

dude you're a moron.

you're putting mountains of logic in queries-- where you need 100
copies of it--
instead of putting logic where it belongs-- on the database server.

Do you honestly think that Jet can handle 12m records?

ROFL good luck with that dude

I used to have a dozen 1m tables, in about 60 distinct applications
running in jet.
I hated it-- it doesn't work well enough.

Upsize to SQL Server and things just work

-Aaron

 

[bcap]

One of us has omitted the closing quotation marks on the last line, which
should read as follows:

Me.Combo17 & """")

n.b. yes, that really is *four* double-quote marks!
(Snip)

YARGGHHH! that fixed it.

I haven't done this much hassling and cussing at quotation marks since
the last time I had to touch something in C++.

Yes, it's fiddly, but it becomes second nature with practice. Honestly!

 

[BruceM]

Go ahead and advocate SQL server for everything down to balancing a
checkbook if you must, but stuff the name calling.

dude you're a moron.

you're putting mountains of logic in queries-- where you need 100
copies of it--
instead of putting logic where it belongs-- on the database server.

Do you honestly think that Jet can handle 12m records?

ROFL good luck with that dude

I used to have a dozen 1m tables, in about 60 distinct applications
running in jet.
I hated it-- it doesn't work well enough.

Upsize to SQL Server and things just work

-Aaron

 

[aaron.kempf]

BECAUSE OF THE FACT THAT IT IS IMPOSSIBLE TO SECURE JET, you should
try to use a database that supports security -- for everything that
you do.

Again.. SQL Server development is _EASIER_ than Jet.
Again.. SQL Server development is _EASIER_ than Jet.
Again.. SQL Server development is _EASIER_ than Jet.
That's what you don't understand.
That's what you don't understand.
That's what you don't understand.

-Aaron

 

[BruceM]

I said to stop with the name calling. Your response, as is typical, is
irrelevant.

BECAUSE OF THE FACT THAT IT IS IMPOSSIBLE TO SECURE JET, you should
try to use a database that supports security -- for everything that
you do.

Again.. SQL Server development is _EASIER_ than Jet.
Again.. SQL Server development is _EASIER_ than Jet.
Again.. SQL Server development is _EASIER_ than Jet.
That's what you don't understand.
That's what you don't understand.
That's what you don't understand.

-Aaron

 

[Tony Toews [MVP]]

Sorry to bug the list so much, I

We don't mind you posting questions. That's what we enjoy doing. Please continue to
ask questions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

uh, you're the one with a hammer and you think that everything should
be done with an obsolete database
uh, you're the one with a hammer and you think that everything should
be done with an obsolete database
uh, you're the one with a hammer and you think that everything should
be done with an obsolete database

 

[aaron.kempf]

Terry;

you're the one that's saying that 'SQL SERVER IS NEVER THE RIGHT
SOLUTION BECAUSE JET IS ALWAYS THE RIGHT SOLUTION'.
You're the one with a hammer.. and everything looks like a nail
(because all you've got is a hammer).

JET IS _NEVER_ THE RIGHT SOLUTION BECAUSE IT"S NOT STABLE.

SQL Server is _EASIER_ it is _CHEAPER_ it is _FASTER_ and it is
_BETTER_.

Build it nice or build it twice.
It's your choice.. do you want to be sodomized by SQL Server directly,
or would you prefer to get sodomized by SharePoint and _THEN_ SQL
Server?

It's all about the number of layers...
I prefer direct client-server-- because it's more powerful, and it's
easier to troubleshoot.

When Jet gets basic PROFILER capabilities, is when you can try to boss
me around!

-Aaron

 

[Marshall Barton]

No, I've just left a job which seemed to take up just about all of my time
for the last couple of years so I've been very inactive in the NGs. I've
just popped in really to have a quick nose at what people are asking about
and couldn't resist the temptation to rehash this discussion with Aaron.

Good to have you around again, Terry.

So does your job situation warrent congratulations or
sympathies?

 

[Marshall Barton]

Hi Marshall,
Mixture really, I've been made redundant but I got home, sat down and
thought "OK, how do I feel,,, " and the answer was Happy! The job was fine,
the person I worked for was a nightmare ( wanted to micro-manage but didn't
want to have problems flagged up, wanted explanations but didn't want them
in "technical jargon", etc. etc. ).

So at the moment I'm a gentleman (sic) of leisure, applying for jobs in a
lousy economic climate and nearly on the wrong side of 50.

I'm trying to work up the enthusiasm to learn ASP.NET and MS Ajax as they
seem to be the in thing. Neither of them looks very difficult but I just
can't get excited about web technologies. The Access, C#, VB, VB.NET, VBA,
Java and SQL Server skills (to get back on topic, sort of <g>) I have look
good and will integrate well with ASP.NET and Ajax so I should be OK if I
can just get some interviews under my belt.

I'm trying to keep busy knocking up a football (soccer) management system as
well at the moment, the idea is to have it available with either a SQL
Server or a Jet db as the data layer with an ADO.NET data objects layer, a
webservice as the middle layer and a C# client (initially) as the UI, the
trouble is I keep thinking of extra things to put in and not really getting
around to speccing it properly. To be honest I'm my own worst client as I
know all the things I could do with it !

I think I remember how all that feels, but it was nearly 20
years ago for me. Good luck on finding a new, interesting
and enjoyable income source. It was independent consulting
for me, which made it easy to phase into retirement over a
10+ year period. Staying active in the newsgroups helped me
stay in touch with technology so I hope you find more time
to hang around here.

As for being your own worst client, I feel like the barefoot
shoemaker myself ;-)

 

[Larry Linson]

just popped in really to have a quick nose at
what people are asking about and couldn't
resist the temptation to rehash this
discussion with Aaron.

It's good to see you back, Terry.

And, you are being so very kind to Aaron when you refer to his posts as part
of a "discussion" -- he's wearing the same set of blinders as always, which
obscure everything but SQL Server (and tend to obscure the difference
between SQL Server editions, even, sometimes).

Best of success, and luck, to you in finding a productive and profitable way
to keep yourself occupied. I was very lucky when I took a "retirement
incentive" back in the early 90s and was able to leap on the soon-released
Access bandwagon and ride the wave of high demand for a few years.

I fear there is no particular product in a similar demand stage just now --
there are some Access die-hards, some classic VB die-hards, some aspiring
dot-netters, some dot-net "experts", but no overwhelming client demand for a
particular tool. The closest might be Microsoft SharePoint -- demand seems
to be building for that (and, it's getting enough publicity and support from
Microsoft that demand may very well stay high for some time).

Larry Linson
Microsoft Office Access MVP

 

[Larry Linson]

Thanks for the welcome, it looks to me like
we only need Dev, Michka and
David Fenton to be back up to full strength

Let me see, IIRC, Dev is doing something other than Access these days,
Michka is a Microsoft employee now, working far removed from Access, but
still keeps his hand in with the Seatle Access user groups and always
attends the Access MVPs' private dinner at the MVP Summit, and David is here
with some regularity.

A few other old-timers are to be found mostly in the Microsoft-sponsored
public newsgroups, Arvin and Tony among them. That's also where you'll find
aaron even more than he's here -- microsoft.public.access.

SharePoint seems less onerous to many than DotNet, though if you want to
extend it with code, the prescribed method seems to be with Visual Studio
and dot net. But in Access 2003 and SP 2.0 you could do some cooperative
work between Access and SP, and that improved with Access 2007 and SP 3.0.
As a user with admin permissions, I've done work on
http://sp.ntpcug.org/accesssig/default.aspx for my user group... nothing at
all out of the ordinary, but made it far easier for Special Interest Group
leaders to post announcements, examples, lists, pictures, etc. than going
through the "web team" for the user groups main web site...
http://www.ntpcug.org.

Larry Linson
Microsoft Office Access MVP (since 2003)