Display data from 2 different tables in one form

[awade]

Good evening all,

I currently have two different excel spreadsheets linked to access 2003. The first few columns are very similar with similar headings. What i have now is a form displaying the data from one spread sheet (Engine Status), and another form dispaying the data from the other (Engine Health). As i am very new to access is there a way that i can link the two spreadsheets together and display all the data on one single form. I would like a combo box to find the required serial number (tail number) and once the serial number is selceted the data from both spreadsheets will be dispayed. The spreadsheets i have to work with are of set standard for ease to input data from numerous other sources. I have attached examples of the two spreadsheets with the headings that are the same between the two although i have attached these to a sinle work book in reality they are seperate work books. What i would like is for a combo box to display the Tail number then have single boxes to display core serial numbers across the form in order of position, then all the other data associated with that core serial number displayed in seperate boxes below the core serial number.
I hope this is possible as it will prevent alot of toggling between forms.

 

[CJ_London]

1. Create a query which links the two linked tables together. It looks like you need to link on tail number to tail number and Pos to position.
2. Then create your form and use the name of your query as the recordsource.
3. In your combo box set the rowsource to "SELECT DISTINCT TailNumber FROM YourQry ORDER BY TailNumber"
4. In your combo box after update event put the following:

Me.filter="[TailNumber] = " & [COLOR=red]YourComboBox[/COLOR]
Me.filteron=True

The bits in red you will need to change to the names you are using

 

[awade]

Thank you for your response. I am having trouble getting it to display the data I want in a format that is required.
I apologise but I am very new to access and am still coming to terms with how to get things to work. What I am after is that I have 4 columns each with 10 text boxes. what I am trying to achieve is when I select the tail number from the combo box, the data from the 4 engines ( position 1,2,3,4) will display in there own separate text box. I have used the code above and can get the individual engines to display but I can't get all 4 to display at once.

 

[CJ_London]

OK, here is another possible solution.

Move the compbo box to the header part of the form - if you can't seethe header then in form design, right click anwhere in the form and select Form/Header footer.

Then set the form format default view to continuous forms.

 

[awade]

CJ, from an early test it looking ok. I will just have to play around with formatting to see if I can only get 4 sets of data to display, as it now displays all of the data in one large column.
Thanks for all your help so far.

Cheers

 

[CJ_London]

Might be able to help if you can post a screenshot of your form

 

[awade]

Might be able to help if you can post a screenshot of your form

CJ, I have managed to get the Query working with the two tables supplying data to the one form. Thanks for that...
Please find attached the screen shots you requested. The form screen shot is where i want the positions to display data from 1 through 4 from right to left. This would happen when i select the tail number. Right now when i select the tail number i have 4 tail numbers to select from each one displaying the data from a specific position, eg the first instance of 5440 in the tail number combo box displays position 1 data in all four coloumns, the second instance of 5440 displays position 2 data in all four columns and so on. I have also inslcuded screen shots of the tables and query in hope that can help you find away to display the data in a way that im after.
Thanks for your help so far.

awade.

 

[CJ_London]

Hi,

Ah I thought you we dispplaying the data horizontally - which is always a possibility to consider.

If you want to display vertically then we need to go a slightly different route and use subforms.

So:

1. Modify your query by adding a calculated field as follows: Ind:[Tail Number] & "_" & [Pos]
2. create a new form which looks like one of your vertical columns of data - including the position control (so could just copy what you already have)
3. set the recordsource for the new form to be query1
4. open your main form in design view and remove the recordsource so it becomes unbound (leave the combo alone but you can remove all the other controls relating to the engines - they will now be on your subform)
5. drag 4 instances of your new form onto your main form and size and position to fit
6. create 4 controls, we'll call them P1, P2, P3 and P4 and position above the subforms. Set each control value to =Combo & "_1" (2/3/4 as required, same as the new Ind field in the query) - you can set their visible property to false once you are happy it is working OK
7. For each of the subforms set the link child field to Ind and the link Master fields to P1 or P2 etc as required.

Job done!

I'm not sure where you are getting the AF hours etc information, but we can modify the combo query to bring that through if required

See how you get on

 

[awade]

Thanks CJ.

I keep getting a "the specified field '[Tail Number]' could refer to more than one table listed in the FROM clause of your SQL statement"

What does that mean.

 

[pr2-eugin]

I am not sure where this occurs, as I have not completely read this thread, but the problem it refers to one or more field would normally occur when you are performing a JOIN between two tables and the tables have same field names..

Example, if you have two tables tableA and tableB..
tableA
someID
prodID

tableB
autoID
prodID
prodDescription
prodPrice

If you see the two tables have the same field name prodID, if the following SQL is used..

SELECT someID, prodDescription 
FROM tableA INNER JOIN tableB ON tableA.prodID = tableB.prodID
WHERE prodID = 75

Which prodID are you refering to is it tableA's or tableB's?? That needs to be clear.. so it is always best to explicitly show them..

SELECT [COLOR=Blue][B]tableA.[/B][/COLOR]someID, [COLOR=Blue][B]tableB.[/B][/COLOR]prodDescription 
FROM tableA INNER JOIN tableB ON [COLOR=Blue][B]tableA.[/B][/COLOR]prodID = [COLOR=Blue][B]tableB.[/B][/COLOR]prodID
WHERE [COLOR=Blue][B]tableB.[/B][/COLOR]prodID = 75

