Ask Experts Questions for FREE Help !
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member

    Aug 26, 2017, 11:07 AM
    Help with form input
    I am using MS Access 2007 under MS Windows 10 Home x64. I have created a form for handling an inventory item in my project. This is an item issued to "Borrower." The issuing form identifies the LoanID, the "Issuer", "Borrower", item properties, date of issue and due date for return, then creating a record of this transaction. When the item is returned, I have another form which uses input of the LoanID to populate the form with data from the saved transaction record and updating using the currently logged on "UserIDIn" and date to update the form. Apparently, I have not correctly defined the LoanID on the "return" form such that it looks at the Loaned transaction to find the relevant data for populating the form. I need help in creating the necessary coding to get these data retrieved and into the form. Of course, I intend that completing this Return form will update the inventory to show that the issued item has been returned to "stock." Currently, the LoanID box is defined as "unbound" with the Record Source as

    SELECT tblAssetsLoaned.LoanID, tblAssetsLoaned.AssetID, tblAssetsLoaned.BorrowerID, tblAssetsLoaned.UserIDOut, tblAssetsLoaned.LoanDate, tblAssetsLoaned.DueDate, tblAssetsLoaned.PrimaryLoc, tblAssetsLoaned.SecondaryLoc, tblAssetsLoaned.TertiaryLoc, tblAssetsLoaned.UserIDIn, tblAssetsLoaned.ReturnedDate, tblAssets.OnHand
    FROM tblAssets INNER JOIN tblAssetsLoaned ON (tblAssets.TertiaryLoc=tblAssetsLoaned.TertiaryLoc ) AND (tblAssets.=tblAssetsLoaned.SecondaryLoc) AND (tblAssets.PrimaryLoc=tblAssetsLoaned.PrimaryLoc) AND (tblAsset.AssetID=tblAssetsLoaned.AssetID);
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man

    Aug 26, 2017, 05:54 PM
    I'm not sure what the problem is. Frankly I would have one form bound to tblAssetLoaned. I would call the form from another form that had buttons for New Loan and Return. In addition I would have a combobox to select Loans not returned. The New Loan button would open the form in Add Mode. The Return button would open the form in Edit mode filtered for the loan being closed.

    You shouldn't need to update the Inventory table. You should be able to tell if an asset is not in stock by whether it has an outstanding loan.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member

    Aug 27, 2017, 07:19 PM
    Hello ScottGem, thanks for your response. I am sure your suggested alternative to solving my problem will be a much more effective approach than my current one.

    However, I would like to understand why my choice didn't work. I believe that I have defined the entry box on my "receiving" form such that when I key in the LoanID>Enter/Tab, this would cause the tblAssetsLoaned to be "searched" and when the entered LoanID is found, the matched information would be populated into the current form. This does not happen now and I don't know what I have defined incorrectly.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man

    Aug 30, 2017, 02:59 AM
    First, Controls do not have a Recordsource, Forms do. Without knowing what code is behind the unbound LoanID control. I'm not sure what's wrong. I would use a Search Combo to find and populate the Loan record.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member

    Aug 30, 2017, 05:39 AM
    Hello ScottGem, Thanks. Regrettably, following my last Win 10 update I can no longer login to my laptop computer where Access exists. It's now in the repair shop. Will continue this issue as soon as it is fixed.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member

    Sep 14, 2017, 09:37 AM
    OK, I'm back, but this has been quite an ordeal. First, it took several days for the repair tech to look into the login issue. Finally, backed out Win update, re-did it successfully. I think I detected a slur in his speech with his explanation of the problem when he said, "It happens." Then, when laptop returned, we're facing Hurricane Irma coming right up the throat of Florida and my house. The fury had diminished to 75 mph when it went through. Lost power for 2 days and just got Internet back last night. Thank God, no major damage.

    My explanation of the issue was obviously not clear. This "Return" form for receiving back assets which have been issued earlier has an "unbound" combo box for LoanID, which offers a numerically-ordered LoanID pull-down option to scroll through the "Loaned Assets" table. Each entry contains the data needed to fill in the rest of the form. The Row Source related to this combo box is "SELECT tblAssetsLoaned.LoanID, tblAssetsLoaned.* FROM tblAssetsLoaned;". In this case, the Asset loaned via LoanID #18 is being returned. When I select "18" from the pull-down menu arrow and ENTER, the data associated from tblAssetsLoaned for LoanID 18 does not populate into the form as expected. Further, I do not find the Search Combo option associated with design of a combo box. I would appreciate help.
    hwinceFL's Avatar
    hwinceFL Posts: 45, Reputation: 1
    Junior Member

    Sep 15, 2017, 05:54 AM
    Let me explain further. BorrowerID #2 is offering to return AssetID #1842, which was issued earlier under LoanID #18 and posted in tblAssetsLoaned. Volunteer/Operator/UserIDIn #3 (that's me) has an on-screen menu with an option button labeled "Accept Returned Asset." Clicking on this button executes the Event Procedure (Do.Cmd.OpenForm "frmVolLoanedAssetBack"). Opening this form initiates the "Return" process described in my previous post.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man

    Sep 21, 2017, 11:45 AM
    I'm pretty sure I understand and I'm telling you your process is wrong. There are two ways to handle this; 1) add a NEW record with a transaction type indicating a return and 2) have a returned date field in your record. While method 1) is more normalized, method 2 is easier to manage. From your initial post, it appears you are using Method 2, but your process sounds like you are using Method 1. This is probably why you are having problems.

    Given that you your table structure uses Method 2, this should be a simple process. You use the Combobox wizard to create a Search combo to retrieve the loaned record. After you use the wizard you want to modify the Rowsource of the combo. Something like this:

    SELECT LoanID, AssetID, BorrowerID
    FROM tblAssetsLoaned
    WHERE ReturnedDate Is Null;

    That will modified the combo to only show Loans that are outstanding. When you select a loan in the combo, the code generated by the wizard will retrieve that loan record and you can fill in the ReturnDate.

    I've used that exact process on similar apps.

    Note: you can add the Asset and Borrowers table to show the Asset name and Borrower name in the combo when the list is pulled down.

