By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,200 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,200 IT Pros & Developers. It's quick & easy.

Help with macro to run lookup query twice with one prompt

P: 1
I want to do a lookup query and append the record to a history table before editing the data.

I created a macro to run the lookup query to append the record to history and it works fine. I did the same with the lookup query that allows the user to edit the record. I then converted them to vb and combined them. Code follows.

My thinking is to create a variable dim keyno longint
then pass keyno to the lookup queries.

I am kind of new to this how do I pass keyno to the queries?

vb script follows:

Expand|Select|Wrap|Line Numbers
  1. Function mcrtest()
  3. On Error GoTo mcrtest_Err
  5.     DoCmd.OpenQuery "qryAppend PersonInfo Key Number Lookup to PersonHistory", acViewNormal, acEdit
  6.     DoCmd.OpenForm "frmPersonInfo Lookup Person Key with Key Number", acNormal, "", "", , acNormal
  8. mcrtest_Exit:
  9.     Exit Function
  11. mcrtest_Err:
  12.     MsgBox Error$
  13.     Resume mcrtest_Exit
  15. End Function
The sql for the append query that follows runs the lookup query below.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblPersonHistory
  2. SELECT [qryPersonInfo Key Number Lookup].*
  3. FROM [qryPersonInfo Key Number Lookup];
The lookup query is

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPersonInfo.[Key Number], tblPersonInfo.[Employee Number], tblPersonInfo.[First Name], tblPersonInfo.[Middle Initial], tblPersonInfo.[Last Name], tblPersonInfo.Department, tblPersonInfo.Active, tblPersonInfo.[Date of Change], tblPersonInfo.[Previous Change Date], tblPersonInfo.Notes, tblPersonInfo.Initials
  2. FROM tblPersonInfo
  3. WHERE (((tblPersonInfo.[Key Number])=[Enter the Key Number:]));
The nuts & bolts of the question is How do I pass keyno to the query rather than having Access prompt for from the where statement?

Thanks in advance.
Mar 12 '09 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 489
Assuming you are using the keyno variable as mentioned, create a function like this.
Expand|Select|Wrap|Line Numbers
  1. Public Function KeyNumber() as long
  2.     KeyNumber = keyno
  3. End Function
Then change line 3 of your lookup query to this
Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblPersonInfo.[Key Number])=KeyNumber()));

Or if the original form is still open you could just change the line 3 to this
Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblPersonInfo.[Key Number])=Forms!FormName!FieldName));
With FormName and FieldName replaced with the name of the form and field that contains your key number.
Mar 14 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.