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

[cap.zadi]

Hi,

Thanks for the feedback.



The data is selected from tblQInspection based on below criteria on Form1 (QCertDetails) when the inspector entered the Product & PDate.

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

This criteria is applied in the append query to fetch the results as per both fields criteria and it works fantastic now but there is one issue now:

The both fields when entered on Form1 create its own record set with other fields null and the the copied data creates next record set without the both fields (since these are not marked to append as was considered to be entered via Form1) in the table.

Kindly advise the needful

So how to manage this now?

[aytee111]

Are you saying that tblQCert is creating a record but the problem is that product and pdate are null? This table should not be storing those fields, it should carry only the inspection number from tblQInspection.

[cap.zadi]

Dear

This is the SQL:

INSERT INTO TblQCertDetails ( InspectionDate, AELength, AIDSpigot, AIDSocket, AODSpigot, AODSocket, AWTSpigot, ASqEnds, ADvStraightness, ACStrength, AWTightness, SSqEnds, SDvStraightness, SCStrength, SWTightness )SELECT QInspToQCert.InspectionDate, QInspToQCert.AELength, QInspToQCert.AIDSpigot, QInspToQCert.AIDSocket, QInspToQCert.AODSpigot, QInspToQCert.AODSocket, QInspToQCert.AWTSpigot, QInspToQCert.ASqEnds, QInspToQCert.ADvStraightness, QInspToQCert.ACStrength, QInspToQCert.AWTightness, QInspToQCert.SSqEnds, QInspToQCert.SDvStraightness, QInspToQCert.SCStrength, QInspToQCert.SWTightness
FROM QInspToQCert
WHERE (((QInspToQCert.TblQualityInspection.Product)=[Forms]![QCertDetails]![Product]) AND ((QInspToQCert.PDate)=[Forms]![QCertDetails]![PDate]) AND ((QInspToQCert.ProductionStatus)="Regular"));

There is a button on form "QCertDetails" to run the append query to copy the above fields into the "TblQCertDetails" but in the next sequence where as "Product" and "Pdate" already in previous table record since it is entered at form "QCertDetails" which is used as criteria for selection of record.

[aytee111]

Firstly, do not repeat any data. If the fields already exist in tblQInspection then do not put them on to tblQCertDetails.

Missing is a field to link the two tables - I would expect to see an inspection number which would link one record to the other. Your WHERE fields would then be WHERE inspection number matches the one on the form.

[cap.zadi]

This is the image of table data after appending data twice.

[aytee111]

I am missing something, can you post a copy of your database with a sample of the data?

[cap.zadi]

Dear

Thanks for your interest.
Enclosed find the copy of the database for your review and solution.


TblQCertDetails

will have almost 4 fields for each quality test parameter.

Field1: Actual Test data (from TblQualityInspection)
Field2: Standard Data required (from tblVCPFGProducts)

*This above two fields required data gathered via query "QInspToQCert" and then append via Query "AppendQInspecToQCert"

Field3: Shall compare the results of Field1 against Field2 and say if its passed or fail. (A formula applied on form field itself)

Field4: For any notes.

As example below from the table:
ASqEnds
SSqEnds
SqResult
SqRemarks

One drive link is below:

https://1drv.ms/u/s!AnATXf2TV3fdewQNTtHMqVO6cIc

[aytee111]

Once again, I am saying to you that for your database to work properly the tables must be designed in a logical manner. DO NOT repeat data. I have made a start of doing that, from the products table to the inspection to the cert details. Also, referential integrity is paramount, so the data flows logically from the top down, in the order of the tables as stated.

(Note, when attaching a database do a compact and repair first to bring the size down)

[cap.zadi]

Dear

Thanks for your time and hard working.

I think, i am unable to explain you the scenario and requirements. Let me shed some more light.

