Sending a text field to a sub routine

[billheath]

I use the following VBA to look up maximum, minimums and current quantities for a number of different parts. I havn'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

 

[MazeWorX]

do you mean Me.Part? When referencing controls on a form in the forms code module use Me.ControlName.

and if you post in TGML then its easier for the rest of us to read

Private Sub Part_BeforeUpdate(Cancel As Integer)
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim Low, High As Currency

    Set db = CurrentDb()
    Set rst = db.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
        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
    Me.Qua1.Value = rst!Current_Qua
    'Now requery the form
End Sub

So what exactly is Me![Part] referencing? is this the value you want to pass?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[lameid]

MazeWorX,

Me.ControlName -- Why that syntax?


I would use

Me!Controlname

Or

Me.Controls("ControlName")


! means default collection... Controls happens to be the default collection for a form object

By comparison in queries I would use tablename.fieldname as that is the proper syntax and is more portable to other databases.

 

[MazeWorX]

Actually both methods are correct there has been considerable debate on which is better how ever bench mark tests indicate that they are about even in performance. One could argue that !(bang) references the controls collection however you could also argue the "." implicitly references the control itself at the highest level but like I said benchmark test have indicated 'much' about nothing. In this case Bill indicated he was a little rusty with his vba and I thought it may help him to read the code easier, when working with record sets we use the bang to reference the record to use the 'dot' to reference the controls as well as proper formatting of the code would make it easier to read and trouble shoot the code

So getting back to bill can you try to explain it a little better for us to understand what it is that you want?

BTW Bill you close the record set after the first If statement

rst.Close

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>