How to extract data from QueryDef in VBA Module? 2

[goolawah]

I have a simple application working with Crystal Reports but would like to use it as an example to learn MS Access and VBA, which should provide a more elegant solution.

I need to combine invoice data from three input files and output it as a text file with header and detail lines.

I have used an Access query to prove that I can get the data I need, all OK. I have used the SQL from this query in my application and it compiles OK!

I have proved I can create the output table, new records are added each time I run the application, OK!

What I can't figure out is how to get data from the input Query into the output Table. Everything I do generates an error of some sort I am clearly missing some key understanding here.

I would appreciate any help offered.

 

[goolawah]

Oooops! Stony silence.
Looks like this is not a place for "newbies". Anyway, maybe I should have included the code so here goes. Otherwise I guess I'll just have to buy some more Access/VBA books

Option Compare Database
Option Explicit

Public Sub Convert()
Dim DB As DAO.Database 'declare a variable that points to the database
Dim RSIN  ' declare a variable that will hold the Input recordset
Dim RSOUT As DAO.Recordset ' declare a variable that will hold the Output recordset
Dim SQL As String 'declare a variable that will contain the SQL query

Set DB = CurrentDb 'set the variable DB to the current database
SQL = "SELECT MultiStoreTrn.StoreCode, MultiStoreTrn.ItemCode, MultiStoreTrn.SellIncl01, paxar_stock.QUANTITYSH, Inventory.Description, paxar_stock.BUYSTORCOD, paxar_stock.SHIPNOTENO, paxar_stock.CUSTOMERCO FROM (paxar_stock LEFT JOIN Inventory ON paxar_stock.INHOUSEPRO = Inventory.Code) LEFT JOIN MultiStoreTrn ON paxar_stock.INHOUSEPRO = MultiStoreTrn.ItemCode WHERE (((MultiStoreTrn.StoreCode) = '001')) ORDER BY paxar_stock.SHIPNOTENO;"
Set RSIN = DB.CreateQueryDef("", SQL) 'This is a temporary query definition using the SQL line above to get the required input data
Set RSOUT = DB.OpenRecordset("Output", dbOpenTable) 'Open the recordset using the table OUTPUT


With RSOUT ' This sequence adds new records to the table OUTPUT OK
	.AddNew
	![Type] = "Header" 'this line adds the word HEADER to the field TYPE and adds a new record each time it runs OK
	'![DocNo_CostPrice] = RSIN.[paxar_stock.SHIPNOTENO] I can't figure out how to get a value from the input query to a field in the output table??
	.Update
End With

Set DB = Nothing
Set RSIN = Nothing
Set RSOUT = Nothing

End Sub

Cheers....

 

[VBslammer]

You don't need to create a temporary QueryDef, just open a recordset using the SQL statement you defined:

Public Sub Convert()
  Dim DB As Database
  Dim RSIN As Recordset
  Dim RSOUT As Recordset
  Dim SQL As String

  Set DB = CurrentDb
  SQL = "SELECT MultiStoreTrn.StoreCode, MultiStoreTrn.ItemCode, MultiStoreTrn.SellIncl01, paxar_stock.QUANTITYSH, Inventory.Description, paxar_stock.BUYSTORCOD, paxar_stock.SHIPNOTENO, paxar_stock.CUSTOMERCO FROM (paxar_stock LEFT JOIN Inventory ON paxar_stock.INHOUSEPRO = Inventory.Code) LEFT JOIN MultiStoreTrn ON paxar_stock.INHOUSEPRO = MultiStoreTrn.ItemCode WHERE (((MultiStoreTrn.StoreCode) = '001')) ORDER BY paxar_stock.SHIPNOTENO;"

  Set RSIN = DB.OpenRecordset(SQL, dbOpenDynaset)
  Set RSOUT = DB.OpenRecordset("Output", dbOpenTable)

  While Not RSIN.EOF
    With RSOUT
        .AddNew
        .Fields("Type") = "Header"
        .Fields("DocNo_CostPrice") = RSIN.Fields("paxar_stock.SHIPNOTENO")
        .Update
    End With
    RSIN.MoveNext
  Wend

End Sub

As for the reason it didn't work, you didn't open the querydef before you tried to use it. One extra line of code will open it and assign the results to a recordset:

Public Sub Convert2()
  Dim DB As Database
  Dim qdf As QueryDef
  Dim RSIN As Recordset
  Dim RSOUT As Recordset
  Dim SQL As String

  Set DB = CurrentDb
  SQL = "SELECT MultiStoreTrn.StoreCode, MultiStoreTrn.ItemCode, MultiStoreTrn.SellIncl01, paxar_stock.QUANTITYSH, Inventory.Description, paxar_stock.BUYSTORCOD, paxar_stock.SHIPNOTENO, paxar_stock.CUSTOMERCO FROM (paxar_stock LEFT JOIN Inventory ON paxar_stock.INHOUSEPRO = Inventory.Code) LEFT JOIN MultiStoreTrn ON paxar_stock.INHOUSEPRO = MultiStoreTrn.ItemCode WHERE (((MultiStoreTrn.StoreCode) = '001')) ORDER BY paxar_stock.SHIPNOTENO;"

  Set qdf = DB.CreateQueryDef("", SQL)
  
  Set RSIN = qdf.OpenRecordset(dbOpenDynaset)
  Set RSOUT = DB.OpenRecordset("Output", dbOpenTable)

  While Not RSIN.EOF
    With RSOUT
        .AddNew
        .Fields("Type") = "Header"
        .Fields("DocNo_CostPrice") = RSIN.Fields("paxar_stock.SHIPNOTENO")
        .Update
    End With
    RSIN.MoveNext
  Wend

