Record in a Subfrm based on another Subfrm (1 Viewer)

[caferacer]

Dear All,

I have been looking for a solution to an issue we are having. Looked at the following sites and tried a blend of them all, but with no luck. I am sure the solution is a variation of one of them or a combination.

http://www.datawright.com.au/access_resources/access_docmd.openform_arguments.htm
http://www.fmsinc.com/free/NewTips/Access/accesstip13.asp
http://allenbrowne.com/ser-28.html

I have a parent form ‘frmPrimeVendors’ with a subform ‘SubfrmEquipment.

Some equipment can be sourced from multiple vendors and I have a link table with many to many relationship between vendors and equipment ‘frmVendorsEquipmment’.

I also have another separate parent form frmPrimeEquipment with a subform called SubfrmVendors. They are essentially just the same as the two previous forms just the other way around.

When in the frmPrimeVendors I have some code which highlights if equipment is available from more than one vendor and if so the user can then press a command button to open the frmPrimeEquipment to enable them to scroll and quickly see who the alternative vendors are.

This all works fine. However, what I would like to happen is when the user opens the frmPrimeEquipment from the command button to view the alternative vendors, the frmPrimeEquipment/SubfrmVendors opens at the same vendor, which currently doesn’t happen.. The users can then scroll as required to see alternatives that supply the same equipment.

At the moment the SubformVendors just opens at one of the other vendors. Didn’t initially think this would or could be a problem, but has caused some confusion to several users when using the DB.

So, I have tried to play about with OpenArgs in the Command Button On Click event and also trying to add a filter but with no success.

Anybody any ideas of where to start.

Thanks

Mark
Access 2010.

 

[kajtam]

Hi Mark,

what is your code to open frmPrimeEquipment form?

 

[caferacer]

Hi Kajtam,

Thanks for the reply. The code was originally from the Wizard, but I have been messing about with to try and get something to work.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmPrimeEquipment"
stLinkCriteria = "[EquipmentID]=" & Me![EquipmentID]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

Thanks

Mark.

 

[kajtam]

Hi Kajtam,

Thanks for the reply. The code was originally from the Wizard, but I have been messing about with to try and get something to work.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmPrimeEquipment"
stLinkCriteria = "[EquipmentID]=" & Me![EquipmentID]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

Thanks

Mark.

Hi Mark, try this one:

stLinkCriteria = "[EquipmentID]=" & Me![EquipmentID]
DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
ObjectName:="[FONT=Calibri]SubfrmVendors[/FONT]", _
PathToSubformControl:="frmPrimeEquipment.[COLOR=red]NavigationSubform[/COLOR]", _
WhereCondition:=stLinkCriteria, _
Page:="", _
DataMode:=acReadOnly

Just change the name of the SubformControl (that's the name of the frame that holds the SubfrmVendors)

Kind Regards
Michal

 

[caferacer]

Hi Michal,

Its coming up with

RTE 6054; "The macro action BrowseTo requires a valid Path argument. A valid Path argument is of the form: MainForm1.Subform1>Form1.Subform1".

Its highlighting all the code in yellow except the first stLinkCriteria line.

Thanks and regards

Mark

 

[kajtam]

Hi Michal,

Its coming up with

RTE 6054; "The macro action BrowseTo requires a valid Path argument. A valid Path argument is of the form: MainForm1.Subform1>Form1.Subform1".

Its highlighting all the code in yellow except the first stLinkCriteria line.

Thanks and regards

Mark

I am not quite sure what is your structure but you need to tell access which form is where. It should look something like that:

stLinkCriteria = "[EquipmentID]=" & Me![EquipmentID]
DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
ObjectName:="SubfrmVendors", _
PathToSubformControl:="frmPrimeEquipment.NavigationSubform>frmPrimeVendors.NavigationSubform", _
WhereCondition:=stLinkCriteria, _
Page:="", _
DataMode:=acReadOnly

 

[caferacer]

Hi Michal,

Sorry have been away on a trip for the last few days with limited internet access.

Also, in my rush to leave for my trip, now I have had time to review this in slower time I have spotted a mistake at my end using the code you provided. I have corrected this and the 6054 RTE no longer exists.

But, the "Enter Parameter Value" dialog box pops up asking for the "Equipment ID".

Any help, much appreciated.

Regards

Mark

 

[caferacer]

Update.

Couldn’t get the BrowseTo to work even after trying to recreate it as an Access Macro, same issue as before RTE 6054. However persevered with OpenArgs which captures the record ID on the open form button on my form and passing this into some FindFirst code in the OnLoad event of the form being opened.

What stumped me initially was the requirements of DoCmd.OpenForm.

Thanks anyway, it forced me into finding the solution.