Setting Fieldname in query with a combobox

[Mafukufuku]

I want to set the fieldname for columns in a query by having the user select
from a combobox.

- What is the right expression to be put in the fieldname in Designmode?
- Do I have to list the tables where the possible fieldnames are referring to?
- How do I specify the table name?

Right now I have this expression:

Expr3: Forms!frm1inserttable.Combo29.Column(2) But it is a undefined
function according to the error I get.

Where column 2 refers to a tablecolumn with text like:

Idbook

or

BCert.

Because [Dtblissuelegal]![Idbook] and [Dtblissuelegal]![BCert] are columns
in a Dtbl that need to be selected for this query. Column (3) has the text
for the table:

Dtblissuelegal

do I set expr3: ["Forms!frm1inserttable.Combo29.Column(2)"] or

Expr3: ["& Forms!frm1inserttable.Combo29.Column(2) &"] I think I tried all
possible combinations

Do I set the Table-name in the query to:
Expr3: Forms!frm1inserttable.Combo29.Column(3) ?

 

[John Spencer]

You cannot directly reference the value of a combobox and use that as a field
name in a query. You would need to use VBA to build the query string and then
assign the query string as the SQL of a query or the record source for a form
or a report or to build a recordset.

So the first question is to you have at least a rudimentary understanding of
VBA?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Mafukufuku]

Rudimentary would be the right word, yes. I am reading and learning.

If you could help, please.

The combobox (combo29) has 3 columns: 1 with the text for the user to read,
one with the column names of the tables used, without ""'s, ()'s or []'s.
It is about health and legal problems of people living with HIV/AIDS in south
Africa:

Dtblissuehealth

Columns:
Coughing
Shingles
Weightloss
Diarrhoea

Dtblissuelegal

Columns:
Idbook
Birthcert
CSG
FCG
OAG

What would a sample string look like? Can this also be used to refer to the
columns in a another query, in much the same way?

Thank you

 

[John Spencer]

Assuming that your combobox has three columns:
Column 1 - friendly text for human
Column 2 - Table name
Column 3 - Field name

Function fBuildQueryString() as String
Dim strSQL as String

strSQL = "SELECT [" & Forms![NameOfForm][Combo29].Col(2) & _
"] FROM [" & Forms![NameOfForm][Combo29].Col(1) & "]"

fBuildQueryString = strSQL
End function
Now you could assign that string to a querydef and open the query.

If you need to add more fields and a where clause it gets more complex.
If you need to add additional tables it gets more complex.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Rudimentary would be the right word, yes. I am reading and learning.

If you could help, please.

The combobox (combo29) has 3 columns: 1 with the text for the user to read,
one with the column names of the tables used, without ""'s, ()'s or []'s.
It is about health and legal problems of people living with HIV/AIDS in south
Africa:

Dtblissuehealth

Columns:
Coughing
Shingles
Weightloss
Diarrhoea

Dtblissuelegal

Columns:
Idbook
Birthcert
CSG
FCG
OAG

This may have come up before, but at the risk of repeating advice...

Your table design is wrong. Storing data in fieldnames is the root of your
difficulties! If you had a tall-thin design modeling a many (patients) to many
(health issues) relationship, using three tables, it would make your queries
much simpler:

Patients
PatientID
LastName
FirstName
<other biographical data>

HealthIssues
IssueID <autonumber primary key>
Issue <e.g. "cough", "weight loss", ...>

PatientIssues
PatientID <link to Patients>
IssueID <link to HealthIssues>
<you could then have additional fields for this issue as applied to this
patient, such as date of onset, severity, comments>

 

[Mafukufuku]

Thank you for all the advice.

I will try to get this working.

I know this design is a big problem. It is a long lasting struggle between
me and my dad aswell. But I designed the thing in SA in 2007.It was my first
Access experience, we left it there to work, it still does and we now need a
quick-fix for some problems to buy time to design a proper, normalised Dbase.

Rudimentary would be the right word, yes. I am reading and learning.

If you could help, please.

The combobox (combo29) has 3 columns: 1 with the text for the user to read,
one with the column names of the tables used, without ""'s, ()'s or []'s.
It is about health and legal problems of people living with HIV/AIDS in south
Africa:

Dtblissuehealth

Columns:
Coughing
Shingles
Weightloss
Diarrhoea

Dtblissuelegal

Columns:
Idbook
Birthcert
CSG
FCG
OAG

This may have come up before, but at the risk of repeating advice...

Your table design is wrong. Storing data in fieldnames is the root of your
difficulties! If you had a tall-thin design modeling a many (patients) to many
(health issues) relationship, using three tables, it would make your queries
much simpler:

Patients
PatientID
LastName
FirstName
<other biographical data>

HealthIssues
IssueID <autonumber primary key>
Issue <e.g. "cough", "weight loss", ...>

PatientIssues
PatientID <link to Patients>
IssueID <link to HealthIssues>
<you could then have additional fields for this issue as applied to this
patient, such as date of onset, severity, comments>

 

[John W. Vinson]

Rudimentary would be the right word, yes. I am reading and learning.

If you could help, please.

The combobox (combo29) has 3 columns: 1 with the text for the user to read,
one with the column names of the tables used, without ""'s, ()'s or []'s.
It is about health and legal problems of people living with HIV/AIDS in south
Africa:

Dtblissuehealth

Columns:
Coughing
Shingles
Weightloss
Diarrhoea

What would a sample string look like? Can this also be used to refer to the
columns in a another query, in much the same way?

Just using this as an example, the code in the AfterUpdate event of the combo
box might be

Private Sub combo29_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT <whatever you want to see> FROM [DtblIssueHealth] WHERE [" _
& Me!combo29.Column(1) & "] = True;"

You can then assign strSQL as the Recordsource of a form or report, or take
whatever action you want.

This will generate a query like

SELECT * FROM [DtblIssueHealth] WHERE [Coughing] = True;

if you want to see all the fields. Adapt the SQL to whatever you want - you
can build the query in the query design window with a literal "coughing" as
the issue, and go to SQL view to get a model for the query you want built.