Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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?
0
sadoiro
Asked:
sadoiro
  • 2
  • 2
  • 2
3 Solutions
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now