It about SQL in Access event editor

[Briton]

Hi

I am using Access and want to do som SQL in the event builder.

I got a Form with a textfield named "Number" and another named "Name".
When the user type a number in "Number" i should start the
After_update event.
Here it should look in a table "T_Errors" if the number is there. If
it's not it should show a msgBox "Try another number" or something
like that.
If the number is in the table, it should put the corresponding name in
the textfield called "Name".
I now how to put the SQL statement together (SELECT T_Errors.Name FROM
T_Errors WHERE T_Errors.Number = ME!Number), but how do I effectuate
it ?

I know that I can use a simple ComboBox to do the job, but I want to
do some more with the data and I want to now how to use SQL in the
Event Editor.
I also now the docmd.RunSQL command, but it don't work in a SELECT
statement.
I have seen a lot of similar questions and answer for it, but that
don't work in my code. There must be something I don't understand.

I have seen code like this:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

Dim strSQL As String

strSQL = "SELECT Varenr_ FROM Table1 WHERE Nummer = Me!Indtast"

Set rst = db.OpenRecordse t(strSQL, dbOpenDynaset)

But if I try this, I gpt an error "Userdefine d type not declared!

best regards
Bjarne

[Larry Linson]

Re: It about SQL in Access event editor

First, I'd suggest some self-study time with a good self-study book. Some I
like, roughly in ascending order of "depth" are Microsoft Access
Step-by-Step from MS Press, Special Edition Using Microsoft Access by
Jennings from QUE, Microsoft Access 2003 Inside Out by John Viescas from MS
Press.

In the meanwhile, check help for the builtin DLookup function, or Data
Access Objects db.Execute.

However, instead of asking the user to try to remember the number and then
verifying it, you might consider creating a Combo or List Box, which allows
the user to select from valid numbers. You can include additional columns in
its Row Source to retrieve the corresponding values from other columns. Not
surprisingly, it is the Column property you'd use to obtain that other
column. Check Help on Combo Box or List Box as a start, then post any
additional questions back here.

Larry Linson
Microsoft Access MVP





"Briton" <briton@get2net .dk> wrote in message
news:6ad1507a.0 503180252.10766 6c6@posting.goo gle.com...[color=blue]
> Hi
>
> I am using Access and want to do som SQL in the event builder.
>
> I got a Form with a textfield named "Number" and another named "Name".
> When the user type a number in "Number" i should start the
> After_update event.
> Here it should look in a table "T_Errors" if the number is there. If
> it's not it should show a msgBox "Try another number" or something
> like that.
> If the number is in the table, it should put the corresponding name in
> the textfield called "Name".
> I now how to put the SQL statement together (SELECT T_Errors.Name FROM
> T_Errors WHERE T_Errors.Number = ME!Number), but how do I effectuate
> it ?
>
> I know that I can use a simple ComboBox to do the job, but I want to
> do some more with the data and I want to now how to use SQL in the
> Event Editor.
> I also now the docmd.RunSQL command, but it don't work in a SELECT
> statement.
> I have seen a lot of similar questions and answer for it, but that
> don't work in my code. There must be something I don't understand.
>
> I have seen code like this:
>
> Dim rst As DAO.Recordset
> Dim db As DAO.Database
> Set db = CurrentDb
>
> Dim strSQL As String
>
> strSQL = "SELECT Varenr_ FROM Table1 WHERE Nummer = Me!Indtast"
>
> Set rst = db.OpenRecordse t(strSQL, dbOpenDynaset)
>
> But if I try this, I gpt an error "Userdefine d type not declared!
>
> best regards
> Bjarne[/color]