RunSQL - I've searched!!

[Guest]

I have searched this newsgroup for the best solution but nothing seems to
work. I have coded the following to the AfterUpdate event of the Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse & "'"

However, it's not working, not returning anything, but no error? What's
wrong?

Thanks

 

[Douglas J Steele]

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let you trap for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse
& "'", dbFailOnError

 

[Guest]

I have a DLookup in the form field which looks up a table with the first two
characters of the post code matched with one of three warehouses, which works
perfectly. After the field looks up the value and is updated, I need that
returned value to store in another table. So the correct value is in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but not updating the
table either.

Thanks.

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let you trap for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have searched this newsgroup for the best solution but nothing seems to
work. I have coded the following to the AfterUpdate event of the Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse & "'"

However, it's not working, not returning anything, but no error? What's
wrong?

Thanks

 

[Douglas J Steele]

So you want every single row in tblContacts to have the same value for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a DLookup in the form field which looks up a table with the first two
characters of the post code matched with one of three warehouses, which works
perfectly. After the field looks up the value and is updated, I need that
returned value to store in another table. So the correct value is in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but not updating the
table either.

Thanks.

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let you trap for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have searched this newsgroup for the best solution but nothing seems to
work. I have coded the following to the AfterUpdate event of the Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse

&
"'"

However, it's not working, not returning anything, but no error? What's
wrong?

Thanks

 

[Guest]

No I want each row to have either "Kendal", "Rushden" or "Bristol" as it's
value depending on postcode.

So you want every single row in tblContacts to have the same value for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a DLookup in the form field which looks up a table with the first two
characters of the post code matched with one of three warehouses, which works
perfectly. After the field looks up the value and is updated, I need that
returned value to store in another table. So the correct value is in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but not updating the
table either.

Thanks.

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let you trap for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have searched this newsgroup for the best solution but nothing seems to
work. I have coded the following to the AfterUpdate event of the
Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse &
"'"

However, it's not working, not returning anything, but no error? What's
wrong?

Thanks

 

[Douglas J Steele]

Your UPDATE statement is incorrect then: because there's no WHERE clause,
it's going to update every row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

No I want each row to have either "Kendal", "Rushden" or "Bristol" as it's
value depending on postcode.

So you want every single row in tblContacts to have the same value for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a DLookup in the form field which looks up a table with the

first
two

characters of the post code matched with one of three warehouses,

which
works

perfectly. After the field looks up the value and is updated, I need that
returned value to store in another table. So the correct value is in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but not

updating
the

table either.

Thanks.


:

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let you trap for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have searched this newsgroup for the best solution but nothing

seems
to

work. I have coded the following to the AfterUpdate event of the
Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" &

strWarehouse
&

"'"

However, it's not working, not returning anything, but no error? What's
wrong?

Thanks

 

[Guest]

Ok, so i have to put

(in English) Warehouse Value is Kendal WHERE postcode starts with LA,
Warehouse Value is Rushden WHERE postcode starts with NE, Warehouse Value is
Bristol where postcode starts with BR, etc, etc.....except theres over 100
postcode values!!! That's why I created the table so the unbound value on the
form could look up the value in the Warehouse table. I have seen somewhere
(on a wizard I think) an option to store a certain value in a certain field,
that's exactly what i need.

Your UPDATE statement is incorrect then: because there's no WHERE clause,
it's going to update every row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

No I want each row to have either "Kendal", "Rushden" or "Bristol" as it's
value depending on postcode.

So you want every single row in tblContacts to have the same value for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a DLookup in the form field which looks up a table with the first
two
characters of the post code matched with one of three warehouses, which
works
perfectly. After the field looks up the value and is updated, I need that
returned value to store in another table. So the correct value is in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but not updating
the
table either.

Thanks.


:

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let you trap for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have searched this newsgroup for the best solution but nothing seems
to
work. I have coded the following to the AfterUpdate event of the
Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse
&
"'"

However, it's not working, not returning anything, but no error?
What's
wrong?

Thanks

 

[Douglas J Steele]

Is Postcode in tblContacts?

If so, then try something like:

CurrentDb.Execute "UPDATE [tblContacts] " & _
SET [Warehouse] = '" & strWarehouse & "'" & _
" WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError

(where strStart contains the first 2 characters of the Postcode).

If Postcode isn't in tblContacts, how do you know which rows need to be
updated?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, so i have to put

