Open form linking criteria to subform

[kaiana]

I have read thru some threads on this but to no avail.

I have a database that is tracking Service calls

Form 1 (ServiceManager) has a button on it to open form2 (Hardware) it currently opens this Hardware linking UserId on the mainform (listing HardwareID's in the first subform and showing the data in the second subform based on the Hardware ID in the first subform)however I want it to open the Hardware form linking straight to the matching HardwareID ie.
ServiceManager.HardwareID - Forms!Hardware!Hardwaresubform.HardwareID.

I have spent lots of time trying to solve this and have a huge headache, would really appreciate any thoughts to point me in the right direction.

I am using Access 2000

Thanks

 

[BillPower]

Not knowing by which method your forms are currently linked, this is a complete shot in the dark.

Have you tried setting:

Link Child Fields on sub2 to HardwareID and
Link Master Fields on sub2 also to HardwareID

You would need to edit/remove any code/criteria that you have at the moment that links sub2 to sub1.

If this doesn't help, maybe you could explain your setup in greater detail.

 

[kaiana]

Ok Sorry, Let me try again

Form 1 - "Hardware"
Primary Key - UserID

Subform 1 - "Hardwaresubform"
linked by UserID

Subform 2 - "DetailsSub"
linked to subform 1 by HardwareID

Form 2 - "ServiceManager"
Primary key - ServiceID
foreign keys - HardwareID and UserID

no subforms
command button - "HardwareDetails"
set to open Form 1 - Hardware using link criteria UserID, this works fine and brings up the UserID that the Hardware relates to but opens subform 2 relating to the first Hardware that is listed in Subform one. This may not be the Hardware that ServiceManager is actually relating to. I want the command button on ServiceManager to open Hardware linking the HardwareID on ServiceManager rather than UserID (UserID is the primary key on the Main form, HardwareID is on the first subform)

Does this clear it up for you any further. I have the 2nd subform nicely requering and displaying the correct information based on an on click event on the HardwareID in the first subform. That form works beautifully when opening on its own, I would just like it to link a little more specifically when opened from another form and automatically display the data relating to the HardwareID selected in ServiceManager.

Hope you can help!!
Thanks

 

[BillPower]

Is it possible to send me a copy of your DB, make a copy of it if you don't want me to see your data and empty the tables of records. Compact the copy, zip it up and send to billpower@cwcom.net, will post any suggestions here and delete your DB from my PC as soon as problem is resolved one way or the other.

Problems like this can take so long to remedy. (e.g.) Now I've got to ask How is DetailsSub linked to Hardwaresubform by HardwareID. Do you want to show all HardwareID's currently in DetailsSub but default to the current HardwareID in Service Manager or to show only the current HardwareID in ServiceManager. can you answer these anyway, please, whether or not you send your DB.

 

[kaiana]

To link DetailSub to Hardwaresubform, I have put this in the criteria under HardwareID of the query associated with the DetailSub and then I requery on the onclick event of the HardwareID field in the Hardwaresubform.
[Forms]![Hardware]![Hardwaresubform]![HardwareID]

I am still happy to have it come up the way it does now with all hardware listed in Hardwaresubform, this can be beneficial to see what other hardware is associated with this hardware. Instead of it automatically selecting the first HardwareID and listing its details in the detailssub, I want it to select the HardwareID from ServiceManager and show its details in the detailssub.

Have zipped file and sending it. Currently still in development stage so data is dummy data. Thanks so much.

 

[BillPower]

Hi kaiana,

I can't find DetailsSub???

There's NetworkConfigSub, SoftwareSub etc, but no DetailsSub.

BTW, your DB looks excellent, love the color schemes and graphics.

Bill

 

[kaiana]

Bill,

Yes, You are right, sorry meant to clarify. I actually have 5 subforms on a custom tab control which are all requeried when clicking on the serial number in the first subform. It was just easier for the sake of the forum and trying to keep it as simplified as possible to just say one. If I can get it to work on one I can get it to work on all.

Thanks, I am very happy with the graphics and colour schemes. As this is for a IT help desk they need to be able to see at a glance which section they are in. Apart from that, I know that I for one get sick of people saying "no not access, it is just grey, grey and more grey" took a lot of work but it definitely is no longer grey!! Got some great ideas off the forum for changing the buttons and got the buttons off a website.

Hope you can help with the linking problem

Regards

Tanya

 

[BillPower]

Sorry for the delay, the last few days I seem to have been getting involved in some pretty in-depth threads.

Anyway, have changed all sub form's Recordsources to:

SELECT [WarrantyClaims].[HardwareID], [WarrantyClaims].[ClaimID], [WarrantyClaims].[ServiceID], [WarrantyClaims].[DateLogged], [WarrantyClaims].[TimeLogged], [WarrantyClaims].[CallNumber], [WarrantyClaims].[Details on Call], [WarrantyClaims].[Response] FROM WarrantyClaims WHERE ((([WarrantyClaims].[HardwareID])=[Forms]![ServiceManager]![HardwareID]));

That's all there was to it.

I am e-mailing the amended Sub Forms in SubForms.zip, make a copy of your DB, delete the Details Sub Forms in that and import the Sub Forms that you receive from me or, you could copy the SQL above and just Paste over the Existing Form's Recordsources in the Copy.

Let me know if this is what you wanted and most importantly let me know if it works.

Once again I must compliment you on the styles you've incorporated in your app, brilliant, and your coding's pretty good too, surprised you needed Tek-Tips help at all.

 

[kaiana]

Thanks Bill,

It wasn't exactly what I was wanting as doing it that way I won't be able to open this form without doing it from the ServiceManager, however it has given me some thing to ponder on. Perhaps I could duplicate the form and use the old one for opening on it's own (in its current state) and open the duplicate from ServiceManager using the recordsource you suggest. Or perhaps I can use the isopen function to determine if the ServiceManager is open and to then select which select statement to run???

Thank you for your compliments, I am very flattered. I have used tek-tips a lot. A lot of the graphic side of things have been assisted greatly by my husband who is great with making it look good and he is doing some programming in VB so helps with some of the VB code. This is about my 6th Database and probably the biggest, so I have learnt as I go and each database gets better and more efficient. Thanks everyone, this forum has definately been a source of great learning.

 

[BillPower]

Hi,

You didn't mention that you wanted to open the Hardware form from more than one Location.

To do this, on the Switchboard Form (am assuming Switchboard will always be open in your Completed Application) create a new unbound Text Box called, you guessed it, HardwareID, set its Visible property to No.

In any Sub/Procedure that opens the Hardware form, immediately before DoCmd.Openform Hardware etc, enter:

On a Main Form:
Me!Switchboard!HardwareID = Me!HardwareID

On a Sub Form:
Me!Switchboard!HardwareID = Me!SubFormName.Form!HardwareID

Change the Recordsource’s of your Sub Forms to read:
SELECT [WarrantyClaims].[HardwareID], [WarrantyClaims].[ClaimID], [WarrantyClaims].[ServiceID], [WarrantyClaims].[DateLogged], [WarrantyClaims].[TimeLogged], [WarrantyClaims].[CallNumber], [WarrantyClaims].[Details on Call], [WarrantyClaims].[Response] FROM WarrantyClaims WHERE ((([WarrantyClaims].[HardwareID])=[Forms]![Switchboard]![HardwareID]));

This method will give you the flexibility to run the query from anywhere within your App.

You’re lucky that your husband helps you, my partner, she keeps nagging me to get off that thing (my PC).

Hopefully this should do what you want or give you more pointers, you've given me some new ideas (presentationally wise)for future projects, Quid pro quo. Let me know how you get on with this.

Best Wishes

 

[kaiana]

Bill,

Tried a few things suggested by you and that does give me an outcome. I would still like to finetune a little.

I need to be able to run the Hardware form as is, exactly the way it is in the copy I sent to you, as there is a link directly to this form from the Switchboard enabling to search Hardware by User. However when a service call is logged I want a link to this form still listing all the hardware in the Hardwaresubform that is listed under that user but making it set focus to the Hardware that is open in the ServiceManager form. Should they want details on the other Hardware listed by that user I still want them to be able to click on each Hardware Id to get that data. All I want is, if we are opening the form from the servicemanager, for it to automatically select the HardwareID brought up in ServiceManager instead of defaulting to the first record. These are the only two options for opening this form.

Hope this makes it a little clearer. I really appreciate the effort you have already made.

By the way, with regard to the graphics and presentation. All the backgrounds and buttons have been created using (excuse the swear word) a Mac.

 

[kaiana]

Ok trying another approach. Using findfirst so that when the form is loaded it determines whether ServiceManager is open (seperate module) and if so finds the record that relates. Great theory but isn't working. Can someone maybe point me in the direction as to why or am I heading in the totally wrong direction???

This is my code

Dim sfrm As Form
Dim rst As DAO.Recordset
If IsOpen("ServiceManager&quot Then
Set sfrm = Forms!Hardware!Hardwaresubform.Form
sfrm.Requery
Set rst = sfrm.RecordsetClone
rst.FindFirst "HardwareID = " & Forms!ServiceManager!HardwareID
If Not rst.NoMatch Then sfrm.Bookmark = rst.Bookmark
End If