linking databases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'd like to know if, after you've linked two tables from two different
Access databases, you can update records in both tables by simply upadating
the records in one of them. So if I change "dtmYear" from say 1997 to 1998 in
one table, then "dtmYear"in the second table would also be changed from 1997
to 1998. Is there way a to do this?
 
Kevin said:
Hi,

I'd like to know if, after you've linked two tables from two different
Access databases, you can update records in both tables by simply
upadating the records in one of them. So if I change "dtmYear" from
say 1997 to 1998 in one table, then "dtmYear"in the second table
would also be changed from 1997 to 1998. Is there way a to do this?
Click to expand...

No. A relationship with enforced RI would be required to do that and you cannot
build such a relationship across tables in more than one file.

It could be done in code if all updates were made via forms though.
 
Thanks for your reply! On one of the databases (the one I'm building, the
other was done by another person) I actually do updates with forms. I've done
this using a command button which uses a macro to open a form which I further
filter using an SQL statement. So if I'm looking a record for say "Canada" i
have command button which when clicked on opens a form for "Canada" where
editing of that record can be done (I've locked the main form already so no
editing can be done there) I'm not very profficient with code though. Would
you be able to suggest code to me that I could use to update 2 or even more
databases using forms?

Thanks.
 
Kevin said:
Thanks for your reply! On one of the databases (the one I'm building,
the other was done by another person) I actually do updates with
forms. I've done this using a command button which uses a macro to
open a form which I further filter using an SQL statement. So if I'm
looking a record for say "Canada" i have command button which when
clicked on opens a form for "Canada" where editing of that record can
be done (I've locked the main form already so no editing can be done
there) I'm not very profficient with code though. Would you be able
to suggest code to me that I could use to update 2 or even more
databases using forms?
Click to expand...

The simplest method would involve using the AfterUpdate event of your form
to execute an update query against the other table. That update could use
the record just saved to update the other table.
 
Hi,

Thanks for your reply. I've looked for code to execute an Update Query and
found the following:

"So lets assume you have two textboxes txtAA and txtBB and you want to save
their values in a second table tblBBB. Note that I will assume that AA is a
String/Text and BB is a number.Lets say the Primary key used in the form and
table tblBBB is called [PkID] in tblBBB and txtPkID on the form

Sub txtBB_OnExit

Dim sSQL as String

sSQL = UPDATE tblBBB SET " & _
" [AA] = '" & Me.txtAA & "', " & _
" [BB] = " & Me.txtBB & " " & _
" WHERE [PkID] = " & Me.txtPkID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

You will need to rebuild this based on you actual fields and textbox names."

I've rebuilt the code in the following way:

Dim sSQL As String

Sub ctbto table 1_OnExit

sSQL = UPDATE ctbto table 1 SET " & _
" [Country Code] = '" & Me.Country Code & "', " & _
"
Code: 
 = " & Me.code & " " & _
" WHERE [ID] = " & Me.ID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, when I enter the name of the 2nd table (ctbto table 1) I get an
'Expected: End of Statement' error message.
I've tried to contact the person who wrote this code, but do you know why
I'm getting this error message and how I could correct it?

Thanks much in advance.
 
It would appear that the name of your table is ctbto table 1, which has
blanks in it. Whenever table or field names have blanks, they must be
enclosed in square brackets:

sSQL = UPDATE [ctbto table 1] SET " & _
" [Country Code] = '" & Me.Country Code & "', " & _
"
Code: 
 = " & Me.code & " " & _
" WHERE [ID] = " & Me.ID & ";"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="Kevin"]
Hi,

Thanks for your reply. I've looked for code to execute an Update Query and
found the following:

"So lets assume you have two textboxes txtAA and txtBB and you want to
save
their values in a second table tblBBB. Note that I will assume that AA is
a
String/Text and BB is a number.Lets say the Primary key used in the form
and
table tblBBB is called [PkID] in tblBBB and txtPkID on the form

Sub txtBB_OnExit

Dim sSQL as String

sSQL = UPDATE tblBBB SET " & _
" [AA] = '" & Me.txtAA & "', " & _
" [BB] = " & Me.txtBB & " " & _
" WHERE [PkID] = " & Me.txtPkID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

You will need to rebuild this based on you actual fields and textbox
names."

I've rebuilt the code in the following way:

Dim sSQL As String

Sub ctbto table 1_OnExit

sSQL = UPDATE ctbto table 1 SET " & _
" [Country Code] = '" & Me.Country Code & "', " & _
" [code] = " & Me.code & " " & _
" WHERE [ID] = " & Me.ID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, when I enter the name of the 2nd table (ctbto table 1) I get an
'Expected: End of Statement' error message.
I've tried to contact the person who wrote this code, but do you know why
I'm getting this error message and how I could correct it?

Thanks much in advance.

[QUOTE="Rick Brandt"]
The simplest method would involve using the AfterUpdate event of your
form
to execute an update query against the other table.  That update could
use
the record just saved to update the other table.
[/QUOTE][/QUOTE]
 
Hi, thanks for answering.