(in English) Warehouse Value is Kendal WHERE postcode starts with LA,
Warehouse Value is Rushden WHERE postcode starts with NE, Warehouse Value is
Bristol where postcode starts with BR, etc, etc.....except theres over 100
postcode values!!! That's why I created the table so the unbound value on the
form could look up the value in the Warehouse table. I have seen somewhere
(on a wizard I think) an option to store a certain value in a certain field,
that's exactly what i need.

Your UPDATE statement is incorrect then: because there's no WHERE clause,
it's going to update every row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

No I want each row to have either "Kendal", "Rushden" or "Bristol" as it's
value depending on postcode.

:

So you want every single row in tblContacts to have the same value for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a DLookup in the form field which looks up a table with the first
two
characters of the post code matched with one of three warehouses, which
works
perfectly. After the field looks up the value and is updated, I

need
that

returned value to store in another table. So the correct value is in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but not updating
the
table either.

Thanks.


:

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let you

trap
for

errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have searched this newsgroup for the best solution but

nothing
seems

to
work. I have coded the following to the AfterUpdate event of the
Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" & strWarehouse
&
"'"

However, it's not working, not returning anything, but no error?
What's
wrong?

Thanks

 

[Guest]

Postcode is in tblContacts. I have another table so that it can lookup the
value supposed to be in the Postcode field of tblContacts. That table is
tblWarehouse.

But....I'm running into guessing now, this is what I have...

Private Sub Warehouse_AfterUpdate()
strWarehouse = Me.Warehouse
strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
CurrentDb.Execute "UPDATE [tblContacts] " & SET [Warehouse] = '" &
strWarehouse & "'" & " WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError
End Sub

I know it's wrong, but this one is more difficult than I thought.

Is Postcode in tblContacts?

If so, then try something like:

CurrentDb.Execute "UPDATE [tblContacts] " & _
SET [Warehouse] = '" & strWarehouse & "'" & _
" WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError

(where strStart contains the first 2 characters of the Postcode).

If Postcode isn't in tblContacts, how do you know which rows need to be
updated?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, so i have to put

(in English) Warehouse Value is Kendal WHERE postcode starts with LA,
Warehouse Value is Rushden WHERE postcode starts with NE, Warehouse Value is
Bristol where postcode starts with BR, etc, etc.....except theres over 100
postcode values!!! That's why I created the table so the unbound value on the
form could look up the value in the Warehouse table. I have seen somewhere
(on a wizard I think) an option to store a certain value in a certain field,
that's exactly what i need.

Your UPDATE statement is incorrect then: because there's no WHERE clause,
it's going to update every row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No I want each row to have either "Kendal", "Rushden" or "Bristol" as it's
value depending on postcode.

:

So you want every single row in tblContacts to have the same value for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a DLookup in the form field which looks up a table with the
first
two
characters of the post code matched with one of three warehouses,
which
works
perfectly. After the field looks up the value and is updated, I need
that
returned value to store in another table. So the correct value is in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but not
updating
the
table either.

Thanks.


:

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let you trap
for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have searched this newsgroup for the best solution but nothing
seems
to
work. I have coded the following to the AfterUpdate event of the
Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
&
"'"

However, it's not working, not returning anything, but no error?
What's
wrong?

Thanks

 

[Douglas J Steele]

What's strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
supposed to be?

If I'm understanding correctly, it should be simply:

strStart = Left([PostCode], 2)

However, let's step back a moment.

You're looking up Warehouse in tblWarehouse and storing it in tblContacts.
Why? Isn't PostalCode in both tables (either entirely, or in part)? You have
sufficient information to join the two tables together in a query, rather
than storing the Warehouse information redundantly.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Postcode is in tblContacts. I have another table so that it can lookup the
value supposed to be in the Postcode field of tblContacts. That table is
tblWarehouse.

But....I'm running into guessing now, this is what I have...

Private Sub Warehouse_AfterUpdate()
strWarehouse = Me.Warehouse
strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
CurrentDb.Execute "UPDATE [tblContacts] " & SET [Warehouse] = '" &
strWarehouse & "'" & " WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError
End Sub

I know it's wrong, but this one is more difficult than I thought.

Is Postcode in tblContacts?

If so, then try something like:

CurrentDb.Execute "UPDATE [tblContacts] " & _
SET [Warehouse] = '" & strWarehouse & "'" & _
" WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError

