lookup field from another table 1

[allenEd]

Hi

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. The field in the form a text box.


I know this sounds dumb..

thanks in advance
Allen.

 

[stephenk1973]

Could do something along the following lines, attaching code to the 'On current'form event.

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 = '" & formfieldname & "';"

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
PriceField = Rec("price&quot
Else
PriceField = "Nope, not found"
End If

 

[allenEd]

Thanks Stephenk,

I ran the code:

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 = "Nope, not found"
End If

End Sub


I get an error "run-time error '3464'
Data type mismatch in critical expression.

I have changed the price fields to currency and made sure that there are no blank price fields...same problem

any ideas.

thanks again.
Allen

 

[allenEd]

Hi Stephank,

This works to a fashion... but all the prices are the same on the form..

private Sub PartNo_AfterUpdate()
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("tblPrices&quot

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

 

[stephenk1973]

Set Rec = db.OpenRecordset(sSQL)

This builds your recordset from the SQL you have created, where the "partno" is your part number field value.

Currently your opening the table, i would guess your price value is from the first row in the table.

I often find it useful to MSgbox out the SQL once it has been built to help spot errors. Just add 'Msgbox sSQL' after the creation of the string. This is what is being used to build your record set.

 

[allenEd]

Hi Stephenk,

Still no joy, but thanks for your help.

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

And I get a data mismatch error on
Set Rec = db.OpenRecordset(sSQL)

do you know what might be causing this.

SQL isn't my area as you probably guessed

thanks
Allen

 

[stephenk1973]

I'd check my (tools)'refrences' in the Microsoft Vb editor. Make sure you've got 'Microsoft Active X Objects 2.6 library' and Microsoft DAO 3.6 Objects ( or try their nearest equivalents).

An alternative method i have been told recently is to use DLOOKUP. DLOOKUP(value,table,criteria)

I'm guessing for yourself this would look like....

=DLookUp("price","tblPrices"," partno='" & [partno] & "' &quot

This is a bit slower but should simply get the result you want

 

[allenEd]

Hi Stephenk,

Sorry to be a pain, The VBA works great now, thanks, I had i few problems importing the data from another program, which affected the code....

The price is selected correctly, but no decimals are picked up. EG £21.89 shows as £22.00, any ideas what is causing this. I cant find anything obivious

the code is

Private Sub PartNo_AfterUpdate()



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 & "' AND CustID = '" & CustID & "';"



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
Me.Requery

End Sub

cheers
ALLEN

 

[stephenk1973]

Could be the format of the form field. Could alternatively be the table field is not formatted as currencty.

Try dropping in a message box to tell you what is comming back for the query [Msgbox Rec("price&quot]. This should help you work out if the problem is in form or the table.