What's wrong with this code?

[Sean25]

Okay, I've set up the following code:

Dim ItemData As ADODB.Recordset
Set ItemData = New ADODB.Recordset

With ItemData
    .Fields.Append "Desc", adChar, 80
    .Fields.Append "Colr", adChar, 25
    .Fields.Append "CAND", adCurrency
    .Fields.Append "USAD", adCurrency
End With

ItemData.Open

With ItemData
    .AddNew
    ![Desc] = "SELECT ItemTable.Description FROM ItemTable WHERE ItemTable.ProductNumber = Me.PartNumberEntry"
    ![Colr] = "SELECT ItemTable.Colour FROM ItemTable WHERE ItemTable.ProductNumber = Me.PartNumberEntry"
    ![CAND] = "SELECT ItemTable.CAD FROM ItemTable WHERE ItemTable.ProductNumber = Me.PartNumberEntry"
    ![USAD] = "SELECT ItemTable.USD FROM ItemTable WHERE ItemTable.ProductNumber = Me.PartNumberEntry"
    .Update
End With

Me.DescriptionEntry = ItemData.Desc
Me.ColourEntry = ItemData.Colr

If Me.Country = "Canada" Then
    Me.UnitPriceEntry = ItemData.CAND
ElseIf Me.Country = "USA" Then
    Me.UnitPriceEntry = ItemData.USAD
End If

So, the problem is I get a comile error that highlights ".Desc" in the line:
Me.DescriptionEntry = ItemData.Desc

and states "Method or Data Member Not Found".

What am I missing?

Thanks,

S.

 

[dcx693]

What am I missing?

Correct syntax:

Me.DescriptionEntry = ItemData.Desc

Try:

Me.DescriptionEntry = ItemData!Desc

or

Me.DescriptionEntry = ItemData("Desc")

 

[Sean25]

Correct syntax:

Me.DescriptionEntry = ItemData.Desc

Try:

Me.DescriptionEntry = ItemData!Desc

Okay, that seems to have taken care of that error, but now when the code is run, I'm getting one that's even more vague:

"Multi-step operation generated errors. Check each status value."

Any ideas on this one? Can't seem to find anything (in an albeit cursory search).

Thanks again,

S.

 

[dcx693]

I think there are some issues with this section of the code:

With ItemData
    .AddNew
    ![Desc] = "SELECT ItemTable.Description FROM ItemTable WHERE ItemTable.ProductNumber = Me.PartNumberEntry"
    ![Colr] = "SELECT ItemTable.Colour FROM ItemTable WHERE ItemTable.ProductNumber = Me.PartNumberEntry"
    ![CAND] = "SELECT ItemTable.CAD FROM ItemTable WHERE ItemTable.ProductNumber = Me.PartNumberEntry"
    ![USAD] = "SELECT ItemTable.USD FROM ItemTable WHERE ItemTable.ProductNumber = Me.PartNumberEntry"
    .Update
End With

Based on your field declarations, I can see that you're intending that the SQL strings actually get executed, and a value stored in the fields of your recordset. That won't work the way you want it to for several big reasons. First, recordset fields can hold one value, SQL strings return entire recordsets (even if they only hold one value in that recordset). Next, you need to place the form references outside of the quotation marks. As in: "SELECT ItemTable.USD FROM ItemTable WHERE ItemTable.ProductNumber = " & Me.PartNumberEntry

The easiest fix I can suggest is to replace those "SELECT...." strings with simple DLookup functions. Something like: DLookup("[USD]","ItemTable","[ProductNumber] = " & Me.PartNumberEntry)