Updating Data In Another Table

[BULYABA]

Another tragedy for me;
i have a form called visit form whose record source is visit table; this contains some similar fields like the Registry; forexample school; so i would like to know if i enter a value for the field school in the Visit form, how do i update the registry table.
Thanks

[PEB]

Hi,

Use an Update query; Select it from Query designer in the menu named Query!

:)

[BULYABA]

Thanks it worked. However is there a vb that can do it automatically?

[PEB]

Hi,

This can do it:

Set mydb = CurrentDb()
Set Myquery = mydb.CreateQuer yDef("My_query" , "SELECT my_field FROM My_table;")

:)

[BULYABA]

It's kind of confusing to me, can u be more specific; this is the more details of what i want to; so put the names i am giving u.

my database is "PIDC"
MY table is "visit" and it's form is Visit" (in this form i want to set a criteria for certains that on entry, they are updated in the "ClRegistry " table i.e the table i want to upadate)

Thanks

[PEB]

It's kind of confusing to me, can u be more specific; this is the more details of what i want to; so put the names i am giving u.

my database is "PIDC"
MY table is "visit" and it's form is Visit" (in this form i want to set a criteria for certains that on entry, they are updated in the "ClRegistry " table i.e the table i want to upadate)

Thanks

Sorry,
If you create an update query in query designer to update your table CIRegistry and its field visit and you switch it in SQL view from View

You'll see:

UPDATE CIRegistry SET Visit="new_valu e";

So my mind is:

When you create your query that updates your records, then see it in SQL view

Copy it
and Paste it in the follow expression in VB:

docmd.runsql "UPDATE CIRegistry SET Visit='new_valu e';"

And everytime when You execute this statement this will run!

:)

[BULYABA]

Thanks for your help but am getting an error on what i have tried, gues i tries it wrongly. still clarify

when i checked the sql view, this is what i saw; UPDATE ClRegistry INNER JOIN visit ON ClRegistry .PIDC = visit.PIDC SET ClRegistry .Name = visit.Name;

(Name is the field in both)
So what i did, i followed your example and cut off the orhers and formulated this; DoCmd.RunSQL "UPDATE try SET try.Name = 'visit.Name';"

i pasted it in the module(i don't if that's where you meant if know pse let me know where and direct me how i get there.)

After all was done and on opening the visit form; an error came up that: "invalid outside procedure"

could i have put it in a wrong place, i couldn't see the follow expression.
Thanks

[PEB]

So your vb command is:

Docmd.runsql "UPDATE ClRegistry INNER JOIN visit ON ClRegistry .PIDC = visit.PIDC SET ClRegistry .Name = visit.Name;"

:)

[BULYABA]

Thanks but where do i put that vb, because when i put it in the module, it complains that "invalid outside procedures"
Thanks

[PEB]

You can put it into a function in vb module!
like

function my_func()
docmd.runsql "Your SQL"
end function

End when you want to start this you have to type in your event code in the forms

i=my_func()

:)

[BULYABA]

tHANKS, WHEN I TYPED IT IN THE MODULE AS YOU TOLD & COMPILED IT DIDN'T BRING ANY ERROR, SO IT WILL WORK

HOWEVER, YOU HAVE WRITTEN AS BELOW;
End when you want to start this you have to type in your event code in the forms

i=my_func()

WHERE AM I SUPPOSED TO TYPE THIS, THE "i=my_func( )" IN EVENT PROCEDURE, IS IT ON CLOSE, LET ME KNOW PSE, I GUES AFTER THAT MY VB WILL BE WORKING WELL.

[PEB]

Your forms have Event procedures!

Also is the same thing for your controls!

So if you want to update your table immediately after the value is entered in the field use it on after update event in the respective field!

You can put it also on close event in your form! It also will work!

:)

[BULYABA]

Thanks for not getting ties of me but am sure am yet get there:

Yes you say i type it i=my_func() in either onclose or event procedure on a control whichever i wanted. I put that statement "i=my_func( )" and an error message occured that "microsoft office access can't find the macro 'i=my_function( )'

what does taht mean? and where is i coming from; if i is the name of the module or macro developed, i tried to name my module i, but it still didn't work.

hope am clear, explain more pse
Thanks

[PEB]

So,

MS Access gives the opportunity that the user creates a user defined functions!

Those functions usually have parameters ans return also a value

The function that we all created together is user defined function that we named my_func()

my_func doesn't have parameters because between parantheses is empty

The structure of the functions assumes function name parameters and result

so when you want to use a function, you have to type:

variable=functi on(parameters)

in our case it seems to me that you try to use something that we didn't define

my_function() and we have created my_func() haven't we?

Take care about writting of functions!

;)

[BULYABA]

in our case it seems to me that you try to use something that we didn't define

my_function() and we have created my_func() haven't we?

Take care about writting of functions!

Yes, you are right, i just made a mistake when typing to you but what i catually is:
(1)in the module

Function my_func()
DoCmd.RunSQL "UPDATE ClRegistry INNER JOIN visit ON ClRegistry .PIDC = visit.PIDC SET ClRegistry .Name = visit.Name;"
End Function

(2)on the event procedure of on close

i=my_func()


but on closing the form the error i told you about comes up.

Pse help
Thaks