Access Variable Not Defined Error

Question

I have the following VB code in an Access form. I have added the field Market to my Query and the string was too long. So I had to break it up into two statements. It seems to work. My issue is with the line:

Market = rs("Market")

I get the variable not defined error. It is in my query. It has been added to the query that generates the form...I'm not sure where to start looking...any help is much appreciated.

Dim db As DAO.Database, rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT TOP 1 CPOrders.Cust, Customer.NAME, CPOrders.CP_Ref, CPOrders.Slsman, CPOrders.Assistant,CPOrders.Date_opn, CPOrders.CPSmall, CPOrders.InvIssu, CPOrders.InvNo, CPOrders.InvDate, CPOrders.DueDate, CPOrders.ETADate, CPOrders.Closed, CPOrders.Cust, CPOrders.Name, CPOrders.BuyerRef, CPOrders.ToCity, CPOrders.ToState, CPOrders.ToCtry, CPOrders.ToPort, CPOrders.Supplier, CPOrders.Origin, CPOrders.Product, CPOrders.GradeType, CPOrders.NoUnits, CPOrders.Pkg, CPOrders.Qty, CPOrders.TotSale, CPOrders.TotCost, CPOrders.GrMargin, CPOrders.[Sale$/Unit], CPOrders.[Cost$/Unit], CPOrders.OceanCost, CPOrders.OceanNotes, CPOrders.BLadingDate, CPOrders.USAPort, CPOrders.FOBCost, CPOrders.FASExportVal, CPOrders.InlandFrt, CPOrders.CommodCode, CPOrders.Notes, CPOrders.ProjCust, CPOrders.ProjValue, CPOrders.ContainerNumber, CPOrders.Vessel, CPOrders.Ord_Terms, CPOrders.Invoice_ID, CPOrders.Movement_Type, CPOrders.Market " & vbCrLf

strSQL = strSQL & "FROM Customer INNER JOIN CPOrders ON Customer.[CUST_#] = CPOrders.Cust ORDER BY CPOrders.Invoice_ID desc;"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
rs.MoveFirst





CP_Ref.Value = rs("CP_Ref")
Slsman.Value = rs("Slsman")
Assistant.Value = rs("Assistant")
Date_opn.Value = rs("Date_opn")
CPSmall.Value = rs("CPSmall")
InvIssu.Value = rs("InvIssu")
InvNo.Value = rs("InvNo")
InvDate.Value = rs("InvDate")
DueDate.Value = rs("DueDate")
Cust.Value = rs("Cust")
BuyerRef.Value = rs("BuyerRef")
ToCity.Value = rs("ToCity")
ToState.Value = rs("ToState")
ToCtry.Value = rs("ToCtry")
ToPort.Value = rs("ToPort")
Product.Value = rs("Product")
Supplier.Value = rs("Supplier")
GradeType.Value = rs("GradeType")
Origin.Value = rs("Origin")
NoUnits.Value = rs("NoUnits")
Pkg.Value = rs("Pkg")
Qty.Value = rs("Qty")
TotSale.Value = rs("TotSale")
TotCost.Value = rs("TotCost")
OceanCost.Value = rs("OceanCost")
OceanNotes.Value = rs("OceanNotes")
BLadingDate.Value = rs("BLadingDate")
USAPort.Value = rs("USAPort")
FOBCost.Value = rs("FOBCost")
Notes.Value = rs("Notes")
InlandFrt.Value = rs("InlandFrt")
CommodCode.Value = rs("CommodCode")
Vessel.Value = rs("Vessel")
ContainerNumber.Value = rs("ContainerNumber")
ProjCust.Value = rs("ProjCust")
ProjValue.Value = rs("ProjValue")
Movement_Type.Value = rs("Movement_Type")
Ord_Terms.Value = rs("Ord_Terms")
Market.Value = rs("Market")



rs.Close
db.Close

HansUp · Accepted Answer

When Access complains "variable not defined" at this line ...

Market = rs("Market")

... the problem is with Market at the beginning of the line; Access thinks that is the name of an undeclared variable. If rs("Market") were the problem, the error message would be about item not found in collection.

The problem you're facing could happen if your form does not include a control named Market. Whether or not that is the explanation, prefix the control name with Me! so that Access understands you mean a named item in the form's default collection (which is its Controls collection) ...

Me!Market.Value = rs("Market")

Access Variable Not Defined Error

Answers (2)

Related Questions