Re: linking databases

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 Country_OnExit ... Dim sSQL as String ... 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. ...
    (microsoft.public.access.externaldata)
  • Re: linking databases
    ... Sub chrCountry_AfterUpdate ... Dim sSQL As String ... Using either the After Update event procedure of the Form or the Exit or ... database I'm trying to update). ...
    (microsoft.public.access.externaldata)
  • Re: linking databases
    ... First I was using the Form's [Event Procedure] After Update property but now ... "Method or Data member not found" compile error message after the Me. ... The name of the column in the table in the 2nd database I want to update is ... Sub chrCountry_AfterUpdate ...
    (microsoft.public.access.externaldata)
  • Re: What is the name of the Language we are using & recommend book
    ... Can I have 2 sub forms in a form that are not sub forms of the other sub ... As for my process I am trying to create my Access Database in shells like ... QSL or Microsoft SQL Server Data Engine or what. ... language of queries, and the query design grid is just a tool to construct ...
    (microsoft.public.access.formscoding)
  • Re: Custom Login Screen
    ... Private Sub cmdLogin_Click ... On Error GoTo ErrorHandler ... You will need to enter your full path to the database file and MDW file in the appropriate places. ... Now make an MDE file from this MDB. ...
    (microsoft.public.access.security)