change table reference in qrydef

[holgi123]

Hi there,

I'm working on a problem that I do not get solved and would appreciate some help. I posted in a previous thread but maybe I did not outline correctly enough.

I create a query which calculates commission rates for sales reps. Some are under a new plan and some are under an old plan, which means they get different commission rates for let's say 100% achievement. So I need to change a table reference in my db.CreateQueryDef.
I first thought it works by simply entering an If-Clause in the With db section, but I got an error "External..blablabla not found". So I created a recordset and than tried this:
===================
Dim db As Database
Dim qryNew As QueryDef
Dim strold As String
Dim strnew As String
Dim strnam As String
Dim strsql As String
Dim rstmaster As DAO.Recordset

Set db = CurrentDb
Set rstmaster = CurrentDb.OpenRecordset("MasterTable")
strnam = "SELECT......."
strnew = "SELECT......."
strold = "SELECT......."
====================
strnam, strnew and strold are similar with one exception being that they refer in some calculations to different commission rate tables (i.e. strnam refers to tblNAMRates, strnew refers to tblNewHireRates.....etc.).
====================
If rstmaster![NewTyp] = "NAM" Then
strsql = strnam
ElseIf rstmaster![NewTyp] = "NEW" Then
strsql = strnew
ElseIf rstmaster![NewTyp] = "OLD" Then
strsql = strold
End If

With db
Set qryNew = db.CreateQueryDef("QueryMaster", strsql)
End With
====================

Now, when I run this code I do NOT get an error BUT all sales reps have the same commission rate applied, which means the if-clause is not executed.

I than replaced the if clause for the recordset and tried looping via
====================
rstmaster.MoveFirst
Do While Not rs.EOF
If rstmaster![NewTyp] = "NAM" Then
strsql = strnam
ElseIf rstmaster![NewTyp] = "NEW" Then
strsql = strnew
ElseIf rstmaster![NewTyp] = "OLD" Then
strsql = strold
End If
rstmaster.MoveNext
Loop
====================

but the system run for ages and I had to stop it.

Could someone please help me solving this or may be show me how to solve the problem otherwise?

Thank You very much for any assistance

 

[PHV]

Replace this:
Do While Not rs.EOF
with this:
Do While Not rstmaster.EOF

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[holgi123]

Hi PHV,

thank you for your suggestion, but unfortunately this did not solve my problem. The code runs without errors but it still does not pick the different tables names.

May be it has to do with the fact that my SELECT statements in the CreateQueryDef - which are coded in the strnam, strnew or strold variable are not only SELECT statements but actually update queries. For example:

==============
strname = "SELECT [MasterTable].*,............into tblmaster from [MasterTable];"
==============
After the code runs, I than check and see if the tblmaster table has the correct commission rate picked but so far it hasn't.

Any ideas what I'm doing wrong?

Thank You again for your help.

Best regards