0

I have an access database which I use to keep track of the quality of incoming raw materials. Typically, the raw materials I receive are fabrics. The form I have built for this has three layers:

1- Shipment level information (Purchase Order #, Total Rolls in Shipment, Vendor, Date)
2- Roll level information (Fabric Details, Dimensions, quality score)
3- Defect Level Information (Defects found on a roll, size, severity)

One shipment can have many rolls, and one roll can have many defects, and as a result my form has three layers. Please see the image below for reference.

enter image description here

What I would like to accomplish is that each time an inspector inputs a PO#, if the shipment already exists in the database it will pull the total rolls, vendor, and receipt date, and allow for more child records to be added. If the PO# is not already in the database it will allow for a new entry.

I have tried a few things but I am not quite sure where to begin. Thank you in advance for your help!

Erik A
  • 31,639
  • 12
  • 42
  • 67
RayArc
  • 15
  • 3
  • So you need to search for existing PO and if found go to that record and if not found go to new record row? This is common topic and can be handled several ways. You should edit question to show attempted code. One approach uses NotInList event of bound combobox. Another uses an unbound combobox to select PO to search for. http://allenbrowne.com/ser-62.html. Advise not to use space nor punctuation/special characters (underscore only exception) in naming convention. – June7 Jan 25 '19 at 21:59
  • Then if you want to automatically batch create child records for new PO, commonly involves INSERT INTO SELECT sql action executed in VBA after committing new PO record to table. https://stackoverflow.com/questions/32057615/insert-into-select-from-linked-table – June7 Jan 25 '19 at 22:04
  • Just realized you specified 'already exists' - concept basically same for existing or new, just in case of new record the parent has to be committed first. Batch creating dependent records would use INSERT INTO SELECT and concatenate selected value into SQL string. – June7 Jan 25 '19 at 23:00

1 Answers1

1

Several questions so several parts: Make sure these assumptions are met. Shipments table has an auto number primary key column labeled something like ShipmentID (there isn’t enough information provided to be sure we can use PO# as a primary key). Similarly the Roll table has RollID but also add the corresponding ShipmentID as a foreign key to form the appropriate 1 to many relationship. Similarly give the Defects table a DefectID primary key and add the corresponding RollID as a foreign key. If you need to redo the forms click on the corresponding table and use the form wizard. It will also help to add the 1 to many relationships to the relationships diagram under database tools.

The point is that the roll and defect section of your form need to be sub forms so they will filter when you change PO# and RollID. So if you have to rebuild your form then start by: making a defect and rolls form that look like the defect and rolls sections of your form. Then drag the new defect form onto the new rolls form a wizard will pop up to add a defect sub form to your rolls form. Then repeat by dragging the rolls form that now has a defects sub form onto the Shipment Form that looks like your shipments section.

  1. By default access will install each new form and sub form with record selectors. These record selectors allow the user to both cycle through records and add new records. Play around with them or check online to learn how the record selectors work. Here are some good tips:

https://www.youtube.com/watch?v=90eOatOmHZA

I think it makes the form easier to use if you hide the record selectors and then add equivalent buttons like in this video:

https://www.youtube.com/watch?v=gL1taUzLQGE

  1. To filter the form by typing a PO# add an Unbound Textbox to the form. Then modify the unbound textbox’s OnChange event Like so:
Private Sub txtPOSearch_Change()
Dim strFilter As String
If Me.txtPOSearch.Text <> "" Then
‘ assuming 34 is typed in txtPOSearch then strFilter will equal: “PO# like “34*”
 strFilter = "PO# like " & Chr(34) & Me.txtPOSearch.Text & Chr(42) & Chr(34)
         Me.Filter = strFilter
        Me.FilterOn = True
    Else
       Me.Filter = ""
        Me.FilterOn = False
    End If
    Me.txtPOSearch.SetFocus
    Me.txtPOSearch.SelStart = Len(Me.txtPOSearch.Text)
End Sub

Source:

https://answers.microsoft.com/en-us/office/forum/office_2007-access/filter-a-form-with-unbound-text-box-in-microsoft/aa6ebf52-c51a-4d28-8933-9618fdb6fee2

  1. Assuming vendor and receipt date are in the shipments table they will work automatically, but if total roles is included in the shipment table delete it as it is redundant. Instead set the total roles textbox to an expression like:

DCOUNT(“RollID”,”Roles”,”ShipmentID = “ & [ShipmentID])

mazoula
  • 1,221
  • 2
  • 11
  • 20
  • this is right on the money. the 2nd part of Roll form part needs to be a sub form, and then the fabric is to be a sub form of that. So we are nested 3 deep. And those child records are NOT added automatic, but only when the user starts entering data (so if no entry occurs in those sub forms, then that is just fine). So access will automatic maintain the relationships, but no such automatic adding of those child records is required nor does Access add them - the user does. – Albert D. Kallal Jan 26 '19 at 21:01
  • Thank you for your advice! This is the answer I was looking for. Only tricky thing is that I do not want the Roll and Defect forms to filter on my master form. I simply want the Purchase Order level data to be called. PO#s are never replicated so I figured it would be safe to use as a unique identifier but perhaps it is easier to use an auto number. – RayArc Jan 28 '19 at 17:00
  • I'll think about this at work tommorrow night where I have access to access – mazoula Jan 30 '19 at 08:00
  • I'm not sure what you meanRayArc. So if you are still having trouble please set up a new question. – mazoula Jan 31 '19 at 05:28