Form code & References??? (1 Viewer)

[paulmcdonnell]

Hi Guys,

I'm using the following code to update a set of address fields from a table when a company is selected from a combo box...

Dim dbf As Recordset, qdf As QueryDef, strSQL As String
strSQL = "SELECT * FROM [Purchases] WHERE " _
& "[Company] = " & Me![Company]
Set dbf = CurrentDb.OpenRecordset(strSQL)
Me![Address_1] = dbf![Address_1]
Me!Address_2 = dbf![Address_2]

However I keep getting the following error

"User-defined type not defined"

with the debugger highlighting...

dbf As Recordset

Is this a reference problem or otherwise?

Hope you can help

Chers
Paul

 

[raskew]

Although I can't find a reference to dbf as a reserved word, 'dbf' is normally used to refer to an xbase (dbase, FoxPro) table. To eliminate a possible conflict, try changing initial lines of code to:

Dim db As DATABASE, rs As Recordset, qdf As QueryDef, strSQL As String
strSQL = "SELECT * FROM [Purchases] WHERE " _
& "[Company] = " & Me![Company]
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

 

[ElsVanMiert]

The short answer:
have you set a reference to the DAO3.6 library?

The long answer:
This is a very good example of poor readibility.
First, you should name your tables and controls in a way that you can easily distinguish them.
Example:
tblPurchases and Purchases_Company
Then do not define vars that you do not use (qdf) and use "friendlier" names.
You do not even need a recordset but you can work with a simple DLookup (might be 1.3 ms slower or faster, but I do not think that such a short delay is not really an issue).

 

[raskew]

You know, Paul asked a reasonable question. Why not wait till he gets 'real stupid'
before you slam-dunk him. Incidentally, DAO 3.6 is not the only library that
works. Kind of depends on the Access version (and we don't know that).

How 'bout providing a solution?

Bob

 

[paulmcdonnell]

Thanks bob. Any help is good help (generally). I am getting better at this stuff but I have a lot of knowledge gaps which cause problems....

Thanks for your help...Let you know how I get on...
Paul