(where strStart contains the first 2 characters of the Postcode).

If Postcode isn't in tblContacts, how do you know which rows need to be
updated?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, so i have to put

(in English) Warehouse Value is Kendal WHERE postcode starts with LA,
Warehouse Value is Rushden WHERE postcode starts with NE, Warehouse

Value
is

Bristol where postcode starts with BR, etc, etc.....except theres over 100
postcode values!!! That's why I created the table so the unbound value

on
the

form could look up the value in the Warehouse table. I have seen somewhere
(on a wizard I think) an option to store a certain value in a certain field,
that's exactly what i need.

:

Your UPDATE statement is incorrect then: because there's no WHERE clause,
it's going to update every row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No I want each row to have either "Kendal", "Rushden" or "Bristol"

as
it's

value depending on postcode.

:

So you want every single row in tblContacts to have the same

value
for

Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a DLookup in the form field which looks up a table with the
first
two
characters of the post code matched with one of three warehouses,
which
works
perfectly. After the field looks up the value and is updated,

I
need

that
returned value to store in another table. So the correct

value is
in

strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but not
updating
the
table either.

Thanks.


:

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let

you
trap

for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have searched this newsgroup for the best solution but nothing
seems
to
work. I have coded the following to the AfterUpdate event

of
the

Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
&
"'"

However, it's not working, not returning anything, but no error?
What's
wrong?

Thanks

 

[Guest]

Ok, for someone thats new at coding last month, I'm getting there.

Postcode is in both tables, however, in tblContacts the data entry person
enters a postcode (eg, LA9 6NH, M3 5EE, NE1 E55) and in tblWarehouse, I have
about 120 records under two fields, PostcodeChar (char short for characters)
and Warehouse. In PostcodeChar I have stored LA, M3, NE, etc, etc and in the
Warehouse column I have put the closest warehouse to the postal code AREA.
If i were to store all the postcodes in Britain I'd have thousands upon
thousands of records and it's just not required.

Can you suggest a better way to do this?

Cheers

What's strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
supposed to be?

If I'm understanding correctly, it should be simply:

strStart = Left([PostCode], 2)

However, let's step back a moment.

You're looking up Warehouse in tblWarehouse and storing it in tblContacts.
Why? Isn't PostalCode in both tables (either entirely, or in part)? You have
sufficient information to join the two tables together in a query, rather
than storing the Warehouse information redundantly.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Postcode is in tblContacts. I have another table so that it can lookup the
value supposed to be in the Postcode field of tblContacts. That table is
tblWarehouse.

But....I'm running into guessing now, this is what I have...

Private Sub Warehouse_AfterUpdate()
strWarehouse = Me.Warehouse
strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
CurrentDb.Execute "UPDATE [tblContacts] " & SET [Warehouse] = '" &
strWarehouse & "'" & " WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError
End Sub

I know it's wrong, but this one is more difficult than I thought.

Is Postcode in tblContacts?

If so, then try something like:

CurrentDb.Execute "UPDATE [tblContacts] " & _
SET [Warehouse] = '" & strWarehouse & "'" & _
" WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError

(where strStart contains the first 2 characters of the Postcode).

If Postcode isn't in tblContacts, how do you know which rows need to be
updated?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok, so i have to put

(in English) Warehouse Value is Kendal WHERE postcode starts with LA,
Warehouse Value is Rushden WHERE postcode starts with NE, Warehouse Value
is
Bristol where postcode starts with BR, etc, etc.....except theres over 100
postcode values!!! That's why I created the table so the unbound value on
the
form could look up the value in the Warehouse table. I have seen
somewhere
(on a wizard I think) an option to store a certain value in a certain
field,
that's exactly what i need.

:

Your UPDATE statement is incorrect then: because there's no WHERE
clause,
it's going to update every row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No I want each row to have either "Kendal", "Rushden" or "Bristol" as
it's
value depending on postcode.

:

So you want every single row in tblContacts to have the same value
for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a DLookup in the form field which looks up a table with the
first
two
characters of the post code matched with one of three warehouses,
which
works
perfectly. After the field looks up the value and is updated, I
need
that
returned value to store in another table. So the correct value is
in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but not
updating
the
table either.

Thanks.


:

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let you
trap
for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have searched this newsgroup for the best solution but
nothing
seems
to
work. I have coded the following to the AfterUpdate event of
the
Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
&
"'"

