Query refrencing form for data

[mousemat]

I have a form with several textboxes on it. If i double click on a textbox it sets the value of another textbox to what I have asked it to in code. This works fine.

I need to use the value from the text box in a query to extract data. The value that is put into the textbox is a particular field in the table. There are 26 similar fields.

I could write 26 queries, refrencing the field I want, but thats not really efficient.

Can I somehow refrence the textbox on the form from the query to extract the data.

 

[grnzbra]

Do you have 26 fields on the form as well as the query or is there only one field on the form into which you type the name of the query field.

If this is the case (1 field on the form), it seems that the easiest way to do it would be to use the value in the text box to modify the query def of the query.

If, on the other hand, you have 26 text boxes, if their names are similar to the query field names, you could loop through them and construct a WHERE clause for the query definition.

 

[mousemat]

Grnzbr

There are 26 fields on the form, that is not a problem. I have used the doubleclick event to input the name of the field into an unbound textbox located on the form.

How would I change the query def of the query. What code would I need to put into the query?

 

[grnzbra]

You could delete the query and recreate it using CreateQueryDef Method in which you modify the SQL statement for the query. You might also be able to modify the query's SQL statement directly, but I'm not sure about that.

 

[grnzbra]

Quick question.
You are entering the field name in the unbound textbox. In which of the 26 other text boxes are you entering the criteria?

 

[mousemat]

Im not, basically 26 text boxes display the number of people that have to complete that particular module. see frmNoOfModules.

Curently that data comes from tblCompleteModules, the query shows the number of people that need to complete a particular module.

My client is still insiting on using the table tblMembers, which is clearly not normalised, despite my efforts to persuadf him otherwise.

That being the case, i want to double click a textbox (module1 for instance) and extract the data from tblMembers instead of tblCompleteModules.

I can write 26 queries that display the records where completex is null, but that is not very efficient. I want to be able to have query which will allow the field to be changed 'on the fly' via the unbound textbox on the form.

Ive added the database.

 

[grnzbra]

If you loop through the text boxes on the form and create a SQL statement and use in something like this:

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Employee List")
qryTest.SQL = "SELECT * FROM Employees;"

you might be able to make it work.

 

[mousemat]

Mmmm seems a little too complicated really.

The sql of the query I am using is this

SELECT *
FROM tblMembers
WHERE (((tblMembers.Complete1) Is Null));

Is there not a way that I can substitute the WHERE clause to change the field name from Complete1 to Complete2 etc using the textbox from the form?

 

[grnzbra]

declare a variable

dim SQLstr as string

then set it equal to the sql statement you have with the WHERE clause which picks up the name of the field from the value you've entered in the text box.

Then use this SQL statement with the querydef process.

 

[mousemat]

Hi grnzbra

I spent the last few hours working on the querydef process and after searching the net, finally worked out how to do it.

Thanks for pointing me in the right direction.