Field names and Combo Box

[Caspius]

This may be the completley wrong way of doing things but I've manually created a lookup table with the field names as the list, the table headings won't change; eg

Main Table (tblMain)
Autonumber ID ¦ cat_guidance_1 ¦ cat_score_1
01 ¦ yes ¦ Yes
02 ¦ no ¦ Yes

Lookup table (tblTitles)
Autonumber (ID) ¦ Field Name(Heading) ¦ Easy to read field name(Title)
01 ¦ cat_guidance_1 ¦ Guidance
02 ¦ cat_score_1 ¦ Scores
etc

In the form I've created a combo box (cbotop1) (0cm;0cm;2.544cm) which uses
SELECT tblTitles.ID, tblTitles.Heading, tblTitles.Title FROM tblTitles;

Next to each combo box I've got a text box that counts the amount of fields that equal a certain criteria.

Say I selected Guidance in the Combo how do I get the expression to pull the field name from the combo box and count:

=DCount("[ID]","[tblMain]"," '" & Forms!frmOrsearch!cbotop1 & "' = -1")

doesn't work.

How do I make it work like

=DCount("[ID]","[tblMain]"," [cat_guidance_1] = -1")

Thanks

 

[vbaInet]

This isn't possible applying it in the Control Source like that, in code it is. So create the Control Source in code and apply it to the textbox.

 

[Caspius]

I did manage that.

Private Sub Combo1_AfterUpdate()
On Error GoTo Err_Combo1_AfterUpdate

    Dim Totalcmb1 As Long
    Dim cmb1 As String
    
    cmb1 = Combo1.Value
    Totalcmb1 = DCount("ID", "tblMain", cmb1)
    Combotxt1.Value = Totalcmb1
    
Exit_Combo1_AfterUpdate:
    Exit Sub

Err_Combo1_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Combo1_AfterUpdate
    
End Sub

If I wanted to create a query would I need to do the same in in VBA?

eg to filter whether someone has put a yes or no.

IIf([forms]![frmOrsearch]![combo1],[tblmain]![ContactEmail]=True,"")

 

[vbaInet]

Create an alias field in the query, then use the alias field in your form.

AliasField: IIf([forms]![frmOrsearch]![combo1],True,"")

Set the ContactEmail textbox's Control Source to AliasField.