Yes, 'ctbto table 1' is the name of the 2nd table I want to update. I've
already tried putting in the brackets but I just get the same error message
for the whole name, i.e. '[ctbto table 1]'. I've also tried putting in an
underscore between the 3 parts of the table name and I still get error
messages. What could I do?

Douglas J. Steele said:
It would appear that the name of your table is ctbto table 1, which has
blanks in it. Whenever table or field names have blanks, they must be
enclosed in square brackets:

sSQL = UPDATE [ctbto table 1] SET " & _
" [Country Code] = '" & Me.Country Code & "', " & _
"
Code: 
 = " & Me.code & " " & _
" WHERE [ID] = " & Me.ID & ";"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="Kevin"]
Hi,

Thanks for your reply. I've looked for code to execute an Update Query and
found the following:

"So lets assume you have two textboxes txtAA and txtBB and you want to
save
their values in a second table tblBBB. Note that I will assume that AA is
a
String/Text and BB is a number.Lets say the Primary key used in the form
and
table tblBBB is called [PkID] in tblBBB and txtPkID on the form

Sub txtBB_OnExit

Dim sSQL as String

sSQL = UPDATE tblBBB SET " & _
" [AA] = '" & Me.txtAA & "', " & _
" [BB] = " & Me.txtBB & " " & _
" WHERE [PkID] = " & Me.txtPkID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

You will need to rebuild this based on you actual fields and textbox
names."

I've rebuilt the code in the following way:

Dim sSQL As String

Sub ctbto table 1_OnExit

sSQL = UPDATE ctbto table 1 SET " & _
" [Country Code] = '" & Me.Country Code & "', " & _
" [code] = " & Me.code & " " & _
" WHERE [ID] = " & Me.ID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, when I enter the name of the 2nd table (ctbto table 1) I get an
'Expected: End of Statement' error message.
I've tried to contact the person who wrote this code, but do you know why
I'm getting this error message and how I could correct it?

Thanks much in advance.

[QUOTE="Rick Brandt"]
Kevin wrote:
Thanks for your reply! On one of the databases (the one I'm building,
the other was done by another person) I actually do updates with
forms. I've done this using a command button which uses a macro to
open a form which I further filter using an SQL statement. So if I'm
looking a record for say "Canada" i have command button which when
clicked on opens a form for "Canada" where editing of that record can
be done (I've locked the main form already so no editing can be done
there) I'm not very profficient with code though. Would you be able
to suggest code to me that I could use to update 2 or even more
databases using forms?

The simplest method would involve using the AfterUpdate event of your
form
to execute an update query against the other table.  That update could
use
the record just saved to update the other table.
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
 
What's the exact code you're using, then (since what you posted before
doesn't have the brackets around the table name)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Kevin said:
Hi, thanks for answering.

Yes, 'ctbto table 1' is the name of the 2nd table I want to update. I've
already tried putting in the brackets but I just get the same error
message
for the whole name, i.e. '[ctbto table 1]'. I've also tried putting in an
underscore between the 3 parts of the table name and I still get error
messages. What could I do?

Douglas J. Steele said:
It would appear that the name of your table is ctbto table 1, which has
blanks in it. Whenever table or field names have blanks, they must be
enclosed in square brackets:

sSQL = UPDATE [ctbto table 1] SET " & _
" [Country Code] = '" & Me.Country Code & "', " & _
"
Code: 
 = " & Me.code & " " & _
" WHERE [ID] = " & Me.ID & ";"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="Kevin"]
Hi,

Thanks for your reply. I've looked for code to execute an Update Query
and
found the following:

"So lets assume you have two textboxes txtAA and txtBB and you want to
save
their values in a second table tblBBB. Note that I will assume that AA
is
a
String/Text and BB is a number.Lets say the Primary key used in the
form
and
table tblBBB is called [PkID] in tblBBB and txtPkID on the form

Sub txtBB_OnExit

Dim sSQL as String

sSQL = UPDATE tblBBB SET " & _
" [AA] = '" & Me.txtAA & "', " & _
" [BB] = " & Me.txtBB & " " & _
" WHERE [PkID] = " & Me.txtPkID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

You will need to rebuild this based on you actual fields and textbox
names."

I've rebuilt the code in the following way:

Dim sSQL As String

Sub ctbto table 1_OnExit

sSQL = UPDATE ctbto table 1 SET " & _
" [Country Code] = '" & Me.Country Code & "', " & _
" [code] = " & Me.code & " " & _
" WHERE [ID] = " & Me.ID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, when I enter the name of the 2nd table (ctbto table 1) I get
an
'Expected: End of Statement' error message.
I've tried to contact the person who wrote this code, but do you know
why
I'm getting this error message and how I could correct it?

Thanks much in advance.

:

Kevin wrote:
Thanks for your reply! On one of the databases (the one I'm
building,
the other was done by another person) I actually do updates with
forms. I've done this using a command button which uses a macro to
open a form which I further filter using an SQL statement. So if I'm
looking a record for say "Canada" i have command button which when
clicked on opens a form for "Canada" where editing of that record
can
be done (I've locked the main form already so no editing can be done
there) I'm not very profficient with code though. Would you be able
to suggest code to me that I could use to update 2 or even more
databases using forms?

