Adding Existing Fields to form doesn't allow for Record Selection

[bytreeide]

So, I have two forms that I am trying to make work the same way.



With F1Entry I can use the combo box in the header to select different request numbers.

With F2Finance I cannot do this. It works if I set the Form Record Source to T2FIN, but when I try to Add Existing Field, something about selector combo box breaks and I cannot select different request numbers.

Please excuse the mess, this isn't exactly in a finished state.

database attached

[June7]

The forms have RecordSource that contains multiple tables with INNER JOIN. INNER JOIN requires related records in all tables for records to display.

There are 5 records in T2FIN but the form retrieves only 1 because of INNER JOINS.

Change the joins or don't include the lookup tables. Ooops, I tried changing joins and query isn't liking. Had to remove T1EntrySubject. Now all 5 records show in form.

If you include the lookup tables, no edits of those fields should be allowed on this form. Set textboxes as Locked Yes and TabStop No.

Normally a form can do data entry/edit for only one table.

You have a 'spider web' of relationships. Be careful with this. Review: http://www.codeproject.com/Articles/...atabase-Design

[bytreeide]

The forms have RecordSource that contains multiple tables with INNER JOIN. INNER JOIN requires related records in all tables for records to display.

Okay, am I missing something in form RecordSource? When I look at the RecordSource for Form: F2Finance the relationships look the same as as the ones for the Tables when I look at the relationships for those. Suggestions?

There are 5 records in T2FIN but the form retrieves only 1 because of INNER JOINS.

Yes this is the problem that I am seeing also

Change the joins or don't include the lookup tables. Ooops, I tried changing joins and query isn't liking. Had to remove T1EntrySubject. Now all 5 records show in form.

Interesting that you removed T1EntrySubject and now all 5 records show on T2FIN. What would it be about T1EntrySubject that T2FIN "doesn't like"? Both T1Entry and T2FIN are ralated to T1EntrySubject and T1EntryUser. T2FIN doesn't have a problem with T1EntryUser.

I am not seeing how I could change the joins (at this point I want to include the lookup tables)

If you include the lookup tables, no edits of those fields should be allowed on this form. Set textboxes as Locked Yes and TabStop No.

I will set the text boxes to "Locked: Yes" and "Tabstop: No". Don't worry, this was on my list of things to do once I got over the current "hump"

Normally a form can do data entry/edit for only one table.

Yeah, so, I want the Combo Boxes to define the data entry for Table: T2FIN. I don't think I need to change any other tables from this form.

You have a 'spider web' of relationships.

In defense of my spider web of relationships, I think it isn't too bad, I don't have any circular relationships, do I? Except for the one relationship from T1Entry to T2FIN. However I need T2FIN to be dependent on T1Entry, so what would the best way to do this be?

[June7]

Queries will automatically adopt the links and join types set in Relationships builder but can change those in query design. Click the link line between tables to open the relationship dialog. Change the join type.

I am not sure why that table had to be removed.

Just be careful when you build queries so the 'spider-web' doesn't get enforced in query design.

[bytreeide]

I am not sure why that table had to be removed.

Well this is something that I want to be able to fix. And I can't figure this one out because I am as my Name Badge says a "Novice."

I can play around with the relationships of the tables to see if I get it to work, but it would be really helpful if I could have someone tell me why it isn't working and how to fix it.

[June7]

Okay, I tried the query again but retained the T1EntrySubject table. It worked this time. Only thing I can think is that I messed up when I changed the join for this link.

[bytreeide]

So how did you do it?

Post SQL?

[June7]

SELECT T2FIN.*, T2FinanceFleetType.T2ValueFT, T2FinanceLeadTime.T2ValueLT, T2FinancePercentTotal.T2ValuePT, T2FinanceReplaceCost.T2ValueRC, T2FinanceReplaceFreq.T2ValueRF, T2FinanceVolume.T2ValueV, T1EntryUser.T1EmployeeFirstName, T1EntryUser.T1EmployeeLastName, T1EntryUser.T1EmployeeEmail, T1EntryUser.T1EmployeeNumber, T1Entry.T1REQ_Num, T1Entry.T1REQ_Type, T1Entry.T1SubjectID, T1EntrySubject.T1Subject, T1EntrySubject.T1SubjectNHA
FROM T1EntrySubject RIGHT JOIN (T1Entry RIGHT JOIN (T1EntryUser RIGHT JOIN (T2FinanceVolume RIGHT JOIN (T2FinanceReplaceFreq RIGHT JOIN (T2FinanceReplaceCost RIGHT JOIN (T2FinancePercentTotal RIGHT JOIN (T2FinanceLeadTime RIGHT JOIN (T2FinanceFleetType RIGHT JOIN T2FIN ON T2FinanceFleetType.T2FleetTypeID = T2FIN.T2FleetID) ON T2FinanceLeadTime.T2LeadTimeID = T2FIN.T2SupplierLeadTimeID) ON T2FinancePercentTotal.T2PercentTotalID = T2FIN.T2PercentRepairCostID) ON T2FinanceReplaceCost.T2RplmtCostID = T2FIN.T2ReplacementCostID) ON T2FinanceReplaceFreq.T2RplmtFreqID = T2FIN.T2ReplacementFrequencyID) ON T2FinanceVolume.T2VolumeID = T2FIN.T2VolumeID) ON T1EntryUser.UserID = T2FIN.T2EmployeeID) ON T1Entry.T1EntryID = T2FIN.T2EntryID) ON T1EntrySubject.SubjectID = T1Entry.T1SubjectID;

[bytreeide]

Thanks June, i'll compare to what I have, make necessary changes and see if it works.

[bytreeide]

It works now, never doubted you for a second. I do have more questions on this.

I noticed you used a "RIGHT JOIN" what is this, how is it different than an "INNER JOIN" (I mean functionally) and how can I set this up to work that way next time I try something like this?

[June7]

INNER JOIN requires related records in tables on each side of the link. RIGHT and LEFT do not. Open the link as described before and read the join descriptions.

Can set these joins in the Relationships builder and queries will adopt.

[bytreeide]

I am sorry for my ignorance, I must have missed something, but I don't see the link you are talking about for relationships, I only see the link to circular references in databases.

[June7]

I am talking about the link lines in the Relationships builder or in Access query object design view.

Click the link line to open the dialog box.