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

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" <owner (AT) chickenfriedsteak (DOT) us> wrote

On Mar 18, 2:06*pm, "bcap" <b... (AT) nospam (DOT) nowhere> wrote:
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

"chickenfriedsteak" <owner (AT) chickenfriedsteak (DOT) us> wrote

On Mar 18, 2:06 pm, "bcap" <b... (AT) nospam (DOT) nowhere> wrote:

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.

On Mar 18, 3:22*pm, "bcap" <b... (AT) nospam (DOT) nowhere> wrote:
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

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!

"chickenfriedsteak" <owner (AT) chickenfriedsteak (DOT) us> wrote

On Mar 18, 3:22 pm, "bcap" <b... (AT) nospam (DOT) nowhere> wrote:
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

On Mar 18, 3:43*pm, "bcap" <b... (AT) nospam (DOT) nowhere> wrote:

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

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




On Mar 18, 12:57*pm, chickenfriedsteak <ow... (AT) chickenfriedsteak (DOT) us>
wrote:

"chickenfriedsteak" <owner (AT) chickenfriedsteak (DOT) us> wrote

On Mar 18, 3:43 pm, "bcap" <b... (AT) nospam (DOT) nowhere> wrote:

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

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

<aaron.kempf (AT) gmail (DOT) com> wrote

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




On Mar 18, 12:57 pm, chickenfriedsteak <ow... (AT) chickenfriedsteak (DOT) us>
wrote: