Retrieve value of a field in the current record

dakota727

Board Regular
Joined
Dec 3, 2006
Messages
164
Office Version
  1. 365
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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

dakota727

Board Regular
Joined
Dec 3, 2006
Messages
164
Office Version
  1. 365
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

Board Regular
Joined
Dec 3, 2006
Messages
164
Office Version
  1. 365
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top