Getting Varible from table in VBA 1

[psbsmms]

I have been converted from Crystal Reports by all your talk of how great the reporting in Access is. I have a access DB that I need to get a invoice out of and I am trying to create a function that retrieves the city state and zip from the customer table and creates a concencrated string that I can place in an IFF function on the invoice report. Every time I try to write the function any reference to the customer table results in an error of undefined variable. i have the function as addres2 and declaied varibles for the city state and zip, but to get the varibles to equal a record in the table has gone no where. Every time I try to connect and set a variable as Database the vba gives me the same undeclaired type or varible since I thought the answer would be to open a DAO or ADO Recordset am I on the right track?

 

[CautionMP]

If I understand your question I think this will help:

Function Address2()
    Dim db As Database
    Dim rec As Recordset
    Dim strSQL As String
    Dim strAddress As String

    strSQL = "SELECT City, State, Zip FROM CustomerTable WHERE [Unique Customer Info];"

    Set db = CurrentDb() 'If CUstomerTable is in same DB
    Set rec = db.OpenRecordset(strSQL)

    strAddress = City & " " & State & " " & Zip

    Address2 = strAddress
End Function

Hope This helps out.

 

[psbsmms]

I understand your code and that is exactly how I am tring to do it, but each time I define db as a database I get user-defined type not defined. I go to the references dialog box and cannot find "data access object" to check mark as is referenced in the help for the error. When I search in VB6 object browser I find class of data with database there but I get the same error when I try to use it in a dim statement. What am I doing Wrong?

 

[CautionMP]

I'm not sure what is causing your error, for ref. her are the three References I have set and I use variations of the above code in several databases;
1) Visual Basic for Applications
2) Microsoft Access 8.0 Object Library
3) Microsoft DAO 3.51 Object Library
Hope this helps.

 

[mcbigj]

I had a problem where I didn't have the DAO library referenced so I would get an error when dimming rs As Recordset.

If I didn't dim rs and wasn't using Option Explicit, it worked.

That is actually very bad programming advice,,LOL so what I think you need to do is reference DAO Object Library

 

[MichaelRed]

depends on which version of Ms. A. you are using NAD which references - AND the ordering of the references.

Typical ver '97 (and 95) would have DAO as Default, while ver 2K would have ADO as default. If both references are included in EITHER version, the order becomes important. To simply assure that there is NO confusion, preface the object declaration with the object type, as in:

Din dbs as DAO.DataBase
Dim rs as DAO.Recordset

(or ADO.) as necessary.


Always remember that MS usually provides three or more ways to do any process - and often requires you to (overtly) indicate which approach you are using.


Slightly askew from your original question, I often just make sure that ALL of the info necessary for a report is included in the report recordsource - even if the query includes redundant info (your name addr ...)

It is often unclear -in Ms. Access- wheather the additional effort to obtain the single (address) record is more efficient than the overall requirement(s) of simply obtaining the "Joined" recordset.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over