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

[aytee111]

each delivery required a certificate but i cant issue more than one certificate for a inspection record which is already linked with certificateID

Why can't you use the same inspection number multiple times? The link between the inspection table to the certificate table will be a one-to-many, i.e. one inspection can point to many certificates. Each time the product/pdate is delivered it can point to the same inspection number.

[cap.zadi]

Well, I checked and created some certificates which showed that it is possible to create many certificates for one inspection no.

The confusion raised beacuse the fields CertID, CertDate & Delivery Note on form showed the same CertID, CertDate & Delivery Note which was created for the last related certificate whereas in the main table, there will be new certID with issued date and linked to the old inspection Id.

Any Advise?

2nd Requirement:

I was looking to control the results of control list "InspList" via combo boxes of TxtProduct & TxtPDate? Once TxtProduct & TxtPDate fields data is entered to by clicking on "CmdSearch" InspList shall shows the records which are matching to the fields of TxtProduct & TxtPDate.

The normal operation to create shall start from the record which is visible in InspList.

Kindly advise the needful.

[aytee111]

I keep saying the same thing - FIRST design the system and design the tables. Then comes everything else, make the forms work for the design, not the other way around. Your confusion on the CertID seems to stem from the fact that you are trying to design your database by creating forms. This is totally the wrong way to go and is back-to-front.

2nd: you don't need a command button. Once a product has been selected, in the AfterUpdate event requery the list for just that product. Same for pdate.

Post your new database.

[cap.zadi]

Well, You are right and i do agree with your opinion.

I am going to attached the almost working DB for your review and further advise.

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

Requirements:

a. The given combobox "TxtProduct" & "TxtPdate" shall use to select the record from the record list "InspList".
b. At double to generate the certificate; the records shown in CertID, Product, PDate and InspectionDate are the 1st record which was used to create certificate where it does not reflect the new record but in the back end "TblQCert" data changed perfectly.
c. The product fields both on form and in Record list shows the PK rather than product name.

You usual support will be appreciated.

[aytee111]

- There are still fields being repeated across tables, this should not be done.
- I am not sure what purpose TblItemMaster serves in the overall design. This may be one table containing all inventory or may need to be split into multiple tables. I changed the linking to the products table for normalization purposes.
- NEVER put lookups on tables. This causes untold problems everywhere. If you want to look something up then use querying.
- Avoid "Save" buttons on forms, this adds complexity and serves no purpose unless there is no other way to save the record. It causes confusion to the user.
- I removed the search button as this is now automated.

[cap.zadi]

Hi,

Thanks for the working.

The purpose of master item table to keep main records of items either raw materials, spare parts or finish goods but like finish goods will be taken relevant table for some additional features which are only related to these items. Is there any other way to manage this scenario?

NEVER put lookups on tables. This causes untold problems everywhere. If you want to look something up then use querying.
- Avoid "Save" buttons on forms, this adds complexity and serves no purpose unless there is no other way to save the record. It causes confusion to the user.
- I removed the search button as this is now automated.

i do agree and i believe its good solution.

Concerns:

a.The Product search combobox worked fantastic but Pdate does not sort out any records and showed zero records.
b. Why we cant create more certificates for one inspection record (one to many relationship). The table structures showed this but in actual on form we cant generate even double click on a record which have certificate no to the record.
c. The previous your corrected databse was able to do so but the issue was to not see the new generated certID, and date on form but in table the data was changed and a new certificate issued to the same inspection number.

Kindly advise the needful.

[aytee111]

a. I cannot reproduce the problem with the pdate, when I type in a date, e.g. 4/30/16, then it gives me the correct records in the list.
b/c. Yes I left this out by mistake, the ability to add more than one record per inspection number. You must learn to fix your own problems, however, do not rely on what others tell you, they are not always right! You could add a command button with "New" and copy the code from the list double-click event.

[aytee111]

Because adding a new certificate record is so easy, the user may add them in error. You may want to think about how to void or delete a record that was created by mistake. Maybe by adding a flag and then ignoring those records in the list.

[cap.zadi]

Hi

I will check out this regarding the date again.

Anyhow, since i dont have the deep knowledge you had and you already brought the project to the final stage and just missing to add more certificates to one inspection record as its requirement of end user.

I will try to follow your suggestion but it was great if you help out for the final edge.

[cap.zadi]

Yeah, you are right and there is chances.

I will consider this matter.

Thanks

[aytee111]

Code for the NEW button:

Private Sub cmdNew_Click()

    If IsNull(Me!InspList) Then
        MsgBox "Please select an item from the list"
    Else
        DoCmd.RunSQL "INSERT INTO TblQCertDetails ( InspectionID, CertDate ) SELECT " & Me!InspList & " AS InspectionID, Date() AS CertDate;"
        Me!InspList.Requery
        Me.Filter = "CertID=" & DLookup("Max(CertID)", "TblQCertDetails", "InspectionID=" & Me!InspList)
        Me.FilterOn = True
        Me.Requery
    End If
    
End Sub

There is an EnteredBy field that you may want to add to this INSERT statement (in both places). Usually it is taken from the user's login - Environ("username") - but I am not sure how your design is for the users.

[cap.zadi]

Hi

Thanks for the code and i will apply this.

There is an EnteredBy field that you may want to add to this INSERT statement (in both places). Usually it is taken from the user's login - Environ("username") - but I am not sure how your design is for the users.

The fields:
Enteredon
Enteredby
Updatedon
Updatedby

are operated by the following codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
        Call StampRecord(Me, False)
    End Sub


Private Sub Form_Current()
        Call LockControls(Me, True)
    End Sub

[cap.zadi]

Hi,

I added the button and checked. Its working fantastic to add a new copied record.

Thanks for this great help.

I am not able to run the TxtPDate and its not working. Always showing zero results even related PDate is available in the list.

[aytee111]

The pdate is working for me. What are you doing differently?

[cap.zadi]

There is no any big difference accept the date format. I am using in my system as dd/mm/yy whereas ur setup showed mm/dd/yyyy.

Is there any difference by this format?