AUTO UPDATE FROM QUERY

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 28 Nov 2005 01:15 GMT

Hi Hello,

I am a new user for MS ACCESS,

I have a form named "order lookup" with criteria of
[forms]![order]![customer ID]

I want to find the field named "Tel" and copy the data in the "order" form
and try to write the code as below :

Private Sub CUSTOMER_ID_AfterUpdate()

DoCmd.OpenForm "order lookup"
Me.[TEL] = Forms![ORDER LOOKUP]![TEL]
DoCmd.Close

End Sub

It's work fine but it's not a good coding, I want to use querydef method but
fail, could you please help send me a code for reference? Thanks in advance!

Reply to this Message

John Vinson - 28 Nov 2005 01:52 GMT

>Hi Hello,
>
>I am a new user for MS ACCESS,
>
>I have a form named "order lookup" with criteria of
>[forms]![order]![customer ID]

Forms don't have criteria. Queries have criteria. Do you mean that you
based a Form on the query, or what?

>I want to find the field named "Tel" and copy the data in the "order" form
>and try to write the code as below :
[quoted text clipped - 9 lines]
>It's work fine but it's not a good coding, I want to use querydef method but
>fail, could you please help send me a code for reference? Thanks in advance!

I'd be glad to if I had more than a half a clue as to what you were
trying to accomplish!

John W. Vinson[MVP]

Reply to this Message

Andy - 28 Nov 2005 02:11 GMT

Hi John,

Yes, I based a Form on the query, I thought the code I wrote is not good,
could I use the query directly? Appreciate your expert advise!

> >Hi Hello,
> >
[quoted text clipped - 24 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 28 Nov 2005 03:08 GMT

>Yes, I based a Form on the query, I thought the code I wrote is not good,
>could I use the query directly? Appreciate your expert advise!

Sure; just base your Form directly on the query, and open the data
display form using a command button on your search form.

John W. Vinson[MVP]

Reply to this Message

Andy - 28 Nov 2005 04:53 GMT

John,

Actually, I want to write a code to accomplish the action.

The code as below but I thought it's not good :

Private Sub CUSTOMER_ID_AfterUpdate()

DoCmd.OpenForm "order lookup"
Me.[TEL] = Forms![ORDER LOOKUP]![TEL]
DoCmd.Close

End Sub

So I try to write another code as below but fail, please advise, thanks!

Private Sub CUSTOMER_ID_AfterUpdate()

Dim qdf As QueryDef
Dim rst As Recordset
Dim db As Database

Set db = CurrentDb
Set qdf = db.QueryDefs("order lookup")
Set rst = qdf.OpenRecordset

Me.TEL = rst.TEL

rst.Close

End Sub

> >Yes, I based a Form on the query, I thought the code I wrote is not good,
> >could I use the query directly? Appreciate your expert advise!
[quoted text clipped - 3 lines]
>
> John W. Vinson[MVP]

Reply to this Message