Two tables, one combobox

[gaker10]

I would like to populate a drop down combobox with info from two tables merged into one column. C/ADatabase and DRAWINGS are tables that are related, but used differently. Sometimes one must look up parts straight from the Database, while other times we are given Drawing numbers that correspond to parts in the Database. I tried using a UNION statement in my Row Source property, but not sure my syntax is quite right.

SELECT [C/ADatabase].[PartNumber], [C/ADatabase].[Description], [C/ADatabase].[PurchaseUOM] FROM [C/ADatabase] UNION SELECT [DRAWINGS].[Drawing] FROM [DRAWINGS];



I get the error: "The number of columns in the two selected tables or queries of a union query do not match"

[June7]

Consider:

... SELECT [Drawing], Null, Null FROM [DRAWINGS];

[gaker10]

That worked! Thank you!

Now, when I select a component or drawing number from my combobox, I want to autofill some form fields AlternateA and AlternateB. I have a table named ALTERNATES, which consists of the PartNumber, AlternateA, and AlternateB. My DRAWINGS table has a similar set up.

When I select a component from my combobox, I want AlternateA and AlternateB fields (if applicable) to be autofilled using my ALTERNATES table. When I choose a drawing number, I want said fields to be autofilled using my DRAWINGS table with corresponding parts. In reality, these drawing numbers will actually be replaced by part numbers, because the drawing numbers have no meaning to whomever is purchasing parts. Sometimes, a part corresponding to a drawing number will have an alternative or two. Even though I have two different tables, the parts are all from the same Database.

Eventually, Ill compose a report of this information with a hierarchy structure where the component is on top and its alternates are beneath in an indented fashion.

[June7]

You have an Alternates table for parts and an Alternates table for drawings? You want to pull values from appropriate Alternates table and save in current record of form? That can be done with VBA.

Should probably modify the combobox RowSource to include a field that identifies which table the row item is from.

SELECT ... , "Parts" AS Source FROM [C/ADatabase] ... , "Drawings" FROM [Drawings];

Then VBA code can reference the Source field to know which Alternates table to pull values from.

[gaker10]

Yes to both questions. I'm not too good with VBA code. How do I tell the fields themselves where to look for autofill information?

[June7]

The Alternates tables are not part of the form RecordSource so code will have to do lookup to the correct table. The search can use DLookup() expressions or open a recordset and then populate the textboxes, like:

If Me.combobox.Column(3) = "Parts"
'search parts alternates
Me.Alternate1 = DLookup("fieldname", "PartsAlternates", "ID=" & Me.combobox)
Else
'search drawings alternates
Me.Alternate1 = DLookup("fieldname", "DrawingsAlternates", "ID=" & Me.combobox)
End If

[gaker10]

The way I have it set up right now is when a part is found in the database, the description and UOM are autofilled because my combobox actually displays the description and UOM corresponding to a part in the database in columns 1 and 2, and then I set my text fields to those columns. But I set the column width for description and UOM to 0 because I dont need to see them in the drop down itself. Not really sure how the combobox column works here in the IF statement. Also, the primary key in my alternates table is the PartNumber, to prevent duplicates and order them accordingly. Same scenario with Drawing numbers in DRAWINGS table.

Another problem I have is the table corresponding to my entry form has an Alternates field. I want to create a subdatasheet in this field to display AlternateA and AlternateB and the Description, UOM, Qty, Price etc information (basically the same info as any part except for the assembly field). Having trouble choosing Child and Master fields. I want AlternateA and AlternateB as my child fields, and Alternates as my master field (I think thats the right order). It will not let me choose them like this.

[gaker10]

Private Sub cboComponent_Change()


'Pseudo-autofill for Description and UOM based on Component entry
    Me.txtComponent = cboComponent.Column(0)
    Me.txtDescription = cboComponent.Column(1)
    Me.txtUOM = cboComponent.Column(2)




End Sub

This is the code that autofills right now from the Database only not including alternates. I need additional code for

Situation A: A part number from the database is selected, autofill AlternateA and AlternateB from ALTERNATES table, use database to autofill AlternateA and AlternateB's Description and UOM fields.

Situation B: A drawing number is selected, autofill AlternateA and AlternateB from DRAWINGS based on selected drawing, autofill AlternateA and AlternateB's description and UOM based on info in database, replace selected drawing number with Primary part number, autofill Primary part number's description and UOM based on info in database.

Yes, there is a difference between Alternates for parts and alternates for parts that correspond to drawings.

[June7]

I provided sample code in post 6 for retrieving data from the appropriate Alternates table.

[gaker10]

What about creating a subdatasheet in the Alternates field for the table?

[gaker10]

Private Sub cboComponent_Change()


'Pseudo-autofill for Description and UOM based on Component entry
    If (Me.cboComponent = "PartNumber") Then
    
        'Autofill if the search directly yields a part
    
        Me.txtComponent = cboComponent.Column(0)
        Me.txtDescription = cboComponent.Column(1)
        Me.txtUOM = cboComponent.Column(2)
    
        Me.txtAlternateA = DLookup("AlternateA", "ALTERNATES", "ID=" & Me.cboComponent)
        Me.txtAlternateB = DLookup("AlternateB", "ALTERNATES", "ID=" & Me.cboComponent)
    
        Me.txtAltADescription = txtAlternateA.Column(1)
        Me.txtAltAUOM = txtAlternateA.Column(2)
        Me.txtAltBDescription = txtAlternateB.Column(1)
        Me.txtAltBUOM = txtAlternateB.Column(2)
    
    Else
    
        'Autofill if the search directly yields a drawing number
    
        Me.txtAlternateA = DLookup("AlternateA", "DRAWINGS", "ID=" & Me.cboComponent)
        Me.txtAlternateB = DLookup("AlternateB", "DRAWINGS", "ID=" & Me.cboComponent)
        
        Me.txtAltADescription = txtAlternateA.Column(1)
        Me.txtAltAUOM = txtAlternateA.Column(2)
        Me.txtAltBDescription = txtAlternateB.Column(1)
        Me.txtAltBUOM = txtAlternateB.Column(2)
        
        Me.txtComponent = DLookup("Primary", "DRAWINGS", "ID=" & Me.cboComponent)
        Me.txtDescription = cboComponent.Column(1)
        Me.txtUOM = cboComponent.Column(2)
       
      End If
        
End Sub

Autofill code. When I start typing in the combobox with the list of parts and drawing numbers I get run time error '2471':

The expression you entered as a query parameter produced this error: 'ID'

[gaker10]

I kind of figured out the subdatasheet. I got it looking the way I want but its creating subdatasheets within subdatasheets. Attached screenshots.

I made a report out of it to get a decent layout. I dont want the comp1 and comp2 repetitions under the assembly1. Also, when I export my report to excel, the text is grey. Dont want to have to change it to black every time I export.

[June7]

I never use tables and queries as the SourceObject for a subform container control. I use forms.

If you don't want the rows that say 'comp1' or 'comp'2 then filter them out of the report dataset.

I don't know why it exports as grey text. What is the color setting for the report textboxes?

[gaker10]

The subform acts as a preview and allows editing within the form without going back and forth between the table and form tabs.

How do I filter them out before creating the report?

Access wont allow any font editing from the report. The grey text in excel is black in access.

[June7]

I would use form/subform arrangement and the subform would be based on another form. I don't build database where users work directly with tables and queries.

Maybe report RecordSource:

SELECT * FROM table WHERE Component <> "Comp1" AND Component <> "Comp2";

"Won't allow font editing from the report" - what does that mean? Open report in design view to edit properties.