Lookup into an SQL table and populate fields

[Griff389]

I have a form with customer details such as customer code, address details, tel number etc.

We also use a SQL database as our main system and i'd like my Access database to lookup the address from the SQL table where the customer code are the same.

I've done a search but can't find anything on how to do this.

I don't want to use combo boxes or link the table as there are thousands of record and that would take too long.

I thought maybe a pass through query somehow, but don't don't know how to get it onto the form.

I can create a basic pass through query and pull information into the query, such as

SELECT custname from RM00101 where custname='0123456'

but I get lost when trying to get it to pass a variable into the sql commands so that it can pull out the right customer code.

Anyone got any tips or links to somewhere that can help?

Regards

Griff

 

[Savil]

Create you pass through query and don't worry about what the SQL is.
to do what you want in code

dim strSQL as string
dim strCustname as string

strCustname = "0123456"

strSQL = "SELECT CustName FROM RM00101 " _
& "WHERE custname = '" & strCustname & "'"

Currentdb.querydefs("YourPassThroughQueryName").sql = strSQL

Forms!YourFormName.recordsource = "YourPassThroughQueryName"

HTH Savil UK

 

[Griff389]

Thanks for that.

I can see what the code above is doing. But it looks like the end result is setting the record source.

What i'm after, is some code on the AfterUpdate event of the CustomerCode textbox on my form so when a user changes the customer code, it pulls the relevant customer details from the RM00101 SQL table and inserts it into the corresponding text boxes on my form.

Cheers

Griff