Two tables, one combobox

[gaker10]

ALTERNATES and DRAWINGS use Text format as their PK because these fields are part numbers from the CADatabase (compilation of ALL parts and descriptions/UOM). I suppose I would have to change my relationships with EntryFormTable. When I first created it, it was just pulling info from CADatabase, and so there was only one relationship. Component/PartNumber fields have identical formats.

[gaker10]

Here are the relationships I have so far. What should I add?



Keep in mind that AlternateA and AlternateB of both ALTERNATES and DRAWINGS are also part numbers, as well as Primary.

[June7]

Just more confusing.

Linking on primary keys (which therefore cannot all be autonumber) implies a 1-to-1 relationship and that's what Access is representing in that diagram. Are you inputting into EntryFormTable the ID from CADatabase?

How does the DRAWINGS table fit? I am lost.

[gaker10]

No, ID here means nothing. I simply added it in to keep entry ordered (which must be by order of input). Before, without ID, entry is SUPPOSED to add to the bottom of the table, but sometimes it didn't, it would add by order of part number, which I do not want.

I'm not sure how to link the DRAWINGS table. Drawing is its own unique number, and each Drawing number is associated with a Primary part number. This Primary part number has alternate part numbers A and B. When I fill out my EntryForm, I will sometimes have to enter a drawing number, but off the top of my head I have no idea what part number is associated with it. So, I want my form to replace my entered drawing number with the Primary field associated with it, and its alternates A and B (if they exist).

[June7]

Sorry, by ID I meant the unique identifier - the primary key.

That last statement makes no sense to me.

[gaker10]

Ah ok. The unique identifier here is PartNumber in every case except for the drawing table. In which case, yes. Entries made into EntryFormTable are by looking up ID/PartNumber/Component, unless they are drawing numbers.

Here's where I'm not sure how to implement relationships with DRAWINGS table: Drawing is a number only useful to engineering. It means nothing to the people who are purchasing the parts, and that's the next stop for this list of materials. So, if I'm inputting a list of materials, and I come across a drawing number (which can sometimes be recognized by the user), I want the textbox containing the typed drawing number to replace that text with the Primary field (a part number). So if I start typing "blahblah6", and I'm not sure whether its a part number or drawing number until it replaces "blahblah6" with "99909" (the Primary part for example). It does not even have to be automatic, there could be a pop up that says "Drawing number 'blahblah6' is Primary part number '99909'. Would you like to replace drawing number 'blahblah6' with Primary part number '99909'?". Then, the user could click yes or no. The reason I want this to be automatic is because there is no practical reason to hit no.

Something to note is there are thousands of drawing numbers, and it is not practical to look them up every time, hence the database table.

[June7]

Okay, maybe the lightbulb is flickering.

So the DRAWINGS table associates drawing numbers with a part number (the Primary field) but drawing number has no relationship with other tables.

You select item from combobox which could be either an ALTERNATIVES (part number) or DRAWINGS key.

You need code to check if the selected item is a DRAWING key and if it is to return the Primary field (part number).

Does this get us on the right path:

Dim strPN As String
strPN = Nz(DLookup("Primary", "Drawings", "Drawing='" & Me.combobox & "'"),"")
If strPN <> "" Then
'this is a DRAWING, set combobox value
Me.combobox = strPN
End If

[gaker10]

Yes There's a lot more to it but that's the point that is giving me the most trouble.

I don't really understand VBA well but I can see some logic behind that code, and I will try it What function should I put it in, if any?

[June7]

Not a function, maybe in the combobox BeforeUpdate event procedure.

Might want to include a message box in the code.

MsgBox "Drawing number found. Part number " & strPN & " will be substituted for your selected item.")

Unless your users are just annoyed by being told something they already expect.

[gaker10]

Some of us are familiar with common drawings, but some "out there" drawings, we have to look them up by hand and find the part. I want the drawing table so that if we do find a new one, we can type it in once and that's that.

That code worked flawlessly! Thank you!

Now I need to autofill the alternatesA and B from the DRAWINGS table into the form.

[June7]

If Primary = PartNumber, wouldn't they be the same Alternate A and B as in the ALTERNATES table? If this is the case, why have the Alternate A and B fields duplicated in DRAWINGS?

The combobox can be a query that joins CADatabase and ALTERNATES tables and include the Alternate A and B fields as columns in the RowSource.

[gaker10]

Primary alternates and part alternates are different. Depends on the job.

[gaker10]

Ok so for now drawing alternatives will do since I haven't compiled regular part alternatives (my alternates table is empty at the moment). So how do I make my combobox into a query like you mentioned?

[gaker10]

Also, before I had the component text box autofilling the primary when a drawing number is typed in the combo. When I changed it to autofill the combo and the text box, I get a "run-time error '-2147352567 (80020009)': The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field."

Private Sub cboComponent_BeforeUpdate(Cancel As Integer)


Dim strPN As String
strPN = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "")
If strPN <> "" Then
'this is a DRAWING, set combobox value
Me.cboComponent = strPN
'Me.txtComponent = strPN
End If


End Sub

UPDATE: I used cascading comboboxes to get my alternates to work. But now it wont autofill description and UOM for my alternates:

