Datasheet selection to specific tab page

[JIMR]

Hello all,

I have a form with a 2 page tab control. I have a datasheet view in tab1 and a single form view in tab2. Both views are created using the same fields. What I would like to do is double click a record in the datasheet view(tab1) and have tab2 display the the selected record from tab1.

I can somewhat make this happen by using the form name that I placed in tab2 but i get a dialog box that I have to enter data into which then displays the form but not in the tab (displays the form used make tab2)

hopefully this is clearer than mud

Anyone have any suggestions?

Thanks

 

[RuralGuy]

Set the LinkMasterField of the SubForm on Tab2 to the PrimaryKey of the SubForm on Tab1.

 

[JIMR]

Thanks, RuralGuy!

I am a novice at access and have read many of the threads in this forum and have found that the task I am attempt to do may get me some suggestions and recommendations to perform this in another fasion such as binding a form myself rather than building the forms and having access bind them.

My novice experience tells me that I can build 2 forms from the same data but in different views but they are not bound. From your most generous reply to linkmasterfield between the 2 tabs. This sounds simple to me and easily accomplished but.... the forms are not bound (this is what could get me some critical finger pointing) not to split hairs but only to understand; you mentioned linking the subforms.

To my knowledge (not being sarcastic) these are not subforms unless placing them in the tab control makes them subforms but I am not sure. Maybe it does not make a difference, so here is my question and I would appreciate yours and or anyone elses assistance. How do I bind forms... i have searched everything i can think of and dont seem to be able to find any information about binding forms other than having access bind them by building forms and subforms.

Is this a simple task or will it require lots of code? Not a programmer but have some ability to see the principles and have used this site to plug in code where it was needed.

Just in case you are wonder why I am trying to do this is simple. I am building as best I can a Management system for our Maintenance Department. All of the systems I have used in the past do just what I am trying to do and it is very handy not to mention user friendly.

Hopefully I did not bable for too long

 

[RuralGuy]

You are correct in that what I proposed requires each form to be bound. Binding the forms after the form is built requires setting the RecordSource of the form to your query/table and then setting the ControlSource of each control to a field in the RecordSource. It is not too difficult to accomplish. If you don't mind me asking, why did you feel you needed to use unbound forms? BTW, the only way to display a form on another form is to use a SubForm control.

 

[JIMR]

Thanks for the reply, much appreciated. The original forms are bound to a table, "did not explain that, my fault" both are using the same data (I will try to explain why soon). I then created another form which I placed the tab control in. In tab1 I placed the data view form and in tab2 the single form view and both forms have the same identicle fields in them.

Explaination of same data different form views
I did this because the dataview form will have many work orders in it. So I planned to have the record in tab1 selectable and populate in tab2 so it can be easily updated and or closed by the user. Their are mulitple tables that make up all of the information in the forms.

The forms within each tab are still bound to the same tables but like you said the control source of the FORM is not bound, thanks for helping with that as I was completely looking over the form and looking at the forms within the tabs. All that said and based on your input I should be able to link the form to the same table and then create the links.

Have I misunderstood your input? I am off to try this but curious if I understand your advise.

 

[RuralGuy]

Post back here if you need further assistance.

 

[JIMR]

Ok, I have the likemasterfield and childfield complete but it now only displays a single item in the dataview which of course I need to have show all work orders.

I tried to attached my database so you have a visual on what I am trying to do but it has failed twice.

 

[RuralGuy]

The LinkMaster/ChildField properties of the Datasheet form will be empty. Create an invisible TextBox on your MainForm and set the ControlSource to the key field of the Datasheet SubForm. Then set the LinkMasterField property of the single form SubForm to point to the invisible textbox on the MainForm and the LinkChildField to the correct field on that form.

 

[RuralGuy]

BTW, if you can not get the zip file of your db < 394KB then use one of the free file hosting sites and just post the link. Be sure and Compact and Repair your db before zipping it.

 

[JIMR]

