How to add a field not in field list to an access report

[tofusunday]

I am pretty new to editing things in vba access. I have a report that didn't have a field that I needed in the field list, I modified the recordsource query, it is a sql pass through query:
SELECT dbo.SalesOrderDelivery.SOD_RecordID,dbo.SalesOrder Delivery.SOD_UserDef1,dbo.SalesOrderDelivery.SOD_U serDef2, dbo.SalesOrder.SOM_SalesOrderID, dbo.SalesOrderline.SOI_PackslipDesc,dbo.SalesOrder .SOM_SpecialInst, dbo.SalesOrderLine.SOI_SOLineNbr, dbo.SalesOrderDelivery.SOD_RequiredDate, dbo.Shipment.SH_ShipDate AS SOS_ShipDate, dbo.ShipmentLine.SHL_ShipQty AS SOS_ShipQty, dbo.ShipmentLine.SHL_CertificationNbr AS SOS_CertificationNbr, dbo.Shipment.SH_PackingSlipNbr AS SOS_PackingSlipNbr,

That's part of it, but basically I typed in the CertificationNbr entry. When I run it in the query screen, I am able to pull the cert# from the table, and get the field to appear on the field list in the report. I then proceed to put it on the report, save the report, and compile the files in our ERP software. After I compile the files, if I re-open the report and query my addition to the query is gone.

I know I'm probably missing a step somewhere, but have no idea what else to try. Thanks in advance for your help.

[patjones]

I then proceed to put it on the report, save the report, and compile the files in our ERP software. After I compile the files, if I re-open the report and query my addition to the query is gone.

I'm not sure what you mean by this, especially "compiling [the] files in our ERP software".

When you save the report, how are you doing it? Exporting it to text file, PDF, or something like that? Please clarify.

Pat

[tofusunday]

I'm saving it in Access. The Access files our used by our ERP software to generate reports. When something is changed in the Access files the software requires you to compile the mdb files.

Did I go about modifying the query correctly? I'm just wondering if typing in the new field I wanted and then saving (closed it and it asked me if I wanted to update the recordsource) was the right step to take. After I did add it in the query, it was in the field list in the report and would pull the value I wanted from the table. It was just after compiling that it went away.

I know that probably sounds confusing. Thanks for your help.

[patjones]

When you open the report inside Access itself (either Report View or Print Preview) does the added column appear?

I'm not sure what the ERP software does or what it's purpose is, but if I had to make a guess I would say that what it's doing is accessing the tables inside your database. If that is the case then naturally your added column isn't going to appear, because you added your column to a report that is internal to Access, not the underlying table on which everything else is based.

Pat

[tofusunday]

Yes, the added column appears in design view of the report. Our ERP software actually uses Access as it's main interface to generate reports. In order to edit anything in the mdb files you open up the ERP software in Admin mode and at the same time it opens access.

Once I have the ERP software compile the MDB files, and open the ERP software in normal user mode, make a call to generate the report, my added data comes back with an error. If I then re-enter the ERP software in admin mode and look at the report and underlying query my addition that I made to the query is gone.

I know this is a weird issue and may have everything to do with our ERP software and not the way I edited the query in Access, but I just wanted to see if I was even editing the query correctly.

Thanks again.