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!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

lbx exists in Subform generates "Compile error: Variable not defined" 1

Status
Not open for further replies.

Emmali

Programmer
Jul 30, 2003
29
US
Immediate problem:
I have a listbox that truly exists in a subform generates "Compile error: Variable not defined". I have verified the reference accurately refers to the the object as named.

Overview:
I am trying to create a "Client Form" (list of unique records) with a "Visit Record Subform" (list of many unique visits by individual clients). Both forms have list boxes which are not working correctly, but only the subform generates and error when attempting compilation.




Center for World Indigenous Studies

"Always carry a tuna sandwich in case of tigers."
 
Bill,
Thanks! It was driving me nuts 'cause the syntax was exactly like the others -but I left out "Me!"

I also need the listboxes to pick from a value list or one-field table and store in a data table.


Here's one (so far):
_________________________________________________


Option Explicit
Option Compare Database
Dim ttFieldValues As String

Private Sub Form_Load()
'Heading Column Title Given
ttFieldValues = "TitleID; Title"
Call LoadTitle(0)
lbxTitle.RowSourceType = "Table/Query"
lbxTitle.RowSource = ttFieldValues
lbxTitle.ColumnCount = 1
lbxTitle.ColumnHeads = False
End Sub

Private Sub LoadTitle(ttID)
Dim db As Database, rs As Recordset, strSql As String
Dim ttNewID As String

'Check for the bottom of the tree
If IsNull(ttID) Then
Exit Sub
End If

Set db = CurrentDb()
strSql = "select * from title where titleid=" & ttID
Set rs = db.OpenRecordset(strSql)

Do While Not rs.EOF
ttFieldValues = ttFieldValues & ttID & ";" & rs("titleid") & ";" & rs("title") & ";"
'Recursive call to check for children
Call LoadTitle(rs("titleid"))
rs.MoveNext
Loop

If Not rs Is Nothing Then
rs.Close
End If
Set rs = Nothing

End Sub

Emmali
 
Hi Emmali,

This should point you in the right direction:

Option Compare Database
Option Explicit

Dim ttFieldValues As String

Private Sub Form_Load()
'Heading Column Title Given
ttFieldValues = "TitleID; Title"
Call LoadTitle(0)
Me!lbxTitle.RowSourceType = "Value List"
Me!lbxTitle.RowSource = ttFieldValues
Me!lbxTitle.ColumnCount = 1
Me!lbxTitle.ColumnHeads = False
End Sub

Private Sub LoadTitle(ttID)
Dim db As Database, rs As Recordset, strSql As String
Dim ttNewID As String

'Check for the bottom of the tree
If IsNull(Me!ttID) Then
Exit Sub
End If

Set db = CurrentDb()
strSql = "select * from title where titleid=" & Me!ttID
Set rs = db.OpenRecordset(strSql)

Do While Not rs.EOF
Me!ttFieldValues = Me!ttFieldValues & ttID & ";" & rs("titleid") & ";" & rs("title") & ";"
'Recursive call to check for children
Call LoadTitle(rs("titleid"))
rs.MoveNext
Loop

If Not rs Is Nothing Then
rs.Close
End If
Set rs = Nothing

End Sub

Bill
 
Bill,

Ack that was old code, but those additional Me!s [bold]were[/bold] missing, thanks.

My brain now resembles a bowl of overcooked, luke-warm tapioca.
I now get:

Run-time error '2465': Microsoft Access can't find the field 'ttID' referred to in your expression.

I suspect I did something silly with the concept of the Value List and how it populates, field names, etc.

None of my books or other sources go into that detail. Any ideas on that?

Thanks. *mushily*
 
Sorry Emmali,

I'm a little lost, ttID I assumed is a Text Box or similar on your form.

Have you got a control called ttID on the form? If so you shouldn't be receiving this message.

Bill


 
Bill,
I knew I was losing it.
I've changed ttID to TitleID, but no better luck.
lbxTitle is a list box on my form; I would like it to pick from a list of titles either from a table or an ethereal Value List, and store the chosen title in the related record in a Contacts table.
(Make more sense now?)
Emmali
 
Hi Emmali,

I've sort of lost it too. You are welcome to send me a copy of your DB, removing any sensitive records first. (Please send in zip file format)

I think it will save a lot more of this toing and froing.

Will post any suggestions here ASAP.

Bill

billpower@cwcom.net
 
Bill,
Any luck?
I emailed the db on Tuesday... Haven't heard from you. Sorry for the lag, but I only work at _this_ job Tuesdays and Thursdays.
Emmali

Center for World Indigenous Studies

"Always carry a tuna sandwich in case of tigers."
 
Bill,
Still there?
If the db bugs weren't enough, now the email server is wonky, and I am not getting much. Did you get the db?
Thanks, Em
 
Hi Emmali,

You haven't a control named lbxTest in your form Visit Data, that is why you are getting a compile error.

There is a control named lbxTest is in the form Visit Data Subform.

Bill
 
Bill,
I'm having trouble e-mailing you, from either of my accounts, either replying to yours or typing in the address, so the problem seems to be in transmission (see thread858-639092) or that your system does not recognize that you exist. Any ideas?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top