Hope that makes sense..

 

[CJ_London]

The bit of the query it is probably referring to is

Ind:[Tail Number] & "_" & [Pos]

change this to Ind:[YourTbl].[Tail Number] & "_" & [Pos]

Substitute YourTbl with the name of your table that you are referring to (something like E21...?)

You may also get the next problem with Pos (can't remember your table details now!) in which case the solution is the same

 

[awade]

CJ thanks for the info so far.

I have dragged 4 lots of the form onto the main form, and I know only have one coy of the dragged forms that I can add information to. the other three "children" are just white boxes, that when I try and drag any information into then it places it on the forma behind.
Is there are setting I haven't got right?
Also each time I open the form up I get a pop up box "enter parameter value P1,P2,P3,P4".

Cheers
awade

 

[awade]

Also I am struggling to find what a control is let alone the control valve.

 

[CJ_London]

the control valve - should be control value

other three "children" are just white boxes

This is OK, you are in design mode so if you wanted the edit the design of the subform, you can only have one version open. Change to normal view and you will see all for subforms as you would expect.

A control is any object on a form or report (textbox,combo box, label etc). To see their properties, right click on the control and select properties or having selected a control select design>property sheet from the ribbon.

Note you can select a number of controls at the same time and change common properties.

You will find the link child master properties under the data tab for the subform properties

Also each time I open the form up I get a pop up box "enter parameter value P1,P2,P3,P4".

This is beacuse you havn't done this

6. create 4 controls, we'll call them P1, P2, P3 and P4 and position above the subforms. Set each control value to =Combo & "_1" (2/3/4 as required, same as the new Ind field in the query) - you can set their visible property to false once you are happy it is working OK

Set each control value to =Combo & "_1"

This should say control source (under the data tab)

 

[awade]

CJ, I have labeled, renamed, changed all of the parameters as suggested and still get the "enter parameter value" pop up box with no data being displayed in the columns. I have attached some screen shots of what i have done.

Sorry, for all of this, but i am very new to access, but thanks for all your help so far. Im learning.....

 

[CJ_London]

OK - I can see what you have done.

You have put P1, etc in the labels Captions - the labels can actually be deleted as they are not required. You can also delete the labels "FORM1A etc as thy also are not required.

The comboboxes you have called combo61 etc should be called P1 etc - also they should be text boxes, not comboboxes. Sorry my post wasn't very clear.

And just to confirm - in your control sources you have =[Combo] & "_2" etc. Combo is the name of your Tail Number combo box? (It needs to be)

 

[awade]

You can probably hear me in the UK now, jumping around yelling "IT WORKS"
Thanks for your help with this CJ. Im sure as I get further into this I may need your help with other problems I may encounter.
Appreciate all your help and patience.

Cheers

awade

 

[CJ_London]

Glad to help!

 

[awade]

Originally Posted by awade
Good Evening CJ,

Thanks for your help the other day in getting my query and 4 sub forms sorted out it has allowed me to display data in a way that has never been seen before on it has impressed a lot of people. Due to this I now would like to display similar data the same way. I have three excel tables directly linked to access and I would like to display the data from these tables directly into a from the same as we did with the query and sub forms.

As these table have hyperlinks I was wondering if I can directly create a form and copy that in 4 times onto the master form and display the data the same way as before, or do I have to run it through a query first?

If I run it through a query can I display the hyperlink columns and have them work on the form? (I'm having trouble in getting hyperlinks working on the form we created the other day)

I have done the same steps as last time but cant get any data to display on the 4 sub forms. But I have directly dragged the information from the table onto a form, and bought 4 of those forms onto a master form. The only difference s that I haven't run it through ha query with the IND function.

I have a Combo Box in the header looking up Tail Numbers directly from the table. [Tail Number]

Each sub form has Link Child Field set to Tail Number and Link Mater Field set to P1, P2,P3,or P4 depending upon which box it is.
I have a text box behind each sub form with the Control source set to =[Tail Number] & "1".."2"..."3"...."4"

Could you please provide some assistance, as I have tried numerous combinations to get it to display data in each sub form with no luck.

Thanks

awade

Cheers
You should really post this as a new thread, or continue the previous one as others will not be able to contribute and I can't guarantee I'll be available to respond.

However on this occasion...

That shouldn't be a problem - you say you have 3 excel tables linked - this is slightly different from before because you only had one table so only needed the one subform so this time you need three subforms - one for each excel table - the main thing is that you need a column in each excel spreadsheet which creates the equivalent of =[Tail Number] & "1".."2"..."3"...."4" (alternatively create a query on your linked excel table and create the column as you did before - you can put the whole query code into the subfrom recordsource if this helps).

If you want to display more information, side by side with the original form, you can add more subforms - I think the limit is around 127 but you would get performance issues at that level.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

 

[awade]

Thanks CJ. I might not have been to clear on what I actually want to do know.
Previously I was using data from two forms via a query to display data in one form with 4 sub forms.

Now I just want to display data from one table onto one form with 4 sub forms without using the query. Can this be done using similar code as before or is the code different. I have tried using similar code, but I can not get any data to display.

Cheers