Firstly, thanks in advance for any 2

[allenEd]

Firstly, thanks in advance for any help.....

How do I look up a field (price) from another table whilst in a form, One table makes up the form, and the other table I need to reference, "price" , are linked by a "part no" field.

I need this to insert the price in the form.

I have had some help from Stephenk1973 (thanks ?) …

The code is

Private Sub Form_Current()

Dim sSQL As String
Dim Rec As Recordset
Dim db As Database

' Query to bring out the price, using variables in the form.
sSQL = "SELECT DISTINCT price FROM tblPrices "
sSQL = sSQL & "WHERE partno = '" & PartNo & "';"

Set db = CurrentDb
Set Rec = db.OpenRecordset(sSQL)

' Check something is returned and set the price field to returned value.
If Rec.RecordCount > 0 Then
Price = Rec("price&quot
Else
Price = "0"
End If
End Sub


When I open the form, I get a runtime error ‘3464’

Can anyone help.

Thanks
Allen


MCSE 2000, A+
When Confronted with a serious problem, you can solve it more easily by reducing it to the question, "How would the Lone Ranger handle this"?

 

[nicsin]

I suggest you debbuged the program and tell us which line actually produces the runtime error.

 

[RoyVidar]

Hi!

Assuming your partno is numeric, you could remove the text qualifiers

[tt]sSQL = sSQL & "WHERE partno = " & PartNo[/tt]

(nice if you could also state the errormsg, cause we don't know them all by heart;-))

HTH Roy-Vidar

 

[allenEd]

Thanks Roy-Vidar,

I changed the fields to text for now..

the code
sSQL = sSQL & "WHERE partno = '" & PartNo & "';

now works, (thanks) some parts have the same code for many customer, so I have to look up the part and the customer to get the right price..

I tried this

sSQL = sSQL & "WHERE partno = '" & PartNo & "';" & "WHERE CustID = '" & CustID & "';"

but get error

3142 Characters found after SQL statement.

thanks in advance
Allen

 

[CajunCenturion]

In the following statement

sSQL = sSQL & "WHERE partno = '" & PartNo & "';" & "WHERE 
CustID = '" & CustID & "';"

the semi-colon after PartNo is causing the extra characters problem. Removing that should eliminate that error, but will cause a different error because of the second where.
You might try the following:

sSQL = sSQL & "WHERE partno = '" & PartNo & "' AND CustID = '" & CustID & "';"

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[allenEd]

PERFECT.
THANKS