VB help accessing unrelated table on Report load (1 Viewer)

Simple Customer table <CustomerID pk>, Invoice Table <InvoiceID pk, CustomerID fk> Form/subForm which propagates all 45 fields to Report2 <an Invoice> fields except some descriptions which I have to retrieve from a description table: Table1-Desc: DescID pk <1-4>,DescName <"Sealing" "Painting" etc>,DescNotes <the description which I want in the Report2 <invoice>

In Invoice table, there are 4 INDicator fields Sealing, Painting, etc <verified, " " or "x">. If the field = "x" then On Report Load I want to put these descriptions moved into Report2 concatenated and skipping a line between...
Private Sub Report_Load()
TempVal = Me.NOTES <there could already be "specific" NOTES fr Invoice table too>
If Me!Sealing = "x" Then
TempVal = TempVal & [Table1-Desc].DescNotes(1) & vbNewLine & " "
etc
The Report2 <invoice> center section NOTES <field fr Invoice table> has a large outlined txt block into which these job descriptions should appear in any combination. The Desc table is NOT open and I do not know how to relate it to the invoice table if need be.

If you are trying to make a "recap" of descriptions, use a subreport. That way you can do it without code. Put it in the report header to have it appear once on the first page of the report. Put it in the page header or footer to have it appear on every page.

An alternative is to join to the description table in the report's RecordSource query and that will allow you to choose the text description rather than the code and display it with each detail record eliminating the need for a recap.

NO this is DETAIL for ONE/EACH invoice/job. This construction firm wants to use the same general long description for PAINTING ie warranting etc, ditto another distinct desc for SEALING, another for PRESSURE CLEANING, & another for PREP... BUT they do not want to have to type all that information on the invoice EVERYTIME they have a client who has any combination of those 4 services. Mind you, this is 4 1-byte fields- the other ~41 fields on the report/invoice come directly from the Customer and Invoice DB. All the user does when creating an invoice is "x" the field e.g. "Sealing" in creation of the invoice, then when I load the Report I want to propagate a large text area in the middle of the Invoice with the appropriate verbiage for any combination of those indicators PLUS the specific NOTES <from the invoice table> for that entire job that the person creating the invoice had to enter by hand. So NO, these field INDicators are different for EVERY invoice/job. They ENTER and PRINT one job/invoice at a time. I just automated everything they did in longhand on invoice forms AND created their tables/databases so I can run all kinds of sales stats, cost & profit info.
I just want to access the DESC TABLE if the indicator ="x" in each of those 4 fields and move the corresponding long description into the invoice.

If an certain indicators ="x" in my invoice form, I want to go to the DESCRIPTION table to get the standard verbiage for that job to then put it in the report/invoice text box. I DO NOT want to store it in the invoice table. I do not know how to set up a relationship to the invoice table; I don't see a "relationship" other than:
Fields in Invoice Table: Sealing, Painting, etc; DescIB pk in Table1-Desc table
DescNotes is field I want
If Me.Sealing ="x", the DescID=1 <the Table1-Desc field I want is DescNotes>
If Me.Painting ="x", the DescID=2
If Me.PressClean ="x", the DescID=3
If Me.Prep ="x", the DescID=4

You need a description table to store the descriptions.
tblDescription
DescID (autonumber primary key)
DescText

tblInvoiceItems
InvoiceItemID (autonumber primary key)
DescID (foreign key to tblDesc)
etc.

You need to join the two tables on DescID (left join in case the DescID field is empty in the tblInvoiceItems) and pick up the details from the detail table and the description from the description table.

The Invoice has over 40 fields these are only 4 of them. I have 4 job indicators which if "ON" I have to get the jobdesc fr the Table-Desc . If none of the 4 are "ON", I simply use Table-Invoice NOTES field in the center of my report/printed invoice. BUT if IND1 is "ON", I have to get the DescName(1) & DescNotes(1) fr Table-Desc, Ditto for IND2 -> IND4. I could have any combination.

TempVal = Me!NOTES

If IND1 = "x" then
TempVal = TempVal & DescName(1) & " " & DescNotes(1) & vbnewline
End If
ditto IND2 and IND3 thru IND4 "ON"
If IND4 = "x" then
TempVal = TempVal & DescName(4) & " " & DescNotes(4) & vbnewline
End If

then I want TempVal to replace NOTES in the large text field in the middle of the Invoice/Report.

It will look like:

This is the NOTES field directly from the invoice field NOTES which describes any particular job/contract descriptions warranties... job particulars.

SEALING: This is the standard SEALING DESCRIPTION for this company and is static for every SEALING job performed by this company for any customer and will detail everything in the owner's verbiage. In entering the INVOICE, they do not want to have to type in these standard job descriptions.

PAINTING: This is the standard PAINTING DESCRIPTION for this company and is static for every PAINTING job performed by this company for any customer and will detail everything in the owner's verbiage. In entering the INVOICE, they do not want to have to type in these standard job descriptions.

PRESSURE WASHING: This is the standard PRESSURE WASHING DESCRIPTION for this company and is static for every PRESSURE WASHING job performed by this company for any customer and will detail everything in the owner's verbiage. In entering the INVOICE, they do not want to have to type in these standard job descriptions.

PREPARATION: This is the standard PREP DESCRIPTION for this company and is static for every PREP job performed by this company for any customer and will detail everything in the owner's verbiage. In entering the INVOICE, they do not want to have to type in these standard job descriptions.


If the job was SEALING only this area would include NOTES, if not blank, and the SEALING description. In any of my languages this would be a simple task setting up a table/array and looping thru.

FOR ONE INVOICE, I NEED THE ENTIRE TABLE-Desc, indexes 1,2,3,4and 4.

The Invoice has over 40 fields these are only 4 of them. I have 4 job indicators which if "ON" I have to get the jobdesc fr the Table-Desc . If none of the 4 are "ON", I simply use Table-Invoice NOTES field in the center of my report/printed invoice. BUT if IND1 is "ON", I have to get the DescName(1) & DescNotes(1) fr Table-Desc, Ditto for IND2 -> IND4. I could have any combination.

TempVal = Me!NOTES

If IND1 = "x" then
TempVal = TempVal & DescName(1) & " " & DescNotes(1) & vbnewline
End If
ditto IND2 and IND3 thru IND4 "ON"
If IND4 = "x" then
TempVal = TempVal & DescName(4) & " " & DescNotes(4) & vbnewline
End If

then I want TempVal to replace NOTES in the large text field in the middle of the Invoice/Report.

It will look like:

This is the NOTES field directly from the invoice field NOTES which describes any particular job/contract descriptions warranties... job particulars.

SEALING: This is the standard SEALING DESCRIPTION for this company and is static for every SEALING job performed by this company for any customer and will detail everything in the owner's verbiage. In entering the INVOICE, they do not want to have to type in these standard job descriptions.

PAINTING: This is the standard PAINTING DESCRIPTION for this company and is static for every PAINTING job performed by this company for any customer and will detail everything in the owner's verbiage. In entering the INVOICE, they do not want to have to type in these standard job descriptions.

PRESSURE WASHING: This is the standard PRESSURE WASHING DESCRIPTION for this company and is static for every PRESSURE WASHING job performed by this company for any customer and will detail everything in the owner's verbiage. In entering the INVOICE, they do not want to have to type in these standard job descriptions.

PREPARATION: This is the standard PREP DESCRIPTION for this company and is static for every PREP job performed by this company for any customer and will detail everything in the owner's verbiage. In entering the INVOICE, they do not want to have to type in these standard job descriptions.


If the job was SEALING only this area would include NOTES, if not blank, and the SEALING description. In any of my languages this would be a simple task setting up a table/array and looping thru