End Sub

VBSlammer

Unemployed in Houston, Texas

 

[PHV]

And what about this ?
SQL = "INSERT INTO Output (Type,DocNo_CostPrice)" _
& " SELECT 'Header',P.SHIPNOTENO" _
& " FROM paxar_stock P INNER JOIN MultiStoreTrn M ON P.INHOUSEPRO=M.ItemCode" _
& " WHERE M.StoreCode = '001'"
CurrentDB.Execute SQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

PHV - C'mon now...you know that won't work in Access...

VBSlammer

Unemployed in Houston, Texas

 

[PHV]

VBslammer, what's wrong ? Seems I need more coffee ?

 

[goolawah]

Thanks very much for your input - greatly appreciated! I'll run with that and see how I go. I guess I'll be back before long with whatever the next question turns out to be.

 

[goolawah]

It looks like I am still getting something wrong. When I try to run this I get a Type MisMatch error 13 on the Set RSIN line. If I swap the RSIN and RSOUT lines I get an error on RSOUT so I guess it is whichever line it hits first. I think the SQL looks OK. The value of (dbOpenDynaset) when it crashes is = 2 but I am not sure what this means. When I swap the lines and it crashes on RSOUT the value of dbOpenTable = 1. In both cases the values of RSIN and RSOUT = Nothing but I guess that is to be expected if it hasn't processed that line.

Public Sub Convert()

Dim DB As Database
Dim qdf As QueryDef
Dim RSIN As Recordset
Dim RSOUT As Recordset
Dim SQL As String

Set DB = CurrentDb
SQL = "SELECT MultiStoreTrn.StoreCode, MultiStoreTrn.ItemCode, MultiStoreTrn.SellIncl01, paxar_stock.QUANTITYSH, Inventory.Description, paxar_stock.BUYSTORCOD, paxar_stock.SHIPNOTENO, paxar_stock.CUSTOMERCO FROM (paxar_stock LEFT JOIN Inventory ON paxar_stock.INHOUSEPRO = Inventory.Code) LEFT JOIN MultiStoreTrn ON paxar_stock.INHOUSEPRO = MultiStoreTrn.ItemCode WHERE (((MultiStoreTrn.StoreCode) = '001')) ORDER BY paxar_stock.SHIPNOTENO;"
Set qdf = DB.CreateQueryDef("", SQL)
[COLOR=blue]Set RSIN = qdf.OpenRecordset(dbOpenDynaset)[/color]
Set RSOUT = DB.OpenRecordset("Output", dbOpenTable)

I'm guessing that it's best to continue this thread rather than start a new one??
Any help greatly appreciated.

 

[goolawah]

Since my last post I have been searching through previous posts and FAQs of this forum, which suggested maybe I should refer to DAO. I checked the Tools | References setting - OK, and changed the Dim statements to DAO.Recordset (in blue below).

This gets past the previous problem but is now stops on the line .Fields("DocNo_CostPrice") = RSIN.Fields("paxar_stock.SHIPNOTENO")(in red below) with an error 3265 - Item not found in this collection. Any suggestions appreciated.

Public Sub Convert()

Dim DB As Database
Dim qdf As QueryDef
[COLOR=blue]Dim RSIN As DAO.Recordset
Dim RSOUT As DAO.Recordset[/color]
Dim SQL As String

Set DB = CurrentDb
SQL = "SELECT MultiStoreTrn.StoreCode, MultiStoreTrn.ItemCode, MultiStoreTrn.SellIncl01, paxar_stock.QUANTITYSH, Inventory.Description, paxar_stock.BUYSTORCOD, paxar_stock.SHIPNOTENO, paxar_stock.CUSTOMERCO FROM (paxar_stock LEFT JOIN Inventory ON paxar_stock.INHOUSEPRO = Inventory.Code) LEFT JOIN MultiStoreTrn ON paxar_stock.INHOUSEPRO = MultiStoreTrn.ItemCode WHERE (((MultiStoreTrn.StoreCode) = '001')) ORDER BY paxar_stock.SHIPNOTENO;"
Set qdf = DB.CreateQueryDef("", SQL)
Set RSIN = qdf.OpenRecordset(dbOpenDynaset)
Set RSOUT = DB.OpenRecordset("Output", dbOpenTable)


While Not RSIN.EOF
    With RSOUT
        .AddNew
        .Fields("Type") = "Header"
        [COLOR=red].Fields("DocNo_CostPrice") = RSIN.Fields("paxar_stock.SHIPNOTENO")[/color]
        .Update
    End With
    RSIN.MoveNext
Wend

End Sub

Thanks for help

 

[VBslammer]

Once you open the recordset you can reference the field names without the table name. The RSOUT recordset should open the table with:

Set RSOUT = DB.OpenRecordset("[blue]NameOfTable[/blue]", dbOpenTable)

Once it's open, you reference a field with:

RSOUT.Fields("SHIPNOTENO")  '<-- field name only

Item not found errors usually occur when you type the name of the field wrong, such as:

RSOUT.Fields("PhoneNumbr") instead of RSOUT.Fields("PhoneNumber")

Does that help?

VBSlammer

Unemployed in Houston, Texas

 

[goolawah]

That fixed it. Thanks VBSlammer, big help.