Linked Files

S

scootermouse

I have 2 files linked on field named Inv_No. On my form I show the fields of
the main file. I also want to show, from the linked file, the last
Estimated_Shipping_Price (which could be on the second record and not the
third) and a total of all the Insertion_price for that particular Inv_No. I
got it to work except that know when I scroll through the records on the form
it shows the main file fields once for every linked file record. I've
attached the code I used. Hopefully, someone can give me some idea of what
to do.

Private Sub Inv_No_GotFocus()
Dim mss As String
Dim db As Database
Dim rec As Recordset
Dim sqlstr As String
Dim sqlstr2 As String
Dim curfld As String
Dim totlInsPrice As Single
Dim lstshpcst As Single
Set db = CurrentDb
curfld = Form_sosnazzyShip.Inv_No
Dim ivno2 As String
sqlstr = "SELECT sosnazzy_listing.Insertion_Price,
sosnazzy_listing.Estimated_Shipping_Cost FROM sosnazzy LEFT JOIN
sosnazzy_listing ON sosnazzy.Inv_No = sosnazzy_listing.Inv_No "
sqlstr2 = "WHERE (((sosnazzy_listing.Inv_No)=" & curfld & "));"
sqlstr = sqlstr & sqlstr2
Set rec = db.OpenRecordset(sqlstr, dbOpenDynaset)
totlInsPrice = 0
lstshpcst = 0

Do
If Not IsNull(rec("Insertion_Price")) Then totlInsPrice = totlInsPrice +
rec("Insertion_Price")
If Not IsNull(rec("Estimated_Shipping_Cost")) Then lstshpcst =
rec("Estimated_Shipping_Cost")
rec.MoveNext
Loop Until rec.EOF
If lstshpcst > 0 Then Form_sosnazzyShip.Max_Of_Estimated_Shipping_Cost =
lstshpcst
Form_sosnazzyShip.Sum_Of_Insertion_Price = totlInsPrice

End Sub
 
P

pietlinden

I have 2 files linked on field named Inv_No.  On my form I show the fields of
the main file.  I also want to show, from the linked file, the last
Estimated_Shipping_Price (which could be on the second record and not the
third) and a total of all the Insertion_price for that particular Inv_No. I
got it to work except that know when I scroll through the records on the form
it shows the main file fields once for every linked file record.  I've
attached the code I used.  Hopefully, someone can give me some idea of what
to do.

Private Sub Inv_No_GotFocus()
Dim mss As String
Dim db As Database
Dim rec As Recordset
Dim sqlstr As String
Dim sqlstr2 As String
Dim curfld As String
Dim totlInsPrice As Single
Dim lstshpcst As Single
Set db = CurrentDb
curfld = Form_sosnazzyShip.Inv_No
Dim ivno2 As String
sqlstr = "SELECT sosnazzy_listing.Insertion_Price,
sosnazzy_listing.Estimated_Shipping_Cost FROM sosnazzy LEFT JOIN
sosnazzy_listing ON sosnazzy.Inv_No = sosnazzy_listing.Inv_No "
sqlstr2 = "WHERE (((sosnazzy_listing.Inv_No)=" & curfld & "));"
sqlstr = sqlstr & sqlstr2
Set rec = db.OpenRecordset(sqlstr, dbOpenDynaset)
totlInsPrice = 0
lstshpcst = 0

Do
If Not IsNull(rec("Insertion_Price")) Then totlInsPrice = totlInsPrice +
rec("Insertion_Price")
If Not IsNull(rec("Estimated_Shipping_Cost")) Then lstshpcst =
rec("Estimated_Shipping_Cost")
rec.MoveNext
Loop Until rec.EOF
If lstshpcst > 0 Then Form_sosnazzyShip.Max_Of_Estimated_Shipping_Cost =
lstshpcst
Form_sosnazzyShip.Sum_Of_Insertion_Price = totlInsPrice

End Sub
Click to expand...

Why not use a domain aggregate function? DMax()?
 
S

scootermouse

Why not use a domain aggregate function? DMax()?
Click to expand...
Because I don't want the Maximum amount. I want the last amount that was
placed in records. The Inv_No may have 3 linked records. The
Estimated_Shipping_Cost in the first record might be higher than the one in
the second record and in the third record there might not be and
Estimated_Shipping_Cost. I would want to be able to pick up the
Estimated_Shipping_Cost from the second record to place on the form.

scootermouse
 
Top