Open form (tab) and find specific record

[hooi]

I would like to be able to allow user to click on a button, say "User Record" from a form based on the current record (say Contract) the user is editing. However, the User record form is in one of the tabs in a lookup form. How can I program the button so that it goes directly to the record the user is editing. The default code generated from the wizard as shown below doesn't work for a form containing tab. How should it be modified for it to work?


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "aLookups"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToControl "[User Record]"

Thanks...

 

[wizcow2]

hooi

Try this on your last line;

DoCmd.GoToRecord acForm, "User Record"

Tom

 

[hooi]

Hi Tom,

Thank you for your reply.

When the line is added to the last line, error is displayed:
The object 'User Record' isn't open.

I've therefore attempted to open the form by adding another line above it:
DoCmd.OpenForm "User Record"

but the problem is that it brings up just the User Record form, it is no longer being displayed in one of the tabs.

 

[wizcow2]

hooi

I'm missing something I think.

I don't believe the tab your problem, as tabs are visual only. All your controls are really still on your form, they only look as though they are in a tab.

Are you trying to open the form "aLookups" from the form "contracts"?

If so you will need a common control.
Say 'Contracts' has the control 'ContractNo' and 'aLookups' will also have to have the control 'ContractNo' .

Then you can try something like this...

DoCmd.OpenForm "aLookups", , ,"[ContractNo] = Forms![Contracts]![ContractNo]"

Delete all the other access generated code and use this line only on your onclick event of your button on the 'Contract' form.

HTH
Tom

 

[hooi]

Hi Tom,

I think the problem with my form (aLookup) is that each tab has a subform which refers to a table different from the rest of the tabs. Therefore when I use the code generated from the button wizard, it is not able to locate the specific record I'm looking for. Is there any other VBA function/subroutine that I can use to allow me to locate the record i'm looking for based on the same ContractNo?

 

[hooi]

Hi Tom,

My problem has been resolved by adding this line:
DoCmd.FindRecord Forms!ufmContract.[ContractNo], , True, , True

Thank you for your help.