Help with macro to run lookup query twice with one prompt

[joeino]

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:

Function mcrtest()
 
On Error GoTo mcrtest_Err
 
    DoCmd.OpenQuery "qryAppend PersonInfo Key Number Lookup to PersonHistory", acViewNormal, acEdit
    DoCmd.OpenForm "frmPersonInfo Lookup Person Key with Key Number", acNormal, "", "", , acNormal
 
mcrtest_Exit:
    Exit Function
 
mcrtest_Err:
    MsgBox Error$
    Resume mcrtest_Exit
 
End Function
 

The sql for the append query that follows runs the lookup query below.

INSERT INTO tblPersonHistory
SELECT [qryPersonInfo Key Number Lookup].*
FROM [qryPersonInfo Key Number Lookup];
 

The lookup query is

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
FROM tblPersonInfo
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.
Joe

[DonRayner]

Assuming you are using the keyno variable as mentioned, create a function like this.

Public Function KeyNumber() as long
    KeyNumber = keyno
End Function

Then change line 3 of your lookup query to this

WHERE (((tblPersonInfo.[Key Number])=KeyNumber()));

Or if the original form is still open you could just change the line 3 to this

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.