By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,390 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Text Box values past into query

ChaseCox
100+
P: 294
Is there a way to pass the values of a text box, will call it
"txtbox" into a query using the In statement without using the txtbox.SetFocus function? I am needing to use more than one txt box to pass values in, and I can not seem to get it to work, since if I use the SetFocus it simpy goes to the next one before the first one had run.
Feb 6 '07 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,315
I have no idea what you're trying to ask here.

Queries that use values from a text box on a form don't get the value passed into the query. Rather the values are retrieved when the query is run.

It'd be helpful if you could post the code and/or SQL of what you're trying to do.
Feb 6 '07 #2

ChaseCox
100+
P: 294
I have no idea what you're trying to ask here.

Queries that use values from a text box on a form don't get the value passed into the query. Rather the values are retrieved when the query is run.

It'd be helpful if you could post the code and/or SQL of what you're trying to do.
You are right, I was not thinking when I posted. Here is the code of the query for the field I am interested in.
Expand|Select|Wrap|Line Numbers
  1. [Generic Material].ORDERED_MODEL, InStr([Forms]![Form1]![txtvoy2].[Text],Mid([ORDERED_MODEL],4,3)) AS OrdModParam
  2.  
I am using
Expand|Select|Wrap|Line Numbers
  1. txtvoy2.SetFocus
to allow the query to be able to "get" the values. This is because the text box is on a form.

However I do the exact same thing for a different field, and only the last use of .SetFocus gets used.
Feb 6 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
The way it's written right now, assuming that your form is open, when you run the query, it will use txtvoy2 regardless of whether or not you set focus to it. Changing the focus of the control doesn't change what the query uses for its value.


If your form is closed, when you run the query, it will ask you to enter a value for txtvoy2.

So what I want to know is what you want to change and can you provide a simplified example?
Feb 6 '07 #4

ChaseCox
100+
P: 294
The way it's written right now, assuming that your form is open, when you run the query, it will use txtvoy2 regardless of whether or not you set focus to it. Changing the focus of the control doesn't change what the query uses for its value.


If your form is closed, when you run the query, it will ask you to enter a value for txtvoy2.

So what I want to know is what you want to change and can you provide a simplified example?
If I do not have the set focus, it will not find it. I do not know why. I have different pages on my form, and each text box is on a different form. I have tried putting both boxes on the same form, but that still does not fix it.
Feb 6 '07 #5

Rabbit
Expert Mod 10K+
P: 12,315
That shouldn't happen. If you referred to everything correctly in your query, then it shouldn't matter what has focus and what doesn't.

How are you running the query?
Can you post the whole SQL of your query? Right-click the title bar of the query in design view and select SQL View. Then post what's in there.
Feb 6 '07 #6

ChaseCox
100+
P: 294
Expand|Select|Wrap|Line Numbers
  1. SELECT [Generic Material].CLAIM_NBR, [Generic Material].DETAIL_NBR, [Generic Material].CLAIM_TYPE_DESCR, [Generic Material].[CLAIM DATE], [Generic Material].[SHIP DATE], [Generic Material].ORDERED_MODEL, InStr([Forms]![Form1]![txtvoy2].[Text],Mid([ORDERED_MODEL],4,3)) AS OrdModParam, [Generic Material].PROD_CODE, InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code]) AS ProdCodeParam, [Generic Material].PROD_CODE_DESCR, [Generic Material].RFC_CODE, [Generic Material].PROD_GRP, [Generic Material].RFC_FAILURE_DESCR, [Generic Material].RFC_FAILURE_MAT_IND, [Generic Material].RFC_FAILURE_LBR_IND, [Generic Material].RFC_LEVEL_ONE_DESCR, [Generic Material].RFC_LEVEL_TWO_DESCR, [Generic Material].RFC_LEVEL_THREE_DESCR, [Generic Material].RFC_LEVEL_FOUR_DESCR, [Generic Material].RFC_SHORT_DESCR, [Generic Material].BU_ID, [Generic Material].PART_PART_NBR, [Generic Material].PART_DESCR, [Generic Material].SUBMIT_OFFICE_NAME, [Generic Material].PARENT_SERIAL_NBR, [Generic Material].COST_CENTER, [Generic Material].TASK_CODE, [Generic Material].TASK_DESCR, [Generic Material].ACCOUNT, [Generic Material].SALES_ORDER_PRIME, [Generic Material].[DATE ENTERED], [Generic Material].[DATE APPROVED], [Generic Material].[DATE STARTED], [Generic Material].[DATE FAILED], [Generic Material].DETAIL_QTY, [Generic Material].SumOfEXP_TYPE_AMOUNT, [Generic Material].CLAIM_JOB_NAME
  2. FROM [Generic Material]
  3. WHERE ((([Generic Material].[CLAIM DATE]) Between [Forms]![Form1]![cmbcd] And [Forms]![Form1]![cmbcde]) AND (([Generic Material].[SHIP DATE]) Between [Forms]![Form1]![cmbsd] And [Forms]![Form1]![cmbed]) AND ((InStr([Forms]![Form1]![txtvoy2].[Text],Mid([ORDERED_MODEL],4,3)))>0 Or (InStr([Forms]![Form1]![txtvoy2].[Text],Mid([ORDERED_MODEL],4,3))) Is Null) AND ((InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code]))>0 Or (InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code])) Is Null) AND (([Generic Material].PART_PART_NBR) Like '*' & [Forms]![Form1]![cmbpn] & '*') AND (([Generic Material].TASK_CODE) Like '*' & [Forms]![Form1]![cmbtc] & '*') AND (([Generic Material].TASK_DESCR) Like '*' & [Forms]![Form1]![cmbtd] & '*') AND (([Generic Material].[DATE STARTED]) Between [Forms]![Form1]![cmcsd1] And [Forms]![Form1]![cmcsd2]) AND (([Generic Material].[DATE FAILED]) Between [Forms]![Form1]![cmcfd1] And [Forms]![Form1]![cmbfd2]));
  4.  
