One table, three fields; two lookup fields and one result field

[gaker10]

I have a table with three fields. Field1 (cboComponent) is a combobox of values which are typed/selected. Field2 (Revision) is a combobox who's rowsource is based on Field1. Field3 (txtComponent) is a text field. Field1 and Field2 are keys to the table.



I know this is not a normalized structure, but is it possible to lookup Field3 based on values in Field1 and Field2?

I currently have code to do this, but I get the following issues:

1) When I type a value in Field1, and Field2 is populated correctly so far with example values V1 and V2, when I select V2, it jumps back to V1 being selected.

2) The fields which are the results of lookups between Field1 and Field2 (e.g. Field3) are not populated, even though values exist for these fields.

All of these fields are in the same table, and I do not have time to re-arrange the structure.

RowSource in Field2 to populate Field2 based on Field1 entry:

SELECT DRAWINGS.Drawing, DRAWINGS.Revision FROM DRAWINGS WHERE (((DRAWINGS.Drawing)=[Forms]![BOM Entry]![cboComponent]));

Code to populate Field3 based on Field1 and Field2:

Private Sub Revision_AfterUpdate()


Me.txtComponent = Nz(DLookup("Primary", "DRAWINGS", "[Drawing]='" & Me.cboComponent & "' AND [Revision] ='" & Me.Revision & "'"), "")

Field2 is refreshed after updates to Field1, and Field3 is populated based on values in Field1 and Field2.

Aside from my poor structure, am I doing this correctly?

Thank you

[June7]

What you are doing makes no sense. Restrictive comboboxes for data entry are supposed to pull their values from a 'lookup' table, not from the same table the record is being created in.

What is the full db structure - what are the tables and how are they related? If you want to provide db for analysis, follow instructions at bottom of my post.

[gaker10]

Sorry, I forgot to mention, the record is not being created in the lookup table. The lookup is being performed on a form, and the record is added to a different table, which is then exported upon completion.

So, the form contains the two fields mentioned above (combo boxes). Field1 has this rowsource:

SELECT [PDatabase].[PartNumber], [PDatabase].[Description], [PDatabase].[PurchaseUOM] FROM [PDatabase] ORDER BY [PartNumber] UNION SELECT [Drawing], Null, Null FROM [DRAWINGS] UNION SELECT [AssemblyKitNumber], Null, Null FROM [ASSEMBLIESKITS];

The following code occurs when Field1 is populated:

Private Sub cboComponent_AfterUpdate()
Dim strPN As String




'Replace drawing number with Primary part if the entry is a drawing
strPN = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "")




'If the string is not empty
If strPN <> "" Then
'this is a DRAWING, set component value and corresponding information
Revision.Requery
'Me.txtComponent = Nz(DLookup("Primary", "DRAWINGS", "[Drawing]=" & Me.cboComponent & " AND Revision ='" & Me.Revision & "'"), "")
'Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
'Me.txtUOM = DLookup("StockUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
'Me.txtCageCode = DLookup("CageCodeA", "Drawings", "[Drawing] = " & Me.cboComponent & " AND Revision='" & Me.Revision & "'")
'Me.txtCageCodeA = DLookup("CageCodeB", "Drawings", "[Drawing] = " & Me.cboComponent & " AND Revision='" & Me.Revision & "'")
'Me.txtCageCodeB = DLookup("CageCodeC", "Drawings", "[Drawing] = " & Me.cboComponent & " AND Revision='" & Me.Revision & "'")
'Me.txtCageCodeC = DLookup("CageCodeD", "Drawings", "[Drawing] = " & Me.cboComponent & " AND Revision='" & Me.Revision & "'")
'
'
''Load AlternateA into the drop down
'On Error Resume Next
'   txtAlternateA.RowSource = "SELECT DRAWINGS.AlternateA " & _
'            "FROM DRAWINGS " & _
'            "WHERE DRAWINGS.Drawing = " & Me.txtComponent & " AND Revision ='" & Me.Revision & "' " & _
'            "ORDER BY DRAWINGS.AlternateA; "
'            Me.txtAlternateA.BackColor = vbYellow
'
''Load AlternateB into the drop down
'On Error Resume Next
'   txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _
'            "FROM DRAWINGS " & _
'            "WHERE DRAWINGS.Drawing = " & Me.txtComponent & " AND Revision ='" & Me.Revision & "' " & _
'            "ORDER BY DRAWINGS.AlternateB;"
'            Me.txtAlternateB.BackColor = vbYellow
'
''Load AlternateC into the drop down
'On Error Resume Next
'   txtAlternateC.RowSource = "Select DRAWINGS.AlternateC " & _
'            "FROM DRAWINGS " & _
'            "WHERE DRAWINGS.Drawing = " & Me.txtComponent & " AND Revision ='" & Me.Revision & "' " & _
'            "ORDER BY DRAWINGS.AlternateC;"
'            Me.txtAlternateC.BackColor = vbYellow


'If this is not a drawing, its an assembly/kit or regular part with possible alternates
ElseIf strPN = "" Then


'Compare entry to assembly and kit numbers in the table
strPN = Nz(DLookup("AssemblyKitNumber", "ASSEMBLIESKITS", "[AssemblyKitNumber]='" & Me.cboComponent & "'"), "")


    'If a match is found
    If strPN <> "" Then
            'this is an assembly or kit
            Me.txtComponent = strPN
            Me.txtDescription = DLookup("Description", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'")
            Me.txtUOM = DLookup("UOM", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'")
        
       'Load all parts for possible alternates in drop down
           txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _
                    "FROM PDatabase " & _
                    "ORDER BY PDatabase.PartNumber; "
                         If txtAlternateA.ListCount = 0 Then
                         txtAlternateA.BackColor = vbWhite
                         
                         Else
                         Me.txtAlternateA.BackColor = vbYellow
                         
                         End If


            txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _
                    "FROM PDatabase " & _
                    "ORDER BY PDatabase.PartNumber; "
                         If txtAlternateB.ListCount = 0 Then
                         txtAlternateB.BackColor = vbWhite
                         
                         Else
                         Me.txtAlternateB.BackColor = vbYellow
                         
                         End If


            txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _
                    "FROM PDatabase " & _
                    "ORDER BY PDatabase.PartNumber; "
                         If txtAlternateC.ListCount = 0 Then
                         txtAlternateC.BackColor = vbWhite
                         
                         Else
                         Me.txtAlternateC.BackColor = vbYellow
                         
                         End If
    Else
    'This is a part number
    Me.txtComponent = Me.cboComponent
        Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
        Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
    
    strPN = Nz(DLookup("AlternateA", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "")
    
    If strPN <> "" Then
       txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateA " & _
                "FROM ALTERNATES " & _
                "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _
                "ORDER BY ALTERNATES.AlternateA; "
                Me.txtAlternateA.BackColor = vbYellow
    Else
       txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _
                "FROM PDatabase " & _
                "ORDER BY PDatabase.PartNumber; "
                Me.txtAlternateA.BackColor = vbYellow


    End If
    
    strPN = Nz(DLookup("AlternateB", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "")
                
    If strPN <> "" Then
       txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateB " & _
                "FROM ALTERNATES " & _
                "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _
                "ORDER BY ALTERNATES.AlternateB; "
                Me.txtAlternateA.BackColor = vbYellow
    Else
        txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _
                "FROM PDatabase " & _
                "ORDER BY PDatabase.PartNumber; "
                Me.txtAlternateB.BackColor = vbYellow
        End If
        
        strPN = Nz(DLookup("AlternateC", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "")
                
    If strPN <> "" Then
       txtAlternateC.RowSource = "SELECT ALTERNATES.AlternateC " & _
                "FROM ALTERNATES " & _
                "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _
"ORDER BY ALTERNATES.AlternateC; "
                Me.txtAlternateC.BackColor = vbYellow
    Else
        txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _
                "FROM PDatabase " & _
                "ORDER BY PDatabase.PartNumber; "
                Me.txtAlternateC.BackColor = vbYellow
        End If
    
    End If
End If


End Sub

When Field2 has a selection (which it will not let me make a different selection):

Private Sub Revision_AfterUpdate()


Me.txtComponent = Nz(DLookup("Primary", "DRAWINGS", "[Drawing]='" & Me.cboComponent & "' AND [Revision] ='" & Me.Revision & "'"), "")
Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
Me.txtUOM = DLookup("StockUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
Me.txtCageCode = Nz(DLookup("CageCodeA", "Drawings", "[Drawing] = " & Me.cboComponent & " AND Revision='" & Me.Revision & "'"), "")
Me.txtCageCodeA = DLookup("CageCodeB", "Drawings", "[Drawing] = " & Me.cboComponent & " AND Revision='" & Me.Revision & "'")
Me.txtCageCodeB = DLookup("CageCodeC", "Drawings", "[Drawing] = " & Me.cboComponent & " AND Revision='" & Me.Revision & "'")
Me.txtCageCodeC = DLookup("CageCodeD", "Drawings", "[Drawing] = " & Me.cboComponent & " AND Revision='" & Me.Revision & "'")




'Load AlternateA into the drop down
On Error Resume Next
   txtAlternateA.RowSource = "SELECT DRAWINGS.AlternateA " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Drawing = " & Me.txtComponent & " AND Revision ='" & Me.Revision & "' " & _
            "ORDER BY DRAWINGS.AlternateA; "
            Me.txtAlternateA.BackColor = vbYellow
                
'Load AlternateB into the drop down
On Error Resume Next
   txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Drawing = " & Me.txtComponent & " AND Revision ='" & Me.Revision & "' " & _
            "ORDER BY DRAWINGS.AlternateB;"
            Me.txtAlternateB.BackColor = vbYellow
            
'Load AlternateC into the drop down
On Error Resume Next
   txtAlternateC.RowSource = "Select DRAWINGS.AlternateC " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Drawing = " & Me.txtComponent & " AND Revision ='" & Me.Revision & "' " & _
            "ORDER BY DRAWINGS.AlternateC;"
            Me.txtAlternateC.BackColor = vbYellow
            
End Sub

It would take me all day to type up the whole structure, and theres a backend and frontend, so it would be very time consuming to take out sensitive data.

I can provide screenshots though

So, here's the DRAWING table structure:



Here's the form where entry/lookup takes place:



Here's where the Rev is selected (When I try to select B from the drop down, it pauses for a couple seconds, and then select Rev A by itself):



The remaining fields are coded to populate based on selections made in Field1 and Field2, but they do not.

[June7]

Then I can only suggest you step debug. The Revision_AfterUpdate procedure is behind the Rev combobox?

[gaker10]

Yes, it is.

[June7]

Not seeing anything in any of that code that would interfere with the Revision combobox selection.

Sorry, can't help.

[gaker10]

What about this statement in the code?

Private Sub cboComponent_AfterUpdate()
Dim strPN As String




'Replace drawing number with Primary part if the entry is a drawing
strPN = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "")

I forgot to add the revision criteria here.

I have this so far, but getting an error:

Private Sub cboComponent_AfterUpdate()
Dim strPN As String




'Replace drawing number with Primary part if the entry is a drawing
strPN = Nz(DLookup("Primary", "DRAWINGS", "Drawing=" & Me.cboComponent & " AND Revision ='" & Me.Revision & "'"), "")




'If the string is not empty
If strPN <> "" Then
'this is a DRAWING, set component value and corresponding information
Revision.Requery

EDIT:

Error:


Error Callout:


END EDIT

Also, I totally forgot about the use of cascading comboboxes. Is that a possible solution here?

[June7]

Is Revision a text type field?

Isn't cascading combobox what you are attempting with the VBA that sets RowSource?

Still don't see anything that prevents selecting value in Revision combobox.

[gaker10]

Yes, the Revision is a text type field.

I suppose it is, just was not sure if using a built-in feature rather than trying to make my own would make a difference.

Is there something wrong with my code in Post #7?

[gaker10]

UPDATE: So I think the error in Post #7 is because when I type an entry into my first combobox, the revision combobox is blank by default (no selection has yet been made), and so it is trying to find a drawing with a revision of blank, which because it is a key value in the table, cannot be null. Am I correct here?

Can I set the default value of a combobox to the first selection as a sort of "placeholder" until a real selection is made?

[gaker10]

So, the default value idea didnt work. And I'm out of ideas.

[gaker10]

So I gave in and here's a copy of the database.


FrontEndlocal.zip

[orange]

gaker10,

Do you have a plain English description of what you are trying to do?
eg a description of business/processes that you are trying to automate

I see Drawing, Revision, Cage Code, BOM, Alternates and Assemblies but there is zilch on what it all means and/or how fits together.

With 230 posts this is not your first database. So, how did you get this far with the database you attached knowing it isn't normalized; there is relationships diagram; and I haven't seen any description as to what it is suppose to support.

Did you start with a specification? If so, could you show readers part of it --the overview?

[gaker10]

I made the database initially without knowledge of normalization, and have not had the time to correct it.

The only table I care about right now is Drawings. I left the others in there so the code wouldnt throw errors for non-existing tables.
Cage Codes is a table of cage codes, which are used in Drawings table has multi-value fields in the cage code fields (cagecodeA, cagecodeB etc).
The Revision is a text field of Drawings.
The Primary and Alternate fields of Drawings table are lists that have rowsources from PDatabase.

As it is right now, the drawing field of the Drawings table was the index key. Which means, we could only have one unique drawing number in the table at a time. If we received a new revision of the drawing, the old revision would simply be overwritten with the new revision information. However, now we need to keep old revisions in the database, which means different Primary/Alternate entries, cagecode entries etc for each revision difference.
I changed the structure to allow a drawing/revision key. So, instead of having drawing1 revision a in the database only, now i can have drawing1 and revisions a, b, c, d etc.


What I am trying to do:

Now, with these different choices of revisions, the BOM Entry form needs to allow us to choose the revision, and then load (or look up) it's corresponding information from the Drawing table based on the entry in the cboComponent field of the BOM Entry form.

Does this clarify?

I'm not sure what you mean by "specification".

[orange]

By Specification I mean did someone give you a statement of requirements
eg
- we need to XX, YY an zz... in order to satisfy our contracts with.... All Drawings and Revisions to same must be....with appropriate..... codes ...bla bla bla.

In broad terms these are 'the marching orders" from on high to allocate resources to develop a database/application.

I would advise against the use of lookups at the table field level. Also I would advise against the use of multi-valued fields. I do realize that M$oft added these "features", and they do exist -- but in my view they hide a lot of things, and can be extremely confusing. There are proven techniques -- use lookups on forms; create lookup tables; .. to accomplish these things.

However, without Normalization, and without a data model/ERD to show the business information involved, I'm not sure how to offer focused advice.

The only thing I can say based on many years in database is -- getting your tables and relationships designed to meet your business rules/requirements is a critical step in any database application. If you do not get this step correct, and vetted with some test data, you will have no end to "work-arounds" trying to correct a fundamental design flaw.

You know your business better than any reader, however I think you have jumped into coding without a clear picture of what you are trying to accomplish - but I don't know your environment nor its data management regime. Perhaps others can decipher what you have and offer constructive advice.

Good luck with your project.