Runtime Error 424 - Object Required...

sanders720 · Feb 24, 2003

I am trying to populate the same way with a variable, and it can't find the object...

B1(q).Value = "2"

This happens with either .Value or .Text... The B1(q) evaluates properly... Thanks in advance for any help you can provide...

Private Sub BUILDQUERY2()

Dim c As Integer
Dim q As Integer

B1 = Array("txtITJobNo", "txtVendor", "txtInvoiceNo", "txtRequestingUser", "txtAuthorizingUser", "txtPurchaseDate", _
"txtWarrantyDate", "txtManufacturer", "txtName", "txtPartNo", "txtSerialNo", "txtCDKey", _
"txtUpgrade", "txtQuantity", "txtSeats", "txtUser", "txtInstalledPC", "txtLicense", _
"txtPurchasedPrice&quot

For q = 1 To 19
If B(q) <> "" And B1(q) <> "2" Then
c = c + 1
If Len(OBJECT2) > 0 Then
OBJECT2 = OBJECT2 & " and (" & A(q) & "= '" & B(q) & "'.Text)"
Else
OBJECT2 = "(" & A(q) & "= '" & B(q) & "'.Text)"
End If
Debug.Print OBJECT2

B1(q).Value = "2"
B(q).Enabled = False

End If

Next q

End Sub

VicM · Feb 24, 2003

sanders:

What is the distinction between B(q) and B1(q)?

If your array is B1(), then B() is a non-defined variable which Access thinks is an array object, but has not reserved memory for.

HTH,

Vic

sanders720 · Feb 24, 2003

Option Base 1

Public A
Public B
Public B1

The arrays are defined as public at the beginning of the program. Is there something else I need to do within the event to reserve memory before using the variable, and what might it look like?

Thanks for the help on this!!!

VicM · Feb 24, 2003

sanders:

If you just declare the public variables as you've shown, then they all start out as type Variant, not arrays. Your code in Sub BUILDQUERY2() redefines B1 as an array and initializes its elements.

Where and how do you set the other variables A and B, declaring them as arrays and initializing their elements?

Do you do something similar to A and B elsewhere in your code?

Vic

sanders720 · Feb 24, 2003

Yes... A and B are defined at the FormLoad event.

Option Compare Database
Option Base 1

Public A
Public B
Public B1

Public OBJECT As String
Public OBJECT2 As String
Public OBJECT3 As String
Public OBJECT4 As String

Private Sub PopulateComboBoxes()
Dim rst As Recordset
Dim sql As String
Dim c As Integer
Dim x As Integer
Dim extprice As Currency

sql = "SELECT * FROM qryInvoice"

Set rst = CurrentDb.OpenRecordset(sql)

rst.Close
Set rst = Nothing

sql = "SELECT qryInvoice.[Product Name], qryInvoice.[Product Manufacturer], qryInvoice.[Product Part Number], " & _
"qryInvoice.Quantity, qryInvoice.[Seats Covered], qryInvoice.[Purchase Price]" & _
"FROM qryInvoice " & _
"WHERE (((qryInvoice.[Invoice Number])= '" & Me.cboInvoiceNumber.Text & "'));"

Set rst = CurrentDb.OpenRecordset(sql)
rst.MoveLast
c = rst.RecordCount
rst.MoveFirst

With msgPurchasedDetail

If .Rows > 1 Then
.Rows = 1
End If

For x = 1 To c
.Rows = .Rows + 1
.TextMatrix(.Rows - 1, 1) = rst("[Product Manufacturer]&quot

.TextMatrix(.Rows - 1, 2) = rst("[Product Name]&quot

.TextMatrix(.Rows - 1, 3) = rst("[Product Part Number]&quot

.TextMatrix(.Rows - 1, 4) = rst("Quantity&quot

.TextMatrix(.Rows - 1, 5) = rst("[Seats Covered]&quot

.TextMatrix(.Rows - 1, 6) = Format(rst("[Purchase Price]&quot

, "$###0.00&quot

.TextMatrix(.Rows - 1, 7) = Format(.TextMatrix(.Rows - 1, 4) * .TextMatrix(.Rows - 1, 6), "$###0.00&quot

rst.MoveNext

Next x
End With

rst.Close
Set rst = Nothing

End Sub

Private Sub cboAuthorizingUser_Click()
Me.txtAuthorizingUser.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboCDKey_Click()
Me.txtCDKey.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboExtendedPrice_Click()
Me.txtExtendedPrice.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboInstalledPC_Click()
Me.txtInstalledPC.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboInvoiceNo_Click()
Me.txtInvoiceNo.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboITJobNo_Click()
Me.txtITJobNo.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboLicense_Click()
Me.txtLicense.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboManufacturer_Click()
Me.txtManufacturer.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboName_Click()
Me.txtName.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboPartNo_Click()
Me.txtPartNo.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboPurchaseDate_Click()
Me.txtPurchaseDate.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboPurchasedPrice_Click()
Me.txtPurchasedPrice.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboQuantity_Click()
Me.txtQuantity.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboRequestingUser_Click()
Me.txtRequestingUser.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboSeats_Click()
Me.txtSeats.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboSerialNo_Click()
Me.txtSerialNo.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboUpgrade_Click()
Me.txtUpgrade.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboUser_Click()
Me.txtUser.Value = "1"
BUILDQUERY
REPOPULATE

End Sub

Private Sub cboVendor_Click()
Me.txtVendor.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboWarrantyDate_Click()
Me.txtWarrantyDate.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub Form_Load()

Dim x As String
Dim c As Integer
Dim q As Integer
Dim sql As String
Dim rs As ADODB.Recordset

With msgPurchasedDetail

.Rows = 1
.Cols = 21

.ColWidth(0) = 0
.ColWidth(1) = 600
.ColWidth(2) = 2000
.ColWidth(3) = 1000
.ColWidth(4) = 1500
.ColWidth(5) = 1500
.ColWidth(6) = 800
.ColWidth(7) = 800
.ColWidth(8) = 2200
.ColWidth(9) = 2400
.ColWidth(10) = 1800
.ColWidth(11) = 2000
.ColWidth(12) = 2200
.ColWidth(13) = 600
.ColWidth(14) = 600
.ColWidth(15) = 600
.ColWidth(16) = 1600
.ColWidth(17) = 1600
.ColWidth(18) = 2200
.ColWidth(19) = 900
.ColWidth(20) = 1000

For q = 0 To 19
.ColAlignment(c) = 1
Next q

.TextMatrix(0, 1) = "IT Job No."
.TextMatrix(0, 2) = "Vendor"
.TextMatrix(0, 3) = "Inv No."
.TextMatrix(0, 4) = "Req User"
.TextMatrix(0, 5) = "Auth User"
.TextMatrix(0, 6) = "Purch Date"
.TextMatrix(0, 7) = "Warr Date"
.TextMatrix(0, 8) = "Manufacturer"
.TextMatrix(0, 9) = "Name"
.TextMatrix(0, 10) = "Part No."
.TextMatrix(0, 11) = "Serial No."
.TextMatrix(0, 12) = "CD Key"
.TextMatrix(0, 13) = "Upgr"
.TextMatrix(0, 14) = "Qty"
.TextMatrix(0, 15) = "Seats"
.TextMatrix(0, 16) = "User"
.TextMatrix(0, 17) = "Inst PC"
.TextMatrix(0, 18) = "License"
.TextMatrix(0, 19) = "Purch Price"
.TextMatrix(0, 20) = "Ext Price"

End With

Set rs = New ADODB.Recordset

A = Array("[IT Job Number]", "Vendor", "[Invoice Number]", "[Requesting User]", "[Authorizing User]", "[Purchase Date]", _
"[Warranty End Date]", "[Product Manufacturer]", "[Product Name]", "[Product Part Number]", "[Product Serial Number]", "[CD Key]", _
"[Upgrade Flag]", "Quantity", "[Seats Covered]", "[User Assignment]", "[Installed PC Name]", "[Product License Number]", _
"[Purchase Price]&quot

B = Array("cboITJobNo", "cboVendor", "cboInvoiceNo", "cboRequestingUser", "cboAuthorizingUser", "cboPurchaseDate", _
"cboWarrantyDate", "cboManufacturer", "cboName", "cboPartNo", "cboSerialNo", "cboCDKey", _
"cboUpgrade", "cboQuantity", "cboSeats", "cboUser", "cboInstalledPC", "cboLicense", _
"cboPurchasedPrice&quot

For q = 1 To 19
Debug.Print A(q)
Debug.Print B(q)

sql = "SELECT qrySoftwareTracking." & A(q) & " FROM qrySoftwareTracking;"
Debug.Print sql

' Forms!frmSoftwareTracking(B(q)).RowSource = sql (syntax for modules)
Me(B(q)).RowSource = sql

' The following code is not necessary, but is good syntax reference for ADO
With rs
.CursorLocation = adUseClient
.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
.MoveLast
c = .RecordCount
Debug.Print c
.MoveFirst

.Update
.Close

End With
Next q

Set rs = Nothing

End Sub

Private Sub BUILDQUERY()

Dim c As Integer
Dim q As Integer

Dim pL As String
Dim pR As String

For q = 1 To 19

If B1(q).Text <> "2" Then Me.B(q).Clear

If B(q) <> "" Then
c = c + 1
If Len(OBJECT) > 0 Then
OBJECT = OBJECT & " and (" & A(q) & "= '" & B(q) & "'.Text)"
Else
OBJECT = "(" & A(q) & "= '" & B(q) & "'.Text)"
End If
Debug.Print OBJECT

End If

Next q

For q = 2 To c
pL = pL & "("
pR = pR & &quot

"
Next q

OBJECT = pL & OBJECT & pR

End Sub

Private Sub BUILDQUERY2()

Dim c As Integer
Dim q As Integer

B1 = Array("txtITJobNo", "txtVendor", "txtInvoiceNo", "txtRequestingUser", "txtAuthorizingUser", "txtPurchaseDate", _
"txtWarrantyDate", "txtManufacturer", "txtName", "txtPartNo", "txtSerialNo", "txtCDKey", _
"txtUpgrade", "txtQuantity", "txtSeats", "txtUser", "txtInstalledPC", "txtLicense", _
"txtPurchasedPrice&quot

For q = 1 To 19
If B(q) <> "" And B1(q) <> "2" Then
c = c + 1
If Len(OBJECT2) > 0 Then
OBJECT2 = OBJECT2 & " and (" & A(q) & "= '" & B(q) & "'.Text)"
Else
OBJECT2 = "(" & A(q) & "= '" & B(q) & "'.Text)"
End If
Debug.Print OBJECT2

B1(q).Value = "2"
B(q).Enabled = False

End If

Next q

End Sub

Private Sub REPOPULATE()

Dim rs As Recordset
Dim q As Integer
Dim c As Integer

Set rs = New ADODB.Recordset

For q = 1 To 19
With rs
OBJECT3 = "SELECT qrySoftwareTracking.* FROM qrySoftwareTracking WHERE 1=1 AND (" & OBJECT2
Debug.Print OBJECT3

' Forms!frmSoftwareTracking(B(q)).RowSource = OBJECT3 (syntax for modules)
Me(B(q)).RowSource = OBJECT3

End With

Next q

End Sub

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Runtime Error 424 - Object Required...

sanders720

Programmer

VicM

Programmer

sanders720

Programmer

VicM

Programmer

sanders720

Programmer

Similar threads

Part and Inventory Search

Sponsor