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

find value of control defined by string

P: 62
Hi All.

I have a form with several text boxes for a user to enter part numbers and quantities: partnum1, partnum2, partnum3 / qty1, qty2, qty3 etc.

I have a loop set up in VBA which goes through each part number and runs a query based on the values in the text boxes. The problem is if one of the text boxes is blank, I want to perform a different query. I have an If statement set up which can handle everything except the changing quantity.

Here's my code:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub buttonUpdate417A7360ModuleFilter_Click()
  2.  
  3.    Dim row As Variant
  4.    Dim frm_string As String 'this is the form where the control exists
  5.    Dim check_string As String 'this is the check box indicating that the filter is to be used, it iterates
  6.    Dim control_string As String 'this is the primary filter source (what you're looking for), it iterates
  7.    Dim qty_string As String 'this is the quantity of the primary filter source, it iterates
  8.    Dim qry_string As String 'this is the name of the query to be used, it iterates
  9. Dim source_table As String ' this is the modpart table which will be the basis for the search, needs .PartNum, .Quantity fields
  10.    Dim qty_path As Variant ' this points to the qty field, it iterates
  11.  
  12.  
  13.    frm_string = "frm417A7360ModuleSearch"
  14.    source_table = "tbl417A7360ModulesPart"
  15.    Set DB = CurrentDb()
  16.  
  17.    row = 1
  18.    Do While row < 5
  19.     check_string = "check" & row
  20.     control_string = "comboPartNum" & row
  21.     qty_string = "textQTY" & row
  22.     qry_string = "qryPart" & row
  23.     qty_path = "Me.textQTY" & row & ".value"
  24.  
  25.     Set Q = DB.QueryDefs(qry_string)
  26.     'If Me.textQTY1.Value <> "" Then
  27.     'If the preceeding line is used instead, the code works fine, however I want the textQTY# to iterate
  28.     If qty_path <> "" Then
  29.         Q.SQL = "SELECT *" & _
  30.         " FROM tbl417A7360ModulesPart" & _
  31. " WHERE (((" & source_table & ".PartNum)=[Forms]![" & frm_string & "]![" & control_string & "])" & _
  32.         " AND ((" & source_table & ".Quantity)=[Forms]![" & frm_string & "]![" & qty_string & "]));"
  33.     Else
  34.         Q.SQL = "SELECT *" & _
  35.         " FROM tbl417A7360ModulesPart" & _
  36. " WHERE (((" & source_table & ".PartNum)=[Forms]![" & frm_string & "]![" & control_string & "]));"
  37.     End If
  38.  
  39.     row = row + 1
  40.    Loop
  41.  
  42.  
  43. End Sub 
Any help would be greatly appreciated. Thanks!
Oct 5 '07 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Nick,

This isn't really a proper question (How to Ask a Question). It gives a very general description of what you're trying to do then asks a stranger to debug a large chunk of code for you.
That is not what we're here for.
We're happy to help you but that's all we do - help.

Having your code posted is good, but you need to explain what you're doing and where it's going wrong. Basically, you do the work and we help by pointing you in the right direction.
It's less daunting than it sounds as we are here, but not as a replacement for your efforts - rather as a support.
Oct 5 '07 #2

P: 62
Nick,

This isn't really a proper question (How to Ask a Question). It gives a very general description of what you're trying to do then asks a stranger to debug a large chunk of code for you.
That is not what we're here for.
We're happy to help you but that's all we do - help.

Having your code posted is good, but you need to explain what you're doing and where it's going wrong. Basically, you do the work and we help by pointing you in the right direction.
It's less daunting than it sounds as we are here, but not as a replacement for your efforts - rather as a support.

Very sorry for my previous post. I didn't mean to imply that I wanted help debugging a bunch of code. I put the whole code in there to hopefully show what I was trying to do. In the middle is a commented section saying that the above line is what I'm trying to do, only with an incrementing value instead of a constant one. I offer my most sincere apologies.

Please let me try to clarify my question.

How can you find the value stored in a form location specified by a string?

I have the following objects:
A text box in a form: [Forms]![frmName]![X1]
A variant called "iteration" which has a value of 1.
A string called "string_X" which has as a value "[Forms]![frmName]![X" & iteration & "]"
The complete "string_X" = "[Forms]![frmName]![X1]"

An empty text box called "Field"

How can I set Me.Field.Value equal to the value contained by the text box specified by "string_X" ?

Again, sorry for my poor post earlier. Hopefully this is more in line with the spirit of this awesome website.

Thanks!
Oct 5 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
No problem Nick.
Perhaps I should have taken more time to read the whole question. Unfortunately the [ CODE=VB ] format uses dim grey as a comment colour & I can barely see it - especially when skimming.

I'll give it another look and see what I can come up with.
Oct 5 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
Try this or an equivalent using your variables (the code for them and their contents may need to be changed slightly to match this format) :
Expand|Select|Wrap|Line Numbers
  1. If Me.Controls("textQTY" & row).Value <> "" Then
Oct 5 '07 #5

P: 62
Try this or an equivalent using your variables (the code for them and their contents may need to be changed slightly to match this format) :
Expand|Select|Wrap|Line Numbers
  1. If Me.Controls("textQTY" & row).Value <> "" Then

worked like a charm! Thanks!
Oct 7 '07 #6

NeoPa
Expert Mod 15k+
P: 31,494
No problem Nick :)
I'm glad it worked for you.
Oct 7 '07 #7

Post your reply

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