In subform, use a command button to open form

[carlchapman]

hi,

Private Sub Command43_Click()
On Error GoTo Err_Command43_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddCnt"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command43_Click:
Exit Sub

Err_Command43_Click:
MsgBox Err.Description
Resume Exit_Command43_Click

End Sub

This is access built in open form command button. This works fine for opening the default form, but what i need to do is to open the form, but load the record into the form from which the command button was clicked.

Basicaly, at present state i have a parent form, with details on companies. Then i have incorporated a subform that lists all staff relating to the company. i have made the sub form to display as continual forms. For each record (staff member) their is a command button which open a form for viewing staff details. Of course, this only opens the default so i have to search for the staff which i already have the details for in the previous form. What a waste of time searching yet again for the staff member. So i need to sum how grab the primary key field from the sub form , so when the Onclick command button use the key, to pull up the record in the staff form.

The best two ways i can think of are (please bare with me i am new to access and VBA) is to A) create a temp table with the key, and run a query to give the staff form the required fields or B) sum how place the table primary key field into a global varible, and as a default in the staff form, to search for a "globle variable" to give the desired result, then have a query (or what ever method used to fill a forms fields).

the latter i think is the desired approach, however i am not the expert. This is why i need your help, any VBA you can pass my way to perform this task would also be appreciated


cheers

 

[carlchapman]

'These variables are the mirror variables for the fields
'to be tracked in the Table. They are set every
'time a record is fetched, and reset after a history write.
'They are referenced in the BeforeUpdate or AfterUpdate
'procedures to determine if a field was changed.

To do the above task should i use bitmaps?

if so, any idea how?

 

[Karma]

When you added the button the wizard will have asked you if you wanted to open the form and display specific data. It would have then asked you which fields link the two forms together. One will be a list of fields in your subform the other will be a list of fields in your pop up form. The two lists should be the same in your case as you are looking at the same data.

The code it generated would have looked something like;

stLinkCriteria = "[STAFFID]=" & Me![STAFFID]

You can either add the code in by hand or add a new button and let the wizard do it.

HTH

K.

 

[Karma]

'These variables are the mirror variables for the fields
'to be tracked in the Table. They are set every
'time a record is fetched, and reset after a history write.
'They are referenced in the BeforeUpdate or AfterUpdate
'procedures to determine if a field was changed.

To do the above task should i use bitmaps?

if so, any idea how?

This looks like a seperate question and is not very clearly explained. Please start a new thread for this problem and clearly state what it is you are trying to achieve.

K.

 

[carlchapman]

yup, that worked thank you

 

[Karma]

no worries