Private Sub cboComponent_AfterUpdate()


On Error Resume Next
   txtAlternateA.RowSource = "Select DRAWINGS.AlternateA " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Primary = '" & txtComponent & "' " & _
            "ORDER BY DRAWINGS.AlternateA;"

On Error Resume Next
   txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _
            "FROM DRAWINGS " & _
            "WHERE DRAWINGS.Primary = '" & txtComponent & "' " & _
            "ORDER BY DRAWINGS.AlternateB;"


End Sub

I understand why it wont autofill, because the row source changed when I changed which table to look at, but I'm not sure how to change it back for the autofill without making the drop down disappear completely (which happened when I tried to change the row source back to CADatabase). My regular component description and UOM wont autofill because it is dependent on my combobox entry, which I had the above run time error due to the red highlighted code. I tried Me.cboComponent.Value = strPN, Me.cboComponent = Me.txtComponent (after text box change), and every possible variation I could think of.


UPDATE2: I used DLookup to solve my autofill problem. A detour around changing row sources.

Private Sub txtAlternateA_Change()


Me.txtAltADescription = DLookup("Description", "CADatabase", "[PartNumber] = '" & Me.txtAlternateA & "'")
Me.txtAltAUOM = DLookup("PurchaseUOM", "CADatabase", "[PartNumber] = '" & Me.txtAlternateA & "'")


End Sub

Private Sub txtAlternateB_Change()


Me.txtAltBDescription = DLookup("Description", "CADatabase", "[PartNumber] = '" & Me.txtAlternateB & "'")
Me.txtAltBUOM = DLookup("PurchaseUOM", "CADatabase", "[PartNumber] = '" & Me.txtAlternateB & "'")


End Sub

So my next step is to add this to my table in a certain order. So if I have everything filled out, I want Assembly, Component, Description, AssemblyQty, and UOM to be added on the first record, then AlternateA and its Description and UOM to be added to the next record, then AlternateB to the third record. So if I have this:


I want my table to look something like this:



One thing I'd like to add to distinguish main parts from alternates is I want to add " \ALT" to the end of the description text of alternates. So like this:




This is my add code (attached to a button) right now:

Private Sub butAdd_Click()


'Insert vs Update options




'If the part is not in the list
    If Me.txtComponent.Tag & "" = "" Then


'add the part to the list
    
    CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, PriceA, PriceB, PriceC) " & _
    " VALUES ('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & Me.numQtyA & "','" & _
    Me.numQtyB & "','" & Me.numQtyC & "','" & Me.dbPriceA & "','" & Me.dbPriceB & "','" & Me.dbPriceC & "')"
    
    
'update an already existing part
    Else
    CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET Assembly='" & Me.txtAssembly & "'" & _
        ", Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", AssemblyQty='" & Me.numAssemblyQty & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", QtyA='" & Me.numQtyA & "'" & _
        ", QtyB='" & Me.numQtyB & "'" & _
        ", QtyC='" & Me.numQtyC & "'" & _
        ", PriceA='" & Me.dbPriceA & "'" & _
        ", PriceB='" & Me.dbPriceB & "'" & _
        ", PriceC='" & Me.dbPriceC & "'" & _
        " WHERE Component='" & Me.frmEntrySub!Component & "'"
    End If
    
'Clear form after add/update
    butClear_Click


'Refresh form
    frmEntrySub.Form.Requery


End Sub

Would I use separate INSERT INTO statements for AlternateA and AlternateB?

UPDATE3: Separate INSERT INTO's worked

Private Sub butAdd_Click()


'Insert vs Update options




'If the part is not in the list
    If Me.txtComponent.Tag & "" = "" Then


'add the part to the list
    
    CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, UnitPriceA, UnitPriceB, UnitPriceC) " & _
    " VALUES ('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & Me.numQtyA & "','" & _
    Me.numQtyB & "','" & Me.numQtyC & "','" & Me.dbPriceA & "','" & Me.dbPriceB & "','" & Me.dbPriceC & "')"
    
    
    CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
    " VALUES ('" & Me.txtAlternateA & "','" & Me.txtAltADescription & "','" & Me.txtAltAUOM & "')"

    CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
    " VALUES ('" & Me.txtAlternateB & "','" & Me.txtAltBDescription & "','" & Me.txtAltBUOM & "')"
    
'update an already existing part
    Else
    CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET Assembly='" & Me.txtAssembly & "'" & _
        ", Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", AssemblyQty='" & Me.numAssemblyQty & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", QtyA='" & Me.numQtyA & "'" & _
        ", QtyB='" & Me.numQtyB & "'" & _
        ", QtyC='" & Me.numQtyC & "'" & _
        ", PriceA='" & Me.dbPriceA & "'" & _
        ", PriceB='" & Me.dbPriceB & "'" & _
        ", PriceC='" & Me.dbPriceC & "'" & _
        " WHERE Component='" & Me.frmEntrySub!Component & "'"
    End If
    
'Clear form after add/update
    butClear_Click


'Refresh form
    frmEntrySub.Form.Requery


End Sub

[June7]

Whew! Is there still an issue to deal with?