Referring to table: Variable not defined

[Ishim684]

Dear Access experts, here am I again with my new problem.

I want to populate the RowSource for my combo box on a form filtering it by a value in the table field.

I’m running the following Sub on the GotFocus event of the combo box (CBO) in the FORM. When I debug it, I get a compile error that says that the variable TABLE is not defined (the first instance of it in the SELECT statement).

I’ve tried to define the table first (Dim TABLE As TableDef), but then I was getting another compile error, this time on Field1: Method or data member not found.

I have the Option Explicit header in my code. I cannot figure out what the problem is (((

Private Sub CBO_GotFocus()

Dim SQL As String

SQL = "SELECT TABLE.Field1, TABLE.Field2 FROM TABLE " & _
"WHERE TABLE.Field3 Like '" & [Forms]![FORM]![Field] & "'"

If TABLE.Field4 = "some text1" Then
Me.CBO.RowSource = "some text2"
Else
Me.CBO.RowSource = SQL
End If

Me.CBO.Requery
End Sub

 

[gemma-the-husky]

If TABLE.Field4 = "some text1" Then

this line is the problem.
how does vba know what this is?

within the SQL string, access converts the SQL statement to return the data to your combo box. But it doesn't know what to do with table.field4

 

[Ishim684]

Dave, thank you very much for your prompt response. Could you give me some pointers how this could be fixed? It's my very first project in VBA and in programming in general, and I definitely don't know very simple things, but I'm trying to learn. I would immensely appreciate you help.

 

[gemma-the-husky]

is the value you are testing in a control on your form?

if so then

If [mycontrolname] = "some text1" Then
     Me.CBO.RowSource = "some text2"
Else
     Me.CBO.RowSource = SQL
end if

 

[Ishim684]

No, this value is not in this form; it is in a table (which is bound to this form).

 

[The_Doc_Man]

IsHim - the problem is that "TABLE.Field4" ONLY works in a form's class module if you have a control on the form with that name and if Field4 is a property of it, or if TABLE is the name of an open recordset and Field4 is a legitimate field thereof.

TABLE.Field4 in isolation has no meaning. In answering your previous question on wanting to set the value of a field, we answered with methods involving open recordsets. The same general comments apply for READING a fields as well as for writing a field. You MUST have an active, open recordset. And in this case, you cannot use a simple select query because by context, you are seeking a single field from a single row. SQL doesn't return a value; it returns a SET of values.

Again, your three choices are:

1. If using recordset operations directly, you must open the recordset; seek the record you wish to read; and then you can use recordset-variable.[Field4] to get the value. One would presume that in this putative recordset operation, you opened table TABLE in mode dbOpenTable - though other options exist and would be equally capable of returning a value.

2. If using a form that happens to contain a control bound to Field4, you could reference [Field4] in that form's class module. Note that the name of the control is in this case ASSUMED to be the same as the underlying field, which would be the case if you used a wizard to build the form (I think).

3. In this case, using a query is actually implied by case 1, but you have another way to read fields that involves SQL. You can do a DLookup( "[Field4]", "TABLE", criteria ), which builds an SQL query to table TABLE and returns [Field4] - BUT... the criteria clause (which is like a WHERE clause without the word "WHERE") has to be specific enough to uniquely select one record in that table because otherwise, you might not get what you wanted. If you already had SQL that did some of the filtration for you, the DLookup could directly reference your SQL statement and in that case, the criteria in the DLookup could be simpler since the SQL statement "pre-filters" what the DLookup would see.

 

[missinglinq]

You also can't use 'Table,' a Reserved Word, as the name of a Table and expect Access to process it as such, as opposed to it being an Object, so you need to change that to something else, hopefully something meaningful!

You also appear to be using 'Form' as the name of a Form...ditto everything said above.

And while 'Field' is not a Reserved Word, you'd do well to change its name, for clarity's sake!

The line

Like '" & [Forms]![FORM]![Field] & "'"

makes no sense at all...the Like operator requires the use of Wildcards (*) which are missing, unless you're actually trying for the condition where

Field3 = Field

in which case you should use the Equal sign, not Like.

Linq ;0)>

 

[Ishim684]

Missinglinq, thank you for your valuable comments! In fact, I didn’t use names like “Table” or “Field” in my real code. But I thought that the real ones were longish and difficult to read for others, so I replaced them in my posts with what I thought were neutral names, just to improve the readability ))))) I am sorry they proved to be misleading.

Thank you for telling me about ‘Like’ and wildcards and string comparison through the equal sign, I didn’t know that. At least I got the impression from the manual on VBA and SQL that I was using that strings can be only compared with ‘Like’, while “=” is for numerical values. Now I know it’s not so ))))

 

[Ishim684]

Dear experts, thank you for your patience and your explanations )))
In the case described above I have now used a Recordset, as advised by The_Doc_Man in #1. It’s the first time I used Recordsets, and although I tried to build it properly, there is still some hitch occurring.

When I try to loop over the Recordset, Access times out and freezes completely. I can’t understand where I run into an infinite loop (as it seems to me). Although the Recordset is quite large, about 11K records, I doubt it can run over the memory of my computer. This is the code I’m having:

Dim db As Database
Dim rs As Recordset