However, it's not working, not returning anything, but no
error?
What's
wrong?

Thanks

 

[Douglas J Steele]

SELECT tblContacts.Field1, tblContacts.Field2, tblWarehouse.Warehouse
FROM tblContacts
LEFT JOIN tblWarehouse
ON Left(tblContacts.Postcode, 2) = tblWarehouse.PostcodeChar

This query will return a every row in tblContacts, with the Warehouse if
there's a match, or Null if there isn't.

You'll have to go into the SQL view to add the Left function into the ON
statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, for someone thats new at coding last month, I'm getting there.

Postcode is in both tables, however, in tblContacts the data entry person
enters a postcode (eg, LA9 6NH, M3 5EE, NE1 E55) and in tblWarehouse, I have
about 120 records under two fields, PostcodeChar (char short for characters)
and Warehouse. In PostcodeChar I have stored LA, M3, NE, etc, etc and in the
Warehouse column I have put the closest warehouse to the postal code AREA.
If i were to store all the postcodes in Britain I'd have thousands upon
thousands of records and it's just not required.

Can you suggest a better way to do this?

Cheers

What's strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
supposed to be?

If I'm understanding correctly, it should be simply:

strStart = Left([PostCode], 2)

However, let's step back a moment.

You're looking up Warehouse in tblWarehouse and storing it in tblContacts.
Why? Isn't PostalCode in both tables (either entirely, or in part)? You have
sufficient information to join the two tables together in a query, rather
than storing the Warehouse information redundantly.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Postcode is in tblContacts. I have another table so that it can

lookup
the

value supposed to be in the Postcode field of tblContacts. That table is
tblWarehouse.

But....I'm running into guessing now, this is what I have...

Private Sub Warehouse_AfterUpdate()
strWarehouse = Me.Warehouse
strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
CurrentDb.Execute "UPDATE [tblContacts] " & SET [Warehouse] = '" &
strWarehouse & "'" & " WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError
End Sub

I know it's wrong, but this one is more difficult than I thought.

:

Is Postcode in tblContacts?

If so, then try something like:

CurrentDb.Execute "UPDATE [tblContacts] " & _
SET [Warehouse] = '" & strWarehouse & "'" & _
" WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError

(where strStart contains the first 2 characters of the Postcode).

If Postcode isn't in tblContacts, how do you know which rows need to be
updated?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok, so i have to put

(in English) Warehouse Value is Kendal WHERE postcode starts with LA,
Warehouse Value is Rushden WHERE postcode starts with NE,

Warehouse
Value

is
Bristol where postcode starts with BR, etc, etc.....except theres

over
100

postcode values!!! That's why I created the table so the unbound

value
on

the
form could look up the value in the Warehouse table. I have seen
somewhere
(on a wizard I think) an option to store a certain value in a certain
field,
that's exactly what i need.

:

Your UPDATE statement is incorrect then: because there's no WHERE
clause,
it's going to update every row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No I want each row to have either "Kendal", "Rushden" or

"Bristol"
as

it's
value depending on postcode.

:

So you want every single row in tblContacts to have the same value
for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a DLookup in the form field which looks up a table

with
the

first
two
characters of the post code matched with one of three warehouses,
which
works
perfectly. After the field looks up the value and is

updated,
I

need
that
returned value to store in another table. So the correct value is
in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error,

but
not

updating
the
table either.

Thanks.


:

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll

let
you

trap
for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse]

= '"
&

strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have searched this newsgroup for the best solution but
nothing
seems
to
work. I have coded the following to the AfterUpdate

event
of

the
Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
&
"'"

However, it's not working, not returning anything, but no
error?
What's
wrong?

Thanks

 

[Guest]

So I put that in the Afterupdate event of the Warehouse field on the form

DoCmd.Run SQL (SELECT tblContacts.Field1, tblContacts.Field2,
tblWarehouse.Warehouse)
FROM tblContacts
LEFT JOIN tblWarehouse
ON Left(tblContacts.Postcode, 2) = tblWarehouse.PostcodeChar

But it really doesn't like it and thinks the LEFT from LEFT JOIN is a
function. Am i putting this code in the wrong place?

SELECT tblContacts.Field1, tblContacts.Field2, tblWarehouse.Warehouse
FROM tblContacts
LEFT JOIN tblWarehouse
ON Left(tblContacts.Postcode, 2) = tblWarehouse.PostcodeChar