The simplest method would involve using the AfterUpdate event of your
form
to execute an update query against the other table.  That update could
use
the record just saved to update the other table.
[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Hi,

I've had a look again at the original "example" code I found:

Sub txtBB_OnExit

Dim sSQL as String

sSQL = "UPDATE tblBBB SET " & _
" [AA] = '" & Me.txtAA & "', " & _
" [BB] = '" & Me.txtBB & "'" & _
" WHERE [PkID] = " & Me.txtPkID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

and apparantly "txtBB" doesn't refer to the table in the second database
that I want to update. It seems to be the name of the text box for the common
columns in both tables that I wish to update. So "txtBB" could be
"Country"(and the same for "AA"). "tblBB" then would be the name of the table
in the second database, so "ctbto table 1". I therefore tried using the
following code:


Sub Country_OnExit

Dim sSQL as String

sSQL = "UPDATE [ctbto table 1] SET " & _
" [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, I'm quite obviously still doing something wrong and
misunderstanding how this code is supposed to work as when I run it, it
deletes the entry on the form in the first database for the particular
country whose record I want to update, while not updating the second table.
I've also tried using the "After Update" event property and deleting the "On
Exit" event property in the code, but then I get compile errors.

Also, how do I let Access know where to find the relevant table in the
second database? I've tried linking that table but to no effect. Please bear
with me here, I'm really not very proficient with SQL and VBA programming
though I'm trying to learn them. I hope you can understand my explanation and
can tell me what I"m doing wrong.

Thanks much for your time.



Douglas J. Steele said:
What's the exact code you're using, then (since what you posted before
doesn't have the brackets around the table name)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Kevin said:
Hi, thanks for answering.

Yes, 'ctbto table 1' is the name of the 2nd table I want to update. I've
already tried putting in the brackets but I just get the same error
message
for the whole name, i.e. '[ctbto table 1]'. I've also tried putting in an
underscore between the 3 parts of the table name and I still get error
messages. What could I do?

Douglas J. Steele said:
It would appear that the name of your table is ctbto table 1, which has
blanks in it. Whenever table or field names have blanks, they must be
enclosed in square brackets:

sSQL = UPDATE [ctbto table 1] SET " & _
" [Country Code] = '" & Me.Country Code & "', " & _
"
Code: 
 = " & Me.code & " " & _
" WHERE [ID] = " & Me.ID & ";"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi,

Thanks for your reply. I've looked for code to execute an Update Query
and
found the following:

"So lets assume you have two textboxes txtAA and txtBB and you want to
save
their values in a second table tblBBB. Note that I will assume that AA
is
a
String/Text and BB is a number.Lets say the Primary key used in the
form
and
table tblBBB is called [PkID] in tblBBB and txtPkID on the form

Sub txtBB_OnExit

Dim sSQL as String

sSQL = UPDATE tblBBB SET " & _
" [AA] = '" & Me.txtAA & "', " & _
" [BB] = " & Me.txtBB & " " & _
" WHERE [PkID] = " & Me.txtPkID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

You will need to rebuild this based on you actual fields and textbox
names."

I've rebuilt the code in the following way:

Dim sSQL As String

Sub ctbto table 1_OnExit

sSQL = UPDATE ctbto table 1 SET " & _
" [Country Code] = '" & Me.Country Code & "', " & _
" [code] = " & Me.code & " " & _
" WHERE [ID] = " & Me.ID & ";"

DoCmd.SetWarnings False
CurrenDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, when I enter the name of the 2nd table (ctbto table 1) I get
an
'Expected: End of Statement' error message.
I've tried to contact the person who wrote this code, but do you know
why
I'm getting this error message and how I could correct it?

Thanks much in advance.

:

Kevin wrote:
Thanks for your reply! On one of the databases (the one I'm
building,
the other was done by another person) I actually do updates with
forms. I've done this using a command button which uses a macro to
open a form which I further filter using an SQL statement. So if I'm
looking a record for say "Canada" i have command button which when
clicked on opens a form for "Canada" where editing of that record
can
be done (I've locked the main form already so no editing can be done
there) I'm not very profficient with code though. Would you be able
to suggest code to me that I could use to update 2 or even more
databases using forms?

The simplest method would involve using the AfterUpdate event of your
form
to execute an update query against the other table.  That update could
use
the record just saved to update the other table.
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Kevin said:
Sub Country_OnExit

Dim sSQL as String

sSQL = "UPDATE [ctbto table 1] SET " & _
" [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, I'm quite obviously still doing something wrong and
misunderstanding how this code is supposed to work as when I run it, it
deletes the entry on the form in the first database for the particular
country whose record I want to update, while not updating the second
table.
I've also tried using the "After Update" event property and deleting the
"On
Exit" event property in the code, but then I get compile errors.
Click to expand...

Not sure I see how that could delete any entries from a database.

Yes, if you point to the wrong table, and that wrong table happens to have
fields named "Region" and "Country", you'd end up overwriting the record
that has that ID, but an Update statement cannot delete data.
Also, how do I let Access know where to find the relevant table in the
second database? I've tried linking that table but to no effect. Please
bear
with me here, I'm really not very proficient with SQL and VBA programming
though I'm trying to learn them. I hope you can understand my explanation
and
can tell me what I"m doing wrong.
Click to expand...

You could use an IN clause:

sSQL = "UPDATE [ctbto table 1] " & _
"IN C:\Folder\File.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"
 
Hi again,

Thanks for your help.
I've included the IN statement but I'm still having problems with other
sections of the code. What I have now looks like this:

Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\CTBTO_situationers_AFD.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE
Code: 
 = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

Using either the After Update event procedure of the Form or the Exit or
After Update event procedures for one of the text boxes, I get a compile
error with the ".chrCountry" line ( ''Metod or Data member not found") and
the "Sub chrCountry_AfterUpdate()" line is highlighted in Yellow.  The text
box names I use after the SET clause (eg. "chrCountry") are the correct
column names in my underlying table so I don't understand why there should be
a compile error there, nor do I know what else I could use there. What could
be the problem here? Do the names of the table columns have to be identical
in both tables for the Upadte Query to work? (After the WHERE statement I've
changed ID to code as this is the primary key in the table in the 2nd
database I'm trying to update).

Thanks again for your time and patience with me.



[QUOTE="Douglas J. Steele"]
[QUOTE="Kevin"]
Sub Country_OnExit

Dim sSQL as String

sSQL = "UPDATE [ctbto table 1] SET " & _
" [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, I'm quite obviously still doing something wrong and
misunderstanding how this code is supposed to work as when I run it, it
deletes the entry on the form in the first database for the particular
country whose record I want to update, while not updating the second
table.
I've also tried using the "After Update" event property and deleting the
"On
Exit" event property in the code, but then I get compile errors.[/QUOTE]

Not sure I see how that could delete any entries from a database.

Yes, if you point to the wrong table, and that wrong table happens to have
fields named "Region" and "Country", you'd end up overwriting the record
that has that ID, but an Update statement cannot delete data.
[QUOTE]
Also, how do I let Access know where to find the relevant table in the
second database? I've tried linking that table but to no effect. Please
bear
with me here, I'm really not very proficient with SQL and VBA programming
though I'm trying to learn them. I hope you can understand my explanation
and
can tell me what I"m doing wrong.[/QUOTE]

You could use an IN clause:

sSQL = "UPDATE [ctbto table 1] " & _
"IN C:\Folder\File.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"
[/QUOTE]
 
When you go into the properties for the text box, does it have [Event
Procedure] as the value for the AfterUpdate property? If it does, what
happens when you click on the ellipsis (...) to the right of the property?
It should take you into the procedure. If it doesn't, are you sure you
didn't make a typo?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Kevin said:
Hi again,

Thanks for your help.
I've included the IN statement but I'm still having problems with other
sections of the code. What I have now looks like this:

Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\CTBTO_situationers_AFD.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE
Code: 
 = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

Using either the After Update event procedure of the Form or the Exit or
After Update event procedures for one of the text boxes, I get a compile
error with the ".chrCountry" line ( ''Metod or Data member not found") and
the "Sub chrCountry_AfterUpdate()" line is highlighted in Yellow.  The
text
box names I use after the SET clause (eg. "chrCountry") are the correct
column names in my underlying table so I don't understand why there should
be
a compile error there, nor do I know what else I could use there. What
could
be the problem here? Do the names of the table columns have to be
identical
in both tables for the Upadte Query to work? (After the WHERE statement
I've
changed ID to code as this is the primary key in the table in the 2nd
database I'm trying to update).

Thanks again for your time and patience with me.



[QUOTE="Douglas J. Steele"]
[QUOTE="Kevin"]
Sub Country_OnExit

Dim sSQL as String

sSQL = "UPDATE [ctbto table 1] SET " & _
" [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, I'm quite obviously still doing something wrong and
misunderstanding how this code is supposed to work as when I run it, it
deletes the entry on the form in the first database for the particular
country whose record I want to update, while not updating the second
table.
I've also tried using the "After Update" event property and deleting
the
"On
Exit" event property in the code, but then I get compile errors.[/QUOTE]

Not sure I see how that could delete any entries from a database.

Yes, if you point to the wrong table, and that wrong table happens to
have
fields named "Region" and "Country", you'd end up overwriting the record
that has that ID, but an Update statement cannot delete data.
[QUOTE]
Also, how do I let Access know where to find the relevant table in the
second database? I've tried linking that table but to no effect. Please
bear
with me here, I'm really not very proficient with SQL and VBA
programming
though I'm trying to learn them. I hope you can understand my
explanation
and
can tell me what I"m doing wrong.[/QUOTE]

You could use an IN clause:

sSQL = "UPDATE [ctbto table 1] " & _
"IN C:\Folder\File.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"
[/QUOTE][/QUOTE]
Click to expand...
 
Hi,

First I was using the Form's [Event Procedure] After Update property but now
I using the text box's [Event Procedure] After Update property on the form
I'm using to update my main form and the underlying table. When I click on
the ellipsis it takes me into the procedure. However I still get the same
"Method or Data member not found" compile error message after the Me.
statement in the SET line of the code. According to the 'SQL Bible (Aklex
Kriegel)' which I'm using, the SET clause is supposed to specify the name of
the column to update and the new value to be assigned to it.
The name of the column in the table in the 2nd database I want to update is
"region", and another is "country", so I don't understand why Access is
giving me the compile error message. Those are the correct names of the
columns and nothing else. I wonder if the problem has to do with using the
After Update property, as this code was written for the "OnExit" property
(Sub txtBB_OnExit). I've tried using that property too but it still doesn't
update the 2nd table. I'm at the knocking my head against the wall stage
now....this should be working but isn't.

Douglas J. Steele said:
When you go into the properties for the text box, does it have [Event
Procedure] as the value for the AfterUpdate property? If it does, what
happens when you click on the ellipsis (...) to the right of the property?
It should take you into the procedure. If it doesn't, are you sure you
didn't make a typo?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Kevin said:
Hi again,

Thanks for your help.
I've included the IN statement but I'm still having problems with other
sections of the code. What I have now looks like this:

Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\CTBTO_situationers_AFD.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE
Code: 
 = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

Using either the After Update event procedure of the Form or the Exit or
After Update event procedures for one of the text boxes, I get a compile
error with the ".chrCountry" line ( ''Metod or Data member not found") and
the "Sub chrCountry_AfterUpdate()" line is highlighted in Yellow.  The
text
box names I use after the SET clause (eg. "chrCountry") are the correct
column names in my underlying table so I don't understand why there should
be
a compile error there, nor do I know what else I could use there. What
could
be the problem here? Do the names of the table columns have to be
identical
in both tables for the Upadte Query to work? (After the WHERE statement
I've
changed ID to code as this is the primary key in the table in the 2nd
database I'm trying to update).

Thanks again for your time and patience with me.



[QUOTE="Douglas J. Steele"]
Sub Country_OnExit

Dim sSQL as String

sSQL = "UPDATE [ctbto table 1] SET " & _
" [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, I'm quite obviously still doing something wrong and
misunderstanding how this code is supposed to work as when I run it, it
deletes the entry on the form in the first database for the particular
country whose record I want to update, while not updating the second
table.
I've also tried using the "After Update" event property and deleting
the
"On
Exit" event property in the code, but then I get compile errors.

Not sure I see how that could delete any entries from a database.

Yes, if you point to the wrong table, and that wrong table happens to
have
fields named "Region" and "Country", you'd end up overwriting the record
that has that ID, but an Update statement cannot delete data.

Also, how do I let Access know where to find the relevant table in the
second database? I've tried linking that table but to no effect. Please
bear
with me here, I'm really not very proficient with SQL and VBA
programming
though I'm trying to learn them. I hope you can understand my
explanation
and
can tell me what I"m doing wrong.

You could use an IN clause:

sSQL = "UPDATE [ctbto table 1] " & _
"IN C:\Folder\File.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
Because you've got blanks in the file name, try putting single quotes around
it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Kevin said:
Hi,

First I was using the Form's [Event Procedure] After Update property but
now
I using the text box's [Event Procedure] After Update property on the form
I'm using to update my main form and the underlying table. When I click on
the ellipsis it takes me into the procedure. However I still get the same
"Method or Data member not found" compile error message after the Me.
statement in the SET line of the code. According to the 'SQL Bible (Aklex
Kriegel)' which I'm using, the SET clause is supposed to specify the name
of
the column to update and the new value to be assigned to it.
The name of the column in the table in the 2nd database I want to update
is
"region", and another is "country", so I don't understand why Access is
giving me the compile error message. Those are the correct names of the
columns and nothing else. I wonder if the problem has to do with using the
After Update property, as this code was written for the "OnExit" property
(Sub txtBB_OnExit). I've tried using that property too but it still
doesn't
update the 2nd table. I'm at the knocking my head against the wall stage
now....this should be working but isn't.

Douglas J. Steele said:
When you go into the properties for the text box, does it have [Event
Procedure] as the value for the AfterUpdate property? If it does, what
happens when you click on the ellipsis (...) to the right of the
property?
It should take you into the procedure. If it doesn't, are you sure you
didn't make a typo?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Kevin said:
Hi again,

Thanks for your help.
I've included the IN statement but I'm still having problems with other
sections of the code. What I have now looks like this:

Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\CTBTO_situationers_AFD.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE
Code: 
 = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

Using either the After Update event procedure of the Form or the Exit
or
After Update event procedures for one of the text boxes, I get a
compile
error with the ".chrCountry" line ( ''Metod or Data member not found")
and
the "Sub chrCountry_AfterUpdate()" line is highlighted in Yellow.  The
text
box names I use after the SET clause (eg. "chrCountry") are the correct
column names in my underlying table so I don't understand why there
should
be
a compile error there, nor do I know what else I could use there. What
could
be the problem here? Do the names of the table columns have to be
identical
in both tables for the Upadte Query to work? (After the WHERE statement
I've
changed ID to code as this is the primary key in the table in the 2nd
database I'm trying to update).

Thanks again for your time and patience with me.



:


Sub Country_OnExit

Dim sSQL as String

sSQL = "UPDATE [ctbto table 1] SET " & _
" [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, I'm quite obviously still doing something wrong and
misunderstanding how this code is supposed to work as when I run it,
it
deletes the entry on the form in the first database for the
particular
country whose record I want to update, while not updating the second
table.
I've also tried using the "After Update" event property and deleting
the
"On
Exit" event property in the code, but then I get compile errors.

Not sure I see how that could delete any entries from a database.

Yes, if you point to the wrong table, and that wrong table happens to
have
fields named "Region" and "Country", you'd end up overwriting the
record
that has that ID, but an Update statement cannot delete data.

Also, how do I let Access know where to find the relevant table in
the
second database? I've tried linking that table but to no effect.
Please
bear
with me here, I'm really not very proficient with SQL and VBA
programming
though I'm trying to learn them. I hope you can understand my
explanation
and
can tell me what I"m doing wrong.

You could use an IN clause:

sSQL = "UPDATE [ctbto table 1] " & _
"IN C:\Folder\File.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"
[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Hi,

I tried it and no effect. I've rewritten the code so that after the SET
statement the field and text box names are those in the target table in the
2nd databse: "CTBTO table 1". (previously using the field and text box names
of my own table after the SET statement was a mistake) I still however get
the same compile error message: "Method or data member not found" with the
text box names after the Me. statement highlighted in blue and the Private
Sub chrCountry_AfterUpdate() string highlighted in yellow.The code as I
currently have it is as follows:

Private Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\'CTBTO_situationers_AFD.MDB' " & _
" SET [region] = '" & Me.region & "', " & _
" [country] = '" & Me.country & "'" & _
" WHERE
Code: 
 = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

"region" and "country" are field and text box names in CTBTO table 1, and
"code" is the primary key there. Do you know what I could possibly try now?

Thanks so much for your great patience with me thus far and your prompt
responses. Please know that I deeply appreciate it.


[QUOTE="Douglas J. Steele"]
Because you've got blanks in the file name, try putting single quotes around
it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="Kevin"]
Hi,

First I was using the Form's [Event Procedure] After Update property but
now
I using the text box's [Event Procedure] After Update property on the form
I'm using to update my main form and the underlying table. When I click on
the ellipsis it takes me into the procedure. However I still get the same
"Method or Data member not found" compile error message after the Me.
statement in the SET line of the code. According to the 'SQL Bible (Aklex
Kriegel)' which I'm using, the SET clause is supposed to specify the name
of
the column to update and the new value to be assigned to it.
The name of the column in the table in the 2nd database I want to update
is
"region", and another is "country", so I don't understand why Access is
giving me the compile error message. Those are the correct names of the
columns and nothing else. I wonder if the problem has to do with using the
After Update property, as this code was written for the "OnExit" property
(Sub txtBB_OnExit). I've tried using that property too but it still
doesn't
update the 2nd table. I'm at the knocking my head against the wall stage
now....this should be working but isn't.

[QUOTE="Douglas J. Steele"]
When you go into the properties for the text box, does it have [Event
Procedure] as the value for the AfterUpdate property? If it does, what
happens when you click on the ellipsis (...) to the right of the
property?
It should take you into the procedure. If it doesn't, are you sure you
didn't make a typo?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi again,

Thanks for your help.
I've included the IN statement but I'm still having problems with other
sections of the code. What I have now looks like this:

Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\CTBTO_situationers_AFD.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [code] = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

Using either the After Update event procedure of the Form or the Exit
or
After Update event procedures for one of the text boxes, I get a
compile
error with the ".chrCountry" line ( ''Metod or Data member not found")
and
the "Sub chrCountry_AfterUpdate()" line is highlighted in Yellow.  The
text
box names I use after the SET clause (eg. "chrCountry") are the correct
column names in my underlying table so I don't understand why there
should
be
a compile error there, nor do I know what else I could use there. What
could
be the problem here? Do the names of the table columns have to be
identical
in both tables for the Upadte Query to work? (After the WHERE statement
I've
changed ID to code as this is the primary key in the table in the 2nd
database I'm trying to update).

Thanks again for your time and patience with me.



:


Sub Country_OnExit

Dim sSQL as String

sSQL = "UPDATE [ctbto table 1] SET " & _
" [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, I'm quite obviously still doing something wrong and
misunderstanding how this code is supposed to work as when I run it,
it
deletes the entry on the form in the first database for the
particular
country whose record I want to update, while not updating the second
table.
I've also tried using the "After Update" event property and deleting
the
"On
Exit" event property in the code, but then I get compile errors.

Not sure I see how that could delete any entries from a database.

Yes, if you point to the wrong table, and that wrong table happens to
have
fields named "Region" and "Country", you'd end up overwriting the
record
that has that ID, but an Update statement cannot delete data.

Also, how do I let Access know where to find the relevant table in
the
second database? I've tried linking that table but to no effect.
Please
bear
with me here, I'm really not very proficient with SQL and VBA
programming
though I'm trying to learn them. I hope you can understand my
explanation
and
can tell me what I"m doing wrong.

You could use an IN clause:

sSQL = "UPDATE [ctbto table 1] " & _
"IN C:\Folder\File.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"
[/QUOTE][/QUOTE]
[/QUOTE]
 
You didn't quote appropriately:

sSQL = "UPDATE [ctbto table 1] " & _
"IN 'J:\leg er\er projects\ES
Missions\ESMissionReports\CTBTO_situationers_AFD.MDB' " & _
" SET [region] = '" & Me.region & "', " & _
" [country] = '" & Me.country & "'" & _
" WHERE
Code: 
 = " & Me.code & ";"

Not 100% certain that will work: if it doesn't, why not create a linked
table to the second database?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="Kevin"]
Hi,

I tried it and no effect. I've rewritten the code so that after the SET
statement the field and text box names are those in the target table in
the
2nd databse: "CTBTO table 1". (previously using the field and text box
names
of my own table after the SET statement was a mistake) I still however get
the same compile error message: "Method or data member not found" with the
text box names after the Me. statement highlighted in blue and the Private
Sub chrCountry_AfterUpdate() string highlighted in yellow.The code as I
currently have it is as follows:

Private Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\'CTBTO_situationers_AFD.MDB' " & _
" SET [region] = '" & Me.region & "', " & _
" [country] = '" & Me.country & "'" & _
" WHERE [code] = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

"region" and "country" are field and text box names in CTBTO table 1, and
"code" is the primary key there. Do you know what I could possibly try
now?

Thanks so much for your great patience with me thus far and your prompt
responses. Please know that I deeply appreciate it.


[QUOTE="Douglas J. Steele"]
Because you've got blanks in the file name, try putting single quotes
around
it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="Kevin"]
Hi,

First I was using the Form's [Event Procedure] After Update property
but
now
I using the text box's [Event Procedure] After Update property on the
form
I'm using to update my main form and the underlying table. When I click
on
the ellipsis it takes me into the procedure. However I still get the
same
"Method or Data member not found" compile error message after the Me.
statement in the SET line of the code. According to the 'SQL Bible
(Aklex
Kriegel)' which I'm using, the SET clause is supposed to specify the
name
of
the column to update and the new value to be assigned to it.
The name of the column in the table in the 2nd database I want to
update
is
"region", and another is "country", so I don't understand why Access is
giving me the compile error message. Those are the correct names of the
columns and nothing else. I wonder if the problem has to do with using
the
After Update property, as this code was written for the "OnExit"
property
(Sub txtBB_OnExit). I've tried using that property too but it still
doesn't
update the 2nd table. I'm at the knocking my head against the wall
stage
now....this should be working but isn't.

:

When you go into the properties for the text box, does it have [Event
Procedure] as the value for the AfterUpdate property? If it does, what
happens when you click on the ellipsis (...) to the right of the
property?
It should take you into the procedure. If it doesn't, are you sure you
didn't make a typo?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi again,

Thanks for your help.
I've included the IN statement but I'm still having problems with
other
sections of the code. What I have now looks like this:

Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\CTBTO_situationers_AFD.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [code] = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

Using either the After Update event procedure of the Form or the
Exit
or
After Update event procedures for one of the text boxes, I get a
compile
error with the ".chrCountry" line ( ''Metod or Data member not
found")
and
the "Sub chrCountry_AfterUpdate()" line is highlighted in Yellow.
The
text
box names I use after the SET clause (eg. "chrCountry") are the
correct
column names in my underlying table so I don't understand why there
should
be
a compile error there, nor do I know what else I could use there.
What
could
be the problem here? Do the names of the table columns have to be
identical
in both tables for the Upadte Query to work? (After the WHERE
statement
I've
changed ID to code as this is the primary key in the table in the
2nd
database I'm trying to update).

Thanks again for your time and patience with me.



:


Sub Country_OnExit

Dim sSQL as String

sSQL = "UPDATE [ctbto table 1] SET " & _
" [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, I'm quite obviously still doing something wrong and
misunderstanding how this code is supposed to work as when I run
it,
it
deletes the entry on the form in the first database for the
particular
country whose record I want to update, while not updating the
second
table.
I've also tried using the "After Update" event property and
deleting
the
"On
Exit" event property in the code, but then I get compile errors.

Not sure I see how that could delete any entries from a database.

Yes, if you point to the wrong table, and that wrong table happens
to
have
fields named "Region" and "Country", you'd end up overwriting the
record
that has that ID, but an Update statement cannot delete data.

Also, how do I let Access know where to find the relevant table
in
the
second database? I've tried linking that table but to no effect.
Please
bear
with me here, I'm really not very proficient with SQL and VBA
programming
though I'm trying to learn them. I hope you can understand my
explanation
and
can tell me what I"m doing wrong.

You could use an IN clause:

sSQL = "UPDATE [ctbto table 1] " & _
"IN C:\Folder\File.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"
[/QUOTE]
[/QUOTE][/QUOTE]
 
It still didn't work. By a linked table do you mean using "File - Get
External Data - Link Tables..." in the database view to link a table in
another database? I've already done this to link "CTBTO table 1" to my
database but to no effect. Though maybe I haven't done everything I should
there.
Or Do you instead mean linking my table to the second database? Or creating
a new table using the SQL statement "Creat table..." to do this?

Douglas J. Steele said:
You didn't quote appropriately:

sSQL = "UPDATE [ctbto table 1] " & _
"IN 'J:\leg er\er projects\ES
Missions\ESMissionReports\CTBTO_situationers_AFD.MDB' " & _
" SET [region] = '" & Me.region & "', " & _
" [country] = '" & Me.country & "'" & _
" WHERE
Code: 
 = " & Me.code & ";"

Not 100% certain that will work: if it doesn't, why not create a linked
table to the second database?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="Kevin"]
Hi,

I tried it and no effect. I've rewritten the code so that after the SET
statement the field and text box names are those in the target table in
the
2nd databse: "CTBTO table 1". (previously using the field and text box
names
of my own table after the SET statement was a mistake) I still however get
the same compile error message: "Method or data member not found" with the
text box names after the Me. statement highlighted in blue and the Private
Sub chrCountry_AfterUpdate() string highlighted in yellow.The code as I
currently have it is as follows:

Private Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\'CTBTO_situationers_AFD.MDB' " & _
" SET [region] = '" & Me.region & "', " & _
" [country] = '" & Me.country & "'" & _
" WHERE [code] = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

"region" and "country" are field and text box names in CTBTO table 1, and
"code" is the primary key there. Do you know what I could possibly try
now?

Thanks so much for your great patience with me thus far and your prompt
responses. Please know that I deeply appreciate it.


[QUOTE="Douglas J. Steele"]
Because you've got blanks in the file name, try putting single quotes
around
it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi,

First I was using the Form's [Event Procedure] After Update property
but
now
I using the text box's [Event Procedure] After Update property on the
form
I'm using to update my main form and the underlying table. When I click
on
the ellipsis it takes me into the procedure. However I still get the
same
"Method or Data member not found" compile error message after the Me.
statement in the SET line of the code. According to the 'SQL Bible
(Aklex
Kriegel)' which I'm using, the SET clause is supposed to specify the
name
of
the column to update and the new value to be assigned to it.
The name of the column in the table in the 2nd database I want to
update
is
"region", and another is "country", so I don't understand why Access is
giving me the compile error message. Those are the correct names of the
columns and nothing else. I wonder if the problem has to do with using
the
After Update property, as this code was written for the "OnExit"
property
(Sub txtBB_OnExit). I've tried using that property too but it still
doesn't
update the 2nd table. I'm at the knocking my head against the wall
stage
now....this should be working but isn't.

:

When you go into the properties for the text box, does it have [Event
Procedure] as the value for the AfterUpdate property? If it does, what
happens when you click on the ellipsis (...) to the right of the
property?
It should take you into the procedure. If it doesn't, are you sure you
didn't make a typo?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Hi again,

Thanks for your help.
I've included the IN statement but I'm still having problems with
other
sections of the code. What I have now looks like this:

Sub chrCountry_AfterUpdate()
Dim sSQL As String

sSQL = "UPDATE [ctbto table 1] " & _
"IN J:\leg er\er projects\ES
Missions\ESMissionReports\CTBTO_situationers_AFD.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [code] = " & Me.code & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

Using either the After Update event procedure of the Form or the
Exit
or
After Update event procedures for one of the text boxes, I get a
compile
error with the ".chrCountry" line ( ''Metod or Data member not
found")
and
the "Sub chrCountry_AfterUpdate()" line is highlighted in Yellow.
The
text
box names I use after the SET clause (eg. "chrCountry") are the
correct
column names in my underlying table so I don't understand why there
should
be
a compile error there, nor do I know what else I could use there.
What
could
be the problem here? Do the names of the table columns have to be
identical
in both tables for the Upadte Query to work? (After the WHERE
statement
I've
changed ID to code as this is the primary key in the table in the
2nd
database I'm trying to update).

Thanks again for your time and patience with me.



:


Sub Country_OnExit

Dim sSQL as String

sSQL = "UPDATE [ctbto table 1] SET " & _
" [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"

DoCmd.SetWarnings False
CurrentDb.Execute sSQL
DoCmd.SetWarnings True

End Sub

However, I'm quite obviously still doing something wrong and
misunderstanding how this code is supposed to work as when I run
it,
it
deletes the entry on the form in the first database for the
particular
country whose record I want to update, while not updating the
second
table.
I've also tried using the "After Update" event property and
deleting
the
"On
Exit" event property in the code, but then I get compile errors.

Not sure I see how that could delete any entries from a database.

Yes, if you point to the wrong table, and that wrong table happens
to
have
fields named "Region" and "Country", you'd end up overwriting the
record
that has that ID, but an Update statement cannot delete data.

Also, how do I let Access know where to find the relevant table
in
the
second database? I've tried linking that table but to no effect.
Please
bear
with me here, I'm really not very proficient with SQL and VBA
programming
though I'm trying to learn them. I hope you can understand my
explanation
and
can tell me what I"m doing wrong.

You could use an IN clause:

sSQL = "UPDATE [ctbto table 1] " & _
"IN C:\Folder\File.MDB " & _
" SET [Region] = '" & Me.chrRegion & "', " & _
" [Country] = '" & Me.chrCountry & "'" & _
" WHERE [ID] = " & Me.dtmID & ";"
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
 
Back
Top