Help with recordsets

[Design by Sue]

I know you can't execute a select query and you need to create a recordset but I am not finding any instructions that are helpful to me. The select statement I am trying to use is a follows:

strSQL = "SELECT [PP Type TBL].[Prepack Type], [PP Type TBL].Width, [PP Type TBL].Height, [PP Type TBL].Depth, [PP Type TBL].[Weight Lb], [PP Type TBL].[Weight Oz]FROM [PP Type TBL]WHERE ((([PP Type TBL].[Prepack Type])=[Forms]![Prepack Edit 2 FRM]![Prepack Type]));"

Can someone help me create the code I need for a record set that I can then use to run an update query?

Thanks
Sue

 

[Galaxiom]

Recordsets don't support Access objects because they are sent directly to the database engine. Hence the values from the controls must be concatenated to the SQL string first.

For a numeric PrepackType:

strSQL = "SELECT [PP Type TBL].[Prepack Type], [PP Type TBL].Width, [PP Type TBL].Height, [PP Type TBL].Depth, [PP Type TBL].[Weight Lb], [PP Type TBL].[Weight Oz]FROM [PP Type TBL]WHERE [PP Type TBL].[Prepack Type]=" & [Forms]![Prepack Edit 2 FRM]![Prepack Type]

For text:

strSQL = "SELECT [PP Type TBL].[Prepack Type], [PP Type TBL].Width, [PP Type TBL].Height, [PP Type TBL].Depth, [PP Type TBL].[Weight Lb], [PP Type TBL].[Weight Oz]FROM [PP Type TBL]WHERE [PP Type TBL].[Prepack Type]='" & [Forms]![Prepack Edit 2 FRM]![Prepack Type] & "'"

 

[MarkK]

A very, very, very common pattern using recordsets is this . . .

dim rst as dao.recordset
dim sql as string

sql = _
   "SELECT * " & _
   "FROM Table " & _
   "WHERE SomeCondition"

set rst = currentdb.openrecordset(sql)
with rst
   do while not .eof
[COLOR="Green"]      'do something here with each row . . .[/COLOR]
      .movenext
   loop
   .close
end with

Hope that's helpful,

 

[Privateer]

Looking at your string, I would also add:
try not to have spaces in the table names nor the field names. Try tblPrePackType for the table and PrePackName for the field
before key SQL words, like from and where, you need a space
in "WHERE ((([PP Type TBL].[Prepack Type])=[Forms]![Prepack Edit 2 FRM]![Prepack Type]));""
Get the variable from the other form and assign it to a local variable like
Dim PPT as string
PPT =[Forms]![Prepack Edit 2 FRM]![Prepack Type]
Then your criteria string becomes
"WHERE ((([PP Type TBL].[Prepack Type])=""" & PPT & """
This way you can check the value of PPT before you open the recordset, meaning if it is empty, end the process. Also, when you stop the code, you can mouse over PPT and see the value the variable is holding.
Hope that helps
Privateer

 

[jdraw]

Further to the advice provided by others, there is more information on recordsets here.

 

[Galaxiom]

before key SQL words, like from and where, you need a space
in "WHERE ((([PP Type TBL].[Prepack Type])=[Forms]![Prepack Edit 2 FRM]![Prepack Type]));""

Strictly speaking, the spaces are not required when the object names are delimited with the square brackets or the keywords are up against parentheses. The query parser can still recognise where the keywords start and end.

However it does help readability to include them.

 

[Design by Sue]

WOW - there is a lot of information here (just got back to checking) Thank you - I am sure I will be able to do what I need from your help.

Sue