Re: linking databases

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)


"Kevin" <Kevin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:644EEA2C-347F-4072-ABF2-600FC2C3A80A@xxxxxxxxxxxxxxxx
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.


"Douglas J. Steele" wrote:

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)


"Kevin" <Kevin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8D27E1B2-5BE0-44E7-93F8-96AFDBE87EF3@xxxxxxxxxxxxxxxx
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" wrote:

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)


"Kevin" <Kevin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0B556BBA-0D03-4541-B0BF-351B803491F8@xxxxxxxxxxxxxxxx
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.



"Douglas J. Steele" wrote:

"Kevin" <Kevin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E0717AF5-37EE-4065-BB60-1D0C547F83CB@xxxxxxxxxxxxxxxx

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 & ";"


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












.

Relevant Pages

  • Re: linking databases
    ... Sub chrCountry_AfterUpdatestring highlighted in yellow.The code as I ... Dim sSQL As String ... Doug Steele, Microsoft Access MVP ... I using the text box's [Event Procedure] After Update property on the form ...
    (microsoft.public.access.externaldata)
  • Error in Log Each Users Session Guide
    ... my database - I currently have to use a frm_Welcome_Menu that everyone has to ... Below is my "On Open" event procedure. ... On Error GoTo ErrHandler ... Exit Sub ...
    (microsoft.public.access.formscoding)
  • Re: linking databases
    ... Doug Steele, Microsoft Access MVP ... I using the text box's [Event Procedure] After Update property on the form ... The name of the column in the table in the 2nd database I want to update ... Sub chrCountry_AfterUpdate ...
    (microsoft.public.access.externaldata)
  • Re: linking databases
    ... another database? ... Doug Steele, Microsoft Access MVP ... Sub chrCountry_AfterUpdatestring highlighted in yellow.The code as I ... I using the text box's [Event Procedure] After Update property on the ...
    (microsoft.public.access.externaldata)
  • Re: Using a function to print
    ... Either create a new database and import everything from your current ... Doug Steele, Microsoft Access MVP ... Private Sub Option62_Click ... Public Const SW_Minimize = 6 ...
    (microsoft.public.access.modulesdaovba)