Page 1 of 4 1234 LastLast
Results 1 to 15 of 48
  1. #1
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481

    Open another form based on fields main form applying Multiple Where Condition!

    Dears



    I need your kind help.

    I have main form "QCertDetails" including major fields as below:

    Form1: QCertDetails (Main Form for Data Entry)
    QCertID: AutoNumber
    Product : Short Text * Combobox on form and filled from tblProducts.
    PDate: Date/Time
    Otherfields.

    Form2: PLTDataSearch (Datasheet form view to review records only). Data supplied by a Query
    TestID: AutoNumber
    Product : Short Text
    PDate: Date/Time
    Otherfields.


    Requirement-1!

    I want to apply a command button on form2 (on click event) to pop up form2 based on the criteria entered in the Form1 fields "Product" & "PDate" to pull the all records matching with "Product" & "PDate".

    Requirement-2!

    How to append one of the record displayed on Form2 into the related fields of Form1. Lets start this later.


    Kind Regards

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    can you show a screenshot of our relationships?

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by cap.zadi View Post

    Requirement-1!

    I want to apply a command button on form2 (on click event) to pop up form2
    Can we assume that this is a typo...and you want the button on Form1?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi, Yeah its typing error. Obviously form1 to have search button to find matching results via popup form2.

  5. #5
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Quote Originally Posted by Homegrownandy View Post
    can you show a screenshot of our relationships?
    Umm. I believe its a typing error? "our relationships?"

  6. #6
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    I believe you are asking about table relationships?

    As i said earlier, there are two main independent transaction tables and the information of product is fed from tblproducts but via combo-box on form instead linking via "lookup wizard to make relationship.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Requirement 1: open form2 with a "where" condition, either VBA or macro, such as - "Product='" & Me!Product & "' AND PDate=#" & Me!PDate & "#"

    Requirement 2: in the OnClose event of form2 - not sure what you mean by "append". Either form2 will update a table, in which case it will be Forms!Form1.Requery. Or it will be moving the data into the textboxes, Forms!form1!textbox=Me!textbox

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You are viewing the same data on both forms, just in different modes - one in single view and one in datasheet? Maybe you should look at Split form. Also review http://www.allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Dear Thanks for your efforts.

    Ok, i will try to fix for the requirement-1 as you suggested.

    Requirement-2: The data which is shown on form2, the user shall be able to select one of the suitable data to append on form-1 fields.

    Any suggestions?

  10. #10
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Dear Sir

    Thanks for your efforts. No, its not the case. Sorry if i am not able to explain.

    Let me explain once again, if it works.

    This is a small database for QC unit.

    A table named as "tblQInspection" which consist of different fields as below and entries are made during inspection when ever product is out at final process stage.

    InspectionNo: Auto
    InspDated:
    Product: Filled on form via Combo-box from tblProducts
    PDate:
    Product Parameters fields: 10 fields which include specific Quality check information.


    There is another table named as "tblQCert" which is issued when a delivery is made to the customer. The warehouse guys will approach QC regarding the product and PDate of the product they are going to dispatch. The certification had its own heading and some of fields related to the product quality parameters which are same as in "tblQInspection"

    "tblQCert"
    QCertID: AutoNumber
    Product : Short Text * Combobox on form and filled from tblProducts.
    PDate: Date/Time
    Otherfields of quality as same in "tblQInspection".

    Requirement: When ever QC guy will open the certification form to issue a certificate shall enter the produt and Pdate and he shall be able to cross check the quality related data from the "tblQInspection" before issuing the certificate.
    The he shall be able to append the favorite date of same product and Pdate to the current form of certification to issue the certificate which remains in database as record to trace out the returns and complains at later stage.

    So to make their life easy:
    Once he entered "product" & PDate in the main form of then query shall help to pop up the search results and i think this split form is best for this purpose.
    Then how to append/transfer once of the data record to the main form "QCertDetails" from research result records.

    Kindly advise.

  11. #11
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure I get it.

    On tblQInspection there are records showing product and Pdate which are unique. QC person will select one record by entering product and PDate, then enter InspDate and all the parameter fields. I don't see where the second form comes into play with this scenario.

    When this is delivered to the customer tblQCert is updated. Is the certification done again, with a second set of parameters? Or what is updated on this record?

    Making this more difficult is the issue of your tables not being "normalized". It would help you to learn about table design.

    - a table containing products and only the ProductID carried to all other tables as necessary
    - the tblQCert is tied to tblQInspection - it should only carry the InspectionNo, not all the other fields repeated

  12. #12
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Dear

    Thanks for your time.

    The requirement-1 is already achieved via click button on main form to extract the data from "tblQInspection" via a query and displayed by pop up form-2 (datasheet view) :

    [Forms]![QCertDetails]![Product]
    [Forms]![QcertDetails].[PDate]

    *Is it possible, if PDate can show results in a range like 10 days plus and 10 days minus.
    Example; If PDate is 15/04/17, then records shall be shown between 05/04/17 to 25/04/17.

    Now coming to your above questions:

    On tblQInspection there are records showing product and Pdate which are unique. QC person will select one record by entering product and PDate, then enter InspDate and all the parameter fields. I don't see where the second form comes into play with this scenario.
    tblQInspection having the records which are done in routine (daily basis) showing product and Pdate are unique including other parameters fields.

    There sales requirement that each delivery shall have Quality certificate which declares some parameter results of sold product.

    The tblQCert had its own heading fields as mentioned above and just need to copy some records of parameter fields from tblQInspection to tblQCert based on Product and PDate.

    Scenario: The delivery guy come up with delivery note which majorly showing the product and PDate for certification.

    The QC guy have to check the records of this specific product based on PDate and prepare the certificate accordingly.
    What they do now: the QC guy have to open big files and check the product and Pdate and then type the parameters in the certificate fields in excel sheet.

    So i tried make this job easy for them.

    tblQInspection shall contain all the tests rather than conventional files/folders.
    tblQCert shall have the certificates record. They guy shall verify the results before making certificate and also copy the required fields to fill automatically in the certificate fields.

    So kindly advise me now: how we can copy/append the results which pop up on Form-2 as result of query.

    Thanks

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    To add or subtract days from a date you can say Between [Forms]![QcertDetails].[PDate] - 10 And [Forms]![QcertDetails].[PDate] + 10. (There is also the DateAdd function whereby you can add or subtract days, weeks, months, etc.)

    It seems to me that the two tables are the same and carry the same information. You seriously need to rethink this, it could stay on one table only and add the additional field(s) for delivery. Then your queries would exclude those with the delivery field(s) filled in. Copying data is very bad practice and will cause you problems down the road.

    Access and Excel are very different to each other and the temptation is to make them behave the same. You are on the right path making things easier for the inspectors, now make it easy for yourself as the developer and keep it simple.

  14. #14
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Dear

    The date range issue is already solved by applying:

    BETWEEN DateAdd("d", -10, [Forms]![QcertDetails].[PDate]) AND DateAdd("d", 10, [Forms]![QcertDetails].[PDate])

    Thanks for your advise but the case is not all the data is same in both tables.

    tblQInspection: Contains the all data of quality inspections records under its unique inspection serial no.

    tblQCert: Contains the data for certification under its own specific head and unique certificate serial no. There is only few parameter fields not all to copy from tblQInspection for the certification purpose.

    Kindly advise.

  15. #15
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Form1 - inspector selects product
    - subform shows all records that have not been delivered in the PDate range
    - inspector selects one of those records
    - inspector enters inspection data

    Form2 - user selects record from tblQInspection (based on inspection number?)
    - on selection (AfterUpdate), run an append query to add the record to tblQCert
    - user enters remaining data

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-13-2016, 12:32 PM
  2. Replies: 14
    Last Post: 06-13-2014, 04:29 AM
  3. Replies: 1
    Last Post: 04-08-2013, 11:58 AM
  4. Replies: 2
    Last Post: 08-11-2011, 10:02 AM
  5. Replies: 5
    Last Post: 08-13-2010, 07:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums