Help With Code Syntax

[Evagrius]

Hello,

I have a comboBox in a form. The combobox is populates in the code below;

Private Sub cboFields_Enter()
Dim oRS As DAO.Recordset, i As Integer
Set oRS = Me.RecordsetClone
cboFields.RowSource = ""
For i = 1 To oRS.Fields.Count - 1
    cboFields.AddItem oRS.Fields(i).Name
Next i
End Sub

What I am trying to do now is have a user select an item from the combobox, and at the afterupdate event, filter the form by the field in the combobox, and the value that is in that field.

So for example, if the user selects "Salary" from the combobox, then the form is filtered by the Field "Salary" for whatever value is currently in the field "Salary." I have tried to do this but I keep getting the syntax wrong - I am not sure how to reference a field name through the combobox.

I know this is horribly wrong but here's what I've been playing with;

Private Sub cboFields_AfterUpdate()
DoCmd.ApplyFilter Me.Name, "[" & cboFields.Value & "]" & "Like & cboFields"
End Sub

 

[vbaInet]

First of all what's your reason for not setting the Row Source of the combo box using a query?

Here's how to filter your form:

http://baldyweb.com/BuildSQL.htm

There's a sample db at the bottom of page 1.

 

[Evagrius]

Hi again vbaInet!

I populated the form using VBA just so I can learn how it is done.

I am sorry, but I couldn't figure out where the link below addressed my particular issue? I am still learning so some of the statements didn't make sense to me. Can you post the particular example you wanted me to reference? Thank you!

 

[vbaInet]

Well, now you've learnt how it's done are you going to revert back to the normal way?

Scroll to the very bottom of the page, click Search form Sample db and that's the file.

 

[Evagrius]

Well - I actually prefer doing it the normal way; so yes!

I have the database open, and I am looking at his code, I am just not sure which part I need to learn from so I can use it?

 

[vbaInet]

Open the main form, see how the search works then go into the code.

By the way, are you using a Continuous form or Datasheet?

 

[Evagrius]

I am sorry vbaInet - but I am struggling to find where in the code it shows how to reference an actual field name through a value in a combobox?

 

[Evagrius]

Can anyone else advice please?

 

[boblarson]

It's not as easy as it sounds unless ALL of your fields are the exact same datatype. If you have dates, numbers, and text then you will have to have code to FIRST determine the datatype of the field and then you can create the filter (I wouldn't use DoCmd.ApplyFilter but instead the Me.Filter and Me.FilterOn = True).

I don't think I have time to help with the code at the moment but you will need to determine your datatype for the field chosen and then run it through a select case statement to figure out the structure for the filter based on the datatype.

 

[Evagrius]

Hi Bob - I understand - it seems I underestimated the task. Well, if you have some time to kill later on - I would appreciate any sample - perhaps I can follow your example once you start it. Thank you for jumping in Bob!

 

[vbaInet]

Without code, follow missinglinq's explanation in this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=196263

If you're using Access 2007 or 2010 then that option is not available. If you're using datasheet view, then this wouldn't work for you.

Maybe a simple code would be this:

Dim rst as DAO.Recordset

set rst = Me.RecordsetClone
rst.FindFirst "[[COLOR=Red][B]FieldID[/B][/COLOR]] = " & Val(Nz(Me.[COLOR=Blue][B]Combo1[/B][/COLOR], 0))
if Not rst.NoMatch then
    me.bookmark = rst.bookmark
end if

Change the names highlighted. This is assuming that the combo box is in the header of the form and the form is Continuous.

 

[boblarson]

Okay, I found where I had this info and so what you will want to do is to create a new STANDARD MODULE (not form or report module) and name the module basTableInfo and paste this in:

Function ReturnCon(num As Integer) As String
    Select Case num
    Case 1
        ReturnCon = "YesNo"
    Case 2
        ReturnCon = "Byte"
    Case 3
        ReturnCon = "Integer"
    Case 4
        ReturnCon = "Long Integer"
    Case 5
        ReturnCon = "Currency"
    Case 6
        ReturnCon = "Single"
    Case 7
        ReturnCon = "Double"
    Case 8
        ReturnCon = "DateTime"
    Case 9
        ReturnCon = "Unknown"
    Case 10
           ReturnCon = "Text"
    Case 11
            ReturnCon = "OLE Object"
    Case 12
            ReturnCon = "Memo"
    Case 20
            ReturnCon = "Decimal"
    Case Else
        ReturnCon = "Unknown"
End Select
End Function


Function GetFieldType(strTDF As String, strField As String) As String
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

        Set tdf = db.TableDefs(strTDF)
    
        Set fld = tdf.Fields(strField)

        GetFieldType = ReturnCon(fld.Type)


Set fld = Nothing
Set tdf = Nothing

End Function

But I was just filling out the rest of this and realized I'm not understanding what you mean by:

if the user selects "Salary" from the combobox, then the form is filtered by the Field "Salary" for whatever value is currently in the field "Salary."

That doesn't make sense. Are you saying that you want the field that is chosen be filtered by the current value of what the currently selected record is?

 

[Evagrius]

"if the user selects "Salary" from the combobox, then the form is filtered by the Field "Salary" for whatever value is currently in the field "Salary."

Yes Bob, your above statement is correct - I hope I have explained it correctly. The combobox holds all the fields. Once a user selects a field from the combobox, the form will be filtered by the field in the combobox, for whatever that field holds in the "current record".

So if they select "Salary" from the combobox, and the current record has $1 in the field salary, then the form will be filtered where [Salary]=$1.

Does that make sense?

 

[boblarson]

Okay, that helps

So you would revise your current code (including adding the previous code I gave you for that standard module) to be:

Private Sub cboFields_AfterUpdate()
Dim strFilter As String

Select Case GetFieldType("YourTableNameHere", Me.cboFields)
Case "Text", "Memo"
   strFilter = "[" & Me.cboFields & "]=" & Chr(34) & Me(Me.cboFields).Value & Chr(34)
Case "DateTime"
   strFilter = "[" & Me.cboFields & "]=#" & Me(Me.cboFields).Value & "#"
Case "Byte", "Integer', "Long Integer", "Currency", "Single", "Double", "Decimal", "YesNo"
   strFilter = "[" & Me.cboFields & "]=" & Me(Me.cboFields).Value 
End Select

Me.Filter = strFilter
Me.FilterOn = True

End Sub

 

[boblarson]

Of course the user could just right click on the field and select FILTER TO SELECTION (which is a ton easier)

 

[Evagrius]

Hi Bob,

I get an "object Required" here,

      Set tdf = db.TableDefs(strTDF)

 

[Evagrius]

Hi Bob - I am hoping you can still help me finish this. What do you think is causing the error mentioned in the previous post? What you posted is a great code and I would like to learn from it. Thank you!

 

[SOS]

Hi Bob,

I get an "object Required" here,

      Set tdf = db.TableDefs(strTDF)

You would need to change the code (see the red part):

Function GetFieldType(strTDF As String, strField As String) As String
[COLOR=red][B]    Dim db As DAO.Database[/B][/COLOR]
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

        Set tdf = db.TableDefs(strTDF)
    
        Set fld = tdf.Fields(strField)

        GetFieldType = ReturnCon(fld.Type)


Set fld = Nothing
Set tdf = Nothing

End Function

 

[Evagrius]

Hi SOS - thank you for jumping in. I still get an "object variable or with block variable not set." I can't figure out why?

   Set tdf = db.TableDefs(strTDF)

 

[SOS]

Hi SOS - thank you for jumping in. I still get an "object variable or with block variable not set." I can't figure out why?

   Set tdf = db.TableDefs(strTDF)

did you add the

Dim db As DAO.Database?

Also, are you passing the table name to the function?