Pass ComboBox selection as FIELD to query

[markchales]

Hi all,
I'm impressed, I've only just found this forum. Unbelievable.

Situation.
Combobox uses data that references fields in a table with 31 fields and 19,333 records.(and growing)
All data of interest in this table is 0/-1 (Yes/No).
All fields in the table are simply called 1, 2, 3 etc.
Using Access 97
Need to count how many -1 for given field between 2 dates.

Question.
Can I use the selected row in the combobox (eg; 16) to have a query use that value as the field to work with?
Hoping you can help.
Many thanks in advance
Mark

 

[ecniv]

If you create the Sql statement in VBA/VB first then yes.

I'd say build an sql statement using one field to output what you want, then change the view on the builder to sql. Copy this into your code and place it in a string variable (strSql). Then whereever there is the field you specified, you need to replace it with either something unique which you can then use a replace function on or with " & combobox.column(0) & ".

example:
[vbcode]
lngSel = cboSel.Column(0)
strsql="Select

.[" & lngsel & "], count(

.[" & lngSel & "] from

"
[/vbcode]
You can then put this sql statement into a control or open a recordset or whatever you need to do.


Vince

 

[Mile-O]

a table with 31 fields and 19,333 records.(and growing)

Is that growing downward and outward?

All data of interest in this table is 0/-1 (Yes/No).
All fields in the table are simply called 1, 2, 3 etc.

So, it's most likely that it's growing in both directions - bad move!

Your table structure is poor. How often do you add new fields to the table?

 

[markchales]

Is that growing downward and outward?




So, it's most likely that it's growing in both directions - bad move!

Your table structure is poor. How often do you add new fields to the table?

No, fields are not added to this table and it's been in use for about 5 years.
Records are only added as rows.

 

[markchales]

If you create the Sql statement in VBA/VB first then yes.

I'd say build an sql statement using one field to output what you want, then change the view on the builder to sql. Copy this into your code and place it in a string variable (strSql). Then whereever there is the field you specified, you need to replace it with either something unique which you can then use a replace function on or with " & combobox.column(0) & ".

example:
[vbcode]
lngSel = cboSel.Column(0)
strsql="Select

.[" & lngsel & "], count(

.[" & lngSel & "] from

"
[/vbcode]
You can then put this sql statement into a control or open a recordset or whatever you need to do.


Vince

Thanks Vince, I'll try to use your suggestion. I'm not really into code but I know enough to get by. I'll see how I go.
Mark

 

[markchales]

Are you there Vince?

Hi Vince,
I got this far but the code doesn't seem to run the query. The result that shows on the message box is the entire line after strSQL =
I'm lost !!!!
Thanks
Mark



Private Sub Command66_Click()
On Error GoTo Err_Command66_Click
Dim lngSel As String
Dim strSQL As String

lngSel = Issues.Column(0)
strSQL = "SELECT Count(ServicesProvided.[" & lngSel & "]) AS Total FROM [Contact Details] INNER JOIN ServicesProvided ON ([Contact Details].[Record ID] = ServicesProvided.RecordID) AND ([Contact Details].[Record ID] = ServicesProvided.RecordID)WHERE (((ServicesProvided.[" & lngSel & "])=-1) AND (([Contact Details].DateEntered) Between [Date1] And [Date2]))"

MsgBox "Total = " & strSQL & " for given date range"


Exit_Command66_Click:
Exit Sub

Err_Command66_Click:
MsgBox Err.Description
Resume Exit_Command66_Click

End Sub

If anyone can help with this I'd really appreciate it. I do think that Vince is on the right track. I just don't know enough about code to make it work.

 

[markchales]

Please help

Can anybody help with this. I'm still messing about with the code but can't make it work.
Thanks
Mark

 

[markchales]

I finally did it

It actually works. Combo box is filled with table that refers to each field in table. Displays description of 'Issue' and is bound to field name. Code runs from Command Button.
This method saved me writing 33 seperate queries.
A big thanks to Vince who pointed in the right direction.

Private Sub Command66_Click()
On Error GoTo Err_Command66_Click
Dim lngSel As String
Dim strSQL As String
Dim stDocName As String

stDocName = "frmResult"
lngSel = Me!Issues.Column(0)
DoCmd.SetWarnings False
strSQL = "SELECT Count(ServicesProvided.[" & lngSel & "]) AS Total INTO Result FROM [ServicesProvided] INNER JOIN [Contact Details] ON [Contact Details].[Record ID] = ServicesProvided.RecordID WHERE Nz((((ServicesProvided.[" & lngSel & "])=-1)) AND (([Contact Details].DateEntered) Between Date1 And Date2));"
DoCmd.RunSQL strSQL
DoCmd.OpenForm stDocName, acNormal, acReadOnly

Exit_Command66_Click:
Exit Sub

Err_Command66_Click:
MsgBox Err.Description
Resume Exit_Command66_Click

End Sub

Many Thanks
Mark