heads up, VB is very new to me, as is SQL. I can program in other languages, C, Fortran, and my one true love MatLab. So i understand Syntax, just not this VB/SQL Beast that is Access.
Feb 6 '07 #7

Rabbit
Expert Mod 10K+
P: 12,315
Your Where statement has become very confused. I am also assuming that all the places where you refer to dates that are on your form are also being stored as a date rather than a string because this will cause problems.

Expand|Select|Wrap|Line Numbers
  1. (
  2. (([Generic Material].[CLAIM DATE]) Between [Forms]![Form1]![cmbcd] And [Forms]![Form1]![cmbcde]) 
  3. AND 
  4. (([Generic Material].[SHIP DATE]) Between [Forms]![Form1]![cmbsd] And [Forms]![Form1]![cmbed]) 
  5. AND 
  6. ((InStr([Forms]![Form1]![txtvoy2].[Text],Mid([ORDERED_MODEL],4,3)))>0 
  7. Or 
  8. (InStr([Forms]![Form1]![txtvoy2].[Text],Mid([ORDERED_MODEL],4,3))) Is Null
  9. AND 
  10. (
  11. (InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code]))>0 
  12. Or 
  13. (InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code])) 
  14. Is Null
  15. AND 
  16. (
  17. ([Generic Material].PART_PART_NBR) Like '*' & [Forms]![Form1]![cmbpn] & '*'
  18. AND 
  19. (
  20. ([Generic Material].TASK_CODE) Like '*' & [Forms]![Form1]![cmbtc] & '*'
  21. AND 
  22. (
  23. ([Generic Material].TASK_DESCR) Like '*' & [Forms]![Form1]![cmbtd] & '*'
  24. AND 
  25. (
  26. ([Generic Material].[DATE STARTED]) Between [Forms]![Form1]![cmcsd1] And [Forms]![Form1]![cmcsd2]
  27. AND 
  28. (
  29. ([Generic Material].[DATE FAILED]) Between [Forms]![Form1]![cmcfd1] And [Forms]![Form1]![cmbfd2])
  30. );
  31.  
Here the where statement is looking for 1) Claim Date between cmbcd and cmbcde in addition to 2) Ship Date between cmbsd and cmded in addition to 3) The InStr function evaluates to larger than 0. Or 4) The InStr evaluates to null. What this means is that all records where 1, 2, and 3 are true at the same time are included in addition to all the records where 4 is true. This sounds wrong to me but it may be what you want.

Statement A:
Expand|Select|Wrap|Line Numbers
  1. (
  2. (([Generic Material].[CLAIM DATE]) Between [Forms]![Form1]![cmbcd] And [Forms]![Form1]![cmbcde]) 
  3. AND 
  4. (([Generic Material].[SHIP DATE]) Between [Forms]![Form1]![cmbsd] And [Forms]![Form1]![cmbed]) 
  5. AND 
  6. ((InStr([Forms]![Form1]![txtvoy2].[Text],Mid([ORDERED_MODEL],4,3)))>0 
  7. Or 
  8. (InStr([Forms]![Form1]![txtvoy2].[Text],Mid([ORDERED_MODEL],4,3))) Is Null
  9. )
  10.  
This next part will return true is either instr evaluates to >0 or is null. Meaning that this will evaluate to true if txtsee is within prod_code or either txtsee or prod_code is null.

Statement B:
Expand|Select|Wrap|Line Numbers
  1. (
  2. (InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code]))>0 
  3. Or 
  4. (InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code])) 
  5. Is Null
  6. )
  7.  
The next few statements look for substrings within a field of the table.

Statement C:
Expand|Select|Wrap|Line Numbers
  1. (
  2. ([Generic Material].PART_PART_NBR) Like '*' & [Forms]![Form1]![cmbpn] & '*'
  3.  
Statement D:
Expand|Select|Wrap|Line Numbers
  1. (
  2. ([Generic Material].TASK_CODE) Like '*' & [Forms]![Form1]![cmbtc] & '*'
  3. )
  4.  
Statement E:
Expand|Select|Wrap|Line Numbers
  1. (
  2. ([Generic Material].TASK_DESCR) Like '*' & [Forms]![Form1]![cmbtd] & '*'
  3. )
  4.  
The next 2 statements check the dates.

Statement F:
Expand|Select|Wrap|Line Numbers
  1. (
  2. ([Generic Material].[DATE STARTED]) Between [Forms]![Form1]![cmcsd1] And [Forms]![Form1]![cmcsd2]
  3. )
  4.  
Statement G:
Expand|Select|Wrap|Line Numbers
  1. (
  2. ([Generic Material].[DATE FAILED]) Between [Forms]![Form1]![cmcfd1] And [Forms]![Form1]![cmbfd2])
  3. )
  4.  
In between each statement you use AND. So every statement must be true for the record to show up in your query results.
Feb 6 '07 #8

NeoPa
Expert Mod 15k+
P: 31,186
Another way to handle this is to build the SQL string within the form.
When a control is changed, the SQL is rebuilt to reflect the current values in the form's controls.
If you're interested there is a generic tutorial which discusses how to build up filters etc (Example Filtering on a Form.).
Another point to remember is the slightly different way the SQL treats date literals (Literal DateTimes and Their Delimiters (#).).

Otherwise, it looks like Rabbit has this one well covered :)
Feb 7 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.