I am currently working to get this to work using your last input but I think there is a snag. You mentioned on a previous post that in order to get this to work I needed a subform. I have accually copied the form in one view pasted and set its view property to dataview. One other snag, it is identical data down to the primary keys, in fact the same primary key "Work Order ID".

I may need to back up and get some suggestions from you as to which is the best way to continue this. Every field does need to be the same but if the primary key is an issue it can be pulled and the data with in it simply displayed as text for informational purposes to the user but of course the data must update properly to the table.

Not getting this to work and likely because of my methods for making these froms.

 

[RuralGuy]

After a Compact and Repair and a zip, how big is the zip file?

 

[JIMR]

DB Size is 242KB I did not compress the DB when I first tried to attach, will try again here.

BTW, I have the double click working with a pop up window form but not yet able to get it to set focus to the tab. One thing to mention is that I would not only like to be able to double click and go to the correct record but also be able to click one time and navigate any tabs "by clicking the tab"(only 2 tabs at this time) I have in the form with the specific record displayed in each. Examples of what would be included with this form would be labor and parts used.

I'll try to clarify, double click Work Order 45 in tab one automatically takes the user to tab 2. Single click Work Order 45 then click on tab 2 and Work Order 45 is displayed.

 

[RuralGuy]

See if this is what you had in mind. At least it should get you started. I renamed a few controls for clarity and created a query.

 

[JIMR]

RuralGuy..... You Rock! That is exactly what I am looking for.

I see the 2 qry's you made but not making since of them yet. So of course you know this is next; how did you do it? I will need to extend this to the rest of my forms.

 

[RuralGuy]

How did I do what? Ask a specific question and I will give a specific answer. BTW, I only created the NewAssetQuery. The other one was already there.

 

[JIMR]

Thanks RuralGuy,

Specifically, the entire process. I found the invisible textbox"txtLink" don't know how you created it as invisible. I see the qry "NewAssetsQry" and see that you created an inner join, is the inner join tied into the forms in any way, I am not familiar with inner joins and can not find were it is used - seems to function just because it was created (working in the background?). I also see this statement on the dblclick function of the tab1 form/Work Order ID field "Me.Parent.TabCtl0 = 1", I think from reading posts on this forum that = 1 takes the user to form 2 (or 1 is the second tab in a tab control) The Linkmasterfields in tab 2 has the txtlink in it and when I select the linkchild field the dialog box for the linked fields comes up blank so I am not sure at this point all of the things that came together to make this happen.

As you can see I am green at access and with a little bit more work I might be able to reproduce what you did with the rest of my DB. When you get an opportunity; walk me thru how you made this happen (specifically, the functionality you created between tabs 1 and 2). Just maybe I can take it from there.

 

[RuralGuy]

You have found most of the changes I made. All I did was impliment what I suggested in post #8 of this thread. The TabControl Index is the zero based value of the current tab display and it is read/write. So setting it to 1 forces the tab control to switch to the 2nd tab page.

 

[JIMR]

ok, I will see if I can figure out how to make this work. Can you tell me if you have the qry attached anywhere or does it simply work in the background?

Thanks

 

[RuralGuy]

The NewAssetQuery is the query you had in the RecordSource of the two forms you are using in you SubForm controls. I simply copied the SQL and pasted it into the SQL view of a new query and then set the sort sequence. I then used the query instead on your SQL code as the RecordSource of the two forms. What part is confusing you?

 

[JIMR]

Got it, that was done by access when I made the forms and I did not pay any attetion to that SQL.

After I transferred your qry to the working db on my PC I deleted it to see if it was needed. Seems that by using the invisible txtbx as you stated in post #8 all is working with out the qry. However; i just discovered that the 1st record does not transfer to tab 2 until i click on another record, once that is done I can select the 1st record and go to tab 2 at the record is there. Do you think this has anything to do with removing the qry?

BTW, I have not edited the copy you sent me, only copy and pasting the qry and using it as a reference.