Solved

Need help editing SQL query criteria in VB

Posted on 2006-06-22
4
245 Views
Last Modified: 2012-06-21
I'm building a database for some language translation teams to track their requests and see how much time they're spending on them. On the main form are some buttons for each team to go to their respective home form. I would like to set it up so that when the Team's home form opens, it adds the criteria of that specific language before running the queries, then displays only their records.

For example, each Team home form has a list box with all the members on that team (it's a list box so others can double-click a name and it pulls up a form with that member's contact information). I would like to have it so that when someone clicks the Spanish Team button on the main form, it pulls up frmSpanishTeamHome and displays only the Spanish team members. I realize that I could do this with separate queries for each team, but that would add up to a LOT of queries.

So I have qryMembers that pulls all the records in tblMembers, and I would like the form VB to change the criteria of qryMembers based on which Team button was clicked on.

I've hunted around for a solution and found a couple that looked promising, but there was always one or two little things that made them not work. For example, I tried setting up the code so it would pull the SQL from the query into a variable and then just add on the WHERE clause, but the query always saved with a ; at the end, making any additions after the variable error out.

So if anyone has any ideas, I would greatly appreciate it!
0
Comment
Question by:jpbay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 17

Expert Comment

by:Arji
ID: 16981664
If you have a foregin key value for nationality of team members that should be faiirly simple:

team mmber table example

TeamMemberID  [PK]
NationalityID        [FK

When you open the Spanish Team form your can send the form a 'message' using the OpenArgs parameter:

DoCmd.OpenForm "frmSpanishTeamHome" ,,,,,"Spanish"      '<---OpenArgs parameter

Now in the On Open event of frmSpanishTeamHome:

if Not IsNuul(Me.OpenArgs) then

   Me.YourMemberList.RowSource  = "select * from qryMembers where Nationality='" & Me.OpenArgs & "'"

end if

In order to do this you MUST somehow identify each member's natianality or language and use that as the foreign key to build your lists.  You may have to add a Natianiltiy  or Language field in your tblMembers and add it to your qryMembers.  
0
 

Author Comment

by:jpbay
ID: 16985294
I tried this (and tried fiddling with it some to get it just right), but then it made the entire list box blank - no names at all. Any ideas?

I do have a TeamID field in the tblMembers that hooks up to a separate table with the actual language names, do I need to put the ID number or the name in the OpenArgs parameter? Should I change it in the table to be the name instead of a number?

I found a way to make it work by just having the rowsource for the list box be the SQL that you gave me (minus the OpenArgs piece), but I would like to be able to have this form automated so that it's just one form and what is displayed changes based on which language buttons are clicked on the main form. If that's too complicated (shouldn't be, once I learn how to do the code right), I can have different forms for each language.
0
 
LVL 17

Accepted Solution

by:
Arji earned 400 total points
ID: 16985838
Numbers are ideal.   Numbers, especially indexed, are much faster than text values for lookup purposes.  Not knowing the actual field names:

DoCmd.OpenForm "frmSpanishTeamHome" ,,,,,1      '<---OpenArgs parameter - 1 is the example PK (TeamID) for "Spanish"

Me.YourMemberList.RowSource  = "select * from qryMembers where TeamID =" & Me.OpenArgs

'Then you might want some code to change the language by creating a function that changes all the label's captions to the appropriate language:

ChangeFormLabels(1)    '<----you'll have to create this function.  I  would send the language ID to the function so it knows what language to load.

However, depending on how many languages you are supporting, it will be a fairly lengthly(not necessarily complex) function.
This is exactly one of the best reasons for OpenArgs.  It allows you to use the same form for different uses.  By detecting the LanguageID you can change the form to accomodate that language.
0
 

Author Comment

by:jpbay
ID: 16987598
Thanks so much, Arji! It works perfectly. :-) I'll play around with OpenArgs and see what I can make it do for me.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
vb.net dbnull syntax 1 59
MS ACCESS VBA FORMATTING 9 64
MS Access query 16 61
Outlook Automation in Access Using "Find" 2 59
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question