Basic VBA Question - Adding a field

[BRIANVH]

I am attempting a minor modification to VBA code used in an Access 2000 report, but I know little about VBA code. I added a field to a query used in the report, and I attempted to modify the VBA code in the report to use the new field. In this case, the VBA code just controls whether a logo is printed on the report (label). It appears my attempted modification will not work because the field I added does not seem to accessable. I don't see the field I added as a member available to use, while I do see the other pre-existing fields from the query. I am pretty good with Access, but VBA code with Access is beyond my normal requirements. What do I need to do to be able to use the field I added? Thanks in advance for any help.

 

[SkipVought]

hi,

What do you mean by an 'added field?'

1. You added a field that already exists in a table, to your query?

2. You added a NEW FIELD to a table that never before existed?

Please post whatever code you have been using.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BRIANVH]

I just added a field that already existed to a query. The query runs off our ERP system tables. The code is pretty simple, it just controls whether a logo is printed or not on a label. I just need to change the code so it will use the field I added to the query. The following is the code:

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Err_Detail_Format

'Run code to determine whether or not the Fredman Bag logo should be displayed.


If IsNull([CUSTOMER_TYPE]) Then
SOLDBY.Visible = False
imLogo.Visible = True

Else
SOLDBY.Visible = True
imLogo.Visible = False

End If



Exit_Detail_Format:
Exit Sub

Err_Detail_Format:
MsgBox Err.Description & " in Detail_Format."
Resume Exit_Detail_Format

End Sub

Private Sub Report_Open(Cancel As Integer)

End Sub


When I replace [CUSTOMER_TYPE] with [USER_2] (the field I added), I get an error. If I look at the object browser, I see [CUSTOMER_TYPE] and other fields from the query, but I do not see [USER_2].

 

[SkipVought]

So are you saying that [User_2] is already a field in one of the tables that your query has in the FROM clause?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BRIANVH]

Yes, [USER_2] has always existed in the tables, but I added it to the query to make it usable in the report.

 

[SkipVought]

Post your SQL code please.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Did you add a text box in the detail section that is bound to USER_2? You can't reference the field value if it is not bound to a control in the report.

Duane
Hook'D on Access
MS Access MVP

 

[BRIANVH]

Below is the sql code for the query, but the trouble I am having is with the VBA code in the associated report. I am not having any trouble with the query.

SELECT [SYSADM_CUST_ORDER_LINE]![CUST_ORDER_ID] & "/" & [LINE_NO] AS JOBORDER, IIf([CUSTOMER_TYPE] Is Null,[CUSTOMER_PO_REF],[CUSTOMER_TYPE]) AS POREF, IIf([BITS] Is Null,[CUSTOMER_PART_ID],[BITS]) AS PARTREF, IIf([CUSTOMER_TYPE]="CUSTOM","ERROR - USE CUSTOM LABEL",IIf([SYSADM_CUSTOMER]![USER_2]="X",[NAME],IIf([CUSTOMER_TYPE] Is Null,"FREDMAN BAG",[NAME]))) AS SOLDBY, SYSADM_CUSTOMER_ORDER.CUSTOMER_TYPE, IIf([CUSTOMER_TYPE] Is Null,"To Reorder Call (414) 462-9400

http://www.fredmanbag.com","")

AS REORDER, SYSADM_CUST_LINE_BINARY.BITS, SYSADM_CUSTOMER_ORDER.CUSTOMER_PO_REF, SYSADM_CUST_ORDER_LINE.CUSTOMER_PART_ID, [ENTER QTY] AS Expr1, SYSADM_CUST_ORDER_LINE.SELLING_UM, SYSADM_CUST_ORDER_LINE.PART_ID, SYSADM_CUST_ORDER_LINE.MISC_REFERENCE, Month(Date()) & "/" & Year(Date()) AS Expr2, SYSADM_PART.STOCK_UM, SYSADM_CUSTOMER.USER_2
FROM (((SYSADM_CUST_ORDER_LINE LEFT JOIN SYSADM_CUST_LINE_BINARY ON (SYSADM_CUST_ORDER_LINE.LINE_NO = SYSADM_CUST_LINE_BINARY.CUST_ORDER_LINE_NO) AND (SYSADM_CUST_ORDER_LINE.CUST_ORDER_ID = SYSADM_CUST_LINE_BINARY.CUST_ORDER_ID)) LEFT JOIN SYSADM_CUSTOMER_ORDER ON SYSADM_CUST_ORDER_LINE.CUST_ORDER_ID = SYSADM_CUSTOMER_ORDER.ID) LEFT JOIN SYSADM_CUSTOMER ON SYSADM_CUSTOMER_ORDER.CUSTOMER_ID = SYSADM_CUSTOMER.ID) LEFT JOIN SYSADM_PART ON SYSADM_CUST_ORDER_LINE.PART_ID = SYSADM_PART.ID
WHERE ((([SYSADM_CUST_ORDER_LINE]![CUST_ORDER_ID] & "/" & [LINE_NO])=[Enter JOB#/LOT#]));

 

[BRIANVH]

dhookom,

Thank you, your suggestion worked. Problem solved.