Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Other Software (https://www.askmehelpdesk.com/forumdisplay.php?f=394)
-   -   Help with form input (https://www.askmehelpdesk.com/showthread.php?t=834107)

  • Aug 26, 2017, 11:07 AM
    hwinceFL
    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);
  • Aug 26, 2017, 05:54 PM
    ScottGem
    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.
  • Aug 27, 2017, 07:19 PM
    hwinceFL
    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.
  • Aug 30, 2017, 02:59 AM
    ScottGem
    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.
  • Aug 30, 2017, 05:39 AM
    hwinceFL
    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.
  • Sep 14, 2017, 09:37 AM
    hwinceFL
    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.
  • Sep 15, 2017, 05:54 AM
    hwinceFL
    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.
  • Sep 21, 2017, 11:45 AM
    ScottGem
    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.

  • All times are GMT -7. The time now is 12:06 AM.