Use popup form to select a record on the main form

[RexesOperator]

Can anyone see what I am doing wrong?

I have a main form that has a tab control on it. The main form is called frm_AddNewCompanyContacts. It is opened in edit mode. To see if a company exists I use a command button to call a popup form with a list of all companies' IDs and names. I then want the user to be able to click on a commad button on the popup form to take the main form to that record. After an intial post, and subsequent search, I found the appropriate code. This is what I am using for the onclick event of the command button on the popup form:

Private Sub cmdGoToCompany_Click()
Dim rst As Recordset
Set rst = Forms![frm_AddNewCompanyContacts].RecordsetClone
rst.FindFirst "[CompanyID]='" & Me![CompanyID] & "'"
If rst.NoMatch = False Then
Forms![frm_AddNewCompanyContacts].Bookmark = rst.Bookmark
End If

End Sub

When I click on the command button I get an error message:

"The expression On Click you entered as the even property setting produced the following error: A problem occurred while Microsoft Office Access was communication with OLE server or ActiveX Control (I don't have an ActiveX Control).

*The expression may not result in the name of a macro, the name of a user-defined function, or [Even Procedure].
*There may have ben an error evaluating the function, event or macro."

 

[Uncle Gizmo]

There is an example in this thread here: which opens a form to a specific record.

However the second method the one marked "the correct way" can also be used to navigate to records on an open form.

You can test this yourself with the example, once you have called the second form, remove the filter with the right click button, move to a different record on the second form and then click the button on the first form again and see it return to the original record. (number four)

 

[RexesOperator]

Thanks for posting the thread, but I know how to go to the specific record on a form using the command wizard. What I want is to click on the popup form (which is a series of continuous forms) and go to the correct record on the main form.

The code above is what I found to do this but it is generating the error message I posted. My question is why?

 

[Uncle Gizmo]

>>> I want is to click on the popup form (which is a series of continuous forms) <<<
In that case your popup form has a subform on it. I believe you have to put the command button on the subform, it will then appear on each record in the sub form window on your popup form, and then you would click that button.

 

[RexesOperator]

That's correct and that's exactly what I have. But why does the code generate the error - what am I doing wrong?

 

[Uncle Gizmo]

>>> I believe you have to put the command button on the subform, <<<

Your Command button code is generating an error because it is on the popup form, not the subform where the information is. You have to put the button on the subform.

I presume the main form, (the one you call the popup form) is not linked to any data, so in this statement "[CompanyID]='" & Me![CompanyID] & "'" ---- this part: --- Me![CompanyID] --- is looking for the companyID in the record source of your popup form. However it hasn't got a record source, it only has a subform. So that's why you get an error, it can't find the field named companyID you are telling it to look for. I should say that's my guess, --- I don't know for sure!

 

[Fear Naught]

Hi RexesOperator,

The example that I posted for you last week allows you to select a record from a combo box without the need for a popup form.

Why don't you use that??

 

[RexesOperator]

Thanks Uncle Gizmo - I will try your solution tonight.

To Fear Naught - that would be too easy - my boss likes the popup form and said he'd rather have that if I can get it working. Since he has to use the db too, I'd like to keep him happy. If I can't get it working I will use your solution.

Thanks for your combined help.

 

[RexesOperator]

Once again (you must get tired of hearing this), thanks to both - I figured out how to get it working. I originally had the popup based on a separate query to the company table. By basing it on the same query as the main form, the command button on the popup works.

Hopefully (I can dream can't I?) that's the last bug I come across. Everything else seems to be doing what I want it to - not gracefully or elegantly, but it works and my boss is happy!

 

[Fear Naught]

You have cracked the most important aspect of development - keeping the boss happy

Good luck with the rest of the project

 

[Uncle Gizmo]

married man?

You have cracked the most important aspect of development - keeping the boss happy

Good luck with the rest of the project

And I would guess, spoken by a married man! Fear naught, are you sure?