Not your question? Ask your question View similar questions


Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.

Check out some similar questions!

Running an open source research project & wondering what input form to use? [ 1 Answers ]

I need people to enter the info into the system and have it all be searchable and readable by a computer so a machine can categorise them, data mine them etc. Does anyone have any specific recommendations or resources which I should begin for an input form that will receive entries like this?

How to file form 1040 with taxable income on form w2 and form 1042s? [ 3 Answers ]

Hi, I'm a graduate student from Taiwan. I checked on IRS web site. I'm very sure that I become a resident alien in 2011 (more than 5 years F1). I have W2 and 1042S for my 2011 taxable income. I don't know how to report W2 and 1042S together on form 1040?

Form 8223, form 8316 and form 843 for F-1 student (India) [ 1 Answers ]

Hi, I am an International student (from India) doing my MS in Computer Science in NY. I worked for a month on CPT in 2009 and I got a w-2 form from my employer recently. And I realized that my employer did withheld Medicare Tax and social Security Tax. And I asked him to refund the SS Tax and...

MS Access 2003 Creating a form with input of ranges of data [ 4 Answers ]

Dear Scott, I tried attaching the front and back end of my database and I got the following message, "Your submission could not be processed because a security token was missing. If this occurred unexpectedly, please inform the administrator and describe the action you performed before you...

Form 1099 received, but do I report on Form 1040 or just Form 2106? [ 1 Answers ]

Greetings. I'm hoping I can get some help on what to do with a 1099-MISC form I received from my employer that were for business expense reimbursements. I have read through some questions and answers already and based on them I'm going to file this as a Form 2106. The question is should I...

View more questions Search