Retrieve value of a field in the current record

[dakota727]

What I would like to do is simply this. When the user is on a form (in datasheet view) I would like to have an autokeys macro runs a procedure that runs a select query with a criteria set to the catalog number of the current record. Or better yet the query makes the current record's catalog num as the default in and input box and then runs the select query.

I am just in search of a VBA method to return the value of a single field of the current record. I'm reasonable sure I can take it from there.

 

[dakota727]

One other issue...
I started out using something like this but I would like to be able to run this code using a shortcut key and have it return the catalog number from the open form not just the form I have indicated in the code "frmSelect".

Is there an easy way to determine the active form?


Sub Locate()

Dim currentCatNum As String
Dim item As String
Dim Mfilter As String

Mfilter = ""
item = Forms![frmSelect].CatNum
currentCatNum = InputBox("Enter the catalog Number to search for anyone who has order the dicated item", "SEARCH FOR ITEM", item)
Mfilter = Mfilter & "[CatNum]= '" & currentCatNum & "'"
DoCmd.OpenForm "frmHistory", acFormDS, Mfilter, Mfilter, acFormReadOnly, acWindowNormal
End Su

 

[dakota727]

I think I have it now:

Dim currentCatNum As String
Dim item As String
Dim Mfilter As String
Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm



Mfilter = ""
item = frmCurrentForm.CatNum
currentCatNum = InputBox("Enter the catalog Number to search for anyone who has order the dicated item", "SEARCH FOR Item", item)
Mfilter = Mfilter & "[CatNum]= '" & currentCatNum & "'"
DoCmd.OpenForm "frmHistory", acFormDS, Mfilter, Mfilter, acFormReadOnly, acWindowNormal
End Sub