I have three tables each with primary keys with relationships to another table in which each of these fields is a foreign key. I have attempted to prepare a query using these tables (requesting parameter input of values for these primary keys) to populate fields on my form. I would intend to use this form to create a transaction entry into a "transaction" record table. So far I am able to enter only two parameters in this sequence and then my form is only opened as a blank. I would appreciate some guidance in what I need to do to clean up this confusion. I would provide my database, if needed. Thanks.

First, what is the SQL of the query. Second why are you using parameters? If you want to add records to a table, I would not use a multi-table query, but rather use subforms.

Hello Scottgem. Thanks so much for your response. The SQL for this form record source is SELECT tblAssets.AssetID, tblAssets.PerformanceType, tblAssets.Title, tblAssets.PrimaryLoc, tblAssets.SecondaryLoc, tblAssets.TertiaryLoc, tblBorrower.BorrowerID, tblBorrower.LName & ", " & tblBorrower.FName AS Fullname, tblBorrower.H_Phone, tblBorrower.Street, tblBorrower.City, tblBorrower.State, tblBorrower.ZIP, tblUser.UserID FROM TertiaryLoc INNER JOIN (SecondaryLoc INNER JOIN (PrimaryLoc INNER JOIN ((tblUser INNER JOIN (tblBorrower . It appears that the SQL is not complete, but I don't know why. I have attached a screen shot of the query showing the table relationships, etc.

The summary scenario for my using this form/query is this. A "user", who knows his/her ID, receives a request to borrow an asset. Identifying the borrower's ID, the user searches the Asset DB to ascertain the O/H status of the requested asset and when found identifies the Asset ID, status, location, etc. Thus, my assumption was that knowing these three IDs, the User could use parameters to input them to create a "loan" form through which the borrower's signature would acknowledge asset identify, date loaned and return due date. It would be intended that the form on update or close, etc. would create a transaction added to a "Loaned Asset Table". I have attached a screen shot of the design view of this form. As explained earlier, I obviously have not yet learned enough to make this form work and I would have to concede I don't know how to accomplish the objective using sub-forms either. I would appreciate your further comments.

You are making this more complex then it needs to be. You need three tables:

TblAssets: information about the assets.
TblBorrowers: information about the people who will borrow an asset
TblLoans: information about the lending of an asset. Something like this: LoanID (PK), AssetID (FK), BorrowerID (FK), CheckoutDate, CheckInDate

You have a main form bound to the Asset table. You have an unbound search box where you can enter/select an asset and bring up that asset. You then have a subform bound to tblLoans. This form shows all loans of that asset. If the asset is out, then there will be one record with a blank checkInDate. If its in all CheckInDates will be filled.

If it s a return then you fill in the CheckInDate. If this is a loan, then a new record is created with a CheckOutdate (if available). Use a combobox to select the Borrower. The AssetID is then entered automatically.

Thank you for your very helpful guidance. Your conclusion that I'm making this more complex than is needed is certainly true. I'm going to have to spend some time digesting your suggested plan. It seems to me I already have the necessary tables: tblAssets, tblBorrower, tblUser, & tblAssetInOut. Now I just need to figure out how to coordinate all that as you suggest. Incidentally, how do I differentiate a Foreign Key. For example, in my tblAssetInOut, I include a field for AssetID, BorrowerID & UserID. Since these are Primary keys in their own table, does this inclusion of them in the tblAssetInOut make them Foreign keys? Or is there a setting that more clearly defines them as such in that table?

A foreign key is not designated except by usage. It is a field that is used to link to a record in a related table. Usually a primary key.

What is the difference between tblBorrower and tblUser?

Thanks for the clarification of the definition of a foreign key. I've been thinking of them in that way, but thought there might be a setting I've been missing.

The User is my acronym for management, staff and volunteers (i.e. per access level for control of assets). Then, Borrower is just that, someone who temporarily takes possession of assets. It might be that a User would become a Borrower, but, if so, they would be ID'd separately as a Borrower and would be handled in that role by a separate User.

Could a borrower not be a user? The only reason to keep them separate is if the borrowers would not be users.

Of course, I see your point. We do have some limitations/requirements on User qualification; but, we do have volunteers so we're pretty flexible. However, as you point out a Borrower might well become a volunteer or staff member (paid) at some point. But, I would see their "identity" completely changing at that point (a new User ID). Having everyone in a single table might save space, but it seems there would have to be some identifier for the different roles and everyone would not meet that need. Anyway, until I become more experienced in Access, I think I better keep them separate. I do appreciate your guidance.

Scott, I'm struggling trying to set up my forms to follow your earlier suggestions. I have set up my Main form bound to Assets and using a combo box am able to select an asset on the Main form. Then, I have my tblAssetsLoaned with relationships linking AssetID, BorrowerID, UserID, etc. But, when I try to prepare a subform using tblAssetsLoaned, under More Forms, I do not see the Forms with subforms option. I would appreciate your help in what I'm missing. I am able to create frmAssetsLoaned based on tblAssetsLoaned, but this form and Main don't seem to be linked.

I generally create my forms (using the wizard) and then embed them as subforms. So if you have frmAssetsLoaned, then open the main form in Design mode. On the Design ribbon find the subform control (looks like a little form) its near the bottom of the list. Select it and go to the form and draw a rectangle to the size you want. The Subform wizard will then open asking you whether you want to use an Existing form (Yes), then giving you a list of forms to choose from. Choose your form and it should prompt you to link on AssetID. Give the subform control a name and you should be done.

Thanks, Scott. That worked just as you described. The selected Asset on the Main form (by AssetID) is linked to the subform and the AssetID field there shows the same number. However, the other fields are not populated, i.e. asset locations from the tblAssets do not show up on the subform fields even though the relationships are there (at least I believe they are properly set up). How do I assure that this is, in fact, done? Also, the LoanID shows as "New", since this transaction record has not yet been completed. I have not tried to add the tblBorrower or tblUser, which will be needed until I have a better understanding of the situation so far. I will surely appreciate your further guidance.

What is the Recordsource of the subform?

I don't see Recordsource, as such. The Source Object is frmAssetsLoaned with Link Main Fields as AssetID and Link Child Fields as AssetID. Further, neither of these is more uniquely identified, e.g. Main as frmAssetsMainForm.AssetID and Child as frmAssetsLoaned.AssetID. Is this satisfied by the form/subform relationship?

You have to make sure the subform is selected before you can check its Recordsource. Or you can open the form outside the subform. If you are only seeing the SourceObject then you are not inside the subform.

Thanks for the clarification. When I open the subform separately, I find the subform, frmAssetsLoaned, the record source is tblAssetsLoaned. Now when I open the main form, frmAssetsMainForm, it opens together with the subform. The main form is populated with the first record information and an unbound box in which I can enter the AssetID which is to be loaned. When this AssetID is entered, the subform LoanID modifies from "New" to "1". The Main Form is populated with the entered AssetID information and the subform is partially updated, but, all fields are not updated. Further, the tblAssetsLoaned is not updated.

Unfortunately, I have had to leave this project since my last post, but am now back looking for help again. So let me review. I have created a form, frmAssetsMainForm, bound to tblAssets. This form has a combo box by which an asset to be loaned can be selected by AssetID from its pull-down list. Then, the form populates the fields AssetID, Title, PrimaryLoc, SecondaryLoc, TertiaryLoc and OnHand status. Then, there is a subform, frmAssetsLoaned, which I have captioned, sfmAssetsLoaned. The record source for this form is tblAssetsLoaned and the form includes all the fields from that table (LoanID, AssetID, BorrowerID, LoanDate, UserIDOut, ReturnDate, UserIDIn, PrimaryLoc, SecondaryLoc and TertiaryLoc); however, it only shows LoanID as "New" and AssetID as the selected value. Any input to the subform, such as BorrowerID, causes the LoanID to be updated to the next available value. So, my question(s). How do I set the subform to then update to today's date and reflect the currently logged-in UserID as the UserIDOut? Further, I want the ReturnDate to be updated with a pre-set increment, e.g. 15-days, 30-days, etc. Finally, the locations for this asset should be updated on the subform. Thus, the tblAssetsLoaned will be updated accordingly and a receipt/checkout form printed for the borrower/library record. Ultimately, I would want this update to be reflected in the tblAssets to show that the asset is no longer OnHand. I would appreciate help in achieving this objective. Help with one step at a time would certainly be welcomed. Thanks.

How do you capture current UserID? Use the DateAdd function to add a specified increment for the Return Date. To tell if an asset is not OnHand, you check tot see if it has no Returned date.

Hello ScottGem, thanks for your response. I should have been clearer in my review. The User is the volunteer/staff handling the transaction of issuing the asset. He/she (likely knowing their own ID) has logged in previously with ID from tblUser. He then queries tblBorrower for BorrowerID. He then queries tblAssets to insure that Borrower's requested asset is, in fact, OnHand (indicated by Yes/No status). Now, I am ready to create the transaction, frmAssetsLoaned, in which I select the AssetID in the combo box. This leads to my question as to how I link the subform info so that if, for example, I enter the BorrowerID that the subform updates with the info from frmAssetsMainForm. I was not aware of the DateAdd function, so thanks for the pointer. If I add it as an expression in the Property Sheet, which entry should I choose? (at this point, I have selected OnChange). Thanks again for your help.

Hello ScottGem, This procedure is still not working. What needed information am I not providing that will lead to the solution. I'm trying as best I know how. I will appreciate your further help. Thanks.