This query will return a every row in tblContacts, with the Warehouse if
there's a match, or Null if there isn't.

You'll have to go into the SQL view to add the Left function into the ON
statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, for someone thats new at coding last month, I'm getting there.

Postcode is in both tables, however, in tblContacts the data entry person
enters a postcode (eg, LA9 6NH, M3 5EE, NE1 E55) and in tblWarehouse, I have
about 120 records under two fields, PostcodeChar (char short for characters)
and Warehouse. In PostcodeChar I have stored LA, M3, NE, etc, etc and in the
Warehouse column I have put the closest warehouse to the postal code AREA.
If i were to store all the postcodes in Britain I'd have thousands upon
thousands of records and it's just not required.

Can you suggest a better way to do this?

Cheers

What's strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
supposed to be?

If I'm understanding correctly, it should be simply:

strStart = Left([PostCode], 2)

However, let's step back a moment.

You're looking up Warehouse in tblWarehouse and storing it in tblContacts.
Why? Isn't PostalCode in both tables (either entirely, or in part)? You have
sufficient information to join the two tables together in a query, rather
than storing the Warehouse information redundantly.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Postcode is in tblContacts. I have another table so that it can lookup
the
value supposed to be in the Postcode field of tblContacts. That table is
tblWarehouse.

But....I'm running into guessing now, this is what I have...

Private Sub Warehouse_AfterUpdate()
strWarehouse = Me.Warehouse
strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
CurrentDb.Execute "UPDATE [tblContacts] " & SET [Warehouse] = '" &
strWarehouse & "'" & " WHERE Postcode LIKE '" & strStart & "*'",
dbFailOnError
End Sub

I know it's wrong, but this one is more difficult than I thought.

:

Is Postcode in tblContacts?

If so, then try something like:

CurrentDb.Execute "UPDATE [tblContacts] " & _
SET [Warehouse] = '" & strWarehouse & "'" & _
" WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError

(where strStart contains the first 2 characters of the Postcode).

If Postcode isn't in tblContacts, how do you know which rows need to be
updated?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok, so i have to put

(in English) Warehouse Value is Kendal WHERE postcode starts with LA,
Warehouse Value is Rushden WHERE postcode starts with NE, Warehouse
Value
is
Bristol where postcode starts with BR, etc, etc.....except theres over
100
postcode values!!! That's why I created the table so the unbound value
on
the
form could look up the value in the Warehouse table. I have seen
somewhere
(on a wizard I think) an option to store a certain value in a certain
field,
that's exactly what i need.

:

Your UPDATE statement is incorrect then: because there's no WHERE
clause,
it's going to update every row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No I want each row to have either "Kendal", "Rushden" or "Bristol"
as
it's
value depending on postcode.

:

So you want every single row in tblContacts to have the same
value
for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a DLookup in the form field which looks up a table with
the
first
two
characters of the post code matched with one of three
warehouses,
which
works
perfectly. After the field looks up the value and is updated,
I
need
that
returned value to store in another table. So the correct
value is
in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no error, but
not
updating
the
table either.

Thanks.


:

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database: that'll let
you
trap
for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET [Warehouse] = '"
&
strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have searched this newsgroup for the best solution but
nothing
seems
to
work. I have coded the following to the AfterUpdate event
of
the
Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse] = '" &
strWarehouse
&
"'"

However, it's not working, not returning anything, but no
error?
What's
wrong?

Thanks

 

[Douglas J Steele]

First of all, it's RunSQL, no space.

Second, the SQL statement you're running needs to be in quotes.

However, you can't use RunSQL for a SELECT statement: it's only for Action
queries (INSERT INTO, UPDATE, DELETE)

What I was suggesting is that you create a query and save it. Then, use that
query wherever you would otherwise have used the table.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So I put that in the Afterupdate event of the Warehouse field on the form

DoCmd.Run SQL (SELECT tblContacts.Field1, tblContacts.Field2,
tblWarehouse.Warehouse)
FROM tblContacts
LEFT JOIN tblWarehouse
ON Left(tblContacts.Postcode, 2) = tblWarehouse.PostcodeChar

But it really doesn't like it and thinks the LEFT from LEFT JOIN is a
function. Am i putting this code in the wrong place?

