Error keeps occuring... PLEASE HELP!! (1 Viewer)

[nick.tait]

Hi all,
Can anyone help me with my following code?? the run-time error '2465' is produced on the 'strcombo = Record_Set1([Asset #])' line. Any help is much appreciated.




Private Sub Custodian_History_Form_Enter()

Me.[Custodian History form].Nodes.Add Text:="Custodian History", Key:="Custodian History"

Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Asset #"

Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Custodian ID"

Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="On Date"

Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Off Date"

Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Changes in Asset"

Dim Data_Base1 As DAO.Database
Dim Record_Set1 As DAO.Recordset
Dim Query_Def1 As QueryDef
Dim strcombo As String
Set Data_Base1 = CurrentDb()
Set Query_Def1 = Data_Base1.CreateQueryDef("")
Query_Def1.SQL = "SELECT [Asset #] FROM [Asset Master];"
Set Record_Set1 = Query_Def1.OpenRecordset()

With Record_Set1
Do While Not .EOF
strcombo = Record_Set1([Asset #])
[Asset #].AddItem strcombo
Me.[Custodian History form].Nodes2.Add relationship:=tvwsibling, Relative:="Changes in asset", Text:="strcombo"
.MoveNext

Loop
.Close

End With

Set rs1 = Nothing
db.Close

End Sub

 

[Guus2005]

On which line does it occure and what is the error description?

 

[nick.tait]

Thanks for the quick reply Guus2005

The error occures on 'strcombo = Record_Set1([Asset #])' line and the error states that 'microsoft office access can't find the field '|' referred to in your expression'

Nick

 

[pbaldy]

Guus appears to be offline. Try either

strcombo = Record_Set1("[Asset #]")

or

strcombo = Record_Set1![Asset #]

BTW, the space and the symbol in the field name are both bad ideas.

 

[nick.tait]

Thank you very much pbaldy, this lets that line of code run. However it produces the same error on the line below ([Asset #].AddItem strcombo). When i try to change the '[asset #]' to ("[Asset #]") it wont let the line run because of a 'compile error'. I then took your advice and changed the name of the from Asset # to AssetNumber which highlights 'Set Record_Set1 = Query_Def1.OpenRecordset()' and claims that there are too few parameters.
I'm Very confused.
do you have any more advice?

once again, thank you for your help

Nick

 

[pbaldy]

Did you change the field name here?

Query_Def1.SQL = "SELECT [Asset #] FROM [Asset Master];"

 

[nick.tait]

yeh i did but it still wont work. ok here is the current code im trying to run at this moment. keep in mind i have changed the name asset # to assetnumber. The error '424' object requires keeps popping up for the line in red.
Private Sub Custodian_History_Form_Enter()

Me.[Custodian History form].Nodes.Add Text:="Custodian History", Key:="Custodian History"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="AssetNumber"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Custodian ID"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="On Date"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Off Date"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Changes in Asset"

Dim Data_Base1 As DAO.Database
Dim Record_Set1 As DAO.Recordset
Dim Query_Def1 As QueryDef
Dim strcombo As String
Set Data_Base1 = CurrentDb()
Set Query_Def1 = Data_Base1.CreateQueryDef("")
Query_Def1.SQL = "SELECT [AssetNumber] FROM [Asset Master];"
Set Record_Set1 = Query_Def1.OpenRecordset()

With Record_Set1

Do While Not .EOF

strcombo = Record_Set1("AssetNumber")
AssetNumber.AddItem strcombo
Me.[Custodian History form].Nodes2.Add relationship:=tvwsibling, Relative:="Changes in asset", Text:="strcombo"
.MoveNext
Loop
.Close

End With

Set rs1 = Nothing

db.Close

End Sub

 

[pbaldy]

Try

Me.NameOfCombo.AddItem strcombo

 

[nick.tait]

ok so it will run until the line in red. Then the line in red produces an error stating that 'element not found'. Thank you for your help so far.. Can you help me with this problem?
Private Sub Custodian_History_Form_Enter()

Me.[Custodian History form].Nodes.Add Text:="Custodian History", Key:="Custodian History"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="AssetNumber"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Custodian ID"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="On Date"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Off Date"
Me.[Custodian History form].Nodes.Add relationship:=tvwChild, Relative:="Custodian History", Text:="Changes in Asset"

Dim Data_Base1 As DAO.Database
Dim Record_Set1 As DAO.Recordset
Dim Query_Def1 As QueryDef
Dim strcombo As String
Set Data_Base1 = CurrentDb()
Set Query_Def1 = Data_Base1.CreateQueryDef("")
Query_Def1.SQL = "SELECT [AssetNumber] FROM [Asset Master];"
Set Record_Set1 = Query_Def1.OpenRecordset()

With Record_Set1
Do While Not .EOF
strcombo = Record_Set1("AssetNumber")
Me.[Custodian History form].Nodes.Add relationship:=tvwsibling, Relative:="[Changes in asset]", Text:=strcombo

.MoveNext

Loop

.Close

End With

Set rs1 = Nothing
db.Close

End Sub

 

[Guus2005]

Did you compile your code recently?
tvwsibling is not a preset constant. tvwChild is. You have already used it.

If Access recognizes the built-in tvwsibling constant, it would alter the case of the name to tvwSibling the moment you enter the next line. (This is only the case when you didn't create one with all lowercase yourself)

HTH

 

[nick.tait]

This is the most recent code. ive totally reworded it and gone with a different approach. The line in red is where it crashes. i really cant figure it out. ive been able to fix everything else but im totally stuck on this. Thanks in advance

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strQuery1 As String
Dim strQuery2 As String

Dim nod As Object
Dim strNode1Text As String
Dim strNode2Text As String
Dim strVisibleText As String
Dim strMessage As String
Dim intVBMsg As Integer

Set db = CurrentDb()
strQuery1 = "select DISTINCT AssetNumber from [Asset Master]"
With Me![Custodian History Form]
Set rs = db.OpenRecordset(strQuery1) ', dbOpenForwardOnly)
Do Until rs.EOF
Debug.Print rs![Assetnumber]
strNode1Text = StrConv("Level1" & rs![Assetnumber], _
vbLowerCase)
Set nod = .Nodes.Add(Key:=strNode1Text, Text:=rs![Assetnumber])
nod.Expanded = False

strQuery2 = "select DISTINCT AssetNumber, [Asset Name] from [Asset Master] where [AssetNumber] = " & nod.Text
Set rs2 = db.OpenRecordset(strQuery2)
Do Until rs2.EOF
strNode2Text = StrConv("Level2" & rs2![Asset Name], vbLowerCase)
strVisibleText = rs2!Assetnumber
'if modelname is null, will have problem
If IsNull(rs2!Assetnumber) Then
MsgBox "number is null"
strVisibleText = "No Name"
End If
Set nod = .Nodes.Add(relative:=strNode1Text, relationship:=tvwChild, _
Key:=strNode2Text, Text:=strVisibleText)
rs2.MoveNext
Loop
rs2.Close
rs.MoveNext
Loop
rs.Close
End With
End Sub

 

[Guus2005]

can you post a sample database?
it will be easier to debug.

 

[nick.tait]

I attached a photo of the database (New Compressed (zipped) Folder.zip), hope it helps.
so the table that you can see is produced in the tree diagram (see attachment 2 (Tree)) however i would like the data to go in a branch called "custodian history" first which you would click on to produce the "AssetName". My most recent code is below.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strQuery1 As String
Dim strQuery2 As String

Dim nod As Object
Dim strNode1Text As String
Dim strNode2Text As String
Dim strVisibleText As String
Dim strMessage As String
Dim intVBMsg As Integer
Dim intCounter As Integer
Dim stringcount As String


intCounter = 1
Set db = CurrentDb()
strQuery1 = "select DISTINCT AssetNumber from [Asset Master]"
With Me![Custodian History Form]
Set rs = db.OpenRecordset(strQuery1) ', dbOpenForwardOnly)
Do Until rs.EOF
Debug.Print rs![Assetnumber]
strNode1Text = StrConv("Level1" & rs![Assetnumber], _
vbLowerCase)
Set nod = .Nodes.Add(Key:=strNode1Text, Text:=rs![Assetnumber])
nod.Expanded = False

strQuery2 = "select DISTINCT AssetNumber, [Asset Name] from [Asset Master] where [AssetNumber] = " & nod.Text
Set rs2 = db.OpenRecordset(strQuery2)
Do Until rs2.EOF
strNode2Text = StrConv("Level2" & rs2![Asset Name], vbLowerCase)
strVisibleText = rs2!Assetnumber
'if modelname is null, will have problem
If IsNull(rs2!Assetnumber) Then
MsgBox "number is null"
strVisibleText = "No Name"
End If
stringcount = CStr(intCounter)
strNode2Text = strNode2Text + stringcount
Set nod = .Nodes.Add(relative:=strNode1Text, relationship:=tvwChild, _
Key:=strNode2Text, Text:=strVisibleText)
rs2.MoveNext
Loop
rs2.Close
rs.MoveNext
Loop
rs.Close
End With
End Sub

I no i have asked alot of confusing questions but i assure you that your help is extremely appreciate. Thank you for everything