Private Sub cboDef_GotFocus()
Set db = CurrentDb
Set rs = db.OpenRecordset("qryQUERY", dbOpenDynaset, dbSeeChanges)
Dim sqlSQL As String

sqlSQL = "SELECT ….. " & _
"WHERE ….. "

Me.cboDef.RowSource = ""
Do Until rs.EOF = True
If rs("SomeField") = "SomeText1" Then
Me.cboDef.RowSource = "SomeText2"
Else
Me.cboDef.RowSource = sqlSQL
rs.MoveNext
End If

Loop
rs.Close
Me.cboDef.Requery

End Sub

 

[Minty]

I think it would be better if you posted the actual code, not a dumbed down version of what you think you wrote.

On top of that, this use of a record set doesn't seem to make any sense in the example, does qryQuery have any parameter set by your form? If it does then why not just set the combo record set to a similar saved query ?

Maybe you could post up a picture of your form with what you are trying to achieve , in plain English - no database jargon. E.g. "I want this box here to display this information based on this other box here"

 

[gemma-the-husky]

@ishim


can I just say I was surprised with the question.
you said it's your first exercise in programming, but the real issue is that you maybe need to review the way tables queries, forms and reports work, before getting into programming - as the problem is not really a vba problem - it's more of a form design issue.

 

[Ishim684]

Hello, Minty, thank you for your response.

What I’m trying to achieve is this. I have a table, tblMASAll which has a field ExcludeFromDisamb; some of the cells in this field contain the text “Exclude_All_Basic”.

Then, I have a form, FORM2Copy which has a combobox cboDef.

I want this combobox to display values depending on the value of the field ExcludeFromDisamb in tblMASAll:

- If a cell has ‘Exclude_All_Basic’ in it, then the combobox cboDef should display only one piece of text: “Discard:All Basic”
- If a cell is either empty or contains any other value than ‘Exclude_All_Basic’ – then I want the SQL statement (DefSource = "SELECT… etc.”) to execute and pass whatever the result into the combobox to display.

Below is the code I’ve written.
The query qryForRecordsetFORM3Copy contain all the cells in the field ExcludeFromDisamb of tblMASAll. Then in the “If..” condition I check whether a current cell contains the required text or not.

Private Sub cboDef_GotFocus()
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryForRecordsetFORM3Copy", dbOpenDynaset, dbSeeChanges)
    Dim DefSource As String
    
        DefSource = "SELECT tblMASall.DictID, tblMASall.Def FROM tblMASall " & _
        "WHERE tblMASall.Lemma = '" & [Forms]![FORM2Copy]![LemmaUpC] & "'"
        
    Me.cboDef.RowSource = ""
    Do Until rs.EOF = True
        If rs("ExcludeFromDisamb") = "Exclude:All Basic" Then
            Me.cboDef.RowSource = "Discard:All Basic"
        Else
            Me.cboDef.RowSource = DefSource
        rs.MoveNext
        End If
    
    Loop
    rs.Close
    Me.cboDef.Requery
    
End Sub

I have also tried the other way: I set up the query qryForRecordsetFORM3Copy to contain only those cell that have the text ‘Exclude_All_Basic’.
Then I changed the “If..” condition to check whether the current cell is identical to the query (at least I think I did) – as below. But then I was getting a ‘type mismatch’ error. I couldn’t find another way to do this check so far.

Private Sub cboDef_GotFocus()
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryForRecordsetFORM3Copy", dbOpenDynaset, dbSeeChanges)
    Dim DefSource As String
    
        DefSource = "SELECT tblMASall.DictID, tblMASall.Def FROM tblMASall " & _
        "WHERE tblMASall.Lemma = '" & [Forms]![FORM2Copy]![LemmaUpC] & "'"
        
    Me.cboDef.RowSource = ""
    Do Until rs.EOF = True
        If rs Then
            Me.cboDef.RowSource = "Discard:All Basic"
        Else
            Me.cboDef.RowSource = DefSource
        rs.MoveNext
        End If
    
    Loop
    rs.Close
    Me.cboDef.Requery
    
End Sub

 

[Minty]

Unfortunately your pictures haven't shown up, you will need to upload them into your post.
I think I know what your issue is but want to see the form to make sure I have the right "End of the Stick"

 

[Ishim684]

Thank you, Minty!

I'm afraid to appear an ultimate dummy, but I can't figure out how the 'Insert image' button of the message window works :banghead:

Last time I pasted a Google Drive link to the screenshots but they didn't display properly.

I've also tried pasting a path to files on my computer but the Preview showed only the links, not the pictures, so I don't think it works.

Minty, I'm sorry, but what is the conventional way to insert pictures into messages?

 

[Ishim684]

Umph! Found it!!! Minty, here are the screenshots of the table and the form:

[/url][/IMG]

image share

 

[Minty]

Okay I think you are massively over complicating this.
Just so we are all clear; What actually determines your combo box recordsource value to change?
As far as I can see you aren't using any value from your form to check against, so the result of your efforts will always be the same. It appears to change only based on the records already in the table.
This statement :

I want this combobox to display values depending on the value of the field ExcludeFromDisamb in tblMASAll:

Seems to confirm this.
I can't see this field on your Form, so as Dave has said I think you are misunderstanding the relationship between Forms and Data, the fact you keep referring to cells, makes me think there is possibly an Excel hangover happening.