SELECT tblContacts.Field1, tblContacts.Field2, tblWarehouse.Warehouse
FROM tblContacts
LEFT JOIN tblWarehouse
ON Left(tblContacts.Postcode, 2) = tblWarehouse.PostcodeChar

This query will return a every row in tblContacts, with the Warehouse if
there's a match, or Null if there isn't.

You'll have to go into the SQL view to add the Left function into the ON
statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, for someone thats new at coding last month, I'm getting there.

Postcode is in both tables, however, in tblContacts the data entry person
enters a postcode (eg, LA9 6NH, M3 5EE, NE1 E55) and in tblWarehouse,

I
have

about 120 records under two fields, PostcodeChar (char short for characters)
and Warehouse. In PostcodeChar I have stored LA, M3, NE, etc, etc and

in
the

Warehouse column I have put the closest warehouse to the postal code AREA.
If i were to store all the postcodes in Britain I'd have thousands upon
thousands of records and it's just not required.

Can you suggest a better way to do this?

Cheers


:

What's strStart = [tblWarehouse.PostCodeChar] = " &

Left([Postcode],2) &
"

supposed to be?

If I'm understanding correctly, it should be simply:

strStart = Left([PostCode], 2)

However, let's step back a moment.

You're looking up Warehouse in tblWarehouse and storing it in tblContacts.
Why? Isn't PostalCode in both tables (either entirely, or in part)?

You
have

sufficient information to join the two tables together in a query, rather
than storing the Warehouse information redundantly.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Postcode is in tblContacts. I have another table so that it can lookup
the
value supposed to be in the Postcode field of tblContacts. That

table
is

tblWarehouse.

But....I'm running into guessing now, this is what I have...

Private Sub Warehouse_AfterUpdate()
strWarehouse = Me.Warehouse
strStart = [tblWarehouse.PostCodeChar] = " & Left([Postcode],2) & "
CurrentDb.Execute "UPDATE [tblContacts] " & SET [Warehouse] = '" &
strWarehouse & "'" & " WHERE Postcode LIKE '" & strStart & "*'",
dbFailOnError
End Sub

I know it's wrong, but this one is more difficult than I thought.

:

Is Postcode in tblContacts?

If so, then try something like:

CurrentDb.Execute "UPDATE [tblContacts] " & _
SET [Warehouse] = '" & strWarehouse & "'" & _
" WHERE Postcode LIKE '" & strStart & "*'", dbFailOnError

(where strStart contains the first 2 characters of the Postcode).

If Postcode isn't in tblContacts, how do you know which rows

need to
be

updated?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ok, so i have to put

(in English) Warehouse Value is Kendal WHERE postcode starts

with
LA,

Warehouse Value is Rushden WHERE postcode starts with NE, Warehouse
Value
is
Bristol where postcode starts with BR, etc, etc.....except

theres
over

100
postcode values!!! That's why I created the table so the

unbound
value

on
the
form could look up the value in the Warehouse table. I have seen
somewhere
(on a wizard I think) an option to store a certain value in a certain
field,
that's exactly what i need.

:

Your UPDATE statement is incorrect then: because there's no WHERE
clause,
it's going to update every row.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


No I want each row to have either "Kendal", "Rushden" or "Bristol"
as
it's
value depending on postcode.

:

So you want every single row in tblContacts to have the same
value
for
Warehouse, and that's not happening?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a DLookup in the form field which looks up a

table
with

the
first
two
characters of the post code matched with one of three
warehouses,
which
works
perfectly. After the field looks up the value and is updated,
I
need
that
returned value to store in another table. So the correct
value is
in
strWarehouse as far as I can tell.


The code you gave me is doing the same thing, no

error,
but

not
updating
the
table either.

Thanks.


:

Do you know that the correct value is in strWarehouse?

Try using the Execute method of the database:

that'll
let

you
trap
for
errors:

CurrentDb.Execute "UPDATE [tblContacts] SET

[Warehouse]
= '"

&
strWarehouse
& "'", dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have searched this newsgroup for the best

solution
but

nothing
seems
to
work. I have coded the following to the

AfterUpdate
event

of
the
Warehouse
field on my form...

strWarehouse = Me.Warehouse
DoCmd.RunSQL "UPDATE [tblContacts] SET [Warehouse]

=
'" &

strWarehouse
&
"'"

However, it's not working, not returning anything,

but
no

error?
What's
wrong?

Thanks