Scenario:
The inspector made some quality inspection tests on selective criteria (Each sample product from each production date) and stored the each inspection record in "TblQualityInspection".
These production batches transferred to W/H on daily basis and waiting for sales.
Once there is a sales order, the sales staff will request to the W/H to delivery the requested product.
The W/H shall enclose a Quality Certificate against each delivery note. Therefore, before loading the vehicle, they will come to Lab to issue the certificate by providing the Product Name and Production date of the product.
Therefore, the Lab guy shall logged in to his certificate issue form and fill the basic details like "CertDate, CertNo, etc) and also fill in the Product and Production Date which is given by W/H guy.

Now finally, the Lab Guy shall be able to fetch the related records of Product based on Production Date from the "TblQualityInspection" and fill up the certificate fields rather than entering manually.

I think, the database you worked out is unable to give the basic requirements.

The current form "QCertDetails" does not support to enter the product and production date and fetch the inspection records from "TblQualityInspection".
The unbound search fields on "QCertDetails" shows only the data regarding the certificates already issued but does not allow to enter the product and production date in real fields.

Kindly advise the needful.

[aytee111]

- an inspection is done: inspection number (PK), product ID, production date (I assume that all product/pdate are inspected)
- with delivery, a quality certificate based on inspection is created: certificate number, inspection number, product ID, pdate

So, the product and pdate is entered the first time when inspection is done. When a certificate is issued, that product/pdate MUST EXIST on the inspection table. The lab guy will select which inspection record to bring up from a list of the inspection table. This can be accomplished in many different ways - comboboxes, listboxes, or a combination of both. You choose the simplest, quickest and easiest for the user (keep in mind that users are all different and have their own ways of doing things).

The point with the table design is that:
1) do not repeat data: the fields that are on the inspection table are not carried over to the certificate table or to the delivery table
2) referential integrity: a new inspection record is entered, this table has a link to certificate table which carries the inspection number; the delivery table has a link to the certificate number

I did not provide you with a working database, I wanted to show you some things and you take it from there. First, however, get the table design correct, before worrying about form design.

[cap.zadi]

Well,

You are absolutely right.

I just want to help the guy to find the record from unbound recordset based on Product & PDate which is on main "QCertDetails" because later at stage with so much data, it will be difficult to scroll down all and then how this record can be printed out.

Regards

[aytee111]

Have two comboboxes, one for product and one for pdate. Also have a listbox containing all entries from inspection table (only those that have not been used, sort in descending date order). User can select from the listbox. User can also enter product, requery listbox to show all entries for that product. User can enter pdate (with or without product) and requery the listbox. When user double-clicks and entry in the listbox filter the form to display that record.

There are many ways to make things easier for the user to find a record. Give as many options as you can think of and let each user decide what is their preferred method.

[cap.zadi]

Hi

You are right but i cant see comboboxes for Product or PDate for user to enter and filter the listbox for required record?

Is it possible to generate duplicate certificate for inspection record?
The reason behind this requirement; there can be a huge lot of product containing same Pdate and consist of many deliveries (loads) whereas each delivery shall contain new certificate (CertID & Issue Date) but may the product and Pdate.

[aytee111]

To change a textbox into a combobox, right-click on the object, select "Change to". Or create a new one from the design menu.

You can design the structure however you need to, duplicate deliveries are part of the system design - allowing it or not depends on the design of the system as a whole. When a user selects an inspection record to turn into a certificate, allow inspection records to be displayed depending on your needs and set criteria as desired.

Once you have a working table design structure, create the certificate form with the selection fields and repost your database if you run into trouble.

[cap.zadi]

Hi

Thanks for your a great help and such a wonderful idea. This logical solutions helps a lot in other matters as well. Like wise,

To create resumption duty for employees who came back from vacation and this worked fantastic to link Vacation and Resumption in one to one relationship as one vacation can have only one duty resumption.

All others gone well with this your solution for the subject but I am still facing problem to issue more than one certificates for a single Inspection record.

As i explained you earlier. This inspection is on random basis and a product and PDate is chosen from a big lot to test whereas for sure there are partial deliveries from this lot and each delivery required a certificate but i cant issue more than one certificate for a inspection record which is already linked with certificateID.

Kindly only advise the solution to issue more than one certificates for a single Inspection Record.

Regards