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 · 2016-03-03 17:22:43Z

1

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")

edited Mar 3, 2016 at 17:22

answered Mar 3, 2016 at 17:15

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Johnny Bones · Accepted Answer · 2016-03-03 17:09:22Z

Try this:

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, " & _
strSQL = strSQL & "CPOrders.CPSmall, CPOrders.InvIssu, CPOrders.InvNo, CPOrders.InvDate, CPOrders.DueDate, " & _
strSQL = strSQL & "CPOrders.ETADate, CPOrders.Closed, CPOrders.Cust, CPOrders.Name, CPOrders.BuyerRef, CPOrders.ToCity, " & _
strSQL = strSQL & "CPOrders.ToState, CPOrders.ToCtry, CPOrders.ToPort, CPOrders.Supplier, CPOrders.Origin, CPOrders.Product, " & _
strSQL = strSQL & "CPOrders.GradeType, CPOrders.NoUnits, CPOrders.Pkg, CPOrders.Qty, CPOrders.TotSale, CPOrders.TotCost, " & _
strSQL = strSQL & "CPOrders.GrMargin, CPOrders.[Sale$/Unit], CPOrders.[Cost$/Unit], CPOrders.OceanCost, CPOrders.OceanNotes, " & _
strSQL = strSQL & "CPOrders.BLadingDate, CPOrders.USAPort, CPOrders.FOBCost, CPOrders.FASExportVal, CPOrders.InlandFrt, " & _
strSQL = strSQL & "CPOrders.CommodCode, CPOrders.Notes, CPOrders.ProjCust, CPOrders.ProjValue, CPOrders.ContainerNumber, CPOrders.Vessel, " & _
strSQL = strSQL & "CPOrders.Ord_Terms, CPOrders.Invoice_ID, CPOrders.Movement_Type, CPOrders.Market " & _
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

Collectives™ on Stack Overflow

Access Variable Not Defined Error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related