Recordset

[benkingery]

I think I almost have this recordset functioning properly, but I'm a little amiss. Can anyone help me out? I'm trying to update several fields on a form from a recordset. This is what I have:

Dim strSQL_AZ As String
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb()
strSQL_AZ = "SELECT ORDERS_AZ_Order_Header.[order-id], ORDERS_AZ_Order_Header.[buyer-name], ORDERS_AZ_Order_Header.[buyer-email], ORDERS_AZ_Order_Header.[recipient-name], ORDERS_AZ_Order_Header.[ship-address-1], ORDERS_AZ_Order_Header.[ship-address-2], ORDERS_AZ_Order_Header.[ship-city], ORDERS_AZ_Order_Header.[ship-state], ORDERS_AZ_Order_Header.[ship-postal-code] FROM ORDERS_AZ_Order_Header WHERE (((ORDERS_AZ_Order_Header.[order-id])=[forms]![returns]![OrderNumber]))"
Set RS = db.OpenRecordset(strSQL_AZ, dbOpenDynaset)
Me.CustomerName = RS![buyer-name]
Me.CustomerEmail = RS![buyer-email]
Me.OriginName = RS![recipient-name]
Me.OriginAddress = RS![ship-address-1]
Me.OriginAddress2 = RS![ship-address-2]
Me.OriginCity = RS![ship-city]
Me.OriginState = RS![ship-state]
Me.OriginPostalCode = RS![ship-postal-code]
Me.ReplacementName = RS![recipient-name]
Me.ReplacementAddress = RS![ship-address-1]
Me.ReplacementAddress2 = RS![ship-address-2]
Me.ReplacementCity = RS![ship-city]
Me.ReplacementState = RS![ship-state]
Me.ReplacementPostalCode = RS![ship-postal-code]
Set RS = Nothing
Set db = Nothing

When I run the code, I get a message indicating there are Too Few Parameters. When I go to debug, the following section is highlighted:

Set RS = db.OpenRecordset(strSQL_AZ, dbOpenDynaset)

Any help rendered is greatly appreciated.

 

[benkingery]

I should have done a little bit more checking. Looks like I needed to concatenate in the value of the form control I was calling in the SQL statement because SQL statements in VBA don't have all the same functionality as SQL statements run through the query section of Access. A great article on it here:

http://access.mvps.org/access/queries/qry0013.htm

HOWEVER, now I'm having a problem wherein the value from the recordset appears to not be accepted within the form where I'm trying to put it. Now once I run, I get the following error message:

"The value you entered isn't valie for this field"

The section of code that is highlighted when I debug is:

Me.CustomerName = RS![buyer-name]

Any ideas here on why I couldn't put in this value? If you want to know, the field where it is trying to write is a Text data type with Data length of 50.

Any thoughts?

 

[InitiallyMoved]

If the data type of the order-id field is numeric:

    strSQL_AZ = " SELECT [order-id], [buyer-name],[buyer-email],[recipient-name]" _
              & ",[ship-address-1],[ship-address-2],[ship-city],[ship-state],[ship-postal-code]" _
              & " FROM ORDERS_AZ_Order_Header" _
              & " WHERE [order-id]=" & Forms!returns!OrderNumber

If the data type of the order-id field is text:

     strSQL_AZ = " SELECT [order-id], [buyer-name],[buyer-email],[recipient-name]" _
              & ",[ship-address-1],[ship-address-2],[ship-city],[ship-state],[ship-postal-code]" _
              & " FROM ORDERS_AZ_Order_Header" _
              & " WHERE [order-id]='" & Forms!returns!OrderNumber & "'"