Update Value of Variable used in another string variable

[Codexx]

Hi All

This is a very basic question but I cant seem to get around it. I have a query that I am using multiple times so I have saved that query in a string variable. Now that query is using ID in the where clause which I need to update dynamically so I made an Int variable and concatenated that in my string variable. Problem I guess is the preceding order of statements:

I define the string variable (which is the query)

I define the ID variable (integer)

Execute the string variable as query

Now the string query does not update the ID int variable after it has been defined which is problematic as I cant re-use it without redefining the string query again, Only work around I can think of is create a func which updates the ID int in the string query and returns the updated string variable.

Any Ideas? Thanks!

Sub QueryDoesntUpdateWhereClause()

dim str as string
dim i as integar

Dim RS as Recordset

i = 5

str = "Select * from Customers where Customer.ID = " & i

set RS=Currentdb.OpenRecordSet(str)

'Do Something with the RecordSet

set RS = Nothing

i = 10

set RS=Currentdb.OpenRecordSet(str) ' This will still show recordset based on i=5!!!

'Do Something

set RS = Nothing

End Sub

 

[Joe4]

Welcome to the board!

Yes, since str is a String, once you have defined it, it stores the value of the string (you cannot retain variables like that).
What exactly are you trying to do and why? I wonder if a loop is what you are looking for.
Even so, you would need to re-define str each time.

 

[Codexx]

Welcome to the board!

Yes, since str is a String, once you have defined it, it stores the value of the string (you cannot retain variables like that).
What exactly are you trying to do and why? I wonder if a loop is what you are looking for.
Even so, you would need to re-define str each time.

Hi Joe

Appreciate the warm welcome

Ill try to explain but please do ask if I am unclear about anything:

I have a series of combo boxes in a row on a form (5-6) now the boxes depend on each other meaning if I set value for the first box the list of all boxes forward get limited to options relating to the selection of the first box. This is the forward update function I have in my combo boxes, and I also have backward update meaning if box 3 value is selected all boxes infront of it get limited to the selection (the forward update) plus all boxes prior to this box also get their values set based on the selection of cbo box 3.

All Combo boxes get values from quering a table, Cbo box 1 table is joined to box 2's table in a one to many relation and so forth.

Now I have managed to create 2 standard queries, one for forward update and one for backward update. It takes parameters such as Source table, Update table and ID (for the where clause). Next I am using an array of controls to loop through the row and set row sources of the boxes using the string sql query after updating it for the parameters. Needless to say there are 2 loops one for the forward update and one for the backward update.

What annoys me is that I have to write the SQL query to the string variable each time for using an update; for example in the forward loop after I update the parameter variables to be used in the query, I have to write the whole sql query to the string variable. Then repeat this writing query in the backward update loop.

Now although this works fine but the sql query in the editor just looked untidy to me in middle of my code. So for now I have saved the forward and backward query in a public function and instead of writing the whole query in my loop I call the function giving the prompts of whether it is a forward or backward query (flag) and the parameter values and it returns the updated sql string (this looks neater).

But since I am new at programming I wanted to reach out and see if there is a better method of updating the variables in a string sql query which will be used in a loop which does not involve me writing the string sql in my code editor after updating the parameter variables.

Hopefully this explains what my intentions are, but if not please feel free to ask me anything! Also I hope I am not violating any forum rules in this post or question but if I am then I would appreciate if you can set me the right way.

Thank you for helping with this!

 

[Joe4]

I have to admit, that is a bit confusing.

But if you queries are mostly the same syntax, save for the criteria values, I would probably recommend having a User Defined Function which builds the SQL string (and takes in the Criteria as parameters). So each time you want to use it, you just call the Function, passing in your parameters, and getting the SQL string returned (which you can then apply).

 

[Codexx]

Thank you Joe for reading all that and pointing me in the right direction

Also on something totally unrelated, would you know good material on separating my forms' UI and databases into separate files and how to connect between the files using vba?

Thanks

 

[Joe4]

Also on something totally unrelated, would you know good material on separating my forms' UI and databases into separate files and how to connect between the files using <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>?

Do you mean like splitting the database?
If so, see this here: https://support.office.com/en-us/ar...database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc