Pass Complex integer parameter by Form To Query

I have a form that allows the user to transfer equipment from one storage site to another

Opening the form reveals the current site where the equipment is stored and the SiteID is passed to a Global variable G_SiteID

A combo box on the newly opened transfer form, “frmTransactor” uses that SiteID in an unbound text box "txtparam" to reveal the allowable sites that can accept accept the equipment. I always use a tect box on the form because I've had such bad luck maintaining stability of operation with variables in a QueryDef.

Select Case G_SiteID
    Case 1
    Forms!frmTransactor!txtParam = 12 & " " & "Or" & " " & 99
    Case 2
    Forms!frmTransactor!txtParam = 1 & " " & "Or" & " " & 99
    Case 12
    Forms!frmTransactor!txtParam = 51
End Select


PARAMETERS txtParam Long;
SELECT tblSite.SiteID, tblSite.SiteName
FROM tblSite
WHERE (((tblSite.SiteID)=[Forms]![frmTransactor]![txtParam]));

However the combo box always come up blank on execution. What am I doing wrong?
sadoiroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
make it 2 Textboxees:


Select Case G_SiteID
    Case 1
    Forms!frmTransactor!txtParam1 = 12
    Forms!frmTransactor!txtParam2 = 99
    Case 2
    Forms!frmTransactor!txtParam1 = 1
    Forms!frmTransactor!txtParam2 = 99
    Case 12
    Forms!frmTransactor!txtParam1 = 51
    Forms!frmTransactor!txtParam2 = 51
End Select


PARAMETERS txtParam Long;
SELECT tblSite.SiteID, tblSite.SiteName
FROM tblSite
WHERE (((tblSite.SiteID)=[Forms]![frmTransactor]![txtParam1]))
OR (((tblSite.SiteID)=[Forms]![frmTransactor]![txtParam2]));
0
GRayLCommented:
In reality, this:

Forms!frmTransactor!txtParam = 12 & " " & "Or" & " " & 99

is this:

Forms!frmTransactor!txtParam = "12 Or 99"

Any time you concatinate a string to a number, the result is always a string.
0
GRayLCommented:
You talk about a combobox and a textbox.  I see reference to your textbox in the SQL string, but nothing about the combo box.  How do you assign the golbal variable?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Leigh PurvisDatabase DeveloperCommented:
Angel's suggestion is correctly pointing to where you're having trouble.
Splitting the criteria up into distinct OR's will make it work.
You'll probably want to dump your
PARAMETERS txtParam Long;
line too.  No point in it.

You _could_ go a more convoluted route to get it in one - but you could suffer in performance eventually (i.e. lose indexing advantages by using functions).
But if you started to have more criteria - then it could be a help.
Something like <air code>

Select Case G_SiteID
    Case 1
    Forms!frmTransactor!txtParam = ",12,99,"
    Case 2
    Forms!frmTransactor!txtParam = ",1,99,"
    Case 12
    Forms!frmTransactor!txtParam = ",51,"
End Select

SELECT tblSite.SiteID, tblSite.SiteName
FROM tblSite
WHERE Instr([Forms]![frmTransactor]![txtParam], "," & SiteID & ",") >0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I wonder why I didn't get involved in the point split... without explanation...
as noted, the accepted answer WILL suffer from the problem that it will not be able to use an index on the field SiteID, due to the Instr function, while my suggestion will be able to do so.
0
Leigh PurvisDatabase DeveloperCommented:
Indeed - my first comment was that Angel's solution is right :-)

The example you gave had only two integers in the criteria.
Using two hidden controls and a separate OR certainly isn't a lot of extra work to put that in place.
If there are indeed going to be more in time - then (for the benefit of the PAQ if nothing else) it would be useful for you to comment.
Future readers may well use this for decision making.

(It's quite simple to re-open a question to alter a split - you just post a request and link to this question in 'Support' linked at the top right of this page)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.