SQL phrasing

[Mark J Kubicki]

I want the default value for a text box to be selected from a table where
the record meets certain criteria:

manufacturer = the form's manufacturer field
catalogueNo = the form's catalogueNo field
and of those fields that do match these criteria, the default value should
be the one with the most recent date

the entered value is the Cost field of the record that matches...


so I enter in the default value as

select (

![Cost])
where (max(

![Date]))
and (

![manufacturer] = me.manufacturer.value
and (

![catalogueNo] = me.catalogueNo.value

but this is not correct
would someone (please) correct this for me?

thanks in advance,
mark

 

[Albert D. Kallal]

Well, first, of course those other fields have to be entered first.

Thus, you can just shove in the sql into some default setting (how would
ms-access know when you are done setting those other fields).

So, the default value can't be set using the default value...because other
fields need to be entered...

Further,
I would in this case use the on-enter event of the Cost field. (as
mentioned, perhaps the before insert event could be used here, but we don't
have the other two fields set yet. So, in effect, we have to put the code in
the two other fields.....

So, lets build the code to set this value

Public Function SetCost()

Dim strSql As String
Dim rst As DAO.Recordset

If IsNull(Me.manufacturer) = True Then
Exit Function
End If

If IsNull(Me.catalogueNo) = True Then
Exit Function
End If

strSql = "select top 1 cost from tblCost " & _
"where manufacturer = " & Me.manufacturer & _
" and " & _
"catalogNo = " & Me.catalogeNo & _
"order by [date] desc"


Set rst = CurrentDb.OpenRecordset(strSql)
If rst.RecordCount > 0 Then
Me.cost = rst!cost
End If
rst.Close


End Function

Now, in the manufacture after update event, go, call the above routine

SetCost

and, also, in the after update event for catalogNo, again call the above
routine.

note that if manufacture, or catalog number is a text field, then you have
to put quotes around the values

eg:

strSql = "select top 1 cost from tblCost " & _
"where manufacturer = '" & Me.manufacturer & "'" & _
" and " & _
"catalogNo = " & Me.catalogeNo & _
"order by [date] desc"

Note that the order by descending means that if there are "many" matches,
the FIRST record in the reocrdset will be the most recent date.