Sending a field value to a subroutine

[billheath]

(I posted this originally in the sq-query forum. Makes more sense here.)

I use the following VBA to look up maximum, minimums and current quantities for a number of different parts. I haven't used VBA in a while and I can't remember how to do this in a single sub routine. I know there is a way to pass the value of the field part to the routine, but I can't find it here. In other words, I need to replace the name in the part field for whatever the name or number is.


Private Sub Part_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset
Dim Low, High As Currency
Set rst = CurrentDb.OpenRecordset("Select St_Sym, Current_Qua,Lowest,Highest from Stock_Data where St_Sym = _'Part'")

If Me![Part] < Forms!Stock_Data![Lowest] Then
Low = Me![Part]
With rst
.Edit
!Lowest = Low
.Update
End With

rst.Close


Forms!Stock_Data![Lowest] = Me![Part]
End If
If Me![Part] > Forms!Stock_Data![Highest] Then
High = Me![Part]
With rst
.Edit
!Highest = High

.Update
End With

'Forms!Stock_Data![Highest] = Me![Part]
End If
With rst
Qua = !Current_Qua
End With

Me!Qua1 = Qua
End Sub

Thanks, I hope this makes sense. Bill

 

[vbajock]

Private Sub Part_BeforeUpdate(Cancel As Integer)

if MyPartsUpdate(me!Part)=true then

'it worked

else
'it didn't

Endif

End Sub


Function MyPartsUpdate(byval strPart as string) as Boolean

Dim rst As DAO.Recordset
Dim Low, High As Currency
Set rst = CurrentDb.OpenRecordset("Select St_Sym, Current_Qua,Lowest,Highest from Stock_Data where St_Sym = _'Part'")

If strPart < Forms!Stock_Data![Lowest] Then
Low = strPart
With rst
.Edit
!Lowest = Low
.Update
End With

rst.Close


Forms!Stock_Data![Lowest] = strPart
End If
If strPart > Forms!Stock_Data![Highest] Then
High = strPart
With rst
.Edit
!Highest = High

.Update
End With

'Forms!Stock_Data![Highest] = strPart
End If
With rst
Qua = !Current_Qua
End With

Me!Qua1 = Qua
End Sub

 

[vbajock]

^you need to add your own true/false value return logic

 

[billheath]

Thanks, vbajock. This is a great start. It has improved my understanding.
When I ran the code though, I got the quantity in the field. What I need to send is the field name or stock number. In other words, I have a quantity field for each part and I want the subroutine to work on the different fields.
For example , I have a field named "AUY20". In that field is the current quantity. I want to send the name, AUY20 to the subroutine and let the sub do the massaging on the mins and maxes, etc.

 

[vbajock]

Do you want to send the value of the field or the name of the field?

 

[billheath]

I finally figured it out. I worked on what you sent and figured out how to send the field name also. Thank you so much for your help!
Bill